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

}