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;

    }

}