jdbc批量插入， 一定要关闭事物的自动提交， 否则效率会非常慢， 对于插入出现异常的一批数据， 暂时做整体回滚操作！
[java] view plain copy 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); } }
