ch00 jdbc批量插入更新工具类
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class JDBCUtils { private static JDBCUtils jdbcUtils = null; private static String jdbc_driver; //jdbc驱动 private static String jdbc_url; //jdbc连接Url private static String user_name; //jdbc连接用户名 private static String user_password; //jdbc连接密码 private static String batch_size; //批量提交数 private JDBCUtils() { } /** * 创建JDBC工具类实例 * @return */ public static synchronized JDBCUtils getInstance(){ if(jdbcUtils == null){ jdbcUtils = new JDBCUtils(); } return jdbcUtils; } /** * 获取 数据库连接 * @return */ public Connection getConnection(){ try { Class.forName(jdbc_driver); Connection conn = DriverManager.getConnection(jdbc_url, user_name, user_password); return conn; } catch (Exception e) { e.printStackTrace(); } return null; } /** * 关闭数据库相关连接 * @param connection */ public void close(ResultSet rs, Statement st, Connection conn) { try { if(rs != null)rs.close();rs=null; } catch (SQLException e) { e.printStackTrace(); }finally{ try { if (st != null) st.close();st=null; } catch (SQLException e) { e.printStackTrace(); } finally { try { if (conn != null) conn.close();conn=null; } catch (SQLException e) { e.printStackTrace(); } } } } /** * 关闭数据库相关连接 * @param connection */ private void close(PreparedStatement pstmt, Connection conn) { try { if(pstmt != null)pstmt.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 增加单条数据 * @param sql sql语句 * @param values 参数值 * @return 是否增加成功 * @throws SQLException */ public boolean saveOrUpdate(String sql,Object ... values) throws SQLException{ Connection conn = getConnection(); //获取数据库连接 PreparedStatement pstmt = null; try { conn.setAutoCommit(false); //设置手动提交事务 pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象 //赋值 for (int i = 0; i < values.length; i++) { pstmt.setObject(i+1, values[i]); } pstmt.execute(); //执行操作 conn.commit(); //提交事务 close(pstmt,conn); //关闭相关连接 } catch (SQLException e) { e.printStackTrace(); }finally{ close(pstmt,conn); //关闭相关连接 } return true; } /** * 删除 * @param sql * @return */ public boolean batchDelete(String sql){ Connection conn = getConnection(); //获取数据库连接 PreparedStatement pstmt = null; try { conn.setAutoCommit(false); //设置手动提交事务 pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象 pstmt.execute(); //执行操作 conn.commit(); //提交事务 close(pstmt,conn); //关闭相关连接 } catch (SQLException e) { e.printStackTrace(); }finally{ close(pstmt,conn); //关闭相关连接 } return true; } /** * 批量增加与修改 * @param sql insert or update 语句 * @param params 参数集合 * @return * @throws SQLException */ public boolean batchSaveOrUpdate(String sql,List<Object[]> paramList) { int count = Integer.parseInt(batch_size)-1; Connection conn = getConnection(); //获取数据库连接 PreparedStatement pstmt = null; try { conn.setAutoCommit(false); //设置手动提交事务 pstmt = conn.prepareStatement(sql); //创建PreparedStatement对象 //赋值 for (int i = 0; i < paramList.size(); i++) { Object[] values = paramList.get(i); for (int j = 0; j < values.length ; j++) { pstmt.setObject(j+1, values[j]); } pstmt.addBatch(); //批量数等于 batch_size 时 提交数据 if(i != 0 && (i%count == 0)){ int ids[] = pstmt.executeBatch(); //执行操作 if(ids.length == count+1 ){ conn.commit(); //提交事务 }else{ conn.rollback(); //事务回滚 } pstmt.clearBatch(); } } int ids[] = pstmt.executeBatch(); //执行操作 if(ids.length == paramList.size()%(count+1) ){ conn.commit(); //提交事务 }else{ conn.rollback(); //事务回滚 } } catch (SQLException e) { e.printStackTrace(); }finally{ close(pstmt,conn); //关闭相关连接 } return true; } public static void main(String[] args) throws SQLException {// JDBCUtils utils = JDBCUtils.getInstance();// // // String sql = "insert into tbl_yitiansystem_systemlog (id,message) values(?,?);";// List paramList = new ArrayList(); // for (int i = 0; i < 10; i++) {// String [] param = new String[]{i+"",i+""};// paramList.add(param);// }// // boolean t = utils.batchSaveOrUpdate(sql, paramList);// System.out.println(t);// }}?