springboot1.5和jpa利用HikariCP實現多數據源的使用
- 2020 年 10 月 13 日
- 筆記
背景
現在已有一個完整的項目，需要引入一個新的數據源，其實也就是分一些請求到從庫上去
技術棧
springboot1.5 (哎，升不動啊)
思路
- 兩個數據源，其中一個設置為主數據源
- 兩個事物管理器，其中一個設置為主默認事物管理器
- 使用非主數據源時，一定要設置對應的事物管理器
- 利用 dao 下的不同包路徑，不同路徑下的對應 Repository 使用不同的數據源
@Service
@Transactional(transactionManager = "transactionManagerSecond", rollbackFor = Exception.class)
public class DashBoardService { }
啟動日誌
[timestamp=2020-10-13 21:09:19.317] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HikariCP pool "mysql-hikari-pool-1" is starting.
[timestamp=2020-10-13 21:09:19.584] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow]
HikariCP連接池配置
連接池名稱："mysql-hikari-pool-1"
最小空閑連接數：1
最大連接數：20
連接超時時間：3000ms
空閑連接超時時間：600000ms
連接最長生命周期：1800000ms
[timestamp=2020-10-13 21:09:19.628] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] Building JPA container EntityManagerFactory for persistence unit 'primaryPersistenceUnit'
[timestamp=2020-10-13 21:09:19.638] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000204: Processing PersistenceUnitInfo [
name: primaryPersistenceUnit
...]
[timestamp=2020-10-13 21:09:19.697] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000412: Hibernate Core {5.0.11.Final}
[timestamp=2020-10-13 21:09:19.698] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000206: hibernate.properties not found
[timestamp=2020-10-13 21:09:19.699] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000021: Bytecode provider name : javassist
[timestamp=2020-10-13 21:09:19.740] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
[timestamp=2020-10-13 21:09:19.904] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
[timestamp=2020-10-13 21:09:20.767] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] Initialized JPA EntityManagerFactory for persistence unit 'primaryPersistenceUnit'
[timestamp=2020-10-13 21:09:20.937] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow]
HikariCP連接池配置
連接池名稱："mysql-hikari-pool-2"
最小空閑連接數：1
最大連接數：20
連接超時時間：3000ms
空閑連接超時時間：600000ms
連接最長生命周期：1800000ms
[timestamp=2020-10-13 21:09:20.967] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] Building JPA container EntityManagerFactory for persistence unit 'secondPersistenceUnit'
[timestamp=2020-10-13 21:09:20.967] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000204: Processing PersistenceUnitInfo [
name: secondPersistenceUnit
...]
[timestamp=2020-10-13 21:09:21.036] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HikariCP pool "mysql-hikari-pool-2" is starting.
[timestamp=2020-10-13 21:09:21.113] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
[timestamp=2020-10-13 21:09:21.369] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] Initialized JPA EntityManagerFactory for persistence unit 'secondPersistenceUnit'
[timestamp=2020-10-13 21:09:21.834] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000397: Using ASTQueryTranslatorFactory
[timestamp=2020-10-13 21:09:26.616] [level=INFO] [tx_id=] [span_id=] [bu_id=JT_MW] [app_id=iflow] HHH000397: Using ASTQueryTranslatorFactory
重要
*在使用非主數據源時，一定要顯式的指定對應使用管理器，不然連接池會耗盡的
*
dao層使用第二數據源， 用EntityManager
package com.daleyzou.multidatasource.dao.second;
import com.daleyzou.multidatasource.po.NodePo;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
/**
* @ClassName NodeNativeSQLDao
* @Description: 在dao層使用第二數據源， 用EntityManager， 需要指定對應的 unitName
* @Author dalelyzou
* @Date 2020/10/7
* @Version V1.0
**/
@Repository
@Slf4j
public class NodeNativeSQLDao {
@PersistenceContext(unitName = "secondPersistenceUnit")
private EntityManager entityManager;
/**
* 使用自定義SQL查詢數據
*
* @param
* @return
* @author daleyzou
*/
public List<NodePo> getAll() {
StringBuilder sb = new StringBuilder();
sb.append("SELECT * from node");
SQLQuery sqlQuery = entityManager.createNativeQuery(sb.toString()).unwrap(SQLQuery.class);
org.hibernate.Query query = sqlQuery.setResultTransformer(Transformers.aliasToBean(NodePo.class));
return query.list();
}
}
對應的unitName是我們自己在數據源的 SecondConfig 里定義的
@Bean(name = "entityManagerFactorySecond")
public LocalContainerEntityManagerFactoryBean entityManagerFactorySecond(EntityManagerFactoryBuilder builder) {
DataSourceConfig.logDS(secondDataSource);
return builder.dataSource(secondDataSource).properties(getVendorProperties(secondDataSource))
.packages("com.daleyzou.multidatasource.po").persistenceUnit("secondPersistenceUnit").build();
}
主要程式碼如下
DataSourceConfig
package com.daleyzou.multidatasource.config;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
/**
* DataSourceConfig
* @description 數據源配置
* @author daleyzou
* @date 2020年10月12日 19:45
* @version 1.4.8
*/
@Configuration
@Slf4j
public class DataSourceConfig {
/**
* 數據源配置對象
* Primary 表示默認的對象，Autowire可注入，不是默認的得明確名稱注入
* @return
*/
@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSourceProperties primaryDataSourceProperties() {
return new DataSourceProperties();
}
/**
* 數據源對象
* @return
*/
@Primary
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return primaryDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSourceProperties secondaryDataSourceProperties() {
return new DataSourceProperties();
}
/**
* 第二個數據源
* @return
*/
@Bean(name = "secondDataSource")
@Qualifier("secondDataSource")
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource secondaryDataSource() {
return secondaryDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
/**
* 顯示資料庫連接池資訊
*
* @param dataSource
*/
public static void logDS(DataSource dataSource) {
HikariDataSource hds = (HikariDataSource) dataSource;
String info = "\n\n\tHikariCP連接池配置\n\t連接池名稱：" + hds.getPoolName() + "\n\t最小空閑連接數：" + hds.getMinimumIdle() + "\n\t最大連接數：" + hds
.getMaximumPoolSize() + "\n\t連接超時時間：" + hds.getConnectionTimeout() + "ms\n\t空閑連接超時時間：" + hds.getIdleTimeout()
+ "ms\n\t連接最長生命周期：" + hds.getMaxLifetime() + "ms\n";
log.info(info);
}
}
PrimaryConfig
package com.daleyzou.multidatasource.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
/**
* PrimaryConfig
* @description 默認的主數據源
* @author daleyzou
* @date 2020年10月12日 19:46
* @version 1.4.8
*/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary", transactionManagerRef = "transactionManagerPrimary", basePackages = {
"com.daleyzou.multidatasource.dao.primary" })
public class PrimaryConfig {
@Autowired
private JpaProperties jpaProperties;
@Autowired
@Qualifier("primaryDataSource")
private DataSource primaryDataSource;
@Primary
@Bean(name = "entityManagerPrimary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
DataSourceConfig.logDS(primaryDataSource);
return builder.dataSource(primaryDataSource).properties(getVendorProperties(primaryDataSource))
.packages("com.daleyzou.multidatasource.po").persistenceUnit("primaryPersistenceUnit").build();
}
private Map<String, String> getVendorProperties(DataSource dataSource) {
return jpaProperties.getHibernateProperties(dataSource);
}
@Primary
@Bean(name = "transactionManagerPrimary")
public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
}
}
SecondConfig
package com.daleyzou.multidatasource.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
/**
* SecondConfig
* @description 第二數據源
* @author daleyzou
* @date 2020年10月12日 19:46
* @version 1.4.8
*/
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
//實體管理
entityManagerFactoryRef = "entityManagerFactorySecond",
//事務管理
transactionManagerRef = "transactionManagerSecond",
//實體掃描,設置Repository所在位置
basePackages = { "com.daleyzou.multidatasource.dao.second" })
public class SecondConfig {
@Autowired
private JpaProperties jpaProperties;
@Autowired
@Qualifier("secondDataSource")
private DataSource secondDataSource;
@Bean(name = "entityManagerSecond")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactorySecond(builder).getObject().createEntityManager();
}
@Bean(name = "entityManagerFactorySecond")
public LocalContainerEntityManagerFactoryBean entityManagerFactorySecond(EntityManagerFactoryBuilder builder) {
DataSourceConfig.logDS(secondDataSource);
return builder.dataSource(secondDataSource).properties(getVendorProperties(secondDataSource))
.packages("com.daleyzou.multidatasource.po").persistenceUnit("secondPersistenceUnit").build();
}
private Map<String, String> getVendorProperties(DataSource dataSource) {
return jpaProperties.getHibernateProperties(dataSource);
}
@Bean(name = "transactionManagerSecond")
PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactorySecond(builder).getObject());
}
}
倉庫程式碼
//github.com/daleyzou/MultiDatasource
