PreparedStatement的Batch功能

Update大量的数据时, 先Prepare一个INSERT语句,再多次的执行, 会导致很多次的网络连接.
要减少JDBC的调用次数改善性能, 可以使用PreparedStatement的AddBatch()方法一次性发送多个查询给数据库.

例如, 下面的例子.

例 1: 多次执行PreparedStatement，多次数据库请求（网络请求）

PreparedStatement ps = conn.prepareStatement(  
   "INSERT into employees values (?, ?, ?)");  
  
for (n = 0; n < 100; n++) {    
  ps.setString(name[n]);  
  ps.setLong(id[n]);  
  ps.setInt(salary[n]);  
  ps.executeUpdate();  
}  

例 2: 使用Batch，以此请求执行多条

PreparedStatement ps = conn.prepareStatement(  
   "INSERT into employees values (?, ?, ?)");  
  
for (n = 0; n < 100; n++) {    
  ps.setString(name[n]);  
  ps.setLong(id[n]);  
  ps.setInt(salary[n]);  
  ps.addBatch();  
}  
ps.executeBatch();  

在例 1中, PreparedStatement被用来多次执行INSERT语句. 在这里, 执行了100次INSERT操作, 共有101次网络往返.

其中
1次往返是预储PreparedStatement,
另外100次往返执行每个迭代.

在例2中, 当在100次INSERT操作中使用addBatch()方法时, 只有两次网络往返.

1次往返是预储PreparedStatement,
另一次是执行batch命令.

虽然Batch命令会用到更多的数据库的CPU周期, 但是通过减少网络往返，性能得到提高.

JDBC的性能最大的增进是减少JDBC驱动与数据库之间的网络通讯.次数

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

• 同样mysql 5.5.28 批量执行的数据最大限度是多少不清楚，但试了1w,2w,3w 都没问题，
• 记得在url 后面添加：rewriteBatchedStatements=true 表示批量插入，
• 如果不添加的话,即使使用addbatch() ,executeBatch() 在后台入库的地方还是不会一次请求入库,而是多次请求入库。

preparestatement 防止sql注入

在JDBC应用中,如果已经是稍有水平开发者,就应该始终以PreparedStatement代替Statement.
也就是说,在任何时候都不要使用Statement.

• 基于以下的原因:

1、代码的可读性和可维护性.
虽然用PreparedStatement来代替Statement会使代码多出几行,
但这样的代码,无论从可读性,还是可维护性上,都比直接用Statement的代码高很多档次:

stmt.executeUpdate("insert into tb_name (col1,col2,col2,col4) values ('"+var1+"','"+var2+"',"+var3+",'"+var4+"')");

perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)");
perstmt.setString(1,var1);perstmt.setString(2,var2);perstmt.setString(3,var3);perstmt.setString(4,var4);
perstmt.executeUpdate();

2、PreparedStatement尽最大可能提高性能.
每一种数据库都会尽最大努力对预编译语句提供最大的性能优化.
因为预编译语句有可能被重复调用.所以语句在被DB的编译器编译后的执行代码被缓存下来,
那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中(相当于一个涵数)就会得到执行.

这并不是说只有一个 Connection中多次执行的预编译语句被缓存,
而是对于整个DB中,只要预编译的语句语法和缓存中匹配.
那么在任何时候就可以不需要再次编译而可以直接执行.

而statement的语句中,即使是相同一操作,而由于每次操作的数据不同所以使整个语句相匹配的机会极小,几乎不太可能匹配.
比如:

insert into tb_name (col1,col2) values ('11','22');
insert into tb_name (col1,col2) values ('11','23');

即使是相同操作,但因为数据内容不一样,所以整个个语句本身不能匹配,没有缓存语句的意义.

• 事实是没有数据库会对普通语句编译后的执行代码缓存.这样每执行一次都要对传入的语句编译一次.

当然并不是所以预编译语句都一定会被缓存,
数据库本身会用一种策略,
比如:使用频度等因素来决定什么时候不再缓存已有的预编译结果.以保存有更多的空间存储新的预编译语句.

3、最重要的一点是极大地提高了安全性.
恶义SQL语法.

String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";

如果我们把[’ or ‘1’ = '1]作为varpasswd传入进来.用户名随意,看看会成为什么?

select * from tb_name = '随意' and passwd = '' or '1' = '1';

因为’1’='1’肯定成立,所以可以任何通过验证.

• 更有甚者:把[';drop table tb_name;]作为varpasswd传入进来,

则:

select * from tb_name = '随意' and passwd = '';
drop table tb_name;

• 有些数据库是不会让成功的,但也有很多数据库就可以使这些语句得到执行.

而如果使用预编译语句.传入的任何内容就不会和原来的语句发生任何匹配的关系.

• 前提是数据库本身支持预编译,但上前可能没有什么服务端数据库不支持编译了,只有少数的桌面数据库,就是直接文件访问的那些

只要全使用预编译语句,就用不着对传入的数据做任何过虑.
而如果使用普通的statement, 有可能要对drop,;等做费尽心机的判断和过虑.

总结

建议使用preparestatement 原因有：

• 上面说了 如果sql中只有数值在变则效率高

• preparestatement 具有防sql注入

• 代码可读性比较好

实例：

• Statement和PreparedStatement的区别就不多废话了,直接说PreparedStatement最重要的addbatch()结构的使用.

PreparedStatement 的addBatch和executeBatch实现批量添加

1.建立链接

   Connection    connection =getConnection();

2.不自动 Commit (瓜子不是一个一个吃,全部剥开放桌子上,然后一口舔了)

connection.setAutoCommit(false);   

3.预编译SQL语句,只编译一回哦,效率高啊.(发明一个剥瓜子的方法,以后不要总想怎么剥瓜子好.就这样剥.)

PreparedStatement statement = connection.prepareStatement("INSERT INTO TABLEX VALUES(?, ?)");   

4.来一个剥一个,然后放桌子上

//记录1
statement.setInt(1, 1); 
statement.setString(2, "Cujo"); 
statement.addBatch();   

//记录2
statement.setInt(1, 2); 
statement.setString(2, "Fred"); 
statement.addBatch();   

//记录3
statement.setInt(1, 3); 
statement.setString(2, "Mark"); 
statement.addBatch();   

//批量执行上面3条语句. 一口吞了,很爽
int [] counts = statement.executeBatch();   

//Commit it 咽下去,到肚子(DB)里面
connection.commit();

实例：批量添加

public static void insertData(List<Map<String,String>> list,Logger log){  
    //获取的数据  
    List <Map<String,String>> nlist= list;  
    String upsql="update   hrd_staff  set position =?  where id=?";  
    Iterator<Map<String,String>> iter= nlist.iterator();  
    Connection con= Utils.getCon();  
    int count=0;  
    try {  
        //在皮脸添加的时候注意事务提交方式  
        con.setAutoCommit(false);  
        //PreparedStatement方法的使用  
        PreparedStatement pstm = con.prepareStatement(upsql);  
        while(iter.hasNext()){  
            count++;  
            Map<String,String> map= iter.next();  
            String jon_name= map.get("job_name");  
            String uid= map.get("uid");  
            pstm.setString(1,jon_name);  
            pstm.setString(2,uid);  
            //添加到缓存中  
            pstm.addBatch();  
            // 如果数据量很大，不能一次性批量添加所以我们要分批次添加，这里就是300条一次  
            if(count%300==0){  
                //持久化  
                int []res=pstm.executeBatch();  
                //提交事务，持久化数据  
                con.commit();  
                pstm.clearBatch();  
                log.info("300整除插入结果: "+res.length);  
            }  
        }  
        //小于300条的在这里持久化  
        int []ress= pstm.executeBatch();  
        //事务提交持久化  
        con.commit();  
        pstm.clearBatch();  
        log.info("插入数据结果："+ress.length);  
    } catch (SQLException e) {  
        try {  
            con.rollback();  
        } catch (SQLException e1) {  
            // TODO Auto-generated catch block  
            e1.printStackTrace();  
        }  
        e.printStackTrace();  
    }finally{  
        try {  
            if(null!=con){  
            con.close();  
            con.setAutoCommit(true);  
            }  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
}  

这里除了下面说的url中的批量设置外，也要注意事务的设置，不能设置为自动提交，要批量添加后在提交事务

addBatch() 就是把你的处理内容添加到批处理单元中。即添加到了batch中。你可以循环加入很多，数据库都不会处理，直到调用如下代码executeBatch() 此时，数据库把刚才加到batch中的命令批量处理。

使用批量插入的好处：
当在100次INSERT操作中使用addBatch()方法时, 只有两次网络往返.

• 1次往返是预储statement,
• 另一次是执行batch命令.

虽然Batch命令会用到更多的数据库的CPU周期, 但是通过减少网络往返，性能得到提高.

记住, JDBC的性能最大的增进是减少JDBC驱动与数据库之间的网络通讯.
如果没有使用批处理则网络往返101次这样会耗很多时间，自然效率也就一般

这里要注意：在mysql 下使用批量执行的时候要在，url 后面添加手动设置支持批量添加 实例如下：

 String url="jdbc:mysql://localhost:3306/music?rewriteBatchedStatements=true";

// 默认情况下rewriteBatchedStatements 的值为false 也就是批量添加功能是关闭的，如果使用则要手动开启！

还有就是事务的设置，不能使自动提交，要批量添加后才提交！！！