微服务架构师封神之路09-Springboot多数据源，Hikari连接池和事务配置

以在应用中配置两个数据源为例。这两个数据源对应两个不同的MySQL数据库，它们有各自的连接池配置，以求达到应用运行的最佳状态。

application.yml

spring:
  datasource:
    master:
      driverClassName: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/demo-master?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
      username: root
      password: root
      initialization-mode: always
      hikari:
        minimum-idle: 25
        maximum-pool-size: 25
        connection-test-query: SELECT 1
        max-lifetime: 1800000
        connection-timeout: 30000
        pool-name: masterdb-connection-pool
        auto-commit: false
        data-source-properties:
          prepStmtCacheSize: 500
          prepStmtCacheSqlLimit: 20480
          cachePrepStmts: true
          useServerPrepStmts: false
          rewriteBatchedStatements: true
          cacheServerConfiguration: true
          cacheResultSetMetaData: true
          metadataCacheSize: 20480
          maintainTimeStats: false
          # 读取超大数据需要，默认false,一次读取全部
          # useCursorFetch: true
          # defaultFetchSize: 50
          # 压缩传输
          useCompression: true
    slave:
      driverClassName: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/demo-slave?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
      username: root
      password: root
      initialization-mode: always
      hikari:
        minimum-idle: 5
        maximum-pool-size: 5
        connection-test-query: SELECT 1
        max-lifetime: 1800000
        connection-timeout: 30000
        pool-name: slavedb-connection-pool
        auto-commit: false
        data-source-properties:
          prepStmtCacheSize: 500
          prepStmtCacheSqlLimit: 20480
          cachePrepStmts: true
          useServerPrepStmts: false
          rewriteBatchedStatements: true
          cacheServerConfiguration: true
          cacheResultSetMetaData: true
          metadataCacheSize: 20480
          maintainTimeStats: false
          # 读取超大数据需要，默认false,一次读取全部
          # useCursorFetch: true
          # defaultFetchSize: 50
          # 压缩传输
          useCompression: true

初始化DataSource

DataSourceConfig的两种写法

写法一

@Slf4j
@Configuration
public class DataSourceConfig {

    @Bean("masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource(){
        return DataSourceBuilder.create().build();
    }
    
    @Bean("masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean("slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
        
}

写法二

@Slf4j
@Configuration
public class DataSourceConfig {

    @Bean("masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource(DataSourceProperties dataSourceProperties){
        return DataSourceBuilder
                .create(dataSourceProperties.getClassLoader())
                .driverClassName(dataSourceProperties.getDriverClassName())
                .url(dataSourceProperties.getUrl())
                .username(dataSourceProperties.getUsername())
                .password(dataSourceProperties.getPassword())
                .build();
    }

    @Bean("slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource(DataSourceProperties dataSourceProperties){
        return DataSourceBuilder
                .create(dataSourceProperties.getClassLoader())
                .driverClassName(dataSourceProperties.getDriverClassName())
                .url(dataSourceProperties.getUrl())
                .username(dataSourceProperties.getUsername())
                .password(dataSourceProperties.getPassword())
                .build();
    }

    @Bean("masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean("slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}

两种方法的区别

其实完全没有区别！一模一样。在DAO中我们直接就可以注入使用相应的JdbcTemplate。

    @Autowired
    @Qualifier("masterJdbcTemplate")
    private JdbcTemplate masterJdbcTemplate;

    @Autowired
    @Qualifier("slaveJdbcTemplate")
    private JdbcTemplate slaveJdbcTemplate;

如果你在项目中这样做了，问题很严重！！！

以上的代码其实是有缺陷的，完全不能在真实的项目中使用。这里面有两个很严峻的问题没有考虑：

• 数据库连接池使用的是默认设置。如果是单一数据源配置，application.yml中spring.datasource.hikari的设置是会起作用的，但是在多数据源的情况下，hikari连接池不会自动加载application.yml中的设置。对应数据源的连接池初始化使用的是默认的参数，一旦这种情况发生数据库的读写性能会很糟糕；
• 不同的数据源应该有自己独立的事务对象。Hikari数据源默认设置是自动提交。如果不是自动提交，又没有配置事务，对数据库的操作全白做了。

让Hikari连接池、DataSource和事务按照我们配置的那样运行

DataSourceSettings.java

用来从application.yml中读取数据源配置项。它由两个class构成。
DataSourceHikariSettings.java

@Data
public class DataSourceHikariSettings {

    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int minimumIdle;
    private int maximumPoolSize;
    private String connectionTestQuery;
    private int maxLifetime;
    private int connectionTimeout;
    private String poolName;
    private boolean autoCommit;

    private Properties dataSourceProperties = new Properties();

    public int getPrepStmtCacheSize(){
        return Integer.valueOf((String)dataSourceProperties.get("prepStmtCacheSize"));
    }

    public int getPrepStmtCacheSqlLimit(){
        return Integer.valueOf((String)dataSourceProperties.get("prepStmtCacheSqlLimit"));
    }

    public boolean getCachePrepStmts(){
        return Boolean.valueOf((String)dataSourceProperties.get("cachePrepStmts"));
    }

    public boolean getUseServerPrepStmts(){
        return Boolean.valueOf((String)dataSourceProperties.get("useServerPrepStmts"));
    }

    public boolean getRewriteBatchedStatements(){
        return Boolean.valueOf((String)dataSourceProperties.get("rewriteBatchedStatements"));
    }

    public boolean getCacheServerConfiguration(){
        return Boolean.valueOf((String)dataSourceProperties.get("cacheServerConfiguration"));
    }

    public boolean getCacheResultSetMetaData(){
        return Boolean.valueOf((String)dataSourceProperties.get("cacheResultSetMetaData"));
    }

    public int getMetadataCacheSize(){
        return Integer.valueOf((String)dataSourceProperties.get("metadataCacheSize"));
    }

    public boolean getMaintainTimeStats(){
        return Boolean.valueOf((String)dataSourceProperties.get("maintainTimeStats"));
    }

    public boolean getUseCursorFetch(){
        return Boolean.valueOf((String)dataSourceProperties.get("useCursorFetch"));
    }

    public int getDefaultFetchSize(){
        return Integer.valueOf((String)dataSourceProperties.get("defaultFetchSize"));
    }

    public boolean getUseCompression(){
        return Boolean.valueOf((String)dataSourceProperties.get("useCompression"));
    }

}

DataSourceSettings.java

@Data
public class DataSourceSettings {
    private String driverClassName;
    private String jdbcUrl;
    private String username;
    private String password;
    private String initializationMode;
    private DataSourceHikariSettings hikari;
}

DataSourceConfig.java按配置文件初始化Hikari连接池、数据源和事务

真正的做到Hikari连接池和数据源按照我们预想的那样运行。

@Slf4j
@Configuration
public class DataSourceConfig {

    @Bean("masterDataSourceSettings")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSourceSettings masterDataSourceSettings(){
        return new DataSourceSettings();
    }

    @Bean("slaveDataSourceSettings")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSourceSettings slaveDataSourceSettings(){
        return new DataSourceSettings();
    }

    public HikariDataSource createHikariDataSource(DataSourceSettings dataSourceSettings){
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(dataSourceSettings.getDriverClassName());
        hikariConfig.setJdbcUrl(dataSourceSettings.getJdbcUrl());
        hikariConfig.setUsername(dataSourceSettings.getUsername());
        hikariConfig.setPassword(dataSourceSettings.getPassword());
        hikariConfig.setPoolName(dataSourceSettings.getHikari().getPoolName());
        hikariConfig.setMinimumIdle(dataSourceSettings.getHikari().getMinimumIdle());
        hikariConfig.setMaximumPoolSize(dataSourceSettings.getHikari().getMaximumPoolSize());
        hikariConfig.setConnectionTestQuery(dataSourceSettings.getHikari().getConnectionTestQuery());
        hikariConfig.setMaxLifetime(dataSourceSettings.getHikari().getMaxLifetime());
        hikariConfig.setConnectionTimeout(dataSourceSettings.getHikari().getConnectionTimeout());
        hikariConfig.setAutoCommit(dataSourceSettings.getHikari().isAutoCommit());
        hikariConfig.setDataSourceProperties(dataSourceSettings.getHikari().getDataSourceProperties());
        return new HikariDataSource(hikariConfig);
    }

    @Bean("masterDataSource")
    public DataSource masterDataSource(@Qualifier("masterDataSourceSettings") DataSourceSettings dataSourceSettings){
        return createHikariDataSource(dataSourceSettings);
    }

    @Bean("slaveDataSource")
    public DataSource slaveDataSource(@Qualifier("slaveDataSourceSettings") DataSourceSettings dataSourceSettings){
        return createHikariDataSource(dataSourceSettings);
    }

    @Bean("masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean("slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "masterDataSourceTransactionManager")
    public PlatformTransactionManager masterDataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "slaveDataSourceTransactionManager")
    public PlatformTransactionManager slaveDataSourceTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

在DAO中使用这些数据源和事务处理

    @Autowired
    @Qualifier("masterJdbcTemplate")
    private JdbcTemplate masterJdbcTemplate;

    @Autowired
    @Qualifier("slaveJdbcTemplate")
    private JdbcTemplate slaveJdbcTemplate;

    @Transactional(value="masterDataSourceTransactionManager")
    public void createUserInMasterDB(User user){
        ...
    }

    @Transactional(value="slaveDataSourceTransactionManager")
    public void createUserInSlaveDB(User user){
        ...
    }

参考

Ref: Hikari DataSource Initialization.