利用jdbc操作数据库——prepareStatement和Statement的比较以及利用batch模式提高效率的心得

1.prepareStatement   vs  statement

(1)prepareStatement预编译SQL语句，批处理效率高

什么是预编译，好处？（参考https://blog.csdn.net/Marvel__Dead/article/details/69486947）
当客户发送一条SQL语句给服务器后，服务器总是需要校验SQL语句的语法格式是否正确，然后把SQL语句编译成可执行的函数，最后才是执行SQL语句。其中校验语法，和编译所花的时间可能比执行SQL语句花的时间还要多。
注意：可执行函数存储在MySQL服务器中，并且当前连接断开后，MySQL服务器会清除已经存储的可执行函数。
如果我们需要执行多次insert语句，但只是每次插入的值不同，MySQL服务器也是需要每次都去校验SQL语句的语法格式，以及编译，这就浪费了太多的时间。如果使用预编译功能，那么只对SQL语句进行一次语法校验和编译，所以效率要高。

看个例子：
statement:

stmt = conn.createStatement();
//循环
for(){
    stmt.executeUpdate(sql);
}

prepareStatement：

ps = conn.prepareStatement(sql);
//循环
for(){
    ps.setString(1,"");
    ps.setString(2,"");
    //...
    ps.executeUpdate();
}

对于prepareStatement，SQL 语句被预编译并存储在 PreparedStatement 对象中
prepareStatement只需初始化时传语句参数，之后利用setXXXX方法传参即可
对于Statement，批量处理SQL语句时，每次都需要校验语法，然后编译

（2）prepareStatement避免了拼接语句，代码可读性好
Statement写sql语句有时会因为字段值本身存在单引号而出错
prepareStatement利用的赋值方法可有效避免而且代码简洁，易于维护

（3）prepareStatement防止sql注入，安全性好
sql注入就是利用sql语句的漏洞来入侵，举个场景
在登录时，需要输入用户名和密码，经Statement拼接sql语句去数据库中查询校验
当用户输入用户名为：‘ or true or ’时，拼接成的sql语句为：

select * from user where name = ' ' or true or ' ' and password = ' '

仍可以正常登录，存在安全问题。但prepareStatement采用set方法传参，就不会出现这种问题

（4）注意：
如果只是执行单次查询语句，不涉及批量处理时，可以使用statement，笔者测试时，statement速度略快于prepareStatement，后者第一次执行的消耗还是不小的。

 

2.prepareStatement  vs  prepareStatement with batch

数据库操作提高效率，核心在于减少数据库驱动与数据库之间的网络请求，batch模式则可以批量的提交sql语句，减少网络请求。而且batchSize的大小对速度也有影响
示例代码如下：

conn.setAutoCommit(false);
for(int i=0;...){
    ps.setXXX()
    ps.addBatch();
    if(i%batchSize==0){
        ps.executeBatch();
        conn.commit();
    }
}
ps.executeBatch();
conn.commit();

注意：使用batch时，一定要设置setAutoCommit(false),采用手动提交事务，否则经笔者测试，只加入batch在效率提升上效果甚微。
setAutoCommit(true)（默认）：sql命令的提交（commit）由驱动程序负责，默认一条SQL就是一个单独事务，执行完自动提交，无法回滚事务 
setAutoCommit(false)：sql命令的提交由应用程序负责，程序必须调用commit或者rollback方法
除了提高效率，手动提交事务则可以充分利用事务回滚能力，当想把多个操作作为一个整体执行时，设置为false，通过try/catch捕获异常，然后手动进行事务回滚，充分体现了事务的原子性

重构的代码如下：

try {
    conn.setAutoCommit(false);
    if(ps!=null){
        ps.clearParameters();
        ps.clearBatch();
    }
    ps = conn.prepareStatement(sql);
    //TODO
    /*for(int i=0;i<t.size();i++){
        ps.setString(1,"");
        ps.setString(2,"");
        ps.addBatch();
        if(i%batchSize==0){
            ps.executeBatch();
            conn.commit();
        }
    }*/
    ps.executeBatch();
    conn.commit();
} catch (SQLException e) {
    try {
        conn.rollback();
        System.out.println("更新出错,事务已回滚！");
    } catch (SQLException e1) {
        System.out.println("更新出错,事务未回滚！");
        e1.printStackTrace();
    }
    e.printStackTrace();
} finally {
    closeDB();
    System.out.println("连接关闭！");
}

3.prepareStatement with batch  vs  prepareStatement with batch + rewriteBatchedStatements=true

在数据库url参数中加入“rewriteBatchedStatements=true”，示例如下：

String dbUrl =  "jdbc:mysql://localhost:3306/User?useUnicode=true&characterEncoding =utf-8&rewriteBatchedStatements

=true";

下面这段引用自：https://blog.csdn.net/tolcf/article/details/52102849#commentBox

MySQL Jdbc驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库

当rewriteBatchedStatements=true后，对delete和update，驱动所做的事就是把多条sql语句累积起来再一次性发出去；
例如：delete from t where id = 1; delete from t where id = 2; delete from t where id = 3
update t set … where id = 1; update t set … where id = 2; update t set … where id = 3
而对于insert，驱动则会把多条sql语句重写成一条sql语句，然后再发出去
例如：insert into t (…) values (…) , (…), (…)

需要注意的是，当batchSize <= 3时，驱动会一条一条地执行SQL，所以针对较大的batch会有提升效果

总结：
（1）对于单次查询，可以使用Statement

（2）对于批量操作（尤其是insert时），使用prepareStatement效率高，经测试，最优组合为
prepareStatement+batch+setAutoCommit(false)+rewriteBatchedStatements=true