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

}