Springboot+mysqlマルチデータソース+ライブラリ間トランザクション

65277 ワード
バックエンド
最初のステップのソースコードDemoアドレス
まずmysqlがxaトランザクションのサポートを開始したかどうかを確認します.innodb_support_xaがONの場合はオンになります
show variables like 'innodb_support_xa';

SpringBoot spring-boot-starter-jta-atomikos開発パッケージの導入
        
            org.springframework.boot
            spring-boot-starter-jta-atomikos

druidとmysql開発パッケージを導入し、バージョンを一致させます.一致しないため、ソースコードに深く入り込んで何度もエラーを確認しました.
        
        
            mysql
            mysql-connector-java
            8.0.11
        

        
        
            com.alibaba
            druid
            1.1.21

application.yml構成
spring:
  application:
    name: springBoot-multipleDataSources
  #druid         
  datasource:
    sys:
      url: jdbc:mysql://123.206.19.217:3306/system?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false&autoReconnect=true&pinGlobalTxToPhysicalConnection=true
      username: root
      password: lzq199528
      driverClassName: com.mysql.cj.jdbc.Driver
    base:
      url: jdbc:mysql://123.206.19.217:3306/base_data?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false&autoReconnect=true&pinGlobalTxToPhysicalConnection=true
      username: root
      password: lzq199528
      driverClassName: com.mysql.cj.jdbc.Driver

    #        
    pool:
      initialSize: 5
      minIdle: 10
      maxActive: 20
      maxWait: 6000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: SELECT 1
      validationQueryTimeout: 10000
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,wall
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      useGlobalDataSourceStat: true
#mybatis     
mybatis:
  type-aliases-package: com.multiple.data.sources.domain.**
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mappers/**/*.xml

Jtaトランザクション管理構成の構成
package com.multiple.data.sources.conf.datasource;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.jta.JtaTransactionManager;

import javax.transaction.UserTransaction;

/**
 * @author admin
 */
@Configuration
public class JtaTransactionManagerConfig {
    @Bean(name = "xatx")
    public JtaTransactionManager regTransactionManager () {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        UserTransaction userTransaction = new UserTransactionImp();
        return new JtaTransactionManager(userTransaction, userTransactionManager);
    }
}

データ・ソースの構成
package com.multiple.data.sources.conf.datasource;

import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * @author admin
 */
@Configuration
public class DataSourceConfig {
    @Autowired
    private Environment env;

    @Bean(name = "sysDataSource")
    @Primary
    public DataSource sysDataSource() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
        ds.setUniqueResourceName("sys");
        ds.setMinPoolSize(10);
        ds.setMaxPoolSize(20);
        ds.setMaintenanceInterval(28000);
        ds.setTestQuery("SELECT 1");
        ds.setXaProperties(build("spring.datasource.sys."));
        return ds;
    }

    @Bean(name = "baseDataSource")
    public DataSource baseDataSource() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
        ds.setUniqueResourceName("base");
        ds.setMinPoolSize(10);
        ds.setMaxPoolSize(20);
        ds.setMaintenanceInterval(28000);
        ds.setTestQuery("SELECT 1");
        ds.setXaProperties(build("spring.datasource.base."));
        return ds;
    }



    private Properties build(String prefix) {
        String prefixPool = "spring.datasource.pool.";
        Properties prop = new Properties();
        prop.put("url", env.getProperty(prefix + "url"));
        prop.put("username", env.getProperty(prefix + "username"));
        prop.put("password", env.getProperty(prefix + "password"));
        prop.put("driverClassName", env.getProperty(prefix + "driverClassName"));

        prop.put("initialSize", env.getProperty(prefixPool + "initialSize", Integer.class));

        prop.put("maxActive", env.getProperty(prefixPool + "maxActive", Integer.class));
        prop.put("minIdle", env.getProperty(prefixPool + "minIdle", Integer.class));
        prop.put("maxWait", env.getProperty(prefixPool + "maxWait", Integer.class));
        prop.put("poolPreparedStatements", env.getProperty(prefixPool + "poolPreparedStatements", Boolean.class));
        prop.put("maxPoolPreparedStatementPerConnectionSize", env.getProperty(prefixPool + "maxPoolPreparedStatementPerConnectionSize", Integer.class));
        prop.put("validationQuery", env.getProperty(prefixPool + "validationQuery"));
        prop.put("validationQueryTimeout", env.getProperty(prefixPool + "validationQueryTimeout", Integer.class));
        prop.put("testOnBorrow", env.getProperty(prefixPool + "testOnBorrow", Boolean.class));
        prop.put("testOnReturn", env.getProperty(prefixPool + "testOnReturn", Boolean.class));
        prop.put("testWhileIdle", env.getProperty(prefixPool + "testWhileIdle", Boolean.class));
        prop.put("timeBetweenEvictionRunsMillis", env.getProperty(prefixPool + "timeBetweenEvictionRunsMillis", Integer.class));
        prop.put("minEvictableIdleTimeMillis", env.getProperty(prefixPool + "minEvictableIdleTimeMillis", Integer.class));
        prop.put("filters", env.getProperty(prefixPool + "filters"));
        return prop;
    }

    /**
     * +      
     * @return MybatisProperties
     */
    @Bean(name = "mybatisData")
    @ConfigurationProperties(prefix = "mybatis")
    @Primary
    public MybatisProperties mybatisProperties() {
        MybatisProperties mybatisProperties = new MybatisProperties();
        return mybatisProperties;
    }
}

データソースコンテキストの設定
package com.multiple.data.sources.conf.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ResourceLoader;

import javax.sql.DataSource;

/**
 * @author admin
 * com.multiple.data.sources.mapper.sys       sys
 */
@Configuration
@MapperScan(basePackages = {"com.multiple.data.sources.mapper.sys"},sqlSessionFactoryRef = "dataSysSqlSessionFactory")
public class SessionSys {
    /**
     *   sys        
     * @param ds
     * @return
     * @throws Exception
     */
    @Bean(name = "dataSysSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("sysDataSource") DataSource ds,
            @Qualifier("mybatisData") MybatisProperties properties,
            ResourceLoader resourceLoader) throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        bean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
        bean.setMapperLocations(properties.resolveMapperLocations());
        return bean.getObject();
    }
    /**
     *   sys        
     * @param sessionFactory
     * @return
     */
    @Bean(name = "dataSysSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("dataSysSqlSessionFactory") SqlSessionFactory sessionFactory) {
        return  new SqlSessionTemplate(sessionFactory);
    }


}



package com.multiple.data.sources.conf.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ResourceLoader;

import javax.sql.DataSource;

/**
 * @author admin
 * com.multiple.data.sources.mapper.base      base   
 */
@Configuration
@MapperScan(basePackages = {"com.multiple.data.sources.mapper.base"},sqlSessionFactoryRef = "dataBaseSqlSessionFactory")
public class SessionBase {

    /**
     *   base        
     * @param ds
     * @return
     * @throws Exception
     */
    @Bean(name = "dataBaseSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("baseDataSource") DataSource ds,
            @Qualifier("mybatisData") MybatisProperties properties,
            ResourceLoader resourceLoader) throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        bean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
        bean.setMapperLocations(properties.resolveMapperLocations());
        return bean.getObject();
    }

    /**
     *   base        
     * @param sessionFactory
     * @return
     */
    @Bean(name = "dataBaseSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("dataBaseSqlSessionFactory") SqlSessionFactory sessionFactory) {
        return  new SqlSessionTemplate(sessionFactory);
    }
}

Druidデータベース接続プールの構成
package com.multiple.data.sources.conf.druid;


import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author admin
 * @author admin
 * @ServletComponentScan //        Servlet、filter、listener
 */
@Configuration
public class DruidConfig {

    @Bean
    public ServletRegistrationBean<StatViewServlet> druidServlet() {
        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        servletRegistrationBean.addInitParameter("loginUsername", "root");
        servletRegistrationBean.addInitParameter("loginPassword", "1234");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public StatFilter statFilter(){
        StatFilter statFilter = new StatFilter();
        //slowSqlMillis    SQL    ，      slowSqlMillis    。
        statFilter.setLogSlowSql(true);
        //SQL    
        statFilter.setMergeSql(true);
        //slowSqlMillis     3000，   3 。
        statFilter.setSlowSqlMillis(1000);
        return statFilter;
    }

    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();
        //      SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        return wallFilter;
    }
}

@Transactional(rollbackFor=Exception.class)を使用してトランザクションを宣言
http://localhost:8080/druiddruidビジュアル化Webページにアクセスしてsqlおよびデータソース情報を表示
発生した問題
1.8時間ごとにアクセスしないで、データベースの機会にリンクを失う
解決策:定時タスクが4時間もなくすべてのライブラリをクエリー
2.XA resource‘recruit’:resume for XID‘3137322 E 31372 E 302 E 342 E 746 D 3135937313734353535393430343835:317332 E 31372 E 302 E 342 E 746 D 353030’raised-7:the XA resource has become unavailable".接続プールリソースが不足しているため、期限切れのxaが接続数を解放していないためである.
ソリューション:Atomikosデータソースのメンテナンス時間の設定
        ds.setMaintenanceInterval(28000);
        ds.setTestQuery("SELECT 1");
C#イベントと依頼(C#学習ノート03)
mysqlテーブル構造自動生成golang struct