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;
        
    }
    /**
     * 批量增