package cn.qg.qaplatform.utils;

import cn.qg.qaplatform.common.enums.Namespace;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 数据库连接工具类
 */
public class DBUtils {
    public static QueryRunner queryRunner = new QueryRunner();
    private String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private String db_url = "";

    private final String username;
    private final String password;
    private Connection conn = null;
    private Statement stmt = null;

    public DBUtils(String namespace, String database, String username, String password) {
        this.username = username;
        this.password = password;
        this.db_url = Namespace.valueOf(namespace.toUpperCase()).getUrl() + database;
    }

    /**
     * 数据库连接
     */
    public void connect() {
        try {
            Class.forName(JDBC_DRIVER);
            this.conn = DriverManager.getConnection(this.db_url, this.username, this.password);
            stmt = this.conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库连接
     */
    public void closeConnection() {
        try {
            if (this.conn != null && this.conn.isValid(0)) {
                this.conn.close();
                this.conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询
     *
     * @param sql
     * @return
     */
    public Map queryForMap(String sql) throws SQLException {
        Map resultMap = new HashMap();
        try {
            this.connect();
            ResultSet resultSet = this.stmt.executeQuery(sql);
            resultMap = resultSetToMap(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (this.conn != null && this.conn.isValid(0)) {
                this.conn.close();
                this.conn = null;
            }
        }
        return resultMap;
    }

    public List queryForList(String sql) throws SQLException {
        List resultList = new ArrayList();
        try {
            this.connect();
            ResultSet resultSet = this.stmt.executeQuery(sql);
            resultList = resultSetToList(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return resultList;
    }

    /**
     * 插入数据
     * @param sql
     */
    public int insert(String sql) throws SQLException {
        int num = 0;
        try {
            this.connect();
            num = this.stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return num;
    }

    /**
     * ResultSet转换成List
     *
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public List resultSetToList(ResultSet resultSet) throws SQLException {
        ResultSetMetaData md = resultSet.getMetaData();
        int num = md.getColumnCount();
        List listOfRows = new ArrayList();
        while (resultSet.next()) {
            Map mapOfColValues = new HashMap(num);
            for (int i = 1; i <= num; i++) {
                mapOfColValues.put(md.getColumnName(i), resultSet.getObject(i));
            }
            listOfRows.add(mapOfColValues);
        }
        return listOfRows;
    }

    /**
     * ResultSet转换成Map
     *
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public Map resultSetToMap(ResultSet resultSet) throws SQLException {
        ResultSetMetaData md = resultSet.getMetaData();
        int num = md.getColumnCount();
        Map map = new HashMap(num);
        while (resultSet.next()) {
            for (int i = 1; i <= num; i++) {
                map.put(md.getColumnName(i), resultSet.getObject(i));
            }
        }
        return map;
    }

    public static void main(String[] args) throws SQLException {
        String namespace = "test1";
        String database = "xyqb_user";
        String phone = "13712345678";
        String sql = "select id, uuid from user where phone_no=" + phone;
        DBUtils dbUtils = new DBUtils(namespace, database, "qa", "qatest");
        Map resultMap = dbUtils.queryForMap(sql);
        System.out.println(resultMap);
    }
}
