[大数据]小白教程---JDBC

JDBC

java DataBase Connectivity

java连接数据库的技术

jdbc仅仅只是一套接口，使用jdbc控制数据库库需要导入对应的包

JDBC常用接口

DriverManager：用来获取Connection对象

Conection：获取此 接口的对象相当于连接上数据库

Statement：此接口的对象用来执行sql

ResultSet：此接口的对象用来存放查询到的结果集

第一个JDBC

导包：导入第三方包（mysql）数据库厂商提供的驱动包

将jar复制到项目中的lib目录中，鼠标右键点击该jar包，选择add as library

注册驱动

Class.forName("com.mysql.jdbc,Driver")

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-741QenXO-1630933232031)(C:\Users\LZ\AppData\Roaming\Typora\typora-user-images\image-20210827102555139.png)]

获取Connection对象

//url指访问 指定数据库的路径   格式：协议名://IP地址:端口号/数据库名
//username 连接数据库的用户名    password  连接数据库的密码
Connection conn=DriverManager.getConnection(url,username,password);

获取Statment对象

Statement stmt = conn..createStatement();

使用Statment对象执行sql语句

/*
    executeUpdate可以执行增删改
    返回值表示影响的行数
*/
int i = stmt.executeUpdate(sql);

练习

package com.qianfeng;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class day30 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java2108";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        Statement stmt = conn.createStatement();
/*
        String sql = "INSERT INTO teacher VALUES(null,'晓星沉',15772001482,'计算机')";
        int i = stmt.executeUpdate(sql);
        System.out.println(i);*/
        String sql1 = "UPDATE teacher SET tname='星星' WHERE tname='晓星沉'";
        int i1 = stmt.executeUpdate(sql1);
        System.out.println(i1);
    }
}

设置操作数据过程的字符集

通过在url中的内容达到设置的效果

jdvc:mysql://IP地址:端口号/数据库名?useUnicode=true&CharacterEncoding=utf8

查询单行数据

查询操作于增删改操作的唯一不同点就是 查询操作有结果集

jdbc使用ResultSet对象存放结果集中的所有数据

Statement对象执行dql的方法：ResultSet executeQuery();

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JruHYJK9-1630933232036)(C:\Users\LZ\AppData\Roaming\Typora\typora-user-images\image-20210827115917113.png)]

package com.qianfeng.day30;

import java.sql.*;

public class Demo02 {
    public static void main(String[] args) throws Exception {
        Teacher teacher = findone(8);
        System.out.println(teacher);
    }

    private static Teacher findone(int i) throws Exception {
        Teacher teacher=null;

        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/java2108?useUnicode=true&CharacterEncoding=utf8";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement stmt = conn.createStatement();
        String sql="SELECT * FROM teacher  WHERE id ="+i;
        ResultSet rs = stmt.executeQuery(sql);

        ResultSetMetaData metaData = rs.getMetaData();
        if (rs.next()){
            teacher=new Teacher();
            teacher.setId(rs.getInt(1));
            teacher.setName(rs.getString(2));
            teacher.setPhoneNum(rs.getString(3));
            teacher.setSubject(rs.getString("subject"));
        }
        return teacher;
    }


}
class Teacher{
    private Integer id;
    private String name;
    private String phoneNum;
    private String subject;

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", phoneNum='" + phoneNum + '\'' +
                ", subject='" + subject + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }
}

查询多行数据

如果查询的结果集中包含了多行记录

则可以解析为多个java对象

package com.qianfeng.day30;

import java.sql.*;
import java.util.ArrayList;

public class Demo02 {
    public static void main(String[] args) throws Exception {
        ArrayList<Teacher> list = findAll();
        System.out.println(list);
    }

    public static ArrayList<Teacher> findAll() throws ClassNotFoundException, SQLException {
        ArrayList<Teacher> list = new ArrayList<>();
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement stmt = conn.createStatement();
        String sql = "SELECT * FROM teacher";
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()){
            Teacher teacher = new Teacher();
            teacher.setId(rs.getInt(1));
            teacher.setName(rs.getString(2));
            teacher.setPhoneNum(rs.getString(3));
            teacher.setSubject(rs.getString(4));
            list.add(teacher);
        }
        return list;
    }

sql注入

用户可以通过欺骗数据库的方法

package com.qianfeng.day30;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Demo03 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        Singer s= new Singer();
        while(true) {
            System.out.println("请输入用户名：");
            String username = scanner.nextLine();
            System.out.println("请输入密码");
            String password = scanner.nextLine();
            s.setUsername(username);
            s.setPassword(password);
            boolean lg = login(s);
            if (lg) System.out.println("登录成功");
            else System.out.println("登录失败");
        }

    }

    private static boolean login(Singer s) throws Exception {
        String username=s.getUsername();
        String password =s.getPassword();

        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8";
        String usn = "root";
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url, usn, pwd);
        Statement stmt = conn.createStatement();
        String sql="SELECT * FROM Singer WHERE username = '"+username+"'"+"AND password = '"+password+"'";
         //可以通过给username赋值为      a' or '1'= '1
        //给password赋值为              a' or '1'= '1
        //通过or 绕过sql语句的判断 达到输入错误的用户名和错误的密码 却能登录成功
        //也就是达到sql注入的效果，也就是达到欺骗数据库的效果
        ResultSet rs = stmt.executeQuery(sql);

        if (rs.next())
            return true;
        return false;
    }
}

class Singer{
    private String username;
    private String password;

    @Override
    public String toString() {
        return "user{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

解决sql注入

sql注入的原因本质上就是因为通过凭借字符串来指定执行的sql语句

我们可以使用PreparedStatement来执行sql语句，避免凭借字符串，以杜绝sql注入

PreparedStatement

是Statement的子接口，额外扩展了预编译的方法

就是在获取该对象时就先把ssql语句交给他

如果sql语句中需要拼接指定的 遍历的值

则使用占位符?表示需要拼接

然后使用setXxx方法将?的值设置进去

预编译：在执行sql之前，先将sql语句进行编译

String sql = "SELECT * FROM singer WHERE username= ? AND password = ?";
PreparedStatement pstmt = conn.preparedStatement(sql);
//在执行sql语句之前需要给占位符赋值
pstmt.setString(1,username);//1代表第一个占位符，username代表给占位符赋予的值
pstmt.setString(2,password)
//执行sql语句
ResultSet rs = pstmt.executeQuery();

效果：避免了字符串拼接

预编译的本质：

在预编译的 时候，将sql语句编译成一个函数，如果有?则将?设计为该函数的形参

使用setXxx方法时，相当于指定实参

则此方法，从根本上解决了拼接字符串可能造成的sql注入问题

package com.qianfeng.day30;

import java.sql.*;
import java.util.Scanner;

public class Demo03 {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        Singer s= new Singer();
        while(true) {
            System.out.println("请输入用户名：");
            String username = scanner.nextLine();
            System.out.println("请输入密码");
            String password = scanner.nextLine();
            s.setUsername(username);
            s.setPassword(password);
            boolean lg = login2(s);
            if (lg) System.out.println("登录成功");
            else System.out.println("登录失败");
        }

    }

    private static boolean login2(Singer s) throws ClassNotFoundException, SQLException {
        String username = s.getUsername();
        String password = s.getPassword();

        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8";
        String uname="root";
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url, uname, pwd);

        String sql = "SELECT * FROM singer WHERE username=? AND password = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,username);
        pstmt.setString(2,password);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()){
            return true;
        }
        return false;

    }

实际开发中尽量都选择PrepareStatement

PreparedStatement的优点

没有凭借字符串，避免了sql注入

执行效率高。因为编译的函数是可以重用的

批处理

就是将多条sql语句缓存起来一次性执行玩，提高整体的执行效率

注意：mysql默认是关闭了批处理，所以高使用批处理需要先启动

开启方式：在url中添加一个参数

rewriteBatchedStatements=true

具体使用：

pstmt.addBatch();//“添加n次”,把任务交给pstmt 但不马上执行

pstmt.executeBatch;//一次性执行完所有任务

package com.qianfeng.day30;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Demo04 {
    public static void main(String[] args) throws Exception {
        addSinger();
        addSingerWithBatch();
    }

    private static void addSinger() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8";
        String uname  = "root";
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url,uname,pwd);
        String sql = "INSERT INTO user VALUES(null,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            pstmt.setString(1,"XXC"+i);
            pstmt.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("不使用批处理的时间："+(end-start));

    }

    private static void addSingerWithBatch() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true";
        String uname = "root";
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url, uname, pwd);
        String sql = "INSERT INTO user VALUES(null,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            pstmt.setString(1,"XX"+i);
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("使用批处理的时间:"+(end-start));
    }
}

jdbc的标准写法

异常需用try-catch处理

资源使用结束后应该关闭

package com.qianfeng.day30;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo05 {
    public static void main(String[] args) {
        deleteUser(1);
    }

    private static void deleteUser(int i) {
        /*
        将user表中username为1的数据删除
         */
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8";
            String uname = "root";
            String pwd = "123456";
            conn = DriverManager.getConnection(url, uname, pwd);
            String sql = "DELETE FROM user WHERE username = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,i);
            int result = pstmt.executeUpdate();
            System.out.println(result);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

JdbcUtiV1

在编写规范jdbc代码时，会出现很多重复代码

1、获取Connection对象的代码

2、关闭资源的代码

方案：创建一个工具类，将获取Connection对象以及关闭资源的代码封装到这个工具类中

package com.qianfeng.day30;

import java.sql.*;

public class JdbcUtilV1 {
    private static String url;
    private static String uname;
    private static String pwd;
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
            url = "jdbc:mysql://localhost:3306/java2108?userUnicode=true&characterEncoding=utf8";
            uname = "root";
            pwd = "123456";
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn() throws SQLException {
        Connection conn = DriverManager.getConnection(url, uname, pwd);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement ps,Connection connection){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试工具类

使用工具类完成删除操作

package com.qianfeng.day30;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo06 {
    public static void main(String[] args) {
        /*
        使用测试工具类
         */
        deleteUser(2);
    }

    private static void deleteUser(int i) {
        Connection  conn = null;
        PreparedStatement pstmt = null;
        try{
            conn = JdbcUtilV1.getConn();
            String sql = "DELETE FROM user WHERE username name = ?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,i);
            int a = pstmt.executeUpdate();
            System.out.println(a);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtilV1.close(null,pstmt,conn);
        }
    }
}

JdbcUtilV2

V1版本会设置4大参数的值

驱动类、url、username、password

是写在源码中的，所有如果需要修改会很不方便

解决方案：将者四大参数设置在知道你过的配置文件中

然后在工具类中读取配置文件的值

好处：不需要改源码，而是修改配置文件就可以指定四大参数的值

package com.qianfeng.day30;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JdbcUtilV2 {
    private static String url;
    private static String username;
    private static String  password;
    private static String driverClass;

    static{
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("JdbcUtilV2.properties"));
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            driverClass=properties.getProperty("driverClass");
            Class.forName(driverClass);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn() throws SQLException {
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement ps, Connection connection){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

配置文件

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8
username=root
password=123456

 } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    if (ps != null){
        try {
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    if (connection != null){
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

}

### 配置文件

```properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2108?useUnicode=true&characterEncoding=utf8
username=root
password=123456