JDBC 事务、批处理和获取主键

柔光的暖阳◎ 2024-04-18 16:16 57阅读 0赞

### 一、事务处理 ###

**事务（Transaction，简写为tx）**：在数据库中，事务是指一组逻辑操作，不论成功与失败都作为一个整体进行工作，要么全部执行成功，要么全部不执行(执行失败)。

**1、处理事务的两个动作**

1）提交（commit）：在整个事务中，所有的逻辑操作都正常执行成功，则提交事务。

2）回滚（rollback）：在整个事务中，有一个或多个逻辑操作执行失败，则回滚事务，撤销该事务中的所有操作，恢复到最初的状态。

**2、事务的ACID属性**

1）原子性（Aromicity）：事务是应用中不可再分的最小逻辑执行单位，要么都执行成功，要么都不执行。
    2）一致性（Consistency）：事务结束后，数据库中的数据是合法正确的（数据不被破坏）。
    3）隔离性（Isolation）：并发执行的事务之间彼此相互独立、互不干扰。
    4）持久性（Durability）：事务提交后，数据时永久性对的、不可回滚。

**3、在代码中处理事务**

1）在JDBC中缺省情况下，事务是自动提交的，控制事务必须先设置事务为手动提交

2）手动提交事务

3）若出现异常必须回滚事务

Connection接口方法：

*   *  <table> 
         <tbody> 
          <tr> 
           <td style="width:74px;"><code>void</code></td> 
           <td><code><a rel="nofollow">setAutoCommit</a>(boolean&nbsp;autoCommit)</code> <p>将此连接的自动提交模式设置为给定状态。</p> </td> 
          </tr> 
          <tr> 
           <td style="width:74px;"><code>void</code></td> 
           <td><code><a rel="nofollow">commit</a>()</code> <p>使自上次提交/回滚以来所做的所有更改都将永久性，并释放此 <code>Connection</code>对象当前持有的任何数据库锁。</p> </td> 
          </tr> 
          <tr> 
           <td style="width:74px;"><code>void</code></td> 
           <td><code><a rel="nofollow">rollback</a>()</code> <p>撤消在当前事务中所做的所有更改，并释放此 <code>Connection</code>对象当前持有的任何数据库锁。</p> </td> 
          </tr> 
         </tbody> 
        </table>

/**
         * 赵云向赵子龙转账1000
         */
        @Test
        public void testTx() {
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                //1、设置事务手动提交
                conn.setAutoCommit(false);
                //查询赵云账号余额
                String sql = "SELECT * FROM t_user WHERE username=? and account >=?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,"赵云");
                ps.setLong(2,1000L);
                rs = ps.executeQuery();
                if (!rs.next()){
                    throw  new RuntimeException("余额不足！");
                }
                //赵云减1000
                sql = "UPDATE t_user set account=account-? WHERE username=?";
                ps = conn.prepareStatement(sql);
                ps.setLong(1,1000L);
                ps.setString(2,"赵云");
                ps.executeUpdate();
                //模拟异常
                int i = 1/0;
                //赵子龙加1000
                sql = "UPDATE t_user set account=IFNULL(account,0) +? WHERE username=?";
                ps = conn.prepareStatement(sql);
                ps.setLong(1,1000L);
                ps.setString(2,"赵子龙");
                ps.executeUpdate();
                //2、手动提交事务
                conn.commit();
            }catch (Exception e){
                e.printStackTrace();
                try {
                    //3、回滚事务
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }finally {
                JdbcUtil.close(conn, ps, rs);
            }
        }

### 二、批处理操作 ###

**批量操作（batch**）：当需要成批插入或者更新记录时，可以采用Java的批量更新机制，它允许多条语句一次性提交为数据库批量处理。通常情况下比单独提交处理更高效。

JDBC的批量处理语句的方法：

**1、Statement接口批处理**

一次性可以执行多条SQL语句，需要编译多次。

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">addBatch</a>(<a rel="nofollow">String</a>&nbsp;sql)</code> <p>将给定的SQL命令添加到此 <code>Statement</code>对象的当前命令列表中。</p> </td> 
          </tr> 
          <tr> 
           <td><code>int[]</code></td> 
           <td><code><a rel="nofollow">executeBatch</a>()</code> <p>将一批命令提交到数据库以执行，并且所有命令都执行成功，返回一个更新计数的数组。</p> </td> 
          </tr> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">clearBatch</a>()</code> <p>清空此 <code>Statement</code>对象的当前SQL命令列表。</p> </td> 
          </tr> 
         </tbody> 
        </table>

/**
         *  Statement 未使用批量处理
         *  InnodDB耗时：6111ms
         *  添加rewriteBatchedStatements=true后
         *  InnodDB耗时：6042ms
         * @throws Exception
         */
        @Test
        public void testSaveByStatement() throws Exception {
            long begin = System.currentTimeMillis();
            Connection conn = JdbcUtil.getConnection();
            Statement st = null;
            for (int i = 0; i <= 3000; i++) {
                String sql = "INSERT INTO t_user(username,age) VALUES('aa',"+i+")";
                st = conn.createStatement();
                st.executeUpdate(sql);
            }
            System.out.println(System.currentTimeMillis() - begin);
            JdbcUtil.close(conn,st,null);
        }
    
        /**
         *  Statement 使用批量处理
         *  InnodDB耗时：5974ms
         *  添加rewriteBatchedStatements=true后
         *  InnodDB耗时：5288ms
         * @throws Exception
         */
        @Test
        public void testSaveByStatementBatch() throws Exception {
            long begin = System.currentTimeMillis();
            Connection conn = JdbcUtil.getConnection();
            Statement st = conn.createStatement();
            for (int i = 0; i <= 3000; i++) {
                String sql = "INSERT INTO t_user(username,age) VALUES('aa',"+i+")";
                st.addBatch(sql); //将SQL语句添加到批处理
                if(i % 300 == 0){
                    st.executeBatch();//执行操作
                    st.clearBatch();//清除操作
                }
            }
            System.out.println(System.currentTimeMillis() - begin);
            JdbcUtil.close(conn,st,null);
        }

**2、PreparedStatement接口批处理**

执行一条SQL语句，编译一次，执行SQL语句的参数不同

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">addBatch</a>()</code> <p>向这个 <code>PreparedStatement</code>对象的一批命令添加一组参数。</p> </td> 
          </tr> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">clearParameters</a>()</code> <p>立即清除当前参数值</p> </td> 
          </tr> 
         </tbody> 
        </table>

/**
         *  PreparedStatement 未使用批量处理
         *  InnodDB耗时：6040ms
         *  添加rewriteBatchedStatements=true后
         *  InnodDB耗时：6118ms
         * @throws Exception
         */
        @Test
        public void testSaveByPreparedStatement() throws Exception {
            long begin = System.currentTimeMillis();
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement ps = null;
            for (int i = 0; i <= 3000; i++) {
                String sql = "INSERT INTO t_user(username,age) VALUES(?,?)";
                ps = conn.prepareStatement(sql);
                ps.setString(1,"aa");
                ps.setInt(2,i);
                ps.executeUpdate();
            }
            System.out.println(System.currentTimeMillis() - begin);
            JdbcUtil.close(conn,ps,null);
        }
        /**
         *  PreparedStatement 使用批量处理
         *  InnodDB耗时：6194ms
         *  添加rewriteBatchedStatements=true后
         *  InnodDB耗时：438ms
         * @throws Exception
         */
        @Test
        public void testSaveByPreparedStatementBatch() throws Exception {
            long begin = System.currentTimeMillis();
            Connection conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO t_user(username,age) VALUES(?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i <= 3000; i++) {
                ps.setString(1,"aa");
                ps.setInt(2,i);
                ps.addBatch(); //将SQL语句添加到批处理
                if(i % 300 == 0){
                    ps.executeBatch();//执行操作
                    ps.clearBatch();//清除操作
                    ps.clearParameters();//清除参数
                }
            }
            System.out.println(System.currentTimeMillis() - begin);
            JdbcUtil.close(conn,ps,null);
        }

**结论：**

MySQ数据库的模板引擎对批处理操作也有影响，参考文章：[MySQL的各种引擎归纳总结][MySQL]

MySQL数据库既不支持PreparedStatement的性能优化，也不支持JDBC的批量操作，但是MySQL在新的JDBC驱动中，通过设置参数支持批处理操作，所以，不管是单语句还是批处理操作，推荐使用PreparedStatement。

url=jdbc:mysql://localhost:3306/jdbcdemo?rewriteBatchedStatements=true

### 三、对 BLOB类型操作 ###

BLOB是可变的数据类型，一般在开发中，不会把二进制的文件(比如图片，视频和音频等)存放在数据库中。而是把文件的存储路径保存到数据库中。

java.nio.file.Files类：

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code>static long</code></td> 
           <td><code><a rel="nofollow">copy</a>(<a rel="nofollow">InputStream</a>&nbsp;in, <a rel="nofollow">Path</a>&nbsp;target, <a rel="nofollow">CopyOption</a>...&nbsp;options)</code> <p>将输入流中的所有字节复制到文件。</p> </td> 
          </tr> 
          <tr> 
           <td><code>static long</code></td> 
           <td><code><a rel="nofollow">copy</a>(<a rel="nofollow">Path</a>&nbsp;source, <a rel="nofollow">OutputStream</a>&nbsp;out)</code> <p>将文件中的所有字节复制到输出流。</p> </td> 
          </tr> 
          <tr> 
           <td><code>static <a rel="nofollow">Path</a></code></td> 
           <td><code><a rel="nofollow">copy</a>(<a rel="nofollow">Path</a>&nbsp;source, <a rel="nofollow">Path</a>&nbsp;target, <a rel="nofollow">CopyOption</a>...&nbsp;options)</code> <p>将文件复制到目标文件。</p> </td> 
          </tr> 
         </tbody> 
        </table>

PreparedStatement接口：

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">setBlob</a>(int&nbsp;parameterIndex, <a rel="nofollow">Blob</a>&nbsp;x)</code> <p>将指定的参数设置为给定的 <code>java.sql.Blob</code>对象。</p> </td> 
          </tr> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">setBlob</a>(int&nbsp;parameterIndex, <a rel="nofollow">InputStream</a>&nbsp;inputStream)</code> <p>将指定的参数设置为 <code>InputStream</code>对象。</p> </td> 
          </tr> 
          <tr> 
           <td><code>void</code></td> 
           <td><code><a rel="nofollow">setBlob</a>(int&nbsp;parameterIndex, <a rel="nofollow">InputStream</a>&nbsp;inputStream, long&nbsp;length)</code> <p>将指定的参数设置为 <code>InputStream</code>对象。</p> </td> 
          </tr> 
         </tbody> 
        </table>

ResultSet接口：

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code><a rel="nofollow">Blob</a></code></td> 
           <td><code><a rel="nofollow">getBlob</a>(int&nbsp;columnIndex)</code> <p>将该 <code>ResultSet</code>对象的当前行中指定列的值作为Java编程语言中的 <code>Blob</code>对象检索。</p> </td> 
          </tr> 
          <tr> 
           <td><code><a rel="nofollow">Blob</a></code></td> 
           <td><code><a rel="nofollow">getBlob</a>(<a rel="nofollow">String</a>&nbsp;columnLabel)</code> <p>将此 <code>ResultSet</code>对象的当前行中指定列的值作为Java编程语言中的 <code>Blob</code>对象检索。</p> </td> 
          </tr> 
         </tbody> 
        </table>

Blob接口：

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code><a rel="nofollow">InputStream</a></code></td> 
           <td><code><a rel="nofollow">getBinaryStream</a>()</code> <p>将此 <code>Blob</code>实例指定的 <code>BLOB</code>值作为流 <code>Blob</code> 。</p> </td> 
          </tr> 
          <tr> 
           <td><code><a rel="nofollow">InputStream</a></code></td> 
           <td><code><a rel="nofollow">getBinaryStream</a>(long&nbsp;pos, long&nbsp;length)</code> <p>返回包含部分 <code>Blob</code>值的 <code>InputStream</code>对象，从pos指定的字节开始，长度为长度字节。</p> </td> 
          </tr> 
         </tbody> 
        </table>

模拟BLOB类型存放一张图片

/**
         * 将磁盘文件保存到数据库
         * @throws Exception
         */
        @Test
        public void saveBLOB() throws Exception {
            String sql = "INSERT INTO t_user(username,age,image) VALUES(?,?,?)";
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"赵云2");
            ps.setInt(2,18);
            FileInputStream inputStream = new FileInputStream(new File("E:/java/赵云.jpg"));
            ps.setBlob(3,inputStream);
            ps.executeUpdate();
            JdbcUtil.close(conn, ps, null);
        }
    
        /**
         * 将数据库文件保存到磁盘
         * @throws Exception
         */
        @Test
        public void getBLOB() throws Exception {
            String sql = "select id,image from t_user where username = ?";
            Connection conn = JdbcUtil.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"赵云2");
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Blob image = rs.getBlob("image");
                //文件copy
                Files.copy(image.getBinaryStream(),Paths.get("E:/java/赵云image.jpg"));
            }
            JdbcUtil.close(conn, ps, rs);
        }

![20190909162842363.png][]    ![20190909162856912.png][]

### 四、获取自动生成的主键 ###

需求：新增一条记录，返回主键id

**1、Statement接口**

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code>static int</code></td> 
           <td><code><a rel="nofollow">NO_GENERATED_KEYS</a></code> <p>常数表示生成的密钥不应该可用于检索。</p> </td> 
          </tr> 
          <tr> 
           <td><code>static int</code></td> 
           <td><code><a rel="nofollow">RETURN_GENERATED_KEYS</a></code> <p>常数表示生成的密钥应该可用于检索。</p> </td> 
          </tr> 
         </tbody> 
        </table>

*   *  <table> 
         <tbody> 
          <tr> 
           <td style="width:116px;"><code>int</code></td> 
           <td><code><a rel="nofollow">executeUpdate</a>(<a rel="nofollow">String</a>&nbsp;sql, int&nbsp;autoGeneratedKeys)</code> <p>执行给定的SQL语句并用给定的标志来向驱动程序发出信号，以了解该 <code>Statement</code>对象产生的自动生成的密钥是否应该可用于检索。</p> </td> 
          </tr> 
          <tr> 
           <td style="width:116px;"><code><a rel="nofollow">ResultSet</a></code></td> 
           <td><code><a rel="nofollow">getGeneratedKeys</a>()</code> <p>检索由执行此 <code>Statement</code>对象而创建的任何自动生成的密钥。</p> </td> 
          </tr> 
         </tbody> 
        </table>

@Test
        public void testSaveByStatement() throws Exception {
            String sql = "INSERT INTO t_user(username,age) VALUES('aa',17)";
            Connection conn = JdbcUtil.getConnection();
            Statement st = conn.createStatement();
            //设置可以获取自动生成的主键
            st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = st.getGeneratedKeys();
            if(rs.next()){
                Long id = rs.getLong(1);//获取第一列 主键
                System.out.println(id); //2
            }
            JdbcUtil.close(conn, st, null);
        }

**2、PreparedStatement接口**

*   *  <table> 
         <tbody> 
          <tr> 
           <td><code><a rel="nofollow">PreparedStatement</a></code></td> 
           <td><code><a rel="nofollow">prepareStatement</a>(<a rel="nofollow">String</a>&nbsp;sql, int&nbsp;autoGeneratedKeys)</code> <p>创建一个默认的 <code>PreparedStatement</code>对象，该对象具有检索自动生成的密钥的能力。</p> </td> 
          </tr> 
         </tbody> 
        </table>

@Test
        public void testSaveByPreparedStatement() throws Exception {
            String sql = "INSERT INTO t_user(username,age) VALUES(?,?)";
            Connection conn = JdbcUtil.getConnection();
            //设置可以获取自动生成的主键
            PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "ss");
            ps.setInt(2, 18);
            ps.executeUpdate();
            //获取自动生成的主键
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                Long id = rs.getLong(1);//获取第一列 主键
                System.out.println(id); //3
            }
            JdbcUtil.close(conn, ps, null);
        }

站在前辈的肩膀上，每天进步一点点

ends~

[MySQL]: https://blog.csdn.net/chenruijia170707/article/details/80301064
[20190909162842363.png]: https://img-blog.csdnimg.cn/20190909162842363.png
[20190909162856912.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/56fb84142fb74ca391235abbb9587141.png