preparedstatement作用和使用（史上最全PrepareStatement与Statement性能比较测试汇总）

前言：想要了解高效的Preparestatement是如何规范使用的，看这里就足够了！

你了解sql的执行过程？

大家都熟悉sql的开发，但是大家却不熟悉数据库是如何执行sql的，数据库拿到一条数据的插入或者更新的sql后，就会先更具sql语句的语法规则对sql语句进行编译，就像每个机器对汇编语言进行编译一样，会变语言会被编译程机器码，而sql语句会被编译程存储过程。就像机器码能被机器立马执行，存储过程也能被数据库系统立马执行。

PrePareStatement是能做到预编译的，即将sql语句编译成存储过程，而批量放入数据是将数据插入到存储过程中的相应位置上去，即大量的数据存入只需要进行一次sql语句的编译，而sql语句的编译过程是消耗时间的。

Statement则每次执行都会对sql语句进行编译成存储过程，因此它执行sql就会消耗大量的时间用来做sql语句的编译，没有预处理，每次都是从0开始执行SQL。

使用PrePareStatement和Statement分别往数据库中插入相同数量的数据，比较它们的耗时情况。

//statement测试代码 public static void testStatement(Connection connection) throws SQLException{ long start = System.currentTimeMillis(); Statement stam = connection.createStatement(); for(int i =1 ; i < 100000 ; i) { String sql = "insert into Test_preparement_2(id ,name) values(" i ",'test')"; stam.execute(sql); } long end = System.currentTimeMillis(); System.out.println("statement的插入耗时:" (end-start) "ms"); }

preparedstatement测试代码块。

public static void testPreparedStatement(Connection connection)throws SQLException{ long start = System.currentTimeMillis(); PreparedStatement pstmt = connection.prepareStatement("insert into Test_preparement_3(id ,name) values(?,?)"); for (int i = 1 , idx = 1; i < 100000 ; i) { try { for(int j = 1 ; j < 1000 ; j , idx) { pstmt.setInt(1, idx); pstmt.setString(2, "test"); pstmt.addBatch(); } pstmt.executeBatch(); connection.commit(); }catch (Exception e) { // TODO: handle exception System.out.println("error executeBatch"); connection.rollback(); pstmt.clearBatch(); } } long end = System.currentTimeMillis(); System.out.println("preparedstatement的插入耗时:" (end-start) "ms"); }

测试结果对比

数据数量（条）

Oracel 10G的JDBC Driver限制最大Batch size是16383条，如果addBatch超过这个限制，那么executeBatch时就会出现“无效的批值”（Invalid Batch Value） 异常。因此在如果使用的是Oracle10G，在此bug减少前，Batch size需要控制在一定的限度。

oracel 11G的JDBC是没有数量限制的，但应该有数据量大小的限制。

mysql 在url 后面添加：rewriteBatchedStatements=true 表示批量插入，如果不添加的话即使使用addbatch() ,executeBatch() 在后台入库的地方还是不会一次请求入库而是多次请求入库。

问题：有些时候，我们会对多张表同时进行批量的更新或者插入，当我们使用多个PrePareStatement语句的时候，如果因为一个PrePareStatement执行出错，会导致多张表的数据不一致以及数据批量提交的影响范围如何隔离？

准备数据：

，总共有999条数据，两张表Test_preparement_2和Test_preparement_3

更新操作：将所有数据的name属性更新为tes，name字段长度为4。

错误演练：设置更新第30条数据的时候更新的name字段数据为：XXXXXX。超过长度4，更新失败。

未做任何规范处理的代码片段

/* * 测试提交过程中字段长度出错，是否还会继续执行接下来的batch */ public static void testUpdateBatch(Connection connection) throws SQLException{ connection.setAutoCommit(false); PreparedStatement pstmt = connection.prepareStatement("update Test_preparement_2 set name = ? where id = ?"); PreparedStatement pstmt_1 = connection.prepareStatement("update Test_preparement_3 set name = ? where id = ?"); for (int i = 1 , idx = 1; i < 10 ; i) { try { System.out.println("第" i "个批次---------------------------------"); for(int j = 1 ; j < 15 ; j , idx) { System.out.println(idx " " i " " j); if(idx==30) pstmt.setString(1, "XXXXXX"); else pstmt.setString(1, "tes"); pstmt.setInt(2, idx); pstmt.addBatch(); if(idx==30) pstmt_1.setString(1, "XXXXXX"); else pstmt_1.setString(1, "tes"); pstmt_1.setInt(2, idx); pstmt_1.addBatch(); } pstmt.executeBatch(); pstmt_1.executeBatch(); connection.commit(); }catch (Exception e) { // TODO: handle exception System.out.println("error executeBatch"); } } }

Test_preparement_2表的数据如下图所示，30-42所有数据都没有更新成功，而这些数据是在第三个批次里的数据。Test_preparement_3表的数据是30-56所有数据没有更新成功，这些数据是在第三和第四批次里的数据。

结论：代码虽然catch了批次执行的错误，但是却不能保证两张表的最终数据的一致性，第二张表数据收到影响的范围明显更大。

正确的处理错误的逻辑和结果应该是：第30编号数据影响的应该就只是第三批次的数据，不能让它影响接下来的批次，且第三批次所有的数据应该全部回滚并将收到影响的数据打印到错误日志当中。因此正确的处理代码如下：

/* * 测试提交过程中字段长度出错，是否还会继续执行接下来的batch */ public static void testUpdateBatch(Connection connection) throws SQLException{ connection.setAutoCommit(false); PreparedStatement pstmt = connection.prepareStatement("update Test_preparement_2 set name = ? where id = ?"); PreparedStatement pstmt_1 = connection.prepareStatement("update Test_preparement_3 set name = ? where id = ?"); for (int i = 1 , idx = 1; i < 10 ; i) { try { System.out.println("第" i "个批次---------------------------------"); for(int j = 1 ; j < 15 ; j , idx) { System.out.println(idx " " i " " j); if(idx==30) pstmt.setString(1, "XXXXXX"); else pstmt.setString(1, "tes"); pstmt.setInt(2, idx); pstmt.addBatch(); if(idx==30) pstmt_1.setString(1, "XXXXXX"); else pstmt_1.setString(1, "tes"); pstmt_1.setInt(2, idx); pstmt_1.addBatch(); } pstmt.executeBatch(); pstmt_1.executeBatch(); connection.commit(); }catch (Exception e) { // TODO: handle exception System.out.println("error executeBatch"); connection.rollback(); pstmt.clearBatch(); pstmt_1.clearBatch(); } } }

查看两张表影响的数据范围，下图分别为Test_preparement_2和Test_preparement_3

如上面三个图所示，两张表的影响范围都是第三个批次，两张表的数据都保证了一致性。