JDBC五：批量操作的四层优化

批量操作

导读

1. 预编译优势

   DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不与要再编译，只要将参数直接传入编译过的语句中就会得到执行。

2. Statement语句中，无法进行预编译，每次执行sql语句，都会进行语法检查、语义检查、再翻译成二进制命令、缓存等操作，对于批量操作极为浪费资源

3. Batch方法及将AutoCommit的默认值设为false，都是为了减少PerparedStatement与数据库的交互次数

一、批量操作层次一

1. 使用Statement，没有预编译，会进行多次次的语法检查等操作，效率低

2. 插入20,000条数据所用时间为：22189ms

   @Test
       public void testStatement() {
           Long start = System.currentTimeMillis();
           Connection conn = null;
           Statement st = null;
           try {
               conn = JdbcUtils.getConnection();
               st = conn.createStatement();
               for (int i = 0; i < 20000; i++) {
                   // Statement处理sql语句时，会用到拼串
                   String sql = "insert into customers(cust_name) values('name_" + i + "')";
                   st.execute(sql);
               }
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JdbcUtils.closeResource(conn,st);
           }
           Long end = System.currentTimeMillis();
           // Statement插入20000条数据所用时间为：22189
           System.out.println("Statement插入20000条数据所用时间为：" + (end - start));
       }
   

二、批量操作层次二

1. 使用PreparedStatement，可以进行预编译，不用重复进行语法检查、语义检查等操作，但仍然需要和数据库进行N次交互

2. 插入20,000条数据操作所需时间：22164ms

   @Test
       public void testPreparedStatement2() {
           Long start = System.currentTimeMillis();
           Connection conn = null;
           PreparedStatement ps = null;
           try {
               conn = JdbcUtils.getConnection();
               String sql = "insert into customers(cust_name) values(?)";
               ps = conn.prepareStatement(sql);
               for (int i = 0; i < 20000; i++) {
                   ps.setObject(1,"name_" + i);
                   // 如果execute()放在循环外，则只会执行for循环的最后一次结果，因为setObject方法的参数索引值一直为1
                   ps.execute();
               }
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JdbcUtils.closeResource(conn,ps);
           }
           Long end = System.currentTimeMillis();
           // PreparedStatement普通预编译执行插入20000条数据操作所需时间：22164
           System.out.println("PreparedStatement普通预编译执行插入20000条数据操作所需时间：" + (end - start));
       }
   

三、批量操作层次三

1. PreparedStatement使用了addBatch()、executeBatch()、clearBatch()方法，可以形成一定的执行缓存，减少与数据库的交互，极大提升效率

2. 需要在配置文件jdbc.properties中，在url字符串后增加 ?rewriteBatchedStatements=true ，确使Batch方法可用，jar包需在5.1.37之上

3. 插入20,000条数据的时间为359ms

   @Test
       public void testPreparedStatement3() {
           Long start = System.currentTimeMillis();
           Connection conn = null;
           PreparedStatement ps = null;
           try {
               conn = JdbcUtils.getConnection();
               String sql = "insert into customers(cust_name) values(?)";
               ps = conn.prepareStatement(sql);
               for (int i = 0; i < 1000000; i++) {
                   ps.setObject(1,"name_" + i);
                   // 添加需要批量处理的sql语句或参数
                   ps.addBatch();
                   // 相当于建立了一个容量为500的缓存池
                   if (i % 500 == 0) {
                       // 一次性处理缓存池内的sql，可以减少于数据库的交互
                       ps.executeBatch();
                       // 清空缓存的数据
                       ps.clearBatch();
                   }
               }
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JdbcUtils.closeResource(conn,ps);
           }
           Long end = System.currentTimeMillis();
           // PreparedStatement使用Batch执行插入20000条数据操作所需时间：359
           System.out.println("PreparedStatement使用Batch执行插入20000条数据操作所需时间：" + (end - start));
       }
   

四、批量操作层次四

1. 则使用Batch的基础上，将自动提交AutoCommit的默认值设置为false，使ps与数据库的交互只有一次，则效率就更高了

2. DML的提交及回顾问题参考博文：MySQL十： DDL (数据库/数据表的增、查、改、删)

3. 插入1,000,000条数据使用时间为5345ms

    @Test
       public void testPreparedStatement4() {
           Long start = System.currentTimeMillis();
           Connection conn = null;
           PreparedStatement ps = null;
           try {
               conn = JdbcUtils.getConnection();
               // 设置DML操作不会自动提交，则可以使ps在使用Batch高效的全部处理完sql后，再一次性与数据库交互
               conn.setAutoCommit(false);
               String sql = "insert into customers(cust_name) values(?)";
   
               ps = conn.prepareStatement(sql);
               for (int i = 0; i < 1000000; i++) {
                   ps.setObject(1,"name_" + i);
                   // 添加需要批量处理的sql语句或参数
                   ps.addBatch();
                   // 相当于建立了一个容量为500的缓存池
                   if (i % 500 == 0) {
                       // 一次性处理缓存池内的sql，可以减少于数据库的交互
                       ps.executeBatch();
                       // 清空缓存的数据
                       ps.clearBatch();
                   }
               }
               // 在ps使用Batch高效的全部处理完sql后，一次性与数据库交互
               conn.commit();
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               JdbcUtils.closeResource(conn,ps);
           }
           Long end = System.currentTimeMillis();
           // PreparedStatement更改commit后执行插入1000000条数据操作所需时间：5345
           System.out.println("PreparedStatement更改commit后执行插入1000000条数据操作所需时间：" + (end - start));
       }