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

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

1.PrePareStatement和Statement原理比较

你了解sql的执行过程？

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

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

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

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

2.PrePareStatement和Statement性能比较

使用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"); }

测试结果对比

数据数量（条）

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

3.PrePareStatement和Statement在oracle与mysql库的使用区别

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() 在后台入库的地方还是不会一次请求入库而是多次请求入库。

4.多个PrePareStatement语句实现批量提交的开发规范

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

准备数据：

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

，总共有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所有数据没有更新成功，这些数据是在第三和第四批次里的数据。

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