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
