jdbc批量插入

来源：互联网发布：手机小夜灯软件编辑：程序博客网时间：2024/05/21 12:22
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);}}
0 0