import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCBatDemo {
private GetConn getConn;
private Connection conn;
private PreparedStatement stmt ;
private ResultSet rs = null;
public JDBCBatDemo(){
getConn = new GetConn();
}
/**
* @param args
*/
public static void main(String[] args) {
JDBCBatDemo demo = new JDBCBatDemo();
// long start = System.currentTimeMillis();//开始的时间
// demo.creatBatInsert();
// long end = System.currentTimeMillis();//结束的时间
// System.out.println("批处理插入的时间是:"+(end - start));
long start = System.currentTimeMillis();//开始的时间
demo.delBat();
long end = System.currentTimeMillis();//结束的时间
System.out.println("批处理删除的时间是:"+(end - start));
}
/**
* 批处理添加数据
*/
public void creatBatInsert(){
conn = getConn.getConnection();
String sql = "insert into test values(?,?,?)";
try {
stmt = conn.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
stmt.setString(1,"name"+i);
stmt.setDate(2, new Date(System.currentTimeMillis()));
stmt.setInt(3, 1000);
//放到包里去
stmt.addBatch();
}
int[] arr = stmt.executeBatch();//执行批处理
System.out.println(arr);
for (int i = 0; i < arr.length; i++) {
System.out.println(arr[i]);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
GetConn.free(stmt, conn, rs);
}
}
/**
* 批处理删除记录
*/
public void delBat(){
conn = getConn.getConnection();
String sql = "delete from test";
try {
stmt = conn.prepareStatement(sql);
stmt.addBatch();
stmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}finally{
GetConn.free(stmt, conn, rs);
}
}
}
public static void main(String[] args) ...{
Class.forName("sqldriver");
Connection conn = DriverManager.getConnection(URL,USER,PASS)
//无参,添加案例
Statement stmt = conn.createStatement();
stmt.addBatch("insert into authors(firstName,lastName) values('fegor','hack')");
stmt.addBatch("insert into authors(firstName,lastName) values('fegors','hacks')");
stmt.addBatch("insert into authors(firstName,lastName) values('fegorsr','hacksr')");
stmt.executeBatch();
stmt.close();
conn.close();
//带参,添加案例
PreparedStatement ps=conn.prepareStatement("insert into authors(firstName,lastName) values(?,?)");
ps.setString(1,"nihao1");//设置参数
ps.setString(2,"wohao1");
ps.addBatch(); //把语句加入批处理队列
ps.setString(1,"nihao2");
ps.setString(2,"wohao2");
ps.addBatch();
ps.setString(1,"nihao3");
ps.setString(2,"wohao3");
ps.addBatch();
ps.executeBatch(); //执行批处理
ps.close(); //最后关闭
conn.close();
}
//总结:
//1.stmt=conn.createStatement()这个后面的括号里面一般没有东西!!!
//2.pstmt=conn.prepareStament("insert into authors(firstName,lastName) values(?,?)")
//这个后面一定有sql语句,并且里面的参数是用?表示的。
//然后用pstmt.setString(1,"fegor")来设定它的值,1表示第1个问号的位置,"fegor",表示我们要加的值
//最后用pstmt.executeUpdate()去执行。
///
SQL批处理是JDBC性能优化的重要武器,经本人研究总结,批处理的用法有三种。
package lavasoft.jdbctest;
import lavasoft.common.DBToolkit;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBC的批量操作三种方式
*
* @author leizhimin 2009-12-4 14:42:11
*/
public class BatchExeSQLTest {
public static void main(String[] args) {
exeBatchStaticSQL();
}
/**
* 批量执行预定义模式的SQL
*/
public static void exeBatchParparedSQL() {
Connection conn = null;
try {
conn = DBToolkit.getConnection();
String sql = "insert into testdb.book (kind, name) values (?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "java");
pstmt.setString(2, "jjjj");
pstmt.addBatch(); //添加一次预定义参数
pstmt.setString(1, "ccc");
pstmt.setString(2, "dddd");
pstmt.addBatch(); //再添加一次预定义参数
//批量执行预定义SQL
pstmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBToolkit.closeConnection(conn);
}
}
/**
* 批量执行混合模式的SQL、有预定义的,还有静态的
*/
public static void exeBatchMixedSQL() {
Connection conn = null;
try {
conn = DBToolkit.getConnection();
String sql = "insert into testdb.book (kind, name) values (?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "java");
pstmt.setString(2, "jjjj");
pstmt.addBatch(); //添加一次预定义参数
pstmt.setString(1, "ccc");
pstmt.setString(2, "dddd");
pstmt.addBatch(); //再添加一次预定义参数
//添加一次静态SQL
pstmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'");
//批量执行预定义SQL
pstmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBToolkit.closeConnection(conn);
}
}
/**
* 执行批量静态的SQL
*/
public static void exeBatchStaticSQL() {
Connection conn = null;
try {
conn = DBToolkit.getConnection();
Statement stmt = conn.createStatement();
//连续添加多条静态SQL
stmt.addBatch("insert into testdb.book (kind, name) values ('java', 'java in aciton')");
stmt.addBatch("insert into testdb.book (kind, name) values ('c', 'c in aciton')");
stmt.addBatch("delete from testdb.book where kind ='C#'");
stmt.addBatch("update testdb.book set kind = 'JAVA' where kind='java'");
// stmt.addBatch("select count(*) from testdb.book"); //批量执行不支持Select语句
//执行批量执行
stmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBToolkit.closeConnection(conn);
}
}
}
注意:JDBC的批处理不能加入select语句,否则会抛异常:
java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1007)
本文出自 “熔 岩” 博客,请务必保留此出处http://lavasoft.blog.51cto.com/62575/238651