springboot 2.0.7.RELEASE 整合多数据源（JdbcTemplate和JpaRepository）

第一步：

添加applicaton.properties数据库连接信息，有两个数据源，一个为主（primary），一个为从（secondary）：

spring.datasource.primary.url = jdbc:mysql://locahost1:3306/dbtest1?autoReconnect=true&serverTimezone=GMT%2B8

spring.datasource.primary.username = root

spring.datasource.primary.password = root

spring.datasource.primary.driver-class-name = com.mysql.jdbc.Driver

spring.datasource.secondary.url = jdbc:mysql://locahost2:3306/dbtest2?autoReconnect=true&serverTimezone=GMT%2B8

spring.datasource.secondary.username = root

spring.datasource.secondary.password = root

spring.datasource.secondary.driver-class-name = com.mysql.jdbc.Driver

第二步：

package com.yudianbank.postloan.config;

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;

多数据源配置

@Configuration

public class DataSourceConfig {

主数据源

@Primary

@Bean(name = "primaryDataSource")

@Qualifier("primaryDataSource")

@ConfigurationProperties(prefix = "spring.datasource.primary")

public DataSource fooDataSource(@Qualifier("dataSourcePropertiesPrimary") DataSourceProperties dataSourceProperties) {

return dataSourceProperties.initializeDataSourceBuilder().build();

}

从数据源

@Bean(name = "secondaryDataSource")

@Qualifier("secondaryDataSource")

@ConfigurationProperties(prefix = "spring.datasource.secondary")

public DataSource barDataSource(@Qualifier("dataSourcePropertiesSecondary") DataSourceProperties dataSourceProperties) {

return dataSourceProperties.initializeDataSourceBuilder().build();

}

}

第三步：

JdbcTemplate多数据源配置

package com.yudianbank.postloan.config;

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 org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

区分数据源

@Configuration

public class JdbcTemplateConfig {

@Primary

@Bean(name = "dataSourcePropertiesPrimary")

@Qualifier("dataSourcePropertiesPrimary")

@ConfigurationProperties(prefix="spring.datasource.primary")

public DataSourceProperties dataSourcePropertiesPrimary() {

return new DataSourceProperties();

}

@Bean(name = "primaryJdbcTemplate")

@Qualifier("primaryJdbcTemplate")

public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {

return new JdbcTemplate(dataSource);

}

@Bean(name = "dataSourcePropertiesSecondary")

@Qualifier("dataSourcePropertiesSecondary")

@ConfigurationProperties(prefix="spring.datasource.secondary")

public DataSourceProperties dataSourcePropertiesSecondary() {

return new DataSourceProperties();

}

@Bean(name = "secondaryJdbcTemplate")

@Qualifier("secondaryJdbcTemplate")

public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {

return new JdbcTemplate(dataSource);

}

}

使用示例：

@Repository

public class CarLoanReturnVisitDao extends BaseDao {

@Autowired

//添加此注解区分数据源

@Qualifier("primaryJdbcTemplate")

private JdbcTemplate jdbcTemplate;

 

public Map<String, Object> getSuggest(List<Integer> list) {

try {

String sql = "SELECT supplement_corp_list_id id,GROUP_CONCAT( handling_suggestion ) str FROM daihou_supplement_handler_history WHERE type = '11' AND supplement_corp_list_id IN ( " + StringUtils.join(list, ",") + " ) GROUP BY supplement_corp_list_id";

List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql);

return list1.stream().collect(Collectors.toMap(m -> String.valueOf(m.get("id")), m -> m.get("str"), (k1, k2) -> k1));

} catch (DataAccessException e) {

return new LinkedHashMap<>();

}

}

}

JpaRepository多数据源

package com.yudianbank.postloan.config;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;

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.annotation.Resource;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

import java.util.Map;

 jpa主数据主数据源配置

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary", //EntityManagerFactory引用

transactionManagerRef = "transactionManagerPrimary", //transactionManager引用

basePackages = {"com.yudianbank.postloan.model"}) //实体类所在的包

public class JpaPrimaryConfig {

@Resource

@Qualifier("primaryDataSource")

private DataSource fooDataSource;

@Primary

@Bean(name = "entityManagerPrimary")

public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return entityManagerFactoryFoo(builder).getObject().createEntityManager();

}

@Resource

private JpaProperties jpaProperties;

private Map<String, Object> getVendorProperties() {

return jpaProperties.getHibernateProperties(new HibernateSettings());

}

设置实体类所在位置

@Primary

@Bean(name = "entityManagerFactoryPrimary")

public LocalContainerEntityManagerFactoryBean entityManagerFactoryFoo(EntityManagerFactoryBuilder builder) {

return builder

.dataSource(fooDataSource)

.packages("com.yudianbank.postloan.model") //实体类所在的包

.persistenceUnit("primaryPersistenceUnit")

.properties(getVendorProperties())

.build();

}

@Primary

@Bean(name = "transactionManagerPrimary")

public PlatformTransactionManager transactionManagerFoo(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(entityManagerFactoryFoo(builder).getObject());

}

}

jpa次主数据主数据源配置

package com.yudianbank.postloan.config;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;

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.annotation.Resource;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

import java.util.Map;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactorySecondary", //EntityManagerFactory引用

transactionManagerRef = "transactionManagerSecondary", //transactionManager引用

basePackages = {"com.yudianbank.postloan.secondaryModel"})

public class JpaSecondaryConfig {

@Autowired

@Qualifier("secondaryDataSource")

private DataSource barDataSource;

@Bean(name = "entityManagerSecondary")

public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return entityManagerFactorySecondary(builder).getObject().createEntityManager();

}

@Resource

private JpaProperties jpaProperties;

private Map<String, Object> getVendorProperties() {

return jpaProperties.getHibernateProperties(new HibernateSettings());

}

@Bean(name = "entityManagerFactorySecondary")

public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {

return builder

.dataSource(barDataSource)

.packages("com.yudianbank.postloan.secondaryModel")//实体类的目录

.persistenceUnit("secondaryPersistenceUnit")

.properties(getVendorProperties())

.build();

}

@Bean(name = "transactionManagerSecondary")

PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());

}

}

使用示例

public abstract class BaseDao {

private static final Logger logger = LoggerFactory.getLogger(BaseDao.class);

private final static int BATCH_COMMIT_SIZE = 100;

@PersistenceContext(unitName = "entityManagerFactoryPrimary") //添加词注解区分数据源

protected EntityManager entityManager;

protected Session getCurrentSession() {

return entityManager.unwrap(Session.class);

}

protected String create(Object o) {

Session ss = getCurrentSession();

return ss.save(o).toString();

}

}

jpa执行saveOrUpdate 的时候事务@Transactional失效无法进行更新，也不报错，解决方案：

@Transactional(rollbackFor = Exception.class,transactionManager="transactionManagerSecondary")

指定事务开启的数据库

transactionManager="transactionManagerSecondary"