2022-08-19 学习笔记 day42-JDBC

JDBC

JDBC (Java Database Connectivity) 是JavaEE的一种规范,用于Java程序连接数据库的规范，本质就是一套接口，这套接口是由Java语言定制的，这套接口的实现类是由各种数据库厂家负责编写的(对于这些接口的实现类又被称为驱动)，我们程序员面向接口编程，降低程序之间的耦合度，提高程序的扩展力

1. 驱动与jar包

• 驱动：xxx与xxx进行通信的程序
• 在java中，驱动以jar包的形式存在，jar包里面存放了接口实现类的字节码文件
• jar包是java的一种文档格式，主要用于存放字节码文件，jar包类似于zip/7z等压缩文件
• jar包与zip区别：jar与zip唯一的区别是jar文件中包含了一个META-INF/MANIFEST.MF文件，这个文件试再jar生成的时候自动创建的

2. JDBC连接数据库

此处以连接Mysql数据库为例

2.1 下载驱动

官网下载地址

2.2 导入驱动jar包

为什么要导入jar包？ ——> 为了在程序运行期间能让类加载器找到jar包中存放的字节码文件。
我们该怎么做？ ——> 我们应该配置应用类加载器，而应用类加载器负责根据classpath环境变量下的路径来找类，所以我们需要配置ClassPath

2.2.1 配置ClassPath

如果使用的是IDE开发，可以忽略这一步骤
这里以我的mysql驱动为例
驱动路径：C:\Program Files\Java\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48-bin.jar

此电脑–>高级系统设置–>环境变量–>ClassPath–>编辑ClassPath环境变量
ClassPath=“.;C:\Program Files\Java\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48-bin.jar”

2.2.2 在IDE(idea)中导入驱动jar包

或者将jar包拖拽到项目下，右键—> Add as Library…

2.3 JDBC编程

2.3.1 注册驱动

1.注册驱动的第一种方式

Driver driver = new Drvier("com.mysql.jdbc.Driver");
DriverManager.registerDriver(driver);
1
2

2.注册驱动的第二种方式
由于在com.mysql.jdbc.Driver实现类在静态代码块中注册驱动，而静态代码块在类加载时刻执行，所以我们只需要调用Class类的静态方法forName()即可执行类加载动作，完成驱动的注册

Class.forName("com.mysql.jdbc.Driver");
1

2.3.2 获取连接

根据上文继续，获取数据库连接对象(传入url、用户名，密码参数)

String url = "jdbc:mysql://127.0.0.1:3306/jsoft?useSSL=false&serverTimezone=UTC&charecterEncoding=UTF-8;";
String user = "root";	// DBMS用户名
String password = "123";	// DBMS密码
Connection conn = DriverManager.getConnection(url,user,password);	
1
2
3
4

URL(统一资源定位符)

什么是URL

1. URL(同一资源定位符)，是网络中某个资源的绝对路径。
2. URL由协议、IP、端口、资源名、参数组成

上面代码中URL分析：

• jdbc:mysql(协议)、
• 127.0.0.1(环回地址代表本机IP)
• 3306(Mysql端口号)
• jsoft(要连接的数据库名)
• useSSL=false&serverTimezone=UTC&charecterEncoding=UTF-8(参数信息)

URL参数

• useSSL=false ----> useSSL=false显式禁用SSL，useSSL = true并为服务器证书验证提供信任库
• serverTimezone=UTC ----> 设置时区
• useUnicode=true ----> 是否使用Unicode字符集，如果参数characterEncoding有设置，本参数值必须设置为true
• charecterEncoding=utf8 ---->当useUnicode设置为true时，指定字符编码
• generateSimpleParameterMetadata=true 设置true防止使用ParameterMetaData时抛出异常(mysql驱动问题)，注意设置true之后只能得到varchar类型

2.3.3 获取数据库操作对象

根据上文继续，通过获取到的Connection实例获取数据库操作对象(以Statement为例)

Statement stmt = conn.createStatement();
1

Java中3个接口分别定义了对数据库的操作的不同的方式(Statement、PrepareStatement、CallablesStatement)

Statement、PreparedStatement、CallablesStatement的联系与区别

1. Statement —> 用来执行静态sql语句并返回他们所生成的结果集对象
2. PreparedStatement —> 预编译，可以使用此对象多次高效的执行该语句
3. CallablesStatement —> 用来执行sql存储过程

Statement接口

PreparedStatement接口

CallablesStatement接

待补充…

2.3.4 执行sql

根据上文继续，执行sql语句

// 执行DML语句
String sql1 = "INSERT INTO emp(empno,ename) VALUES(8888,'张三')";
int result = stmt.executeUpdate(sql1);
// 执行DQL语句
String sql2 = "SELECT empno,ename FROM emp";
ResultSet rt = stmt.executeQuery(sql2);
1
2
3
4
5
6

2.3.5 处理sql返回结果

根据上文继续，处理sql返回结果

// 处理DML语句返回结果
System.out.println("sql1执行了" + result + "条语句");
// 处理DQL语句返回结果
while(rt.next()) {
	int empno = rt.getInt("empno");
	String ename = rt.getString("ename");
	System.out.println(empno + "\t" + ename);
}
1
2
3
4
5
6
7
8

ResultSet接口

待补充…

ResultSetMetaData接口

简单来说就是ResultSetMetaData中存放了表的字段信息（字段名、字段的类型）

ParameterMetaData接口

可用于获取关于 PreparedStatement 对象中每个 参数标记 的类型和属性信息的对象。对于某些查询和驱动程序实现，由 ParameterMetaData 对象返回的数据在 PreparedStatement 执行前可能不可用。

Mysql中ParameterMegtaData一般只用于获取参数的个数
因为在默认情况下：因为Mysql驱动的原因，不支持得到参数元数据的类型。
MySQL驱动对参数元数据的数据类型支持不理想，需要如下配置才能得到参数的MySQL数据类型，而且只能得到VARCHAR类型，如果不配置这个参数则会出现异常。
如何配置? ----> 在url参数后面加上generateSimpleParameterMetadata=true

参考文章

2.3.6 释放资源

注意资源的释放要写在finally语句块内，确保资源一定能被关闭，此处为了使JDBC编程执行顺序更加清晰明了，并未对异常进行处理以及使用finally语句块确保流的关闭

if (rt != null) {
          rt.close();
      }
if (stmt != null) {
       stmt.close();
   }
if (conn != null) {
     conn.close();
 }
1
2
3
4
5
6
7
8
9

2.3.7 完整的JDBC编程代码

注意：如果使用cmd窗口编程，要配置classpath，并且在获取数据库连接对象时，参数设置要有useSSL=false，否则在运行过程中会抛出com.mysql.jdbc.exceptions.jdbc4.CommunicationsException (通信异常)

设置useSSL=false后，可以与Mysql正常通信了

1. 常规编程

@Test
    public void test1() throws ClassNotFoundException, SQLException {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost/jsoft";
        String user = "root";
        String password = "334";
        String dql = "SELECT empno,ename FROM emp;";

        // 1.注册驱动
        Class.forName(driver);
        // 2.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        // 3.获取数据库操作对象
        Statement stmt = conn.createStatement();
        // 4.执行sql
        ResultSet rt = stmt.executeQuery(dql);
        // 5.处理查询结果集
        while (rt.next()) {
            int empno = rt.getInt("empno");
            String ename = rt.getString("ename");
            System.out.println(empno + "\t" + ename);
        }

        // 6.关闭资源，要使用finally语句块确保资源一定关闭
        if (rt != null) {
            rt.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2. Properties+IO绑定属性配置文件

 @Test
    public void test2() throws IOException {

        // 使用Properties+IO绑定属性配置文件
        Properties pros = new Properties();
        // 注意getResourceAsStream中使用的是类路径，属性配置文件要写到src下才有效
        pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc_info.properties"));
        String driver = pros.getProperty("driver");
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");

        // 1.注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        // 2/3/6. 获取连接/获取数据库操作对象/try...with...resourse自动释放资源
        try(Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement ps = conn.prepareStatement("");) {

            // 4/5 执行sql，并处理查询结果集
            String sql = "SELECT empno,ename FROM emp;";
            try(ResultSet rt = ps.executeQuery(sql);) {
                while(rt.next()) {
                    Integer empno = rt.getInt("empno");
                    String ename = rt.getString("ename");
                    System.out.println(empno + "\t" + ename);
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }



    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

3. ResourceBundle绑定属性配置文件

 @Test
    public void test3() {
        // 使用资源绑定器，此处只能是类路径,不要有后缀名
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc_info");

        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        // 1.注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        // 2/3/6. 获取连接/获取数据库操作对象/try...with...resourse自动释放资源
        try(Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement ps = conn.prepareStatement("");) {

            // 4/5 执行sql，并处理查询结果集
            String sql = "SELECT empno,ename FROM emp;";
            try(ResultSet rt = ps.executeQuery(sql);) {
                while(rt.next()) {
                    Integer empno = rt.getInt("empno");
                    String ename = rt.getString("ename");
                    System.out.println(empno + "\t" + ename);
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

最好使用Properties+IO 或者 ResourceBundle绑定属性配置文件。(符合OCP原则)

4. 使用PreparedStatement防止sql注入

使用Statement操作数据库，通过拼接的方式拼接成的sql在执行时存在安全问题，用户可以通过输入非法字符串间接修改执行的sql语句，这种情况我们称作为sql注入

具体案例
这是测试用到的user表

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | admin    | 123      |
|  2 | user1    | 111      |
|  3 | user2    | 222      |
+----+----------+----------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

模拟用户登录(使用Statement)

@Test
public void test01() {
	// 用户通过输入非法字符跳过验证
    login1("admin","113' or '1=1");
}

public boolean login1(String username, String passowrd) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM user WHERE username='");
        sql.append(username);
        sql.append("'");
        sql.append(" and ");
        sql.append("password='");
        sql.append(passowrd);
        sql.append("'");
        System.out.println("执行的sql语句：" + sql);
        try (Connection conn = DATA_SOURCE.getConnection();
             Statement stmt = conn.createStatement()) {

            try (ResultSet rt = stmt.executeQuery(sql.toString())) {
                if (rt.next() && rt.getInt(1) > 0) {
                    System.out.println("登录成功！");
                    return true;
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        System.out.println("登录失败！");
        return false;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

运行结果如下，可以看出用户通过非法输入间接跳过登录验证，产生这种情况的根本原因是未对用户输入的字符串进行验证，就直接提交给数据库，导致查询结果出现偏差。那么我们该怎么预防呢？—> 使用PreparedStament 通过给占位符赋值完成对字符串合法性的验证并处理

执行的sql语句：SELECT count(*) FROM user WHERE username=‘admin’ and password=‘113’ or ‘1=1’
登录成功！

模拟用户登录(使用PreparedStatement)

@Test
public void test01() {
    login2("admin","113' or '1=1");
}

public boolean login2(String username, String password) {
        String sql = "SELECT count(*) FROM user WHERE username=? AND password=?;";
        try (Connection conn = DATA_SOURCE.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1,username);
            ps.setString(2,password);
            System.out.println("执行的sql语句：" + ps);
             try (ResultSet rt = ps.executeQuery()) {
                if (rt.next() && rt.getInt(1) > 0) {
                    System.out.println("登录成功！");
                    return true;
                }
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        System.out.println("登录失败！");
        return false;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

运行结果如下，成功防止用户通过sql注入的方式跳过用户验证

执行的sql语句：com.mysql.jdbc.JDBC4PreparedStatement@1de5f259: SELECT count(*) FROM user WHERE username=‘admin’ AND password=‘113\’ or \‘1=1’;
登录失败！

具体实现原理：通过给占位符赋值，可以将用户输入字符串中非法字符转义

5. 使用PreparedStatement完成sql批处理

为什么要批处理？ —> 为了执行效率。
如果有多条sql语句，每次使用executeUpdate()或executeQuery()等执行时，都需要将sql语句推送到DBMS中 (100条sql就需要推送100次)，如果有多条sql语句需要执行，通过这种方式无疑会浪费大量资源。所以在有多条sql执行时，建议将这些sql一起推送给DBMS，那么具体是如何实现的呢？请看下面代码

user表数据

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | admin    | 123      |
+----+----------+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

我们向user表中插入10个密码相同的用户(user1~user10)

 @Test
    public void test01() {
        String sql = "INSERT INTO user(username,password) values(?,?);";
        try (Connection conn = DATA_SOURCE.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            for (int i = 0; i < 10; i++) {
                ps.setString(1,"user" + (i+1));
                ps.setString(2,"123456");
                // 将需要执行的sql装起来
                ps.addBatch();
            }
            // 将装起来的sql推送给DBMS
            ps.executeBatch();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

java代码执行后的user表

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | admin    | 123      |
|  5 | user1    | 123456   |
|  6 | user2    | 123456   |
|  7 | user3    | 123456   |
|  8 | user4    | 123456   |
|  9 | user5    | 123456   |
| 10 | user6    | 123456   |
| 11 | user7    | 123456   |
| 12 | user8    | 123456   |
| 13 | user9    | 123456   |
| 14 | user10   | 123456   |
+----+----------+----------+
11 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18