当前位置： 首页 > news >正文

最新版本jdbcutils集成log4j做详细sql日志、自动释放连接...等

news 2025/6/27 11:02:34

在这里插入图片描述

maven坐标

      <!-- MySQL 8 --><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.0.33</version></dependency><!-- Druid连接池 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.16</version></dependency><!-- Jackson用于JSON处理 --><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId><version>2.15.2</version></dependency><!-- Log4j2日志 --><dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-api</artifactId><version>2.20.0</version></dependency><dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.20.0</version></dependency>

import com.alibaba.druid.pool.DruidDataSource;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;public class JDBCUtils {private static DruidDataSource dataSource = null;private static ThreadLocal<ConnectionWrapper> connectionThreadLocal = new ThreadLocal<>();private static final Logger logger = LogManager.getLogger(JDBCUtils.class);private static final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);private static final ObjectMapper objectMapper = new ObjectMapper();private static final Pattern paramPattern = Pattern.compile("\\?");private static final DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");private static void initializeDataSource() {if (dataSource == null) {synchronized (JDBCUtils.class) {if (dataSource == null) {dataSource = new DruidDataSource();dataSource.setUrl("jdbc:mysql://localhost:3306/hello-travel");dataSource.setUsername("root");dataSource.setPassword("root");dataSource.setInitialSize(5);dataSource.setMinIdle(5);dataSource.setMaxActive(20);try {dataSource.setFilters("stat");logger.info("Database connection pool initialized at {}",LocalDateTime.now().format(dateFormatter));} catch (SQLException e) {logger.error("Initialize Druid connection pool failed at {}",LocalDateTime.now().format(dateFormatter), e);throw new RuntimeException(e);}}}}}public static Connection getConnection() {ConnectionWrapper wrapper = connectionThreadLocal.get();if (wrapper == null || wrapper.isExpired()) {if (dataSource == null) {initializeDataSource();}try {Connection conn = dataSource.getConnection();wrapper = new ConnectionWrapper(conn);connectionThreadLocal.set(wrapper);scheduleConnectionRelease(wrapper);logger.debug("New database connection created at {}",LocalDateTime.now().format(dateFormatter));} catch (SQLException e) {logger.error("Get database connection failed at {}",LocalDateTime.now().format(dateFormatter), e);throw new RuntimeException("Get database connection failed", e);}}wrapper.renew();return wrapper.getConnection();}private static void scheduleConnectionRelease(ConnectionWrapper wrapper) {scheduler.schedule(() -> {if (wrapper.isExpired()) {closeConnection();logger.debug("Expired connection closed at {}",LocalDateTime.now().format(dateFormatter));}}, 5, TimeUnit.SECONDS);}public static void closeConnection() {ConnectionWrapper wrapper = connectionThreadLocal.get();if (wrapper != null) {try {wrapper.getConnection().close();logger.debug("Database connection closed at {}",LocalDateTime.now().format(dateFormatter));} catch (SQLException e) {logger.error("Close database connection failed at {}",LocalDateTime.now().format(dateFormatter), e);} finally {connectionThreadLocal.remove();}}}public static Object execute(String sql, Object... params) throws SQLException {// Record start timeLocalDateTime startTime = LocalDateTime.now();String formattedStartTime = startTime.format(dateFormatter);Connection connection = getConnection();ObjectNode logEntry = objectMapper.createObjectNode();logEntry.put("prePareStatementSql", sql);logEntry.put("startTime", formattedStartTime);// Create parameter mappingMap<String, Object> paramMap = createParamMap(sql, params);logEntry.set("params", objectMapper.valueToTree(paramMap));try (PreparedStatement stmt = connection.prepareStatement(sql)) {// Set parametersfor (int i = 0; i < params.length; i++) {stmt.setObject(i + 1, params[i]);}boolean isQuery = sql.trim().toLowerCase().startsWith("select");String actualSql = replaceSqlParams(sql, params);if (isQuery) {try (ResultSet rs = stmt.executeQuery()) {ArrayNode results = objectMapper.createArrayNode();ResultSetMetaData metaData = rs.getMetaData();int columnCount = metaData.getColumnCount();while (rs.next()) {ObjectNode row = objectMapper.createObjectNode();for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnLabel(i);Object value = rs.getObject(i);if (value != null) {row.putPOJO(columnName, value);} else {row.putNull(columnName);}}results.add(row);}// Log execution infoLocalDateTime endTime = LocalDateTime.now();String formattedEndTime = endTime.format(dateFormatter);long executionTimeMs = java.time.Duration.between(startTime, endTime).toMillis();logEntry.put("type", "query");logEntry.put("resultCount", results.size());logEntry.put("actualSql", actualSql);logEntry.put("endTime", formattedEndTime);logEntry.put("executionTimeMs", executionTimeMs);logger.info("SQL Execution Log: {}", logEntry.toString());return results;}} else {int affected = stmt.executeUpdate();// Log execution infoLocalDateTime endTime = LocalDateTime.now();String formattedEndTime = endTime.format(dateFormatter);long executionTimeMs = java.time.Duration.between(startTime, endTime).toMillis();ObjectNode result = objectMapper.createObjectNode();result.put("affectedRows", affected);logEntry.put("type", "update");logEntry.put("affectedRows", affected);logEntry.put("actualSql", actualSql);logEntry.put("endTime", formattedEndTime);logEntry.put("executionTimeMs", executionTimeMs);logger.info("SQL Execution Log: {}", logEntry.toString());return result;}} catch (SQLException e) {// Log error infoLocalDateTime endTime = LocalDateTime.now();String formattedEndTime = endTime.format(dateFormatter);long executionTimeMs = java.time.Duration.between(startTime, endTime).toMillis();logEntry.put("error", e.getMessage());logEntry.put("endTime", formattedEndTime);logEntry.put("executionTimeMs", executionTimeMs);logger.error("SQL Execution Log: {}", logEntry.toString());throw e;}
}private static Map<String, Object> createParamMap(String sql, Object[] params) {Map<String, Object> paramMap = new LinkedHashMap<>();Matcher matcher = paramPattern.matcher(sql);int paramIndex = 0;while (matcher.find() && paramIndex < params.length) {// 获取?前后的上下文int start = Math.max(0, matcher.start() - 20);int end = Math.min(sql.length(), matcher.end() + 20);String context = sql.substring(start, end);// 尝试提取参数名String paramName = extractParamName(context);String key = paramName != null ?String.format("%s (param%d)", paramName, paramIndex + 1) :String.format("param%d", paramIndex + 1);Object value = params[paramIndex];paramMap.put(key, value != null ? value : "null");paramIndex++;}return paramMap;}private static String extractParamName(String context) {Pattern pattern = Pattern.compile("(WHERE|AND|OR|SET|INSERT|UPDATE|DELETE)\\s+([\\w_]+)\\s*=\\s*\\?",Pattern.CASE_INSENSITIVE);Matcher matcher = pattern.matcher(context);if (matcher.find()) {return matcher.group(2);}return null;}private static String replaceSqlParams(String sql, Object[] params) {StringBuilder result = new StringBuilder(sql);int offset = 0;for (int i = 0; i < params.length; i++) {int questionMarkIndex = result.indexOf("?", offset);if (questionMarkIndex == -1) break;String paramValue = params[i] == null ? "NULL" :params[i] instanceof String || params[i] instanceof Date ?"'" + params[i] + "'" : params[i].toString();result.replace(questionMarkIndex, questionMarkIndex + 1, paramValue);offset = questionMarkIndex + paramValue.length();}return result.toString();}public static void shutdown() {logger.info("Initiating JDBCUtils shutdown at {}",LocalDateTime.now().format(dateFormatter));scheduler.shutdownNow();try {if (!scheduler.awaitTermination(5, TimeUnit.SECONDS)) {logger.warn("Scheduler did not terminate within 5 seconds at {}",LocalDateTime.now().format(dateFormatter));}} catch (InterruptedException e) {Thread.currentThread().interrupt();logger.error("Shutdown interrupted at {}",LocalDateTime.now().format(dateFormatter), e);}if (dataSource != null) {dataSource.close();logger.info("Database connection pool closed at {}",LocalDateTime.now().format(dateFormatter));}}private static class ConnectionWrapper {private final Connection connection;private long lastAccessTime;private static final long TIMEOUT = 5000; // 5 seconds timeoutpublic ConnectionWrapper(Connection connection) {this.connection = connection;this.lastAccessTime = System.currentTimeMillis();}public Connection getConnection() {return connection;}public void renew() {this.lastAccessTime = System.currentTimeMillis();}public boolean isExpired() {return System.currentTimeMillis() - lastAccessTime > TIMEOUT;}}
}

resource目录的log4j2.xml文件内容

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN"><Properties><!-- 定义日志格式，添加颜色支持 --><Property name="LOG_PATTERN">%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %highlight{%-5level}{ERROR=red, WARN=yellow, INFO=green} %logger{36} - %msg%n</Property></Properties><Appenders><!-- 控制台输出 --><Console name="Console" target="SYSTEM_OUT"><PatternLayout pattern="${LOG_PATTERN}"/></Console></Appenders><Loggers><!-- 根日志记录器配置 --><Root level="INFO"><AppenderRef ref="Console"/></Root></Loggers>
</Configuration>

在这里插入图片描述
在这里插入图片描述

http://www.lryc.cn/news/468939.html

相关文章：

  • jQuery快速填充非form数据
  • 语音语言模型最新综述！ 关于GPT-4o背后技术的尝试
  • 根据用户选择的行和列数据构造数据结构(跨行跨列)
  • Spark教程5-基本结构化操作
  • 内置数据类型、变量名、字符串、数字及其运算、数字的处理、类型转换
  • Win/Mac/Android/iOS怎麼刪除代理設置？
  • 数据结构------手撕顺序表
  • UDP(用户数据报协议)端口监控
  • 【Java小白图文教程】-05-数组和排序算法详解
  • OpenCV视觉分析之目标跟踪(1)计算密集光流的类DISOpticalFlow的介绍
  • Lucas带你手撕机器学习——套索回归
  • 面试中的一个基本问题：如何在数据库中存储密码？
  • XML HTTP Request
  • TLS协议基本原理与Wireshark分析
  • 当遇到 502 错误（Bad Gateway）怎么办
  • 学习记录：js算法（七十五）： 加油站
  • 强心剂！EEMD-MPE-KPCA-LSTM、EEMD-MPE-LSTM、EEMD-PE-LSTM故障识别、诊断
  • yarn的安装与使用以及与npm的区别（安装过程中可能会遇到的问题）
  • 大数据行业预测
  • 可能是NextJs(使用ssr、api route)打包成桌面端(nextron、electron、tauri)的最佳解决方式
  • 二百七十、Kettle——ClickHouse中增量导入清洗数据错误表
  • CentOS6升级OpenSSH9.2和OpenSSL3
  • 2024 年 MathorCup 数学应用挑战赛——大数据竞赛-赛道 A：台风的分类与预测
  • kotlin实现viewpager
  • RabbitMQ最新版本4.0.2在Windows下的安装及使用
  • 东方博宜1180 - 数字出现次数
  • LeetCode: 3274. 检查棋盘方格颜色是否相同
  • datax编译并测试
  • 2-133 基于matlab的粒子群算法PSO优化BP神经网络
  • 复盘秋招22场面试（四）形势重新评估与后续措施