java 8.0Mysql 助手类

由于mysql版本问题, 8.0的配置文件变了

配置文件

driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/studentmanage?
      characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true 
username = root
password = 123456

Sqlhelper

package edu.rjxy.xjdx.emps.common.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.sql.*;

public class SqlHelper {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    //连接数据库的参数
    private static String url = null;
    private static String userName =null;
    private static String driver =null;
    private static String passwd =null;
    private static CallableStatement cs = null;
    private static Properties  pp = null;
    private static InputStream fis = null;

    public static CallableStatement getCs() {
        return cs;
    }

    public static Connection getCt() {
        return conn;
    }

    public static PreparedStatement getPs() {
        return ps;
    }

    public static ResultSet getRs() {
        return rs;
    }

    static {
        try {
            pp = new Properties();
            fis = SqlHelper.class.getClassLoader().getResourceAsStream("dbinfo.properties");//配置文件
            pp.load(fis);
            url = pp.getProperty("url");
            driver = pp.getProperty("driverClassName");
            userName = pp.getProperty("username");
            passwd = pp.getProperty("password");

            Class.forName(driver);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                fis.close();
            }
            catch(IOException e) {e.printStackTrace();}
            fis = null;//垃圾回收站上收拾
        }
    }

    public static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(url, userName,passwd);
        }
        catch(Exception e) {e.printStackTrace();}
        return conn;
    }

    //*************callPro1存储过程函数1*************
    public static CallableStatement callPro1(String sql, String[] parameters) {
        try {
            conn = getConnection();
            cs = conn.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, conn);
        }
        return cs;
    }

    //*******************callpro2存储过程2************************
    public static CallableStatement callPro2(String sql, String[] inparameters, Integer[] outparameters) {
        try {
            conn = getConnection();
            cs = conn.prepareCall(sql);
            if(inparameters != null) {
                for(int i = 0; i < inparameters.length; ++i) {
                    cs.setObject(i+1,inparameters[i]);
                }
            }
            //cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
            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 cs;
    }

    public static <T> ArrayList<T> executeQuery(Class<T> type, String sql, String[] parameters) {
        ArrayList<T> list = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if(parameters!=null) {
                for(int i = 0; i < parameters.length; ++i) {
                    ps.setString(i+1,parameters[i]);
                }
            }

            rs = ps.executeQuery();
            //得到结果集（rs）的结构
            ResultSetMetaData rsmd = rs.getMetaData();

            list = new ArrayList<T>();

            //通过rsmd可以得到该结果集有多少列
            int columnNum = rsmd.getColumnCount();

            //获取字段名
            String[] clounmNames  = new String[columnNum];
            for (int i = 0; i < columnNum; i++) {
                clounmNames[i] = rsmd.getColumnLabel(i+1);
            }
            Method[] methods = type.getDeclaredMethods();

            //从rs中取出数据，并且封装到ArrayList中
            while (rs.next()) {
                Object obj = type.getDeclaredConstructor().newInstance();
                for (int i = 0; i < columnNum; i++) {
                    String clounmName = clounmNames[i];
                    Object clounmValue = rs.getObject(i + 1);
                    String setterName = "set"+clounmName.substring(0,1).toUpperCase()+clounmName.substring(1);// setName
                    Method setter = null;
                    for (int j = 0; j < methods.length; j++) {
                        if (methods[j].getName().equals(setterName)){
                            setter = methods[j];
                            break;
                        }
                    }
                    if(setter!=null ){
//                        System.out.println(""+setter + ":"+clounmValue.getClass().getName()  );
                        if(setter.getParameterTypes()[0].getName().equals("boolean") || setter.getParameterTypes()[0].getName().equals("java.lang.Boolean")){
                            clounmValue = ((Integer)clounmValue==0)?false:true;
                        }

                        boolean isBasic = false;
                        String parameter0Name = setter.getParameterTypes()[0].getName();
                        String[] allBasic =new String[]{"byte","short","int","long","float","double","char","boolean"};
                        for (String each:
                                allBasic ) {
                            if(parameter0Name.equals(each)){
                                isBasic= true;
                                break;
                            }
                        }

                        if( !( clounmValue==null && isBasic ) ){
                            setter.invoke(obj,clounmValue);
                        }
                    }
                    //获取字段值
                }
                list.add((T)obj);
            }
            return list;
        }
        catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, conn);
        }
    }
    
    public static int queryCount(String sql, String[] parameters) {
        ArrayList<Object[]> list = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if(parameters!=null) {
                for(int i = 0; i < parameters.length; ++i) {
                    ps.setString(i+1,parameters[i]);
                }
            }

            rs = ps.executeQuery();
            if (!rs.next()) return 0;

            return Integer.parseInt(rs.getObject(1).toString());
        }
        catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, conn);
        }
    }

    public static ArrayList<Object[]> executeQuery(String sql, String[] parameters) {
        ArrayList<Object[]> list = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if(parameters!=null) {
                for(int i = 0; i < parameters.length; ++i) {
                    ps.setString(i+1,parameters[i]);
                }
            }

            rs = ps.executeQuery();
            //得到结果集（rs）的结构
            ResultSetMetaData rsmd = rs.getMetaData();

            list = new ArrayList<Object[]>();

            //通过rsmd可以得到该结果集有多少列
            int columnNum = rsmd.getColumnCount();

            //从rs中取出数据，并且封装到ArrayList中
            while (rs.next()) {
                Object []objects = new Object[columnNum];
                for(int i = 0; i < objects.length; ++i) {
                    objects[i] = rs.getObject(i + 1);
                }
                list.add(objects);
            }
            return list;
        }
        catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, conn);
        }
    }

    public static void executeUpdate2(String[] sql,String[][] parameters) {
        try {
            conn = getConnection();
            conn.setAutoCommit(false);

            for(int i = 0; i < sql.length; ++i) {
                if(null != parameters[i]) {
                    ps = conn.prepareStatement(sql[i]);
                    for(int j = 0; j < parameters[i].length; ++j) {
                        ps.setString(j + 1, parameters[i][j]);
                    }
                    ps.executeUpdate();
                }
            }

            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw  new RuntimeException(e.getMessage());
        } finally {
            close(rs,ps, conn);
        }
    }

    //先写一个update、delete、insert
    //sql格式：update 表名 set 字段名 =？where 字段=？
    //parameter神应该是（”abc“,23）
    public static void executeUpdate(String sql,String[] parameters) {
        try {
            conn =getConnection();
            ps = conn.prepareStatement(sql);
            if(parameters!=null) {
                for(int i=0;i<parameters.length;i++) {
                    ps.setString(i+1,parameters[i]);
                }

            }
            ps.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();//开发阶段
            //抛出异常
            //可以处理，也可以不处理
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs,ps, conn);
        }
    }

    public static void close(ResultSet rs,Statement ps,Connection ct)
    {
        //关闭资源(先开后关)
        if(rs!=null) {
            try {
                rs.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
            rs=null;
        }
        if(ps!=null) {
            try {
                ps.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
            ps=null;
        }
        if(null!=ct) {
            try {
                ct.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
            ct=null;
        }
    }

    public static  List<Object> resultSetToList(ResultSet rs) throws java.sql.SQLException {
        if (rs == null) return Collections.emptyList();
        ResultSet md = (ResultSet) rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = ((ResultSetMetaData) md).getColumnCount(); //返回此 ResultSet 对象中的列数
        List<Object> list = new ArrayList<Object>();
        Map<Object, Object> rowData = new HashMap<Object, Object>();
        while (rs.next()) {
            rowData = new HashMap<Object, Object>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(((ResultSetMetaData) md).getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }
}