微服务架构师封神之路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){
...
}