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;
- /**
- * @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; <span style="font-family: Arial, Helvetica, sans-serif;">ResourceBundle jdk1.7中可以用来读取配置文件的类</span>
- 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);
- }
- }