JDBC 操作 MySQL

1. 开发环境

• Win10
• IDEA 2019.3.3
• JDK1.8
• maven 3.6.3
  
• MySQL 8.0.19

  2. 连接 MySQL

  2.1 添加 MySQL 的 JDBC驱动。

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.19</version>
</dependency>

2.2 启动 MySQL 服务

net start mysql

没有报错就说明连接成功了。

2.3 进行连接

import java.sql.*;
public class Main {
    public static void main(String argv[]) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        // JDBC 连接基本格式
        // jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2
        String JDBC_URL = "jdbc:mysql://localhost:3306/learn_jdbc?useSSL=false&serverTimezone=UTC";
        String JDBC_USER = "root";
        String JDBC_PASSWORD = "root";
        // 使用try来确保及时释放资源
        // 连接数据库
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
        }
    }
}

没有报错就说明连接成功。

3. 初始数据准备

-- 创建数据库learn_jdbc:
DROP DATABASE IF EXISTS learn_jdbc;
CREATE DATABASE learn_jdbc;
-- 创建表students:
USE learn_jdbc;
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);

添加完查看一下。

4. 查询

注意使用 PreparedStatement 以防止SQl注入问题。后续其他操作也都是如此。
嵌套使用try语句以保证及时释放资源。

String sql = "SELECT id, grade,name,gender FROM students WHERE gender=? AND grade=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    // 设置每个占位符的值, 下标从1开始
    ps.setObject(1, 1);
    ps.setObject(2, 1);
    // 传入SQL语句并获取返回的结果集
    try (ResultSet results = ps.executeQuery()) {
        // 迭代获取结果
        while (results.next()) {
            // 按类型获取各个属性的值, 索引下标从1开始
            long id = results.getLong("id");
            int gender = results.getInt("gender");
            String name = results.getString("name");
            int grade = results.getInt("grade");
            System.out.println(id+" "+name+" "+grade+" "+gender);
        }
    }
}

查询结果符合预期。

1 小明 1 1
2 小红 1 1

5. 插入

本质上也是用PreparedStatement执行一条SQL语句，不过最后执行的是 executeUpdate()

String sql = "INSERT INTO students (id, grade, name, gender, score) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    // 设置每个占位符的值, 下标从1开始
    ps.setObject(1, 999);
    ps.setObject(2, 1);
    ps.setObject(3, "Bob");
    ps.setObject(4, 1);
    ps.setObject(5, 88);
    // 表示插入的记录数量
    int n = ps.executeUpdate();
    System.out.println(n); // 1
}

成功插入后，可以发现多了一条关于“Bob”的记录。

插入后的结果

此时再次执行程序，就会因为插入相同主键的记录而报错

Exception in thread "main" java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '999' for key 'students.PRIMARY'

6. 插入并获取主键

在准备数据的时候可以发现，我们的字段 id 是一个自增主键，在执行 INSERT 语句时，并不需要指定主键，数据库会自动分配主键。对于使用自增主键的程序，有个额外的步骤，就是如何获取插入后的自增主键的值。
要获取自增主键，不能先插入，再查询。因为两条SQL执行期间可能有别的程序也插入了同一个表。获取自增主键的正确写法是在创建 PreparedStatement 的时候，指定一个 RETURN_GENERATED_KEYS 标志位，表示JDBC驱动必须返回插入的自增主键。

String sql = "INSERT INTO students (grade, name, gender, score) VALUES (?, ?, ?, ?)";
// 指定标志位, 表示返回插入的自增主键
try (PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setObject(1, "1");
    ps.setObject(2, "Jack");
    ps.setObject(3, 2);
    ps.setObject(4, 67);
    int n = ps.executeUpdate();
    // 获取自增主键的值
    try (ResultSet results = ps.getGeneratedKeys()) {
        while (results.next()) {
            long id = results.getLong(1);
            System.out.println(id);    // 1000
        }
    }
}

查看新增记录的主键 id 的值，确实是1000。

7. 更新

更新操作，除了 SQL 语句不同，没有什么特别。

String sql = "UPDATE students SET score=? WHERE name=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setObject(1, 999);
    ps.setObject(2, "Bob");
    int n = ps.executeUpdate();
    System.out.println(n); // 1
}

执行更新操作后，name="Bob" 的记录的 score 属性被成功修改为 999。

8. 删除

修改 SQL 语句，其余无特别。

String sql = "DELETE FROM students WHERE name=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setObject(1, "Bob");
    int n = ps.executeUpdate();
    System.out.println(n); // 1
}

name="Bob" 的记录被成功删除了。

9. 事务

9.1 事务的概念

数据库事务（Transaction）是由若干个SQL语句构成的一个操作序列。
数据库系统保证在一个事务中的所有SQL要么全部执行成功，要么全部不执行，即数据库事务具有ACID特性：

• 事务的原子性（ Atomicity）：一组事务，要么成功；要么撤回。
• 一致性 （Consistency）：事务执行后，数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功否，参与转账的两个账号余额之和应该是不变的。
• 隔离性（Isolation）：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 持久性（Durability）：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。

9.2 MySQ 中的事务

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务（start transaction）和结束事务（commit或rollback）。
在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所作出的影响会持久到数据库中，或者rollback，表示回滚到事务的起点，之前做的所有操作都被撤销了。

9.3 JDBC 中的事务

在JDBC中处理事务，都是通过Connection完成的。同一事务中所有的操作，都在使用同一个Connection对象。
Connection的三个方法与事务有关：

1. setAutoCommit(boolean)

设置是否为自动提交事务，如果true（默认值为true）表示自动提交，也就是每条执行的SQL语句都是一个单独的事务，如果设置为false，那么相当于开启了事务了。

1. commit()

提交结束事务。

1. rollback()

回滚结束事务。

基本形式

try{
    // 开启事务
    connection.setAutoCommit(false);
    /*
     一系列数据库操作
     */
    // 提交事务      
    connection.commit();
} catch() {
    // 回滚事务
    connection.rollback();
} finally {
    // 恢复自动提交
    connection.setAutoCommit(true);
    // 释放资源
    connection.close();
}

9.4 具体实现

本节数据库中的初始状态和上一节结束时一致。、

Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
try {
    // 关闭自动提交
    connection.setAutoCommit(false);
    // 执行多条 SQL 语句
    insert(connection); // 1
    update(connection); // 1
    // delete(connection); // 1
    // 手动提交事务
    connection.commit();
} catch (Exception e) {
    // 事务执行可能失败, 失败需要回滚
    connection.rollback();
} finally {
    // 恢复自动提交
    connection.setAutoCommit(true);
    // 释放资源
    connection.close();
}

为了能更明显地看出变化，这里只执行插入和更新两个操作。可以看到数据表 students 中多出来了一条name="Bob" 且 score=999 的记录，说明事务执行成功。

10. Batch

SQL数据库对SQL语句相同，但只有参数不同的若干语句可以作为batch执行，即批量执行，这种操作有特别优化，速度远远快于循环执行每个SQL。
在JDBC代码中，我们可以利用SQL数据库的这一特性，把同一个SQL但参数不同的若干次操作合并为一个batch执行。
执行batch和执行一个SQL不同点在于，需要对同一个PreparedStatement反复设置参数并调用addBatch()，这样就相当于给一个SQL加上了多组参数，相当于变成了“多行”SQL。
第二个不同点是调用的不是executeUpdate()，而是executeBatch()，因为我们设置了多组参数，相应地，返回结果也是多个int值，因此返回类型是int[]，循环int[]数组即可获取每组参数执行后影响的结果数量。

String sql = "INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)";
String[] names = {"A", "B", "C"};
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    // 反复设置参数并添加到 batch
    for (String name : names) {
        ps.setObject(1, name);
        ps.setObject(2, 1);
        ps.setObject(3, 2);
        ps.setObject(4, 60);
        ps.addBatch();
    }
    // 执行 batch
    int[] ns = ps.executeBatch();
    for (int n : ns) {
        // 每个 SQL 语句的执行效果
        System.out.println(n + "inserted");
    }
}

可以看到三条只有 name 属性不同的记录被成功添加了。

11. 连接池

在执行JDBC的增删改查的操作时，如果每一次操作都来一次打开连接，操作，关闭连接，那么创建和销毁JDBC连接的开销就太大了。为了避免频繁地创建和销毁JDBC连接，我们可以通过连接池（Connection Pool）复用已经创建好的连接。
数据库连接池是一种复用Connection的组件，它可以避免反复创建新连接，提高JDBC代码的运行效率。
JDBC连接池有一个标准的接口javax.sql.DataSource，注意这个类位于Java标准库中，但仅仅是接口。要使用JDBC连接池，我们必须选择一个JDBC连接池的实现。常用的JDBC连接池有：

• HikariCP
• C3P0
• BoneCP
• Druid

目前使用最广泛的是HikariCP。我们以HikariCP为例，要使用JDBC连接池，先添加HikariCP的依赖如下：

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

HikariConfig config=new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/learn_jdbc?useSSL=false&serverTimezone=UTC");
config.setUsername("root");
config.setPassword("root");
// 连接超时, 1000ms
config.addDataSourceProperty("connectionTimeout", "1000");
// 空闲超时, 60000ms
config.addDataSourceProperty("idleTimeout", "60000");
// 最大连接数
config.addDataSourceProperty("maximumPoolSize", "10");
// 创建连接池实例
DataSource ds=new HikariDataSource(config);
// 从连接池获取连接, 并及时关闭
try (Connection connection=ds.getConnection()){
    // 在此处执行操作
    delete(connection);
}

成功执行删除操作，name="Bob" 的记录被删除了。

通过连接池获取连接时，并不需要指定JDBC的相关URL、用户名、口令等信息，因为这些信息已经存储在连接池内部了（创建HikariDataSource时传入的HikariConfig持有这些信息）。一开始，连接池内部并没有连接，所以，第一次调用ds.getConnection()，会迫使连接池内部先创建一个Connection，再返回给客户端使用。当我们调用conn.close()方法时（在try(resource){...}结束处），不是真正“关闭”连接，而是释放到连接池中，以便下次获取连接时能直接返回。
因此，连接池内部维护了若干个Connection实例，如果调用ds.getConnection()，就选择一个空闲连接，并标记它为“正在使用”然后返回，如果对Connection调用close()，那么就把连接再次标记为“空闲”从而等待下次调用。这样一来，我们就通过连接池维护了少量连接，但可以频繁地执行大量的SQL语句。
通常连接池提供了大量的参数可以配置，例如，维护的最小、最大活动连接数，指定一个连接在空闲一段时间后自动关闭等，需要根据应用程序的负载合理地配置这些参数。此外，大多数连接池都提供了详细的实时状态以便进行监控。

12. 参考链接

https://www.liaoxuefeng.com/wiki/1252599548343744/1255943820274272
https://www.cnblogs.com/gdwkong/p/7633016.html

13. 完整工程文件

JDBC_Demo.zip