JDBC的学习（二）——SqlHelper类的编写

package cn.yn.myutil;
/**
 * 
 * @author nickY9527
 *
 */
import java.sql.*;
public class SqlHelper {

    private static String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String DB_URL = "jdbc:mysql://localhost:3306/textjdbc?serverTimezone=GMT";
    private static String USER = "root";
    private static String PWD = "123";
    
    private static CallableStatement cs = null;
    public static CallableStatement getCs() {
        return cs;
    }

    private static Connection ct = null;
    public static Connection getCt() {
        return ct;
    }

    public static PreparedStatement getPs() {
        return ps;
    }

    public static ResultSet getRs() {
        return rs;
    }

    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    
    static {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }
    }
    
    /**
     * 得到连接
     * @return
     */
    public static Connection getConnection() {
        try {
            ct = DriverManager.getConnection(DB_URL, USER, PWD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ct;
    }
    
    /**
     * update / delete / insert
     * sql格式 update 表名 set 字段名=? where 字段=?
     * @param sql
     * @param parameters
     */
    public static void executeUpdate(String sql, String[] parameters) {
        //创建ps
        try {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            //给问号赋值
            if(parameters != null) {
                for(int i=0; i<parameters.length; i++) {
                    ps.setString(i+1, parameters[i]);
                }
            }
            //执行
            ps.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();//开发阶段
            //抛出异常，运行异常，可以给调用该函数的函数一个选择，可以处理也可以放弃处理
            throw new RuntimeException(e.getMessage());
        }finally {
            //
            close(rs, ps, ct);
        }
    }
    
    /**
     * 如果有多个事务需要处理
     * @param sql
     * @param parameters
     */
    public static void executeUpdate2(String sql[], String [][] parameters) {
        try {
            //获得连接
            ct = getConnection();
            //因为此时用户可能传入多个sql语句
            ct.setAutoCommit(false);
            for(int i=0; i<sql.length; i++) {
                if(parameters[i] != null) {
                    ps = ct.prepareStatement(sql[i]);
                for(int j=0; j<parameters[i].length; j++) {
                    ps.setString(j+1, parameters[i][j]);
                }
                ps.executeUpdate();
            }
        }
            ct.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //回滚
            try {
                ct.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            throw new RuntimeException(e.getMessage());
        }finally {
            close(rs, ps, ct);
        }
    }
    
/**
 * 统一的select
 * Result -> ArrayList
 */
    public static ResultSet executeQuery(String sql, String []parameters) {
        try {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if(parameters != null) {
                for(int i=0; i<parameters.length; i++) {
                    ps.setString(i+1, parameters[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally {
            
        }
        return rs;
    }
        
    /**
     * 分页问题
     */
    public static ResultSet executeQuery2() {
        return null;
    }
    
    /**
     * 调用存储过程
     * sql 像 {call  过程(?,?,?)}
     */
    public static void callPor1(String sql, String []parameters) {
        
        try {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            //给问号赋值
            if(parameters != null) {
                for(int i=0; i<parameters.length; i++) {
                    cs.setObject(i+1, parameters[i]);
                }
            }
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally {
            close(rs, cs, ct);
        }
    }
    
    /**
     * 调用存储过程
     * 有返回值 Result
     * sql call 过程(?,?,?)
     */
    public static ResultSet callPro2
    (String sql, String []inparameters, int []outparameters) {
        try {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            if(inparameters != null) {
                for(int i=0; i<inparameters.length; i++) {
                    cs.setObject(i+1, inparameters[i]);
                }
            }
            //给out参数赋值
            if(outparameters != null) {
                for(int i=0; i<outparameters.length; i++) {
                    cs.registerOutParameter(inparameters.length+1+i, outparameters[i]);
                }
            }
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally {
            //不需要关闭
        }
        return rs;
    }
    
    /**
     * 关闭函数
     * @param rs
     * @param ps
     * @param ct
     */
    public static void close(ResultSet rs, Statement ps, Connection ct) {
        if(ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            ps = null;//垃圾回收
        }
        if(ct  != null) {
            try {
                ct.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            ct = null;
        }
        if(rs != null) {
            try {
                rs.close();
            } catch (Exception e2) {
                // TODO: handle exception
            }
        }
    }        
}