package book.database;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 执行一批SQL执行
* @author wuhailin
*
*/
public class Batch {
/**
* 判断数据库是否支持批处理
* @param con
* @return
*/
public static boolean supportBatch(Connection con){
try{
//得到数据库的元数据
DatabaseMetaData md=con.getMetaData();
return md.supportsBatchUpdates();
}catch(SQLException e){
e.printStackTrace();
}
return false;
}
/**
* 执行一批SQL语句
* @param con 数据库的连接
* @param sqls 待执行的SQL数组
* @return
*/
public static int[] goBatch(Connection con,String[] sqls){
if(sqls==null){
return null;
}
Statement sm=null;
try{
sm=con.createStatement();
for(int i=0;i<sqls.length;i++){
//将所有的SQL语句添加到Statement
sm.addBatch(sqls[i]);
}
//一次执行多条SQL语句
return sm.executeBatch();
}catch(SQLException e){
e.printStackTrace();
}finally{
OperateDB.closeStatement(sm);
}
return null;
}
public static void main(String[] args)throws ClassNotFoundException,SQLException{
String dbName="SGCC";
String tableName="student_basic";
String userName="sgccadmin";
String password="sgccadmin";
String[] sqls=new String[3];
sqls[0]="update student_basic set score=95 where ";
sqls[1]="insert into student_basic(ID,name,age,score,description) values(1,'lisi',17,78,'The name is bi')";
sqls[2]="delete from student_basic where ";
Connection con=null;
try{
//获得数据库的连接
con=DBConnector.getOracleConnection(null,null,null, dbName, userName, password);
boolean supportBatch=Batch.supportBatch(con);
System.out.println("支持Batch?"+supportBatch);
if(supportBatch){
//执行一批SQL语句
int[] results=Batch.goBatch(con, sqls);
//分析执行的结果
for(int i=0;i<sqls.length;i++){
if(results[i]>=0){
System.out.println("语句:"+sqls[i]+" 执行成功,影响了"+results[i]+"行数据");
}else if(results[i]==Statement.SUCCESS_NO_INFO){
System.out.println("语句:"+sqls[i]+"执行成功,影响的行数未知");
}else if(results[i]==Statement.EXECUTE_FAILED){
System.out.println("语句:"+sqls[i]+"执行失败");
}
}
}
}catch(ClassNotFoundException e1){
throw e1;
}catch(SQLException e2){
throw e2;
}
finally{
OperateDB.closeConnection(con);
}
}
}