uni/src/main/java/com/scbfkj/uni/process/DataBase.java

package com.scbfkj.uni.process;

import com.fasterxml.jackson.databind.JsonNode;
import com.scbfkj.uni.library.DataFormatUtil;
import com.scbfkj.uni.library.DbUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.pool.HikariPool;
import jakarta.annotation.Nonnull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class DataBase {

    public static List<Map<String, Object>> query(String connectionStr, String sql, List<Object[]> argsList) throws Exception {
        HikariPool dataSourcePool = getDataSourcePool(connectionStr);


        return argsList.parallelStream().flatMap(args -> {
            try (Connection connection = dataSourcePool.getConnection();
                 PreparedStatement preparedStatement = connection.prepareStatement(sql)
            ) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
                ResultSet resultSet = preparedStatement.executeQuery();
                List<Map<String, Object>> result = DbUtil.getResult(connectionStr, sql, resultSet);
                return result.stream();
            } catch (Exception exception) {
                throw new RuntimeException("数据异常: %s\n sql: %s ;\n args: %s ".formatted(exception.getMessage(), sql, DataFormatUtil.toDate(args)));
            }
        }).toList();

    }

    public static int[] updateBatch(String connectionStr, String sql, List<Object[]> argsList) throws Exception {
        HikariPool dataSourcePool = getDataSourcePool(connectionStr);
        try (Connection connection = dataSourcePool.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)
        ) {
            int[] result = null;
            int index = 0;
            try {
                while (argsList.size() > index) {
                    Object[] args = argsList.get(index);
                    for (int i = 0; i < args.length; i++) {
                        preparedStatement.setObject(i + 1, args[i]);
                    }
                    preparedStatement.addBatch();
                    index++;
                }
                result = preparedStatement.executeBatch();
                connection.commit();
            } catch (SQLException e) {
                connection.rollback();
                throw new RuntimeException(e.getMessage());
            }

            return result;
        }
    }

    public static int exec(String connectionStr, String sql) throws Exception {
        HikariPool dataSourcePool = getDataSourcePool(connectionStr);
        try (Connection connection = dataSourcePool.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)
        ) {
            int i = preparedStatement.executeUpdate();
            try {
                connection.commit();
            } catch (Exception e) {
                connection.rollback();
                throw e;
            }
            return i;
        }
    }

    public static List<Map<String, Object>> query(String connectionStr, String sql, List<Map<String, Object>> argsList, List<String> filterColumns, Map<String, Object> filterLines) throws Exception {

        sql = sql.replaceAll("(\\r)?\\n", " ");

        if (Objects.nonNull(filterLines) && !filterLines.isEmpty()) {
            sql = " (%s %s %s) ".formatted(sql, sql.contains(" where ") ? " " : " where ", filterLines.entrySet().stream().map(it -> "%s = %s".formatted(it.getKey(), it.getValue())).collect(Collectors.joining(" and ")));
        }
        if (Objects.nonNull(filterColumns) && !filterColumns.isEmpty()) {
            sql = "select %s from %s".formatted(filterColumns.stream().collect(Collectors.joining(",")), sql);
        } else {
            sql = "select * from %s".formatted(sql);
        }

        List<List<Object>> result = new ArrayList<>();
        List<String> names = new ArrayList<>();
        if (sql.matches("《.*》")) {
            Pattern compile = Pattern.compile("(?=《).*(?=》)");
            Matcher matcher = compile.matcher(sql);
            int index = 0;
            while (matcher.find()) {
                String name = matcher.group(index);
                names.add(name);
            }
        }
        if (names.size() == 1 && names.contains("whereStr")) {
            List<Map<String, Object>> list = new ArrayList<>();
            for (Map<String, Object> stringObjectMap : argsList) {
                List<Object> args = new ArrayList<>();
                StringJoiner joiner = new StringJoiner(" and ");
                for (Map.Entry<String, Object> entry : stringObjectMap.entrySet()) {
                    String formatted = "%s = ?".formatted(entry.getKey());
                    args.add(entry.getValue());
                    joiner.add(formatted);
                }
                String whereStr = " " + joiner;
                String sqlStr = sql.replace("《whereStr》", whereStr);
                List<Map<String, Object>> apply = query(connectionStr, sqlStr, new ArrayList<>() {{
                    add(args.toArray());
                }});
                list.addAll(apply);
            }
            return list;
        } else {
            for (String it : names) {
                sql = sql.replace("《%s》".formatted(it), " ? ");
            }
            for (Map<String, Object> map : argsList) {
                List<Object> args = new ArrayList<>();
                for (String name : names) {
                    args.add(map.get(name));
                }
                result.add(args);
            }
            return query(connectionStr, sql, result.stream().map(List::toArray).toList());
        }
    }


    private final static Map<String, HikariPool> dataSourcePools = new HashMap<>();


    private static HikariPool getDataSourcePool(String connectionStr) throws Exception {
        if (dataSourcePools.containsKey(connectionStr)) {
            return dataSourcePools.get(connectionStr);
        }
        JsonNode jsonNode = DataFormatUtil.stringToJsonNode(connectionStr);

        JsonNode jdbcUrl = jsonNode.get("jdbcUrl");
        JsonNode username = jsonNode.get("username");
        JsonNode password = jsonNode.get("password");
        JsonNode driverClassName = jsonNode.get("driverClassName");
        HikariPool dataSourcePool = createDataSourcePool(jdbcUrl.asText(), Objects.isNull(username) ? null : username.asText(), Objects.isNull(password) ? null : password.asText(), driverClassName.asText(), null);
        dataSourcePools.put(connectionStr, dataSourcePool);
        return dataSourcePool;
    }


    private static HikariPool createDataSourcePool(@Nonnull String url, String username, String password, String driver, String poolName) throws Exception {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setMaximumPoolSize(Math.min(Runtime.getRuntime().availableProcessors(), 8));//最大核心数:当前可用CPU数，最大16
        hikariConfig.setKeepaliveTime(60000); //用于跟数据库保持心跳连接
        hikariConfig.setMaxLifetime(60000 * 60 * 4); //4小时 接在池中的最大生存时间，超过该时间强制逐出
        hikariConfig.addDataSourceProperty("cachePrepStmts", "true"); //是否自定义配置，为true时下面两个参数才生效
        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250"); //连接池大小默认25，官方推荐250-500
        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); //单条语句最大长度默认256，官方推荐2048
        hikariConfig.addDataSourceProperty("useServerPrepStmts", "true"); //新版本MySQL支持服务器端准备，开启能够得到显著性能提升
        hikariConfig.addDataSourceProperty("useLocalSessionState", "true");
        hikariConfig.addDataSourceProperty("useLocalTransactionState", "true");
        hikariConfig.addDataSourceProperty("rewriteBatchedStatements", "true");//批量新增
        hikariConfig.addDataSourceProperty("cacheResultSetMetadata", "true");
        hikariConfig.addDataSourceProperty("cacheServerConfiguration", "true");
        hikariConfig.addDataSourceProperty("elideSetAutoCommits", "true");
        hikariConfig.addDataSourceProperty("maintainTimeStats", "false");

        hikariConfig.setJdbcUrl(url);

        if (Objects.nonNull(username)) {
            hikariConfig.setUsername(username);
        }
        if (Objects.nonNull(password)) {
            hikariConfig.setPassword(password);
        }
        if (Objects.nonNull(driver)) {
            hikariConfig.setDriverClassName(driver);
        }
        if (Objects.nonNull(poolName)) {
            hikariConfig.setPoolName(poolName);
        }
        hikariConfig.setAutoCommit(false);
        return new HikariPool(hikariConfig);
    }


}