JDBC总结

1、JDBC简介

JDBC：Java DataBase Connectivity

Java通过JDBC接口来访问数据库，而在底层JDBC接口通过JDBC驱动真正访问数据库。

Java标准库自带的JDBC接口就是定义一组接口，具体的JDBC驱动则是实现了这些接口的类。

以MySQL为例，MySQL的JDBC驱动就是jar包。

我们的代码只需要引用Java标准库java.sql包下边的相关接口，再间接通过MySQL驱动的jar包通过网络访问MySQL服务器，所有网络通讯细节都被封装到JDBC驱动中。因此，Java程序本身只需要引入MySQL驱动的jar包（mysql-xxx.jar）就可以正常访问MySQL服务器：

2、JDBC操作（查询）

JDBC是一套接口程序，存放于java.sql中，接口需要具体的实现类才能实例化。

我们使用哪个数据库，就去使用该数据库的实现类，这个实现类所在的jar包称为JDBC驱动。

以MySQL为例，我们添加它的JDBC驱动，需要引入一个第三方jar包，直接添加Maven依赖即可：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

这里<scope>是runtime，因为编译期并不需要这个jar包，运行期才需要。

 

2.1、案例数据

为方便后续操作，引入一批数据：

-- 创建数据库learjdbc:
DROP DATABASE IF EXISTS learnjdbc;
CREATE DATABASE learnjdbc;

-- 创建登录用户learn/口令learnpassword
CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword';
GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 创建表students:
USE learnjdbc;
CREATE TABLE students (
  id BIGINT AUTO_INCREMENT NOT NULL,
  name VARCHAR(50) NOT NULL,
  gender TINYINT(1) NOT NULL,
  grade INT NOT NULL,
  score INT NOT NULL,
  PRIMARY KEY(id)
) Engine=INNODB DEFAULT CHARSET=UTF8;

-- 插入初始数据:
INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);
INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96);
INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99);
INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86);
INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79);
INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85);
INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90);
INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91);
INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);

 

 

 

 

2.2、JDBC连接

Java程序到数据库的连接称为JDBC连接（TCP连接）。一个连接需要URL+用户名+口令。

2.2.1、URL

URL由数据库厂商指定格式，MySQL的格式为：

 jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2

 例子

• 主机名：本机localhost；

• 端口：3306（标准db端口）

• 数据库：learnjdbc

jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8

后边还有两个参数：

• useSSL：是否使用SSL加密；

• characterEncoding：字符编码。

2.2.2、Java数据库连接

获取连接

Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

关闭连接

conn.close();

在获取连接后，关闭连接前，通过conn进行访问数据库。

try语句块

JDBC连接是一种昂贵的资源，所以使用之后要及时释放。使用try(resource)来自动释放JDBC连接是一个好方法。

使用try语句块就不需要在最后关闭连接。

例子

// JDBC连接的URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// 获取连接:
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    // TODO: 访问数据库...
}

2.3、JDBC查询

2.3.1、JDBC查询

查询的前提是获取连接（2.2节），查询步骤如下：

①通过Connection提供的createStatement()方法创建一个Statement对象，它是执行查询的主体；

Statement stmt = conn.createStatement()

②执行Statement对象的 executeQuery("SELECT语句")，传入SQL语句，执行查询并获得返回的结果集，使用ResultSet来引用结果集：

ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")

③反复调用rs.next()方法读取每一行的结果。调用rs.getXxx(n)方法，获取第n列的值，Xxx为类型名：

while (rs.next()) {
        long id = rs.getLong(1); // 注意：索引从1开始
        long grade = rs.getLong(2);
        String name = rs.getString(3);
        int gender = rs.getInt(4);
}

全代码：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
            while (rs.next()) {
                long id = rs.getLong(1); // 注意：索引从1开始
                long grade = rs.getLong(2);
                String name = rs.getString(3);
                int gender = rs.getInt(4);
            }
        }
    }
}

要点

• Connection、Statement、ResultSet都是资源，需要用嵌套的try语句块回收；

• 刚开始的ResultSet并非第一行，先调用一个rs.next()才到了第一行（如果ResultSet非空的话）；

• rs.next()是直接移动到下一行，没有就返回Null；

• 通过rs.getXxx(n)访问第n列时，n从1开始；

• 如果getXxx(n)的类型Xxx与第n列的数据类型对不上，会报错。

2.3.2、数据类型

java.sql.Type包

SQL数据类型	Java数据类型
BIT	Boolean
INTEGER	Integer
BIGINT	Long
REAL	Float
FLOAT, DOUBLE	Double
CHAR（1）	Character
VARCHAR	String
DECIMAL	BigDecimal
DATE	java.sql.Timestamp
TIME	java.sql.Timestamp

2.3.3、SQL注入与解决方法

直接拼写SQL查询语句是不安全的，因为可能通过拼写导致SQL注入攻击，一种方法是用转义，不过这种方法比较麻烦，另一种方法是用PreparedStatement，这是常用的方法。

PreparedStatement始终用?作为占位符，并且把数据连同SQL语句本身传入数据库，这样就保证了每次传入的SQL语句是相同的，不同的只是占位符上的数据：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    String SQL="SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?";
    try (PreparedStatement ps = conn.prepareStatement(SQL)) {
        ps.setObject(1, "M"); // 注意：索引从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");
                String gender = rs.getString("gender");
            }
        }
    }
}

使用PreparedStatement与Statement的唯一不同，就是传入的SQL语句中存在占位符?，需要用ps.setObject(n,value)在第n个占位符上填入值value。

最终获取到的对象总为ResultSet，所以之后的提取操作都是相同的。

 

3、增删改查

增删改查：CRUD——Create、Retrieve、Update、Delete。

• 查询：executeQuery()

• 增删改查：executeUpdate()

3.1、插入 INSERT

3.1.1、插入

流程：

①通过DriverManager.getConnection(URL,USER,PASSWORD)获取Connection；

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {

②用INSERT SQL通过conn.prepareStatement(SQL)构造PreparedStatement，并用ps.setObject(n,Value)填充SQL语句中的占位符？；

String SQL = "INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)";
 try (PreparedStatement ps = conn.prepareStatement(SQL) {
        ps.setObject(1, 999); // 注意：索引从1开始
        ps.setObject(2, 1); // grade
        ps.setObject(3, "Bob"); // name
        ps.setObject(4, "M"); // gender

③调用ps.executeUpdate()执行插入；

int n = ps.executeUpdate(); // 1

最后返回的值n，是插入的行数。

3.1.2、插入并获取主键

如果数据库表设置了自增主键，那么插入后数据库会自动为插入项设置自增主键。那么如何获取自增主键？

解决方法：

在创建PreparedStatement，传入参数Statement.RETURN_GENERATED_KEYS，表示查询后返回自增主键：

try (PreparedStatement ps = conn.prepareStatement(SQL,
            　　　　　　　　　　　　　　　　Statement.RETURN_GENERATED_KEYS)){
　　...
}

如果要获取自增主键，则需在查询后通过ps.getGeneratedKeys()获取：

try (ResultSet rs = ps.getGeneratedKeys()) {
      if (rs.next()) {
         long id = rs.getLong(1); // 注意：索引从1开始
      }

完整代码：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    String SQL = "INSERT INTO students (grade, name, gender) VALUES (?,?,?)";
    try (PreparedStatement ps = conn.prepareStatement(
            SQL,Statement.RETURN_GENERATED_KEYS)) {
        ps.setObject(1, 1); // grade
        ps.setObject(2, "Bob"); // name
        ps.setObject(3, "M"); // gender
        int n = ps.executeUpdate(); // 1
        
        //获取自增主键
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                long id = rs.getLong(1); // 注意：索引从1开始
            }
        }
    }
}

要点：

• 调用preparedStatement()时，第二个参数必须传入常量Statement.RETURN_GENERATED_KEYS，才会返回自增主键；

• 执行executeUpdate()方法后，必须调用getGeneratedKeys()获取一个ResultSet对象，这个对象包含了数据库自动生成的主键的值，获取该对象的每一行来获取自增主键的值。

• 如果一次插入多条记录，那么这个ResultSet对象就会有多行返回值。如果插入时有多列自增，那么ResultSet对象的每一行都会对应多个自增值（自增列不一定必须是主键）。

3.2、更新 UPDATE 与 删除 DELETE

流程与INSERT完全相同，不同之处只有SQL语句而已：

①通过DriverManager.getConnection(URL,USER,PASSWORD)获取Connection；

②用SQL通过conn.prepareStatement(SQL)构造PreparedStatement，并用ps.setObject(n,Value)填充SQL语句中的第n个占位符？；

③调用ps.executeUpdate()执行更新；

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {

    //UPDATE SQL
    String SQL= "UPDATE students SET name=? WHERE id=?";
    //DELETE SQL
    String SQL = "DELETE FROM students WHERE id=?";

    try (PreparedStatement ps = conn.prepareStatement(SQL)) {
        //UPDATE时占位符填充
        ps.setObject(1, "Bob"); // 注意：索引从1开始
        ps.setObject(2, 999);

        //DELETE时占位符填充
        ps.setObject(1, 999); // 注意：索引从1开始

        int n = ps.executeUpdate(); // 返回更新/删除的行数
    }
}

最后返回的值n，是受影响的的行数。

 

4、JDBC事务

数据库事务（Transaction）是由若干个SQL语句构成的一个操作序列，有点类似于Java的synchronized同步。数据库系统保证在同一个事务中的所有SQL要么全部执行成功，要么全部不执行。

数据库事务具有ACID特性：

• Atomicity：原子性

• Consistency：一致性

• Isolation：隔离性

• Durability：持久性

要在JDBC中执行事务，本质上是讨论如何把多条SQL包裹在一个数据库事务中执行。

JDBC事务代码步骤：

①获取数据库连接Connection，方法跟之前一样；

②开启事务，conn.ssetAutoCommit(false)，关闭自动提交；

③执行若干条SQL语句，作为事务代码；

④提交事务，conn.commit()；

由于事务执行或提交时可能抛出SQL异常，所以要用try...catch...finally句式，②③④语句放在try中

⑤catch捕获SQL异常，捕获后用conn.rollback()回滚事务

⑥finally中，通过conn.setAutoCommit(true)把Connection对象的状态恢复到初始值，并用conn.close()关闭数据库。

完整代码

//①
Connection conn = openConnection();
try {
    // ②关闭自动提交:
    conn.setAutoCommit(false);
    // ③执行多条SQL语句:
    insert(); update(); delete();
    // ④提交事务:
    conn.commit();

    //⑤捕获异常
} catch (SQLException e) {
    // 回滚事务:
    conn.rollback();
    //⑥恢复conn状态，关闭连接
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

如果不通过第②步关闭自动提交，那么我们每执行一个SQL语句都会提交一下。

只要关闭了Connection的自动提交，那么就可以在一个事务中执行多条语句，事务以commit()方法结束。

4.1、隔离级别

数据库事务可以并发执行，而数据库系统从效率考虑，对事务定义了不同的隔离级别。SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

可以用conn.setTransactionIsolation(级别)设置事务的隔离级别：

// 设定隔离级别为READ COMMITTED:
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

如果没有调用上述方法，那么会使用数据库的默认隔离级别。MySQL的默认隔离级别是REPEATABLE READ。

 

5、JDBC批处理

使用数据库时，经常会执行一些批量操作——格式相同，值不同的SQL语句。

例如：

INSERT INTO coupons (user_id, type, expires) VALUES (123, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (234, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (345, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (456, 'DISCOUNT', '2030-12-31');
...

虽然我们可以通过List+循环的方式多次插入，但这样性能很低：

for (var params : paramsList) {
    String SQL = "INSERT INTO coupons (user_id, type, expires) VALUES (?,?,?)";
    PreparedStatement ps = conn.preparedStatement(SQL);
    ps.setLong(params.get(0));
    ps.setString(params.get(1));
    ps.setString(params.get(2));
    ps.executeUpdate();
}

正确的解决方法是使用批量(batch)执行，这种操作被SQL优化过，所以速度要快于循环SQL。

在JDBC中，我们可以把相同SQL格式但参数不同的多次操作合并为一个batch执行。

步骤如下：

①数据库连接；

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    ...
}

②用带占位符?的SQL语句，构造PreparedStatement对象；

String SQL = "INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(SQL)) {
    ...
}

③通过for循环，对同一个PreparedStatement调用ps.setXxx(n,value)反复设置参数，并用ps.addBatch()添加到一个batch；

    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
    }

④调用ps.executeBatch()执行batch，返回一个int [ ]，其中每个元素是batch中每个SQL影响的行数；

    int [] ns = ps.executeBatch();
    for (int n : ns) {
        System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
    }

完整代码

//①
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
　　 //②    
　　 String SQL = "INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)";
    try (PreparedStatement ps = conn.prepareStatement(SQL)) {
    　　 // ③ 对同一个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
        }
        // ④ 执行batch:
        int[] ns = ps.executeBatch();
        for (int n : ns) {
            System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
        }
}

执行batch与执行普通SQL的区别：

1. batch需要对同一个PreparedStatement反复设置参数并调用addBatch()，这样就相当于给一个SQL加上了多组参数，相当于变成了多行SQL；

2. 执行时不用executeUpdate()，而是executeBatch()；

3. 由于我们设置了多组参数，相应的，返回结果也是多个int值，类型是int[ ]，循环int [ ]数组即可获取每个SQL影响的行数。

 

6、JDBC连接池

在学习多线程的时候，我们知道线程是一种昂贵的资源，频繁地创建与销毁会消耗大量系统资源，甚至时间有可能比任务执行时间还长。所以为了提高效率，可以使用线程池，实现线程复用。

类似的，在执行JDBC的增删改查操作时，如果每次操作都来一次打开连接、操作、关闭连接，那么创建和销毁JDBC连接的开销就太大了。为了避免频繁创建和销毁连接，我们可以通过连接池（Connection Pool）复用已经创建好的连接。

接口：javax.sql.DataSource

实现类：

• HikariCP（最常用）
• C3P0
• BoneCP
• Druid

最常用的HikariCP，它的Maven依赖是：

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.7.1</version>
</dependency>

我们需要用它构造一个DataSource实例，这个实例就是连接池，步骤如下：

①构造HikariConfig对象；

HikariConfig config = new HikariConfig();

②为HikariConfig调用config.setXxx(Value)设置基本配置，包括URL、Username、Password；

config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");

③为HikariConfig调用config.addDataSourceProperty(Key,Value)设置连接池（DataSource）配置；

config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10

④构造DataSource

DataSource ds = new HikariDataSource(config);

 

由于DataSource的构造也是一个昂贵操作，所以DataSource实例总是作为全局变量存储，贯穿整个程序生命周期。

如何使用连接池？

与之前所讲的代码类似，只是获取Connection时，用ds.getConnection()而非DriverManager.getConnection()

try (Connection conn = ds.getConnection()) { // 在此获取连接
    ...
} // 在此“关闭”连接

而且getConnection()时，无需指定URL、User、Password，因为我们已经在构造DataSource的第②步，通过config.setXxx()设置了。

 

连接池的运行过程：

1. 刚开始，连接池中并没有连接；

2. 第一次调用ds.getConnection()，会迫使连接池内部先创建一个Connection，再返回给客户端使用；

3. 调用conn.close()方法时（在try(resource){...}结束处），并非真正“关闭”连接，而是释放到连接池中，以便下次获取连接时能直接返回；

4. 之后，连接池中就维护了若干个Connection实例，如果调用ds.getConnection()，就选择一个空闲连接，把它标记为“正在使用”然后返回，如果对Connection调用close()，那么就把连接再次标记为“空闲”从而等待下一次调用。

这样一来，我们就通过连接池维护了少量连接，但却可以频繁执行大量的SQL语句。

通常连接池提供了大量参数可以配置，例如，维护的最小、最大活动连接数，指定一个连接在空闲一段时间之后自动关闭等，需要根据应用程序的负载合理地配置这些参数。此外，大多数连接池都提供了详细的实时状态以便进行监控。