数据同步新增连接池配置

zrj

2024-07-03 0b1e9e70818f0e3eb32dd6c029d42d93236ecdc6

数据同步新增连接池配置

 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();
            }
        }
    }

 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;

 src/main/java/org/springblade/binlog/constant/FromConstants.java

New file
@@ -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;
        }
    }

}



 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;

 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

 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

 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