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);

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

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

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);	

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();

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

Statement、PreparedStatement、CallablesStatement的联系与区别

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

Statement接口

方法	介绍
boolean execute(String sql)	参数：参数可以是任意的sql语句(DCL、TCL、DDL、DML、DQL) 返回值：如果返回的结果是一个结果集返回true，否则返回false(只要是DQL语句都返回true)
int executeUpdate(String sql)	参数：参数只能是DML语句或是无返回值的sql语句(例如DDL、DCL)，不支持TCL、DCL 返回值：返回此sql语句执行后影响的记录条数
ResultSet executeQuery(String sql)	参数：参数只能是DQL语句 返回值：返回由DQL查询结果封装的ResultSet对象
ResultSet getResultSet()	返回值：通过当前实例最后执行的sql返回的结果集，如果该sql不是DQL则返回null
void addBatch(String sql)	将给定的 SQL 命令添加到此 Statement 对象的当前命令列表中
int[] executeBatch()	将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组

PreparedStatement接口

方法	介绍
void clearParameters()	立即清除当前参数值(清空占位符被赋值的内容)
ResultSetMetaData getMetaData()	获取包含有关 ResultSet 对象列信息的 ResultSetMetaData实例
ParameterMetaData getMetaData()	获取此 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);

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);
}

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();
 }

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();
        }

    }

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();
        }



    }

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();
        }

    }

最好使用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)

模拟用户登录(使用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;
    }

运行结果如下，可以看出用户通过非法输入间接跳过登录验证，产生这种情况的根本原因是未对用户输入的字符串进行验证，就直接提交给数据库，导致查询结果出现偏差。那么我们该怎么预防呢？—> 使用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;
    }

运行结果如下，成功防止用户通过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)

我们向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();
        }
    }

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)