JDBC 代码

• JDBC 都是 从 1 开始 计数的

AutoCloseable

• java.lang.AutoCloseable 与 try-with-resources 配合，让代码更简洁
• 从 JDK 1.7 开始
• AutoCloseable#close 抛出的异常需要在 try-with-resources 的 catch 语句中捕捉
• 执行顺序是 try -> close -> catch -> finally

public class AutoCloseableTest {
    public static void main(String[] args) {
        try (AutoCloseableObject app = new AutoCloseableObject()) {
            System.out.println("--执行main方法--");
            int i = 1 / 0;
        } catch (Exception e) {
            System.out.println("--exception--");
        } finally {
            System.out.println("--finally--");
        }
    }

    //自定义类 并实现 AutoCloseable
    public static class AutoCloseableObject implements AutoCloseable {
        @Override
        public void close() throws Exception {
            System.out.println("--close--");
            throw new RuntimeException("xxx");
        }
    }
}
/*
* 
* 执行结果：
* 
    --执行main方法--
    --close--
    --exception--
    --finally--
* 
* */

使用 Statement

/**
 * JDBC 使用 Statement 执行 sql 语句
 *
 * @throws SQLException
 * @throws ClassNotFoundException
 */
@Test
public void testStatement() throws SQLException, ClassNotFoundException {

    String dbUrl = "jdbc:mysql://192.168.181.129:3306/mybatis";
    String dbUser = "root";
    String dbPassword = "123456";
    String sql = "select * from user";

    // 1. 注册驱动，可省略
    // 加载类 DriverManager 时，会以 SPI 的方式将驱动加载
    // {@code java.sql.DriverManager#loadInitialDrivers}
    Class.forName("com.mysql.jdbc.Driver");

    // 2. 获取连接，表示 JVM 进程与数据库进程之间的通道打开了
    // 3. 获取数据库操作对象，专门执行 sql 语句的对象
    // 4. 执行 sql 语句，获取结果集

    // 6. try-with-resources 释放资源
    try (Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {

        // 5. 处理查询结果集
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String username = resultSet.getString("username");

            User user = new User();
            user.setId(id);
            user.setUsername(username);
            System.out.println(user);
        }
    }
}

使用 PreparedStatement

/**
 * JDBC 使用 PreparedStatement 执行 sql 语句
 *
 * @throws SQLException
 * @throws ClassNotFoundException
 */
@Test
public void testPreparedStatement() throws SQLException, ClassNotFoundException {

    String dbUrl = "jdbc:mysql://192.168.181.129:3306/mybatis";
    String dbUser = "root";
    String dbPassword = "123456";
    String sql = "select * from user where id = ?";

    // 1. 注册驱动，可省略
    // 加载类 DriverManager 时，会以 SPI 的方式将驱动加载
    // {@code java.sql.DriverManager#loadInitialDrivers}
    Class.forName("com.mysql.jdbc.Driver");

    // 2. 获取连接，表示 JVM 进程与数据库进程之间的通道打开了
    // 3. 获取数据库操作对象，专门执行 sql 语句的对象
    // 4. 执行 sql 语句，获取结果集

    // 6. try-with-resources 释放资源
    try (Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword); PreparedStatement preparedStatement = getPreparedStatement(sql, connection); ResultSet resultSet = preparedStatement.executeQuery()) {

        // 5. 处理查询结果集
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String username = resultSet.getString("username");

            User user = new User();
            user.setId(id);
            user.setUsername(username);
            System.out.println(user);
        }
    }
}

private PreparedStatement getPreparedStatement(String sql, Connection connection) throws SQLException {
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1, 2);
    return preparedStatement;
}

对比 Statement 和 PreparedStatement

• Statement 存在 sql 注入问题，PreparedStatement 解决了 sql 注入问题
  • sql 注入并不是完全有害的，有时反而是必须的
• 带参数的 sql 语句，Statement 是编译一次执行一次，PreparedStatement 是编译一次，可执行 N 次
• PreparedStatement 会在编译阶段做类型的安全检查

操作 Blob 类型数据

// 1. 插入 Blob
sql = "insert into user(id, username, photo) values(?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 3);
preparedStatement.setString(2, "u3");

// 设置 Blob 内容
InputStream inputStream = Files.newInputStream(new File("lm.jpg").toPath());
preparedStatement.setBlob(3, inputStream);

int rowCount = preparedStatement.executeUpdate();
if (rowCount > 0) {
    System.out.println("插入成功");
} else {
    System.out.println("插入失败");
}

// 2. 读取 Blob
sql = "select * from user where id = 3";
resultSet = preparedStatement.executeQuery(sql);
if (resultSet.next()) {
    Blob photo = resultSet.getBlob("photo");
    InputStream InputStream = photo.getBinaryStream();
    Files.copy(InputStream, new File("test.jpg").toPath());
    System.out.println("读取成功");
}

批量插入数据

• JDBC 连接串上需要增加参数 rewriteBatchedStatements=true，否则速度和单条插入区别不大

@Test
public void testBatch() throws SQLException {

    long start = System.currentTimeMillis();

    sql = "insert into user(username) values(?)";
    preparedStatement = connection.prepareStatement(sql);
    for (int i = 0; i < 1000000; i++) {
        preparedStatement.setString(1, "batch-" + i);
        // preparedStatement.executeUpdate();   // 10w - 57s

        preparedStatement.addBatch();           // 100w - 4s

        if ((i + 1) % 1000 == 0) {
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }
    }

    long end = System.currentTimeMillis();
    System.out.println("共花费时间：" + (end - start) / 1000);
}

手动控制事务

@Test
public void testTx() throws SQLException {
    // 设置关闭自动提交
    connection.setAutoCommit(false);
    statement = connection.createStatement();
    sql = "insert into user(username) values('1')";
    statement.executeUpdate(sql);

    // 测试，抛出异常
    // int i = 1 / 0;

    sql = "insert into user(username) values('2')";
    statement.executeUpdate(sql);

    // 手动提交
    connection.commit();

    // 还原默认设置
    // 如果使用连接池，需要恢复自动提交设置
    connection.setAutoCommit(true);

    System.out.println("执行完成");
}

事务隔离级别

事务的 ACID 属性

• 原子性 - Atomicity
• 一致性 - Consistency
• 隔离性 - Isolation
• 持久性 - Durability

数据库的并发问题

• 脏读

  对于两个事务 T1、T2，T1 读取到了已经被 T2 更新但还没有提交的字段。之后，若 T2 回滚，T1 读取的内容就是临时且无效的

• 不可重复读

  对于两个事务 T1、T2，T1 读取到了一个字段，然后 T2 更新 了该字段。之后，T1 再次读取同一字段，值就不同了。

• 幻读

  对于两个事务 T1、T2，从一个表中读取了一个字段，然后 T2 在该表中 插入 了一些新的行。之后，如果 T1 再次读取同一个表，就会多出几行。

隔离级别

悲观锁和乐观锁

• 使用 MySQL
• MySQL 的事务是默认自动提交的

悲观锁

又称 行级锁

1. 准备数据表

   DROP TABLE IF EXISTS employee;
   
   CREATE TABLE IF NOT EXISTS employee (
     id      INTEGER NOT NULL,
     money   INTEGER,
     version INTEGER,
     PRIMARY KEY (id)
   )
     ENGINE = INNODB;
   
   INSERT INTO employee VALUE (1, 0, 1);
   
   SELECT * FROM employee;
   

2. 开启两个终端，分别执行以下语句

   -- 自动提交
   set autocommit = false;
   -- 加悲观锁
   select * from employee where id = 1 for update; 
   

3. 第二个终端被挂起，无法执行完成，超时后报错

   select * from employee where id = 1 for update
   > 1205 - Lock wait timeout exceeded; try restarting transaction
   > 时间: 51.043s
   

• 如果没有设置自动提交为 false，两个终端都可以执行完成，因为 MySQL 默认是自动提交的，所以第一个终端执行完后，事务被提交
• 如果第二个终端的查询结果与第一个终端不重合，可以执行完成。例如，第一个终端查询条件为 id=1，第二个终端查询条件为 id=2

乐观锁

• 乐观锁在数据库上的实现完全是逻辑的，数据库本身不提供支持，而是需要开发者自己来实现
• 类似于 CAS
• 参考实现

execute、executeUpdate、executeQuery三者的区别

1. ResultSet executeQuery(String sql);

   执行SQL查询，并返回 ResultSet 对象。

2. int executeUpdate(String sql);

   可执行增，删，改，返回执行受到影响的行数。

3. boolean execute(String sql);

   可执行任何SQL语句，返回一个布尔值，表示是否返回 ResultSet ，如果返回，可以通过 getResultSet 方法获取

参考资料

• JDBC从入门到精通视频教程-JDBC实战精讲
• 尚硅谷JDBC核心技术(新版jdbc)
• 使用AutoCloseable 实现自动关闭资源
• Mysql锁机制--乐观锁 & 悲观锁
• execute、executeUpdate、executeQuery三者的区别（及返回值）