jdbc批量插入, 一定要关闭事物的自动提交, 否则效率会非常慢, 对于插入出现异常的一批数据, 暂时做整体回滚操作!
package com.thinkive.import_data.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;
import oracle.sql.DATE;
import org.apache.log4j.Logger;
import com.thinkive.import_data.bean.Bean;
/**
* @desc:
* @author: zona
* @time: 2016年11月29日 下午12:33:41
*/
public class DBHelp {
private static Logger logger = Logger.getLogger(DBHelp.class);
private static Connection conn;
private static PreparedStatement ps;
private static SimpleDateFormat format = new SimpleDateFormat(Constants.DATE_FORMATE_yyyyMMddHHmmss);
public static final String SQL_INSTALL_IDNO_THIRD = "INSERT INTO t_stkkh_idno_third (ID, IDNO, NAME, VERIFY_RESULT, VERIFY_TIME, CHANNEL_TYPE, CREATE_TIME, UPDATE_TIME) VALUES (seq_stkkh_idno_third.nextval, ?,?,?,?,?,?,?)";
/**
* 获取连接对象
* @return 连接对象
* @throws Exception
*/
private static Connection getConnection() throws Exception {
// 在项目根目录下配置oracle.properties文件, 配置url,username,password; ResourceBundle jdk1.7中可以用来读取配置文件的类
ResourceBundle rb = ResourceBundle.getBundle("oracle");
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = rb.getString("url");
conn = DriverManager.getConnection(url, rb.getString("username"), rb.getString("password"));
return conn;
}
/**
* 执行增删改操作
* @param sql语句
* @param beans封装bean的集合
* @param pattern 日期格式
* @return int 影响行数
* @throws Exception
*/
public static int[] executeUpate(String sql,List<Bean> beans, String pattern) throws Exception{
if(pattern !=null && pattern.length() > 0) {
format = new SimpleDateFormat(pattern);
}
if(conn == null) {
conn=getConnection();
}
// 关闭事物自动提交
conn.setAutoCommit(false);
try {
ps=conn.prepareStatement(sql);
//注入参数
if(beans!=null && beans.size() > 0){
int k = 1;
for (Bean bean : beans) {
java.sql.Timestamp date = new java.sql.Timestamp(new Date().getTime());
ps.setObject(k++, bean.getIdno());
ps.setObject(k++, bean.getName());
ps.setObject(k++, bean.getVerify_result());
ps.setTimestamp(k++, new java.sql.Timestamp(format.parse(bean.getVerify_time()).getTime()));
ps.setObject(k++, bean.getChannel_type());
ps.setTimestamp(k++, date);
ps.setTimestamp(k++, date);
k = 1;
// 添加到批处理
ps.addBatch();
}
}
int[] res = ps.executeBatch();
conn.commit();
return res;
} catch (SQLException e) {
conn.rollback();
logger.error("部分数据导入失败", e);
}finally {
ps.clearBatch();
if(ps != null) {
ps.close();
ps = null;
}
}
return new int[] {0};
}
/**
* 关闭资源
*/
public static void closeSources(Connection conn, PreparedStatement ps){
try {
if (ps != null) {
ps.close();
ps = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
logger.error("数据库关闭失败");
}
}
public static Connection getConn() {
return conn;
}
public static PreparedStatement getPs() {
return ps;
}
public static void main(String[] args) throws Exception {
List<Bean> beans = new ArrayList<Bean>();
beans.add(new Bean());
beans.add(new Bean());
beans.add(new Bean());
// 批量插入
executeUpate(SQL_INSTALL_IDNO_THIRD, beans, "");
// 关闭链接
closeSources(conn, ps);
}
}