jdbc批量插入

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);
	}
}