20201115 JDBC

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 再次读取同一个表，就会多出几行。

隔离级别

隔离级别	描述	被解决的并发问题
READ UNCOMMITTED	读未提交	无
READ COMMITTED	读已提交	脏读
REPEATABLE READ	可重复读	脏读、不可重复读
SERIALIZABLE	串行化	脏读、不可重复读、幻读

悲观锁和乐观锁

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

	悲观锁	乐观锁
概念	查询时直接锁住记录使得其它事务不能查询，更不能更新	提交更新时检查版本或者时间戳是否符合
语法	select ... for update	使用 version 或者 timestamp 进行比较
实现者	数据库本身	开发者
适用场景	并发量大	并发量小
类比Java	Synchronized 关键字	CAS 算法

悲观锁

又称 行级锁

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三者的区别（及返回值）