连接Oracle，Oracle批量插入

1、加载资源

DriverManagerDataSource dataSource = JdbcTemplateUtil.getDataSource("driverClassName", "url", "username", "password");
JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dataSource); 

2、执行查询sql

jdbcTemplate.queryForList(sql);

3、事务管理

DataSourceTransactionManager transactionManager = JdbcTemplateUtil.getTransactionManager(dataSource);
TransactionStatus status = JdbcTemplateUtil.getTransactionStatus(transactionManager,
						TransactionDefinition.PROPAGATION_REQUIRED);
String sql = "insert into db(id1,id2) values(?,?)";
try {
    //初始化事务
	jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        // 这个方法设定更新记录数，通常saveList里面存放的都是我们要更新的，所以返回list.size();
	    public int getBatchSize() {
	        return saveList.size();
	    }
	    public void setValues(PreparedStatement ps, int i) throws SQLException {
		    Map<String, Object> map = (Map<String, Object>) saveList.get(i);
            //根据sql问号顺序
		    ps.setString(1, StringUtils.isEmpty(map);
		    ps.setString(2, StringUtils.isEmpty(data);
	    }
	});
    //提交事务
	transactionManager.commit(status);
} catch (Exception e) {
	// 出现异常，回滚事务，以免出现脏数据，数据不完整的问题
	transactionManager.rollback(status);
	log.info(MessageFormat.format("入库操作异常。数据为:[{0}]", e.toString()));
	e.printStackTrace();
}

做过一些项目，以前没想过总结。特此总结一下，希望能帮助需要帮助的人同学，如有描述不准确的，恳请指正，谢谢。

 

=============补充分割线==========

工具类：

public class JDBCConnPoolUtil {

	public JDBCConnPoolUtil() {
		super();
	}
	static LinkedList<Connection> pool = null;
	public static Connection getConnection(String driver, String url, String username, String password, int initSize)
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		// 判断是否为空
		if (pool == null) {
			pool = new LinkedList<Connection>();
			Class.forName(driver);
			for (int i = 0; i < initSize; i++) {
				conn = DriverManager.getConnection(url, username, password);
				pool.add(conn);
			}
		}
		if (pool.size() > 0) {
			conn = pool.removeFirst();
		} else {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		}
		return conn;
	}
	// 写回收方法
	public static void backConnection(Connection conn) {
		if (pool.size() > 2) {
			closeConn(conn);
		} else {
			pool.add(conn);
		}
	}
	public static void closeRS(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void closeStat(Statement stat) {
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void closeConn(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

代码： 

public static void InsertVehData(List<Map<String, Object>> dataList, String data) {
        System.out.println("插入操作");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCConnPoolUtil.getConnection("oracle.jdbc.driver.OracleDriver", "数据库地址", "账号", "密码", 1);
            String sql = "insert into temp t (t.vin,t.cdate,t.vom) values(?,?,?) ";
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (Map<String, Object> map : dataList) {
                ps.setString(1, map.get("vin").toString());
                ps.setString(2, data);
                ps.setString(3, map.get("vecOptMod").toString());
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCConnPoolUtil.closeStat(ps);
            JDBCConnPoolUtil.backConnection(conn);
        }
    }