jiangling500

JDBC 本质

官方定义的一套操作所有关系型数据库的规则，即接口，位于 java.sql 包下。各个数据库厂商去实现这套接口，提供数据库驱动 jar 包。我们可以使用这套接口(JDBC)编程，真正执行的代码是驱动 jar 包中的实现类。

下载 MySQL 驱动包

打开MySQL驱动包官网下载地址，操作系统选择平台无关的，即Platform Independent，然后下载zip格式的，解压缩后即可得到 MySQL 驱动 jar 包。

导入 jar 包到 idea 中

方法一

File-->Project Structure-->Modules-->Dependencies-->添加 MySQL 驱动 jar 包。

方法二

在 idea 项目中新建 libs 目录，然后复制 MySQL 驱动 jar 包，在 idea 中选择 libs 目录，直接粘贴，即可把 MySQL 驱动 jar 包复制到了 libs 目录中。在 idea 的 libs 目录中，右键 MySQL 驱动 jar 包，选择Add as Library，即可导入 MySQL 驱动 jar 包到项目中。

方法三

Maven：TODO
参考：https://www.liaoxuefeng.com/wiki/1252599548343744/1321748435828770
重点：scope里要填runtime，而不是compile

编程

查询

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD)) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT  id, grade, name, gender FROM students WHERE gender = ? AND grade = ?")) {
        ps.setObject(1, 1);
        ps.setObject(2, 3);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long id = rs.getLong("id");
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                boolean gender = rs.getBoolean("gender");
                System.out.println("id: " + id + ", grade: " + grade + ", name: " + name + ", gender: " + gender);
            }
        }
    }
}

注意：

• MySQL 的 JDBC URL 格式：jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2，如果连接的是本机 MySQL服务器，并且 MySQL 服务器默认端口为 3306，则 URL 可以简写为jdbc:mysql:///<db>
• DriverManager.getConnection()会自动扫描 classpath，找到所有的 JDBC 驱动，然后根据传入的 URL 自动选择一个合适的驱动
• Connection、PrepareStatement以及ResultSet都是需要关闭的资源，可使用 JDK 7 新语法 try(resource)
• 使用PrepareStatement，而不是Statement，可以有效防止 SQL 注入
• 调用PreparedStatement.setObject()设置占位符?的值时，索引从 1 开始
• JDBC 的查询的返回值总是ResultSet，即使是聚合查询
• ResultSet.next()用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行(注意，一开始获得ResultSet时当前行并不是第一行，而是查询列名)
• 如果结果集只可能有 0 或者 1 条记录时，可将ResultSet.next()方法放在if语句中，否则必须放在while语句中
• 从结果集读取列值时，推荐使用String类型的重载方法，而不是索引，如果要使用索引的话，索引也是从1开始的

插入

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD)) {
    try (PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO students (grade, name, gender, score) VALUES (?, ?, ?, ?)",
            Statement.RETURN_GENERATED_KEYS)) {
        ps.setObject(1, 1);
        ps.setObject(2, "Bob");
        ps.setObject(3, 1);
        ps.setObject(4, 98);
        int n = ps.executeUpdate();
        System.out.println("n = " + n);
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                long id = rs.getLong(1);
                System.out.println("id = " + id);
            }
        }
    }
}

注意：

• 执行插入操作使用的是PreparedStatement.executeUpdate()方法，返回值为成功插入的行数
• 要获取自增主键，不能先插入，再查询，因为两条 SQL 执行期间可能有别的程序也插入了同一个表
• 如果要获取自增主键，在创建PreparedStatement对象时，必须要传入常量RETURN_GENERATED_KEYS，表示 JDBC 驱动必须返回插入的自增主键
• 调用PreparedStatement.getGeneratedKeys()方法返回的ResultSet对象，如果一次插入多条记录，那么这个ResultSet对象就会有多行返回值。如果插入时有多列自增，那么ResultSet对象的每一行都会对应多个自增值

更新

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD)) {
    try (PreparedStatement ps = conn.prepareStatement("UPDATE students SET name = ? WHERE id = ?")) {
        ps.setObject(1, "Bob");
        ps.setObject(2, 1000);
        int n = ps.executeUpdate();
        System.out.println("n = " + n);
    }
}

注意：

• 执行更新操作也是使用的PreparedStatement.executeUpdate()方法，返回值为成功更新的行数

删除

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD)) {
    try (PreparedStatement ps = conn.prepareStatement("DELETE from students WHERE id = ?")) {
        ps.setObject(1, 1000);
        int n = ps.executeUpdate();
        System.out.println("n = " + n);
    }
}

注意：

• 执行删除操作也是使用的PreparedStatement.executeUpdate()方法，返回值为成功删除的行数

JDBC CRUD 小结

• 查询操作使用PreparedStatement.executeQuery()方法，返回类型为ResultSet的结果集
• 使用 JDBC 执行 INSERT、UPDATE 和 DELETE 都可以视为更新操作，更新操作使用 PreparedStatement.executeUpdate()方法，返回受影响的行数

事务

Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWD);
try {
    // 关闭自动提交
    conn.setAutoCommit(false);
    // 执行多条SQL语句
    // ...
    // 提交事务
    conn.commit();
} catch (SQLException e) {
    // 回滚事务
    conn.rollback();
} finally {
    // 恢复自动提交
    conn.setAutoCommit(true);
    conn.close();
}

注意：

• 默认情况下，我们获取到Connection连接后，总是处于自动提交模式，也就是每执行一条 SQL 都是作为事务自动执行的，因此只要关闭了Connection的自动提交，就可以在一个事务中执行多条语句了
• MySQL 的默认隔离级别是REPEATABLE READ，可使用Connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED)更改隔离级别为READ COMMITTED

批量执行

使用循环执行批量操作：

for (var params : paramList) {
    PreparedStatement ps = conn.preparedStatement("INSERT INTO student (name, name, grade, score) VALUES (?, ?, ?, ?)");
    ps.setString(1, params.get(0));
    ps.setBoolean(2, params.get(1));
    ps.setInt(3, params.get(2));
    ps.setInt(4, params.get(3));
    ps.executeUpdate();
}

使用 batch 执行批量操作：

try (PreparedStatement ps = conn.preparedStatement("INSERT INTO student (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
    // 对同一个 PreparedStatement 反复设置参数并调用 addBatch()
    for (Student s : students) {
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch();
    }
    // 执行 batch
    int[] ns = ps.executeBatch();
    for (int n : ns) {
        System.out.println(n + " inserted."); // batch 中每个 SQL 执行的结果数量
    }
}

两者的不同：

• 对于内容相同，参数不同的 SQL，通过一个循环来执行每个PreparedStatement性能很低，而 batch 操作有特别优化，速度远远快于循环执行每个 SQL
• batch 操作调用的是PreparedStatement.executeBatch()，返回类型为int[]，而不是PreparedStatement.executeUpdate()，返回类型为int

连接池

JDBC 连接池有一个标准的接口javax.sql.DataSource，要使用 JDBC 连接池，必须选择一个 JDBC 的是实现。我们以目前使用最广泛的 HikariCP 为例，首先要导入 HikariCP jar 包，可使用如下两种方式：

jar 包

TODO：jar包下载地址

Maven

TODO：参考https://www.liaoxuefeng.com/wiki/1252599548343744/1321748528103458
有了连接池后，在获取Connection时，就不再使用DriverManger.getConnection()了，而是使用DataSource.getConnection()：

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10
DataSource ds = new HikariDataSource(config);
try (Connection conn = ds.getConnection()) { // 在此获取连接
    // ...
} // 在此关闭连接

注意：

• 在try(resource){...}结束处调用conn.close()方法时，并不是真的关闭连接，而是将连接释放到连接池中，以便下次获取连接时能直接返回

MySQL 数据类型与 Java 数据类型对应关系

BIT, BOOL-->boolean
INTEGER-->int
BIGINT-->long
REAL-->float
FLOAT, DOUBLE-->double
CHAR, VARCHAR-->String
DECIMAL-->BigDecimal
DATE-->java.sql.Date, LocalDate
TIME-->java.sql.Time, LocalTime

参考

• https://www.liaoxuefeng.com/wiki/1252599548343744