car-wash-java/car-wash-admin/src/main/java/com/kym/admin/utils/DBHelper.java

package com.kym.admin.utils;

import com.kym.DbUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 数据库链接工具
 */
public class DBHelper implements AutoCloseable {

    private Logger logger = LoggerFactory.getLogger(DBHelper.class);

    public final String DIALECT_MYSQL = "mysql";
    public final String DIALECT_PGSQL = "postgresql";


    private Connection conn = null;
    private PreparedStatement pst = null;

    public void open(String url, String username, String password) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        this.conn = DriverManager.getConnection(url, username, password);
    }

    public List<Map<String, Object>> executeQueryList(String sql, Object... params) {
        List<Map<String, Object>> result = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            logger.info("executeQueryList sql:\n{},params:\n{}", sql, params);
            pst = this.conn.prepareStatement(sql);
            DbUtil.setParameter(pst, params);
            resultSet = pst.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> data = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i).toLowerCase();
                    data.put(columnName, resultSet.getString(i));
                }
                result.add(data);
            }
        } catch (Exception e) {
            logger.error("DBHelper executeQueryList error", e);
        } finally {
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    logger.error("DBHelper executeQueryList close error", e);
                }
            }
        }

        return result;
    }

    public Map<String, Object> executeQuery(String sql, Object... params) throws Exception {
        Map<String, Object> result = new HashMap<>();
        ResultSet resultSet = null;
        logger.info("executeQuery sql:\n{}", sql);
        try {
            pst = this.conn.prepareStatement(sql);//准备执行语句
            DbUtil.setParameter(pst, params);
            resultSet = pst.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i).toLowerCase();
                    result.put(columnName, resultSet.getString(i));
                }
            }
        } catch (Exception e) {
            logger.error("DBHelper executeQuery error", e);
        } finally {
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    logger.error("DBHelper executeQuery close error", e);
                }
            }
        }
        return result;
    }


    public void executeUpdate(String sql, Object... params) throws Exception {
        try {
            logger.info("executeUpdate sql:\n{}", sql);
            pst = this.conn.prepareStatement(sql);//准备执行语句
            DbUtil.setParameter(pst, params);
            int i = pst.executeUpdate();
            logger.info("DBHelper executeUpdate rows:{}", i);
        } catch (Exception e) {
            logger.error("DBHelper executeUpdate error", e);
        }
    }

    //关闭
    public void closeDb() {
        try {
            if (null != pst) {
                if (!pst.isClosed()) {
                    pst.close();
                    logger.info("close preparedStatement...");
                }
            }
            if (null != conn) {
                if (!conn.isClosed()) {
                    conn.close();
                    logger.info("close connection..");
                }
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
    }


    @Override
    public void close() throws Exception {
        closeDb();
    }
}