springboot1.5和jpa利用HikariCP实现多数据源的使用

  • 2020 年 10 月 13 日
  • 笔记

背景

现在已有一个完整的项目，需要引入一个新的数据源，其实也就是分一些请求到从库上去

技术栈

springboot1.5 (哎，升不动啊)

思路

  1. 两个数据源，其中一个设置为主数据源
  2. 两个事物管理器，其中一个设置为主默认事物管理器
  3. 使用非主数据源时，一定要设置对应的事物管理器
  4. 利用 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

VirMach 便宜 VPS

QNews

QNews