From 5d2411baa5ae84890430a2d7e360fc6777054797 Mon Sep 17 00:00:00 2001
From: shicf <shi_chongfu@163.com>
Date: 星期一, 08 九月 2025 11:44:48 +0800
Subject: [PATCH] 判断事务是否自动提交，执行提交 ，添加日志输出
---
product-server-datasource/src/main/java/com/product/datasource/connection/ConnectionManager.java | 487 ++++++++++++++++++++++++++++++-----------------------
1 files changed, 277 insertions(+), 210 deletions(-)
diff --git a/product-server-datasource/src/main/java/com/product/datasource/connection/ConnectionManager.java b/product-server-datasource/src/main/java/com/product/datasource/connection/ConnectionManager.java
index 0718459..c278ca4 100644
--- a/product-server-datasource/src/main/java/com/product/datasource/connection/ConnectionManager.java
+++ b/product-server-datasource/src/main/java/com/product/datasource/connection/ConnectionManager.java
@@ -1,5 +1,9 @@
package com.product.datasource.connection;
+import cn.hutool.core.util.ArrayUtil;
+import cn.hutool.core.util.NumberUtil;
+import com.alibaba.druid.pool.DruidDataSource;
+import com.google.common.collect.Maps;
import com.product.common.lang.StringUtils;
import com.product.core.config.Global;
import com.product.core.exception.BaseException;
@@ -9,7 +13,10 @@
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
+import javax.sql.DataSource;
import java.sql.*;
+import java.util.Map;
+import java.util.concurrent.ConcurrentHashMap;
/**
* @Author cheng
@@ -18,237 +25,297 @@
*/
public class ConnectionManager {
- static Logger log = LoggerFactory.getLogger(ConnectionManager.class);
+ static Logger log = LoggerFactory.getLogger(ConnectionManager.class);
- public static void main(String[] args) {
- DataBaseEntity DataBaseEntity = new DataBaseEntity(DataBaseType.MYSQL.getValue());
- DataBaseEntity.setDbName("product_db_v2.0.0");
- DataBaseEntity.setUserName("root");
- DataBaseEntity.setIp("127.0.0.1");
- DataBaseEntity.setPassWord("root123");
- DataBaseEntity.setPort("3306");
- DataBaseEntity.setCustomParams("&serverTimezone=Asia/Shanghai");
- getConnection(DataBaseEntity);
- }
+ private static final Map<String, DruidDataSource> DB_DRUID_DATA_SOURCE_MAP = new ConcurrentHashMap<>();
- /**
- * 鑾峰彇绯荤粺杩炴帴
- *
- * @return
- */
- public static Connection getSystemConnection() throws BaseException {
- String url = Global.getSystemConfig("data.source.url", "");
- String userName = Global.getSystemConfig("data.source.user", "");
- String userPassword = Global.getSystemConfig("data.source.password", "");
- //鎵归噺鎻愪氦鍙傛暟鏄惁寮�鍚�
- if (url.indexOf("rewriteBatchedStatements=true") == -1) {
- url += "&rewriteBatchedStatements=true";
- }
- DataBaseEntity DataBaseEntity = new DataBaseEntity(DataBaseType.MYSQL.getValue());
- DataBaseEntity.setUserName(userName);
- DataBaseEntity.setPassWord(userPassword);
- return getConnection(url, DataBaseEntity);
- }
+ public static void main(String[] args) {
+ DataBaseEntity DataBaseEntity = new DataBaseEntity(DataBaseType.MYSQL.getValue());
+ DataBaseEntity.setDbName("product_db_v2.0.0");
+ DataBaseEntity.setUserName("root");
+ DataBaseEntity.setIp("127.0.0.1");
+ DataBaseEntity.setPassWord("root123");
+ DataBaseEntity.setPort("3306");
+ DataBaseEntity.setCustomParams("&serverTimezone=Asia/Shanghai");
+ getConnection(DataBaseEntity);
+ }
+
+ /**
+ * 鑾峰彇绯荤粺杩炴帴
+ *
+ * @return
+ */
+ public static Connection getSystemConnection() throws BaseException {
+ String url = Global.getSystemConfig("data.source.url", "");
+ String userName = Global.getSystemConfig("data.source.user", "");
+ String userPassword = Global.getSystemConfig("data.source.password", "");
+ //鎵归噺鎻愪氦鍙傛暟鏄惁寮�鍚�
+ if (url.indexOf("rewriteBatchedStatements=true") == -1) {
+ url += "&rewriteBatchedStatements=true";
+ }
+ DataBaseEntity DataBaseEntity = new DataBaseEntity(DataBaseType.MYSQL.getValue());
+ DataBaseEntity.setUserName(userName);
+ DataBaseEntity.setPassWord(userPassword);
+ return getConnection(url, DataBaseEntity);
+ }
- /**
- * 鑾峰彇杩炴帴
- *
- * @param dbe
- * @return
- * @throws BaseException
- */
- public static Connection getConnection(DataBaseEntity dbe) throws BaseException {
+ /**
+ * 鑾峰彇杩炴帴
+ *
+ * @param dbe
+ * @return
+ * @throws BaseException
+ */
+ public static Connection getConnection(DataBaseEntity dbe) throws BaseException {
- try {
- if (dbe.getDataBaseType() == null) {
- throw new BaseException(ErrorCode.UNKNOWN_DATABASE_TYPE);
- }
- //瑁呰浇椹卞姩
- Class.forName(dbe.getDataBaseType().getDriver());
- if (DataBaseType.MYSQL.equals(dbe.getDataBaseType())) {
- // MYSQL
- return getMysqlConnection(dbe);
- } else if (DataBaseType.ORACLE.equals(dbe.getDataBaseType())) {
- // ORACLE
- return getOracleConnection(dbe);
- } else if (DataBaseType.SQLSERVER.equals(dbe.getDataBaseType())) {
- // SQLSERVER
- return getSqlServerConnection(dbe);
- } else if (DataBaseType.INFORMIX.equals(dbe.getDataBaseType())) {
- // INFORMIX
- return getInformixConnection(dbe);
- } else if (DataBaseType.PSQL.equals(dbe.getDataBaseType())) {
- return getPSQLConnection(dbe);
- }
- throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
- } catch (BaseException e) {
- e.printStackTrace();
- throw e;
- } catch (Exception e) {
- e.printStackTrace();
- throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
- }
- }
+ try {
+ if (dbe.getDataBaseType() == null) {
+ throw new BaseException(ErrorCode.UNKNOWN_DATABASE_TYPE);
+ }
+ //瑁呰浇椹卞姩
+ Class.forName(dbe.getDataBaseType().getDriver());
+ if (DataBaseType.MYSQL.equals(dbe.getDataBaseType())) {
+ // MYSQL
+ return getMysqlConnection(dbe);
+ } else if (DataBaseType.ORACLE.equals(dbe.getDataBaseType())) {
+ // ORACLE
+ return getOracleConnection(dbe);
+ } else if (DataBaseType.SQLSERVER.equals(dbe.getDataBaseType())) {
+ // SQLSERVER
+ return getSqlServerConnection(dbe);
+ } else if (DataBaseType.INFORMIX.equals(dbe.getDataBaseType())) {
+ // INFORMIX
+ return getInformixConnection(dbe);
+ } else if (DataBaseType.PSQL.equals(dbe.getDataBaseType())) {
+ return getPSQLConnection(dbe);
+ }
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ } catch (BaseException e) {
+ e.printStackTrace();
+ throw e;
+ } catch (Exception e) {
+ e.printStackTrace();
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ }
+ }
- public static Connection getPSQLConnection(DataBaseEntity dbe) {
- String templateUrl = "jdbc:postgresql://%s:%s/%s%s";
- //鏇挎崲URL妯℃澘
- String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams());
- return getConnection(url, dbe);
- }
+ public static Connection getPSQLConnection(DataBaseEntity dbe) {
+ String templateUrl = "jdbc:postgresql://%s:%s/%s%s";
+ //鏇挎崲URL妯℃澘
+ String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams());
+ return getConnection(url, dbe);
+ }
- public static long getConnectionSystemTime(Connection connection, DataBaseType dbType) throws BaseException {
- String queryTimeSql;
- if (DataBaseType.MYSQL.equals(dbType)) {
- queryTimeSql = "select current_timestamp(3) `timestamp` ";
- } else if (DataBaseType.ORACLE.equals(dbType)) {
- queryTimeSql = "select systimestamp as timestamp from dual";
- }
+ public static long getConnectionSystemTime(Connection connection, DataBaseType dbType) throws BaseException {
+ String queryTimeSql;
+ if (DataBaseType.MYSQL.equals(dbType)) {
+ queryTimeSql = "select current_timestamp(3) `timestamp` ";
+ } else if (DataBaseType.ORACLE.equals(dbType)) {
+ queryTimeSql = "select systimestamp as timestamp from dual";
+ }
// else if (DriverTypeEnum.SqlServer.equals(dbType)) {
// queryTimeSql = "SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 00:00:00.000', GETUTCDATE())) * 60000 + DATEPART(S,GETUTCDATE()) * 1000 + DATEPART(MS, GETUTCDATE()) as timestamp";
// } else if (DriverTypeEnum.Informix.equals(dbType)) {
// queryTimeSql = "select current as timestamp from sysmaster:sysshmvals";
// }
- else {
- throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
- }
- try (PreparedStatement pst = connection.prepareStatement(queryTimeSql); ResultSet resultSet = pst.executeQuery();) {
- while (resultSet.next()) {
- final Object timestamp = resultSet.getObject("timestamp");
- if (timestamp != null) {
- if (timestamp instanceof Time) {
- return ((Time) timestamp).getTime();
- } else if (timestamp instanceof Timestamp) {
- return ((Timestamp) timestamp).getTime();
- } else {
- return resultSet.getTimestamp("timestamp").getTime();
- }
- }
- return resultSet.getLong("timestamp");
- }
- throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
- } catch (BaseException e) {
- throw e;
- } catch (Exception e) {
- e.printStackTrace();
- throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
- }
- }
+ else {
+ throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
+ }
+ try (PreparedStatement pst = connection.prepareStatement(queryTimeSql); ResultSet resultSet = pst.executeQuery();) {
+ while (resultSet.next()) {
+ final Object timestamp = resultSet.getObject("timestamp");
+ if (timestamp != null) {
+ if (timestamp instanceof Time) {
+ return ((Time) timestamp).getTime();
+ } else if (timestamp instanceof Timestamp) {
+ return ((Timestamp) timestamp).getTime();
+ } else {
+ return resultSet.getTimestamp("timestamp").getTime();
+ }
+ }
+ return resultSet.getLong("timestamp");
+ }
+ throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
+ } catch (BaseException e) {
+ throw e;
+ } catch (Exception e) {
+ e.printStackTrace();
+ throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL);
+ }
+ }
- /**
- * 鑾峰彇MySql杩炴帴
- *
- * @param dbe
- * @return
- * @throws SQLException
- */
- private static Connection getMysqlConnection(DataBaseEntity dbe) {
- String templateUrl = "jdbc:mysql://%s:%s/%s%s";
- //鏇挎崲URL妯℃澘
- String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams());
- return getConnection(url, dbe);
- }
+ /**
+ * 鑾峰彇MySql杩炴帴
+ *
+ * @param dbe
+ * @return
+ * @throws SQLException
+ */
+ private static Connection getMysqlConnection(DataBaseEntity dbe) {
+ String templateUrl = "jdbc:mysql://%s:%s/%s%s";
+ //鏇挎崲URL妯℃澘
+ String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams());
+ return getConnection(url, dbe);
+ }
- /**
- * 鑾峰彇Sql Server杩炴帴
- *
- * @param dbe
- * @return
- * @throws SQLException
- */
- private static Connection getSqlServerConnection(DataBaseEntity dbe) {
- String dbInstance = dbe.getDbInstance();
- String url;
- if (StringUtils.isEmpty(dbInstance)) {
- url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbName());
- } else {
- // 鏈夊疄渚嬪悕
- url = String.format("jdbc:sqlserver://%s:%s;instanceName=%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbInstance(), dbe.getDbName());
- }
- return getConnection(url, dbe);
- }
+ /**
+ * 鑾峰彇Sql Server杩炴帴
+ *
+ * @param dbe
+ * @return
+ * @throws SQLException
+ */
+ private static Connection getSqlServerConnection(DataBaseEntity dbe) {
+ String dbInstance = dbe.getDbInstance();
+ String url;
+ if (StringUtils.isEmpty(dbInstance)) {
+ url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbName());
+ } else {
+ // 鏈夊疄渚嬪悕
+ url = String.format("jdbc:sqlserver://%s:%s;instanceName=%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbInstance(), dbe.getDbName());
+ }
+ return getConnection(url, dbe);
+ }
- /**
- * 鑾峰彇Oracle杩炴帴
- *
- * @param dbe
- * @return
- * @throws SQLException
- */
- private static Connection getOracleConnection(DataBaseEntity dbe) {
- String url;
- if (!StringUtils.isEmpty(dbe.getSid())) {
- url = String.format("jdbc:oracle:thin:@%s:%s:%s", dbe.getIp(), dbe.getPort(), dbe.getSid());
- } else if (!StringUtils.isEmpty(dbe.getServerName())) {
- url = String.format("jdbc:oracle:thin:@//%s:%s/%s", dbe.getIp(), dbe.getPort(), dbe.getServerName());
- } else {
- throw new BaseException(ErrorCode.GET_ORACLE_SID_SERVERNAME_EMPTY);
- }
- return getConnection(url, dbe);
- }
+ /**
+ * 鑾峰彇Oracle杩炴帴
+ *
+ * @param dbe
+ * @return
+ * @throws SQLException
+ */
+ private static Connection getOracleConnection(DataBaseEntity dbe) {
+ String url;
+ String[] params = {dbe.getIp(), dbe.getPort(), null};
+ if (!StringUtils.isEmpty(dbe.getSid())) {
+ url = String.format("jdbc:oracle:thin:@%s:%s:%s", dbe.getIp(), dbe.getPort(), dbe.getSid());
+ params[2] = dbe.getSid();
+ } else if (!StringUtils.isEmpty(dbe.getServerName())) {
+ url = String.format("jdbc:oracle:thin:@//%s:%s/%s", dbe.getIp(), dbe.getPort(), dbe.getServerName());
+ params[2] = dbe.getServerName();
+ } else {
+ throw new BaseException(ErrorCode.GET_ORACLE_SID_SERVERNAME_EMPTY);
+ }
+ Boolean enabling = Global.getPropertyToBoolean("data.system.oracle.connection-pool.enabling", "false");
+ if (enabling) {
+ synchronized (url.intern()) {
+ DruidDataSource druidDataSource = DB_DRUID_DATA_SOURCE_MAP.get(ArrayUtil.join(params, ","));
+ if (druidDataSource == null || druidDataSource.isClosed()) {
+ druidDataSource = new DruidDataSource();
+ druidDataSource.setUrl(url);
+ druidDataSource.setUsername(dbe.getUserName());
+ druidDataSource.setPassword(dbe.getPassWord());
+ // 鍒濆鍖栨椂寤虹珛鐗╃悊杩炴帴鐨勪釜鏁�
+ druidDataSource.setInitialSize(getProperty("data.system.oracle.connection-pool.initial-size", 20));
+ // 鏈�澶ф椿鍔ㄨ繛鎺ユ暟
+ druidDataSource.setMaxActive(getProperty("data.system.oracle.connection-pool.max-active", 100));
+ // 鏈�灏忕┖闂茶繛鎺ユ暟
+ druidDataSource.setMinIdle(getProperty("data.system.oracle.connection-pool.min-idle", 30));
+ // 鏍￠獙鏌ヨ璇彞
+ druidDataSource.setValidationQuery(dbe.getDataBaseType().getValidationQuery());
+ // 褰撹繛鎺ョ┖闂叉椂鏄惁娴嬭瘯杩炴帴鏈夋晥鎬�
+ druidDataSource.setTestWhileIdle(true);
+ // 涓ゆ绌洪棽杩炴帴娓呴櫎涔嬮棿鐨勬椂闂撮棿闅� 12灏忔椂
+ druidDataSource.setTimeBetweenEvictionRunsMillis(43200000L);
+ druidDataSource.setMaxWait(60000L);
+ druidDataSource.setPoolPreparedStatements(true);
+ druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(100);
+ druidDataSource.setRemoveAbandoned(true);
+ //鍗婂皬鏃跺己鍒跺綊杩樿繛鎺�
+ druidDataSource.setRemoveAbandonedTimeout(60 * 30);
+ DB_DRUID_DATA_SOURCE_MAP.put(ArrayUtil.join(params, ","), druidDataSource);
+ }
+ try {
+ //鑾疯幏鍙栬繛鎺�
+ Connection connection = druidDataSource.getConnection();
+ //娴嬭瘯杩炴帴
+ if (!connectionValidity(connection, dbe.getDataBaseType())) {
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ }
+ return connection;
+ } catch (BaseException e) {
+ throw e;
+ } catch (Exception e) {
+ e.printStackTrace();
+ log.error("鑾峰彇閾炬帴澶辫触", e);
+ log.error(url);
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ }
+ }
+ }
+ return getConnection(url, dbe);
+ }
- /**
- * 鑾峰彇Informix 杩炴帴
- *
- * @param dbe
- * @return
- * @throws SQLException
- */
- private static Connection getInformixConnection(DataBaseEntity dbe) throws BaseException {
- String url = String.format("jdbc:informix-sqli://%s:%s/%s:INFORMIXSERVER=%s;%s", dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getDbInstance(), dbe.getCustomParams());
- return getConnection(url, dbe);
- }
+ private static int getProperty(String key, int defaultValue) {
+ String systemConfig = Global.getSystemConfig(key, defaultValue + "");
+ if (!NumberUtil.isNumber(systemConfig)) {
+ return defaultValue;
+ }
+ return NumberUtil.parseInt(systemConfig);
+ }
- /**
- * 鑾峰彇杩炴帴
- *
- * @param url jdbc杩炴帴url
- * @param dbe 鏁版嵁搴撳熀鏈暟鎹�
- * @return
- * @throws SQLException
- */
- private static Connection getConnection(String url, DataBaseEntity dbe) throws BaseException {
- try {
- //鑾疯幏鍙栬繛鎺�
- Connection connection = DriverManager.getConnection(url, dbe.getUserName(), dbe.getPassWord());
- //娴嬭瘯杩炴帴
- if (!connectionValidity(connection, dbe.getDataBaseType())) {
- throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
- }
+ /**
+ * 鑾峰彇Informix 杩炴帴
+ *
+ * @param dbe
+ * @return
+ * @throws SQLException
+ */
+ private static Connection getInformixConnection(DataBaseEntity dbe) throws BaseException {
+ String url = String.format("jdbc:informix-sqli://%s:%s/%s:INFORMIXSERVER=%s;%s", dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getDbInstance(), dbe.getCustomParams());
+ return getConnection(url, dbe);
+ }
- return connection;
- } catch (BaseException e) {
- throw e;
- } catch (Exception e) {
- e.printStackTrace();
- log.error("鑾峰彇閾炬帴澶辫触", e);
- log.error(url);
- throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
- }
- }
+ /**
+ * 鑾峰彇杩炴帴
+ *
+ * @param url jdbc杩炴帴url
+ * @param dbe 鏁版嵁搴撳熀鏈暟鎹�
+ * @return
+ * @throws SQLException
+ */
+ private static Connection getConnection(String url, DataBaseEntity dbe) throws BaseException {
+ try {
+ //鑾疯幏鍙栬繛鎺�
+ Connection connection = DriverManager.getConnection(url, dbe.getUserName(), dbe.getPassWord());
+ //娴嬭瘯杩炴帴
+ if (!connectionValidity(connection, dbe.getDataBaseType())) {
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ }
- /**
- * 楠岃瘉杩炴帴鏈夋晥鎬�
- *
- * @param connection
- * @param DriverTypeEnum
- * @return
- */
- public static boolean connectionValidity(Connection connection, DataBaseType DriverTypeEnum) {
- try {
- String validationQuery = DriverTypeEnum.getValidationQuery();
- try (PreparedStatement pst = connection.prepareStatement(validationQuery)) {
- boolean execute = pst.execute();
- return execute;
- }
- } catch (Exception e) {
- log.error("楠岃瘉杩炴帴鏈夋晥鎬уけ璐�", e);
- return false;
- }
+ return connection;
+ } catch (BaseException e) {
+ throw e;
+ } catch (Exception e) {
+ e.printStackTrace();
+ log.error("鑾峰彇閾炬帴澶辫触", e);
+ log.error(url);
+ throw new BaseException(ErrorCode.GET_CONNECTION_FAIL);
+ }
+ }
- }
+ /**
+ * 楠岃瘉杩炴帴鏈夋晥鎬�
+ *
+ * @param connection
+ * @param DriverTypeEnum
+ * @return
+ */
+ public static boolean connectionValidity(Connection connection, DataBaseType DriverTypeEnum) {
+ try {
+ String validationQuery = DriverTypeEnum.getValidationQuery();
+ try (PreparedStatement pst = connection.prepareStatement(validationQuery)) {
+ boolean execute = pst.execute();
+ return execute;
+ }
+ } catch (Exception e) {
+ log.error("楠岃瘉杩炴帴鏈夋晥鎬уけ璐�", e);
+ return false;
+ }
+
+ }
}
--
Gitblit v1.9.2