From 0b1e9e70818f0e3eb32dd6c029d42d93236ecdc6 Mon Sep 17 00:00:00 2001
From: zrj <646384940@qq.com>
Date: Wed, 03 Jul 2024 12:07:01 +0800
Subject: [PATCH] 数据同步新增连接池配置
---
src/main/java/org/springblade/binlog/constant/FromConstants.java | 110 +++++++++++++++++++++++++++
src/main/java/org/springblade/binlog/listener/TourBinLogListener.java | 2
src/main/java/org/springblade/binlog/client/MysqlClient.java | 83 ++++++++------------
src/main/resources/application-dev.yml | 6 -
src/main/resources/application-prod.yml | 6 -
src/main/resources/application-test.yml | 6 -
src/main/java/org/springblade/binlog/constant/BinLogConstants.java | 9 --
7 files changed, 147 insertions(+), 75 deletions(-)
diff --git a/src/main/java/org/springblade/binlog/client/MysqlClient.java b/src/main/java/org/springblade/binlog/client/MysqlClient.java
index 8d8f720..88fff9f 100644
--- a/src/main/java/org/springblade/binlog/client/MysqlClient.java
+++ b/src/main/java/org/springblade/binlog/client/MysqlClient.java
@@ -1,15 +1,13 @@
package org.springblade.binlog.client;
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
-import org.springblade.binlog.constant.BinLogConstants;
+import org.springblade.binlog.constant.FromConstants;
import org.springframework.stereotype.Component;
-import javax.annotation.PostConstruct;
-import javax.annotation.Resource;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.SQLException;
+import java.sql.*;
+import java.util.Properties;
/**
* mysql 客户端连接
@@ -18,20 +16,26 @@
@Component
public class MysqlClient {
- //声明对象
- private static MysqlClient mysqlClient;
+ private static String url = "jdbc:mysql://127.0.0.1:3308/srjw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
+ private static String username = "root";
+ private static String password = "root";
+ private static final HikariDataSource ds = createDataSource();
- @Resource
- private BinLogConstants binLogConstants;
+ public static HikariDataSource createDataSource() {
+ Properties properties = System.getProperties();
+// String profile = properties.getProperty("blade.env");
+ String profile = properties.getProperty("spring.profiles.active");
+ HikariConfig config = new HikariConfig();
+ config.setJdbcUrl(FromConstants.setUrl(profile));
+ config.setUsername(FromConstants.setUsername(profile));
+ config.setPassword(FromConstants.setPassword(profile));
+ config.addDataSourceProperty("cachePrepStmts", "true");
+ config.addDataSourceProperty("prepStmtCacheSize", "250");
+ config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
- /**
- * 初始化
- */
- @PostConstruct
- public void init(){
- mysqlClient = this;
- mysqlClient.binLogConstants = this.binLogConstants;
+ HikariDataSource ds = new HikariDataSource(config);
+ return ds;
}
/**
@@ -39,39 +43,22 @@
* @param sql
*/
public static void sqlConnect(String sql,Integer type){
- String driver = "com.mysql.cj.jdbc.Driver";
- String url = mysqlClient.binLogConstants.getFromUrl();
- String user = mysqlClient.binLogConstants.getFromUsername();
- String password = mysqlClient.binLogConstants.getFromPassword();
- Connection conn = null;
- PreparedStatement ps = null;
+ Connection connection = null;
+ PreparedStatement preparedStatement = null;
+ ResultSet resultSet = null;
try {
- Class.forName ( driver );
- conn = (Connection) DriverManager.getConnection ( url, user, password );
- if (!conn.isClosed ()) {
- log.info( "数据库连接成功!" );
- ps = conn.prepareStatement ( sql );
- //判断是否为修改，删除
- if (type==1){
- //修改删除
- ps.executeUpdate();
- log.info( "数据已发送成功!" );
- }else {
- //新增
- ps.execute();
- log.info( "数据已发送成功!" );
- }
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
+ connection = ds.getConnection();
+ preparedStatement = connection.prepareStatement(sql);
+ preparedStatement.executeUpdate();
} catch (SQLException e) {
- e.printStackTrace ();
- }finally {
+ e.printStackTrace();
+ } finally {
try {
- ps.close();
- conn.close();
- } catch (SQLException throwables) {
- throwables.printStackTrace();
+ if (resultSet != null) resultSet.close();
+ if (preparedStatement != null) preparedStatement.close();
+ if (connection != null) connection.close();
+ } catch (SQLException e) {
+ e.printStackTrace();
}
}
}
diff --git a/src/main/java/org/springblade/binlog/constant/BinLogConstants.java b/src/main/java/org/springblade/binlog/constant/BinLogConstants.java
index 446c3fd..bfcda24 100644
--- a/src/main/java/org/springblade/binlog/constant/BinLogConstants.java
+++ b/src/main/java/org/springblade/binlog/constant/BinLogConstants.java
@@ -31,15 +31,6 @@
@Value("${binlog.table}")
private String table;
- @Value("${binlog.from.datasource.url}")
- private String fromUrl;
-
- @Value("${binlog.from.datasource.username}")
- private String fromUsername;
-
- @Value("${binlog.from.datasource.password}")
- private String fromPassword;
-
public static final int consumerThreads = 5;
// public static final long queueSleep = 1000;
diff --git a/src/main/java/org/springblade/binlog/constant/FromConstants.java b/src/main/java/org/springblade/binlog/constant/FromConstants.java
new file mode 100644
index 0000000..f81f190
--- /dev/null
+++ b/src/main/java/org/springblade/binlog/constant/FromConstants.java
@@ -0,0 +1,110 @@
+package org.springblade.binlog.constant;
+
+import org.springblade.core.launch.constant.AppConstant;
+
+/**
+ * 监听配置信息
+ *
+ * @author zrj
+ * @since 2024/07/03
+ **/
+public interface FromConstants {
+ /**
+ * URL dev 地址
+ */
+ String URL_DEV_ADDR = "jdbc:mysql://127.0.0.1:3308/srjw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
+
+ /**
+ * URL prod 地址
+ */
+ String URL_PROD_ADDR = "jdbc:mysql://czfw_wx_web.xzga.top:13005/ewm_shangrao?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
+
+ /**
+ * URL test 地址
+ */
+ String URL_TEST_ADDR = "jdbc:mysql://127.0.0.1:3308/srjw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
+
+ /**
+ * username dev 地址
+ */
+ String USERNAME_DEV_ADDR = "root";
+
+ /**
+ * username prod 地址
+ */
+ String USERNAME_PROD_ADDR = "ewm_user";
+
+ /**
+ * username test 地址
+ */
+ String USERNAME_TEST_ADDR = "root";
+
+ /**
+ * password dev 地址
+ */
+ String PASSWORD_DEV_ADDR = "root";
+
+ /**
+ * username prod 地址
+ */
+ String PASSWORD_PROD_ADDR = "ft@135246";
+
+ /**
+ * username test 地址
+ */
+ String PASSWORD_TEST_ADDR = "root";
+
+ /**
+ * 动态获取url
+ *
+ * @param profile 环境变量
+ * @return
+ */
+ static String setUrl(String profile) {
+ switch (profile) {
+ case (AppConstant.PROD_CODE):
+ return URL_PROD_ADDR;
+ case (AppConstant.TEST_CODE):
+ return URL_TEST_ADDR;
+ default:
+ return URL_DEV_ADDR;
+ }
+ }
+
+ /**
+ * 动态获取 USERNAME
+ *
+ * @param profile 环境变量
+ * @return
+ */
+ static String setUsername(String profile) {
+ switch (profile) {
+ case (AppConstant.PROD_CODE):
+ return USERNAME_PROD_ADDR;
+ case (AppConstant.TEST_CODE):
+ return USERNAME_TEST_ADDR;
+ default:
+ return USERNAME_DEV_ADDR;
+ }
+ }
+
+ /**
+ * 动态获取 password
+ *
+ * @param profile 环境变量
+ * @return
+ */
+ static String setPassword(String profile) {
+ switch (profile) {
+ case (AppConstant.PROD_CODE):
+ return PASSWORD_PROD_ADDR;
+ case (AppConstant.TEST_CODE):
+ return PASSWORD_TEST_ADDR;
+ default:
+ return PASSWORD_DEV_ADDR;
+ }
+ }
+
+}
+
+
diff --git a/src/main/java/org/springblade/binlog/listener/TourBinLogListener.java b/src/main/java/org/springblade/binlog/listener/TourBinLogListener.java
index e854e73..bc67b44 100644
--- a/src/main/java/org/springblade/binlog/listener/TourBinLogListener.java
+++ b/src/main/java/org/springblade/binlog/listener/TourBinLogListener.java
@@ -1,6 +1,8 @@
package org.springblade.binlog.listener;
import cn.hutool.core.collection.CollectionUtil;
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springblade.binlog.client.MysqlClient;
import org.springblade.binlog.config.DataSourceConfig;
diff --git a/src/main/resources/application-dev.yml b/src/main/resources/application-dev.yml
index aff44eb..50f8ad8 100644
--- a/src/main/resources/application-dev.yml
+++ b/src/main/resources/application-dev.yml
@@ -51,9 +51,3 @@
table: jczz_house,jczz_household,jczz_house_rental,jczz_user_house_label,jczz_label,jczz_place,jczz_place_check,jczz_place_door,jczz_place_ext,jczz_place_poi_label,jczz_place_practitioner,jczz_police_affairs_grid,jczz_task_label_reporting_event,jczz_patrol_group,jczz_patrol_group_item,jczz_patrol_record,blade_attach_data,jczz_category,jczz_grid,jczz_backblast_pub_record,jczz_backblast_warn_han_rec,jczz_dispute_record,jczz_police_alarm_records,jczz_task_residence_permit_apply,jczz_backblast_pub_person,jczz_polic_trajectory_point
# table: blade_attach_data
enabled: true
- # 目标数据库
- from:
- datasource:
- url: jdbc:mysql://106.225.193.35:3306/srjw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
- username: root
- password: HCyj@2022
diff --git a/src/main/resources/application-prod.yml b/src/main/resources/application-prod.yml
index eaa8f8f..5aa9fb7 100644
--- a/src/main/resources/application-prod.yml
+++ b/src/main/resources/application-prod.yml
@@ -50,9 +50,3 @@
table: jczz_house,jczz_household,jczz_house_rental,jczz_user_house_label,jczz_label,jczz_place,jczz_place_check,jczz_place_door,jczz_place_ext,jczz_place_poi_label,jczz_place_practitioner,jczz_task_label_reporting_event,jczz_patrol_group,jczz_patrol_group_item,jczz_patrol_record,blade_attach_data,jczz_category,jczz_grid,jczz_backblast_pub_record,jczz_backblast_warn_han_rec,jczz_dispute_record,jczz_police_alarm_records,jczz_task_residence_permit_apply,jczz_backblast_pub_person,jczz_polic_trajectory_point
# table: blade_attach_data
enabled: true
- # 目标数据库
- from:
- datasource:
- url: jdbc:mysql://czfw_wx_web.xzga.top:13005/ewm_shangrao?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
- username: ewm_user
- password: ft@135246
diff --git a/src/main/resources/application-test.yml b/src/main/resources/application-test.yml
index 100430f..5f607d1 100644
--- a/src/main/resources/application-test.yml
+++ b/src/main/resources/application-test.yml
@@ -51,9 +51,3 @@
table: jczz_house,jczz_household,jczz_house_rental,jczz_user_house_label,jczz_label,jczz_place,jczz_place_check,jczz_place_door,jczz_place_ext,jczz_place_poi_label,jczz_place_practitioner,jczz_police_affairs_grid,jczz_task_label_reporting_event,jczz_patrol_group,jczz_patrol_group_item,jczz_patrol_record,blade_attach_data,jczz_category,jczz_grid,jczz_backblast_pub_record,jczz_backblast_warn_han_rec,jczz_dispute_record,jczz_police_alarm_records,jczz_task_residence_permit_apply,jczz_backblast_pub_person,jczz_polic_trajectory_point
# table: blade_attach_data
enabled: true
- # 目标数据库
- from:
- datasource:
- url: jdbc:mysql://106.225.193.35:3306/srjw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
- username: root
- password: HCyj@2022
--
Gitblit v1.9.3