第一步:
添加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"