源码-DbUtil.java

package com.tetralogy.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class DbUtil {
    private static DbUtil instance;
//    连接池数据源(即连接池)
    DataSource dataSource;

    public static DbUtil getInstance() {
        if (null == instance) {
            instance = new DbUtil();
        }
        return instance;
    }
    private DbUtil() {
        Properties prop = new Properties();
        InputStream is = DbUtil.class.getResourceAsStream("/druid.properties");
        try {
            prop.load(is);
            //返回的是DataSource
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws Exception {
        Connection conn = dataSource.getConnection();;
        return conn;
    }

    public static int Execute(String sql,Object[] object) throws Exception{
        Connection con = null;
        PreparedStatement pstmt = null;
        int count = -1;
        try {
            DbUtil instance = DbUtil.getInstance();
            con = instance.getConnection();
            pstmt = con.prepareStatement(sql);
            if(object==null ||object.equals("")){ //判断一下 object 是否为空 如果为空直接执行sql语句
                count = pstmt.executeUpdate();
            }else{
                for(int i=0;i<object.length;i++){
                    pstmt.setObject(i+1,object[i]);
                }
                count = pstmt.executeUpdate(); //最后执行executeQuery()方法 执行sql语句
            }
        } catch (Exception e) {
            throw e;
        }finally {
            DbUtil.close(con,pstmt,null);
        }
        return count;
    }

    public static int executeByParams(String sql, Object...params) throws Exception{
        List<Object> list = new ArrayList<>();
        if(null!=params){
            for(int i=0;i<params.length;i++){
                list.add(params[i]);
            }
        }
        return Execute(sql,list.toArray());
    }

    public static List queryForList(String sql, Object[] object) throws Exception {
        DbUtil instance = DbUtil.getInstance();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List list = null;
        try {
            con = instance.getConnection();
            pstmt = con.prepareStatement(sql);
            if(object==null){
                rs = pstmt.executeQuery();
            }else{
                for(int i=0;i<object.length;i++){
                    pstmt.setObject(i+1,object[i]);
                }
                rs = pstmt.executeQuery();
            }
            //转化
            list = DbUtil.rsToList(rs);
        } catch (Exception e) {
            throw e;
        }finally {
            DbUtil.close(con,pstmt,rs);
        }
        return list;
    }

    //T:代表我返回的结果，他是集合里面使用的泛型的结果;他和我们传过来的字节码文件也是一个类型
    // class<T> cls :我们实体类的字节码对象
    public static <T> List<T> queryForList(Class<T> cls, String sql, Object[] object) throws Exception {
        DbUtil instance = DbUtil.getInstance();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List list = null;
        try {
            con = instance.getConnection();
            pstmt = con.prepareStatement(sql);
            if(object==null){
                rs = pstmt.executeQuery();
            }else{
                for(int i=0;i<object.length;i++){
                    pstmt.setObject(i+1,object[i]);
                }
                rs = pstmt.executeQuery();
            }
            //转化
            list = DbUtil.rsToEntity(cls,rs);
        } catch (Exception e) {
            throw e;
        }finally {
            DbUtil.close(con,pstmt,rs);
        }
        return list;
    }

    public static <T> T queryForEntity(Class<T> cls, String sql, Object[] param) throws Exception {
        List<T> list = queryForList(cls,sql,param);
        if(list!=null && list.size()>0){
            return list.get(0);
        }
        return null;
    }

    public static String queryForOne(String sql, Object[] object) throws Exception {
        DbUtil instance = DbUtil.getInstance();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String result = "";
        try {
            con = instance.getConnection();
            pstmt = con.prepareStatement(sql);
            if(object==null){
                rs = pstmt.executeQuery();
            }else{
                for(int i=0;i<object.length;i++){
                    pstmt.setObject(i+1,object[i]);
                }
                rs = pstmt.executeQuery(); 
            }
            while (rs.next()) {
                result = rs.getString(1);
                break;
            }

        } catch (Exception e) {
            throw e;
        }finally {
            DbUtil.close(con,pstmt,rs);
        }
        return result;
    }

    //关闭流原则，先创建后关闭
    private static void close(Connection con, PreparedStatement ps, ResultSet rs) throws Exception {
        if (ps != null) {
            ps.close();
            ps = null;
        }
        if (rs != null) {
            rs.close();
            rs = null;
        }
        if (con != null) {
            con.close();
            con = null;
        }
    }

    private static List rsToList(ResultSet rs) throws Exception{
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        while (rs.next()) {
            Map rowData = new HashMap();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }

    private static <T> List<T> rsToEntity(Class<T> cls, ResultSet rs) throws Exception{
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        List<T> list = new ArrayList<>();
        while (rs.next()) {
            T t = cls.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                String colName = md.getColumnLabel(i);
                Object valueObj = rs.getObject(colName);
                Field field = cls.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(t,valueObj);
            }
            list.add(t);
        }
        return list;
    }
}