数据库JDBC操作类封装

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.MessageFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;


public class DBUtil {
    private static final Logger LOG = LoggerFactory.getLogger(DBUtil.class);

    /**
     * 数据库链接对象
     */
    public static class Client{
        private String driver;
        private String jdbcUrl;
        private String username;
        private String password;

        public String getDriver() {
            return driver;
        }

        public void setDriver(String driver) {
            this.driver = driver;
        }

        public String getJdbcUrl() {
            return jdbcUrl;
        }

        public void setJdbcUrl(String jdbcUrl) {
            this.jdbcUrl = jdbcUrl;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }
    }

    /**
     * 定义结果类
     */
    public static class Result{
        //是否成功执行
        private boolean isSuccess = true;
        //执行sql
        private String sql = "";
        //错误信息
        private String errorMessage = "";
        //列信息（列名称-列类型）
        private Map<String,String> columnInfo;
        //返回的数据集-List-Map
        private List<Map<String, Object>> data;

        public boolean isSuccess() {
            return isSuccess;
        }

        public void setSuccess(boolean success) {
            isSuccess = success;
        }

        public String getSql() {
            return sql;
        }

        public void setSql(String sql) {
            this.sql = sql;
        }

        public String getErrorMessage() {
            return errorMessage;
        }

        public void setErrorMessage(String errorMessage) {
            this.errorMessage = errorMessage;
        }

        public Map<String, String> getColumnInfo() {
            return columnInfo;
        }

        public void setColumnInfo(Map<String, String> columnInfo) {
            this.columnInfo = columnInfo;
        }

        public List<Map<String, Object>> getData() {
            return data;
        }

        public void setData(List<Map<String, Object>> data) {
            this.data = data;
        }
    }

    /**
     * 获取连接对象
     * @param client
     * @return
     */
    public static Connection getConnect(Client client){
        Connection conn = null;
        try{
            Class.forName(client.getDriver());
            conn = DriverManager.getConnection(client.getJdbcUrl(),client.getUsername(),client.getPassword());
        }catch (Exception ex){
            LOG.error("数据库连接失败！",ex);
        }

        return conn;
    }

    /**
     * 格式化结果集
     * @param resultSet
     * @return List<Map<String, Object>>
     */
    private static Map<String,Object> resultFormat(ResultSet resultSet){
        Map<String,Object> retData = new HashMap<>();
        ResultSetMetaData md = null;
        try {
            //捕获异常判断当前执行语句是否存在返回值
            try{
                md = resultSet.getMetaData();
            }catch (NullPointerException ex){
                return retData;
            }

            int columnCount = md.getColumnCount(); //列数
            Map<String,String> columnInfo = new HashMap<>();
            for (int i = 1;i<=columnCount;i++){
                columnInfo.put(md.getColumnName(i),md.getColumnTypeName(i));
            }

            retData.put("columnInfo",columnInfo);

            List<Map<String, Object>> mapData = new ArrayList<>();
            //历遍结果数据
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i).toUpperCase(), resultSet.getObject(i));
                }
                mapData.add(rowData);
            }
            retData.put("mapData",mapData);

        } catch (Exception e){
            LOG.error("SQL结果集合格式化失败！",e);
        }
        return retData;
    }

    /**
     * 执行sql语句
     * @param connection
     * @param sql
     * @return
     */
    public static Result exec(Connection connection, String sql) {
        Result result = new Result();
        result.setSql(sql);
        PreparedStatement ps = null;
        ResultSet ret = null;
        try {
            ps = connection.prepareStatement(sql);
            ret = ps.executeQuery();

            Map<String,Object> retFormatObject = resultFormat(ret);
            result.setColumnInfo((Map<String, String>) retFormatObject.get("columnInfo")); //列信息
            result.setData((List<Map<String, Object>>) retFormatObject.get("mapData"));    //行数据

        } catch (Exception e) {
            result.setSuccess(false);
            result.setErrorMessage(Method.getExceptionToString(e));
            LOG.error("SQL执行失败:"+result.getSql(),e);
        } finally {
            if (ret != null){
                try {
                    ret.close();
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }
            if (ps != null){
                try {
                    ps.close();
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     * 插入数据
     * @param connection
     * @param tableName:插入表名称，全名称包含数据库名
     * @param object:插入的数据对象
     */
    public static Boolean insert(Connection connection ,String tableName,Object object){
        PreparedStatement preparedStatement = null;
        Boolean flag = true;
        try {
            connection.setAutoCommit(false); //取消自动提交
            String sql = "INSERT INTO {0}(\"{1}\") VALUES({2})";

            //匹配传入参数对象类型
            if (object instanceof List){
                //List类型
                List<Object> addListData = (List<Object>) object;
                if (addListData.get(0) instanceof Map){
                    //List-Map嵌套多行
                    List<Map<String,Object>> listMapData = (List<Map<String, Object>>) object;
                    List<String> columnName = new ArrayList<>(listMapData.get(0).keySet());
                    String col = String.join("\",\"", columnName);   //列拼接
                    String placeholder = String.join(",", Collections.nCopies(columnName.size(), "?")); //占位符
                    String addSql = MessageFormat.format(sql, tableName, col, placeholder);
                    LOG.info("INSERT SQL:"+addSql);
                    preparedStatement = connection.prepareStatement(addSql);

                    for (int i = 0; i < listMapData.size(); i++) {
                        for (int k = 0; k< columnName.size();k++){
                            // 从1开始计数
                            preparedStatement.setObject(k+1,listMapData.get(i).get(columnName.get(k)));
                        }
                        preparedStatement.addBatch();
                        // 一万行执行一次批量操作
                        if ((i+1)%10000 == 0){
                            preparedStatement.executeBatch();
                            preparedStatement.clearBatch();
                            connection.commit();
                        }
                    }

                }else {
                    LOG.error("不支持插入当前对象类型！");
                    return false;
                }

            }else if (object instanceof Map){
                //Map类型（单行记录）
                Map<String,Object> onceMapData = (Map<String, Object>) object;
                List<String> columnName = new ArrayList<>(onceMapData.keySet());
                String col = String.join("\",\"", columnName);   //列拼接
                String placeholder = String.join(",", Collections.nCopies(columnName.size(), "?")); //占位符
                String addSql = MessageFormat.format(sql, tableName, col, placeholder);
                LOG.info("INSERT SQL:"+addSql);
                preparedStatement = connection.prepareStatement(addSql);

                for (int k = 0; k< columnName.size();k++){
                    // 从1开始计数
                    preparedStatement.setObject(k+1,onceMapData.get(columnName.get(k)));
                }
                preparedStatement.addBatch();

            }else{
                LOG.error("不支持插入当前对象类型！");
                return false;
            }

            preparedStatement.executeBatch();
            connection.commit();
            LOG.info("数据插入完成！");

        }catch (Exception e){
            LOG.error("数据插入失败！",e);
            try {
                connection.rollback(); //回滚
            } catch (SQLException throwables) {
                //throwables.printStackTrace();
            }
            flag = false;
        }finally {
            if (preparedStatement != null);{
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    //throwables.printStackTrace();
                }
            }
        }
        return flag;

    }

    /**
     * 释放资源连接
     * @param connection Connection
     */
    public static void releaseResources(Connection connection){
        if (connection != null){
            try {
                connection.close();
            } catch (Exception e) {
                //e.printStackTrace();
            }
        }
    }
    
    public static void main(String[] args) {
        Client client = new Client();
        client.setDriver("ru.yandex.clickhouse.ClickHouseDriver");
        //client.setDriver("com.github.housepower.jdbc.ClickHouseDriver");
        client.setJdbcUrl("jdbc:clickhouse://127.0.0.1:8123");
        client.setUsername("default");
        client.setPassword("123456");
        Connection conn = getConnect(client);
        Result result = exec(conn,"SELECT * FROM DATASOURCE.SYNC_LOG_BAK LIMIT 5");
        System.out.println(result.getColumnInfo());

        List<String> columnName = new ArrayList<>(result.getColumnInfo().keySet());
        //第一种插入方式（单行Map）
        Map<String,Object> onceMapData = new HashMap<>();
        onceMapData.put("BATCH_ID","111222333");
        onceMapData.put("STATUS","onceMapData");
        onceMapData.put("READ_RECORD","1234");
        onceMapData.put("END_DATE",LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        insert(conn,"DATASOURCE.SYNC_LOG_BAK",onceMapData);

        //第二种插入方式（List嵌套Map）
        List<Map<String,Object>> listMapData = new ArrayList<>();
        onceMapData.put("STATUS","listMapData");
        listMapData.add(onceMapData);
        insert(conn,"DATASOURCE.SYNC_LOG_BAK",listMapData);

        releaseResources(conn);

    }

}```