package utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class DBUtils {
    private DBUtils() {
    }
    //properties
    // private final static String DIVER_NAME = "com.mysql.jdbc.Driver";
    // private final static String URL = "jdbc:mysql://localhost:3306/myt";
    // private final static String USERNAME = "root";
    // private final static String PWD = "root";

    //加载驱动
    static {
        try {
            // 需要创建 db.properties 文件
            ResourceBundle bundle = ResourceBundle.getBundle("db");
            String DIVER_NAME = bundle.getString("DIVER_NAME");
            System.out.println(DIVER_NAME);
            Class.forName(DIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("驱动类没有找到，加载失败!");
        }
    }

    //实例化
    public static DBUtils getInstance() {
        return new DBUtils();
    }

    //获取连接方法，用于DAO
    public Connection getConnection() {
        try {
            ResourceBundle bundle = ResourceBundle.getBundle("db");
            String URL_01 = bundle.getString("URL");
            String DB_NAME = bundle.getString("DB_NAME");
            String USERNAME = bundle.getString("USERNAME");
            String PWD = bundle.getString("PWD");
            String URL = URL_01 + DB_NAME;
            System.out.println("URL-->" + URL);
            System.out.println("USERNAME-->" + USERNAME);
            System.out.println("PWD-->" + PWD);
            System.out.println("DB_NAME-->" + DB_NAME);

            return DriverManager.getConnection(URL, USERNAME, PWD);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("获取连接失败，请重新连接!");
        }
    }


    /**
     * 增删改方法可以直接调用
     *
     * @param sql
     * @param params
     * @return
     */
    /**
     * 执行变更语句,INSERT/UPDATE/DELETE语句
     * JDBC中执行变更语句的过程如下
     * 加载驱动类->获取数据库连接->设置指令(SQL)->获取执行SQL语句的预处理对象->执行SQL->关闭资源
     * @param sql
     * @param params
     */
    public Boolean update(String sql , Object...params) {
        Connection conn = null;
        PreparedStatement ps = null;
        int p=-1;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1, params[i]);//索引值从1开始
            }
            p = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行变更SQL语句失败");
        }finally {
        	
            this.close(conn, ps);
        }
        if(p>=0){
        	return true;
        }else{       	
        	return false;
        }
    }

    public Map<String, Object> queryForMap(String sql,Object...params){
        Map<String, Object> map = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1, params[i]);//索引值从1开始
            }
            //获取结果集对象
            rs = ps.executeQuery();
            if(rs.next()) {//判断查询结果是否存在
                map = new HashMap<String, Object>();//创建Map容器
                ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据
                int columnCount = rsmd.getColumnCount();//查询的列数
                for(int i=1;i<=columnCount;i++) {//索引值从1开始
                    String columnName = rsmd.getColumnLabel(i);//获取虚拟表的列表
                    Object columnValue = rs.getObject(columnName);//虚拟表字段对应值
                    map.put(columnName,columnValue);//存储数据
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行queryForMap方法失败");
        }finally {
            this.close(conn, ps, rs);
        }
        return map;
    }
    public List<Map<String,Object>> queryForList(String sql,Object...params){
        List<Map<String,Object>> list = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1, params[i]);//索引值从1开始
            }
            rs = ps.executeQuery();//获取结果集对象
            if(rs.next()) {
                list = new ArrayList<Map<String,Object>>();
                ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的元数据
                int columnCount = rsmd.getColumnCount();//查询的列数
                do {
                    Map<String,Object> map = new HashMap<String, Object>();
                    for(int i=1;i<=columnCount;i++) {//索引值从1开始
                        String columnName = rsmd.getColumnLabel(i);//获取虚拟表的列表
                        Object columnValue = rs.getObject(columnName);//虚拟表字段对应值
                        map.put(columnName,columnValue);//存储数据
                    }
                    list.add(map);//存储数据
                }while(rs.next());
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行queryForList方法失败");
        }finally {
            this.close(conn, ps, rs);
        }
        return list;
    }

    public Object queryForObjectValue(String sql,Object...params) {
        Object value = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1, params[i]);//索引值从1开始
            }
            rs = ps.executeQuery();//获取结果集对象
            if(rs.next()) {
                value = rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行queryForObjectValue方法失败");
        }finally {
            this.close(conn, ps, rs);
        }
        return value;
    }
    public Integer queryForInteger(String sql,Object...params) {
        return Integer.parseInt(this.queryForString(sql, params));
    }
    public String queryForString(String sql,Object...params) {
        return String.valueOf(this.queryForObjectValue(sql, params));
    }

    private Field getField(String key,Class<?> clazz) {
        try {
            return clazz.getDeclaredField(key);
        } catch (NoSuchFieldException | SecurityException e) {
            return null;
        }
    }

    public <T> T queryForObject(Class<?> clazz,String sql,Object...params) {
        Map<String,Object> map = this.queryForMap(sql, params);
        T t = null;
        if(map!=null) {
            try {
                t = (T)clazz.newInstance();//创建对象
                Set<String> keySet = map.keySet();//如何遍历Map
                for (String key : keySet) {
                    Field field = this.getField(key, clazz);//获取字段对象
                    if(field!=null) {
                        Object value = map.get(key);//通过KEY获取数据
                        field.setAccessible(true);//破坏封装性
                        field.set(t, value);//赋值
                    }
                }
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return t;
    }

    public <T> List<T> queryForList(Class<?> clazz,String sql,Object...params){
        List<Map<String,Object>> tempList = this.queryForList(sql, params);
        List<T> list = null;
        if(tempList!=null) {
            list = new ArrayList<T>();
            for (Map<String,Object> map : tempList) {
                try {
                    T t = (T)clazz.newInstance();
                    for(Map.Entry<String, Object> entry:map.entrySet()) {
                        String key = entry.getKey();
                        Field field = this.getField(key, clazz);
                        if(field!=null) {
                            Object value = entry.getValue();
                            field.setAccessible(true);
                            field.set(t, value);
                        }
                    }
                    list.add(t);
                } catch (InstantiationException | IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    public void updateBatch(String...sqls) {
        Connection conn = null;
        Statement st = null;
        try {
            conn = this.getConnection();
            conn.setAutoCommit(false);//关闭自动提交
            st = conn.createStatement();
            for (String sql : sqls) {
                st.addBatch(sql);
            }
            st.executeBatch();
            conn.commit();//手动提交
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("执行updateBatch方法失败");
        }finally {
            this.close(conn, st);
        }
    }
    private void close(Connection conn , PreparedStatement ps) {
        this.close(conn, ps, null, null);
    }
    private void close(Connection conn, PreparedStatement ps,ResultSet rs) {
        this.close(conn, ps, null, rs);
    }
    private void close(Connection conn, Statement st) {
        this.close(conn, null, st, null);
    }
    private void close(Connection conn,PreparedStatement ps,Statement st,ResultSet rs) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();//给程序员看
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps!=null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}