JDBC工具类

package com.haier.bi.db;
import java.sql.*;
/**
 * JDBC工具类
 *
 * @author : initit@aliyun.com
 * @version 1.0.0
 * @date : 2021-09-13 22:13
 */
public class JdbcUtil {
    /**
     * 获取数据库连接
     *
     * @param driver   驱动类
     * @param url      数据源地址
     * @param userName 用户名
     * @param password 密码
     * @return Connection
     */
    public static Connection getConnection(String driver, String url, String userName, String password) throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName(driver);
        //2.获取数据库连接
        return DriverManager.getConnection(url, userName, password);
    }
    /**
     * 获取数据库预声明
     *
     * @param connection 数据库连接
     * @param sql        SQL语句
     * @param params     SQL参数
     * @return PreparedStatement
     */
    public static PreparedStatement getPreparedStatement(Connection connection, String sql, Object... params) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (null != params && params.length > 0) {
            for (int i = 1; i <= params.length; i++) {
                preparedStatement.setObject(i, params[i - 1]);
            }
        }
        return preparedStatement;
    }
    /**
     * 获取数据库结果集
     *
     * @param preparedStatement 数据库预声明
     * @return ResultSet
     */
    public static ResultSet getResultSet(PreparedStatement preparedStatement) throws SQLException {
        return preparedStatement.executeQuery();
    }
    /**
     * 关闭数据库连接并释放资源
     *
     * @param connection 连接
     * @param statement  声明
     * @param resultSet  结果集
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        //1.先关结果集
        closeResultSet(resultSet);
        //2.再关声明
        closeStatement(statement);
        //3.最后关连接
        closeConnection(connection);
    }
    /**
     * 关闭数据库连接
     *
     * @param connection 据库连接
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 关闭数据库声明
     *
     * @param statement 数据库声明
     */
    public static void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 关闭数据库结果集
     *
     * @param resultSet 数据库结果集
     */
    public static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

DB工具类

package com.haier.bi.db;
import cn.hutool.core.util.StrUtil;
import com.haier.bi.db.ds.Hive;
import com.haier.bi.db.ds.MySQL;
import com.haier.bi.exception.BizException;
import java.sql.*;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
/**
 * 数据库操作类
 *
 * @author : initit@aliyun.com
 * @version 1.0.0
 * @date : 2021-09-13 22:13
 */
public class Db {
    private final String driver;
    private final String url;
    private final String userName;
    private final String password;
    private Db(String driver, String url, String userName, String password) {
        this.driver = driver;
        this.url = url;
        this.userName = userName;
        this.password = password;
    }
    public static Db create(String driver, String url, String userName, String password) {
        return new Db(driver, url, userName, password);
    }
    public static Db createHive() {
        return create(Hive.driver, Hive.url, Hive.userName, Hive.password);
    }
    public static Db createMysql() {
        return create(MySQL.driver, MySQL.url, MySQL.userName, MySQL.password);
    }
    /**
     * 执行查询操作
     *
     * @param sql    SQL语句
     * @param params SQL参数
     * @return List<LinkedHashMap < String, Object>>
     */
    public synchronized List<LinkedHashMap<String, Object>> query(String sql, Object... params) {
        List<LinkedHashMap<String, Object>> list = new LinkedList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtil.getConnection(driver, url, userName, password);
            preparedStatement = JdbcUtil.getPreparedStatement(connection, sql, params);
            resultSet = JdbcUtil.getResultSet(preparedStatement);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = resultSet.getObject(columnName);
                    if (null == columnValue) {
                        columnValue = "";
                    }
                    map.put(columnName, columnValue);
                }
                list.add(map);
            }
            return list;
        } catch (SQLException | ClassNotFoundException e) {
            String msg = StrUtil.format("执行查询操作异常,\nSQL：{}\nParams：{}", sql, params);
            throw new BizException(msg, e);
        } finally {
            JdbcUtil.close(connection, preparedStatement, resultSet);
        }
    }
    /**
     * 执行更新操作
     *
     * @param sql    SQL语句
     * @param params SQL参数
     * @return 受影响的行数
     */
    public synchronized int update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtil.getConnection(driver, url, userName, password);
            preparedStatement = JdbcUtil.getPreparedStatement(connection, sql, params);
            return preparedStatement.executeUpdate();
        } catch (SQLException | ClassNotFoundException e) {
            String msg = StrUtil.format("执行更新操作异常,\nSQL：{}\nParams：{}", sql, params);
            throw new BizException(msg, e);
        } finally {
            JdbcUtil.close(connection, preparedStatement, null);
        }
    }
}