JDBC的批处理操作三种方式

SQL批处理是JDBC性能优化的重要武器,经本人研究总结,批处理的用法有三种。

 1 package lavasoft.jdbctest;
 2 
 3 import lavasoft.common.DBToolkit;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 import java.sql.Statement;
 9 
10 /**
11 * JDBC的批量操作三种方式
12 *
13 * @author leizhimin 2009-12-4 14:42:11
14 */
15 public class BatchExeSQLTest {
16 
17         public static void main(String[] args) {
18                 exeBatchStaticSQL();
19         }
20 
21         /**
22          * 批量执行预定义模式的SQL
23          */
24         public static void exeBatchParparedSQL() {
25                 Connection conn = null;
26                 try {
27                         conn = DBToolkit.getConnection();
28                         String sql = "insert into testdb.book (kind, name) values (?,?)";
29                         PreparedStatement pstmt = conn.prepareStatement(sql);
30                         pstmt.setString(1, "java");
31                         pstmt.setString(2, "jjjj");
32                         pstmt.addBatch();                     //添加一次预定义参数
33                         pstmt.setString(1, "ccc");
34                         pstmt.setString(2, "dddd");
35                         pstmt.addBatch();                     //再添加一次预定义参数
36                         //批量执行预定义SQL
37                         pstmt.executeBatch();
38                 } catch (SQLException e) {
39                         e.printStackTrace();
40                 } finally {
41                         DBToolkit.closeConnection(conn);
42                 }
43         }
44 
45         /**
46          * 批量执行混合模式的SQL、有预定义的,还有静态的
47          */
48         public static void exeBatchMixedSQL() {
49                 Connection conn = null;
50                 try {
51                         conn = DBToolkit.getConnection();
52                         String sql = "insert into testdb.book (kind, name) values (?,?)";
53                         PreparedStatement pstmt = conn.prepareStatement(sql);
54                         pstmt.setString(1, "java");
55                         pstmt.setString(2, "jjjj");
56                         pstmt.addBatch();    //添加一次预定义参数
57                         pstmt.setString(1, "ccc");
58                         pstmt.setString(2, "dddd");
59                         pstmt.addBatch();    //再添加一次预定义参数
60                         //添加一次静态SQL
61                         pstmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'");
62                         //批量执行预定义SQL
63                         pstmt.executeBatch();
64                 } catch (SQLException e) {
65                         e.printStackTrace();
66                 } finally {
67                         DBToolkit.closeConnection(conn);
68                 }
69         }
70 
71         /**
72          * 执行批量静态的SQL
73          */
74         public static void exeBatchStaticSQL() {
75                 Connection conn = null;
76                 try {
77                         conn = DBToolkit.getConnection();
78                         Statement stmt = conn.createStatement();
79                         //连续添加多条静态SQL
80                         stmt.addBatch("insert into testdb.book (kind, name) values ('java', 'java in aciton')");
81                         stmt.addBatch("insert into testdb.book (kind, name) values ('c', 'c in aciton')");
82                         stmt.addBatch("delete from testdb.book where kind ='C#'");
83                         stmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'");
84 //                        stmt.addBatch("select count(*) from testdb.book");                //批量执行不支持Select语句
85                         //执行批量执行
86                         stmt.executeBatch();
87                 } catch (SQLException e) {
88                         e.printStackTrace();
89                 } finally {
90                         DBToolkit.closeConnection(conn);
91                 }
92         }
93 }

注意:JDBC的批处理不能加入select语句,否则会抛异常:

1 ava.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().
2   at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1007)

本文出自 “熔 岩” 博客,请务必保留此出处http://lavasoft.blog.51cto.com/62575/238651

posted @ 2014-04-10 13:46  zhangyongjian  阅读(479)  评论(0编辑  收藏  举报