SpringBoot JPA多数据源配置
SpringBoot + JPA + MySQL多数据源配置,附带常见问题处理
1、配置文件(此处我使用的是properties文件,yml也一样的)
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#数据源1
spring.datasource.operation.url = jdbc:mysql://你自己的数据源1
spring.datasource.operation.username = 数据源1的用户名
spring.datasource.operation.password = 数据源1的密码
spring.datasource.operation.driverClassName = com.mysql.jdbc.Driver
#数据源2
spring.datasource.zhiyi.url = jdbc:mysql://你自己的数据源2
spring.datasource.zhiyi.username = 数据源2的用户名
spring.datasource.zhiyi.password = 数据源2的密码
spring.datasource.zhiyi.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10
spring.main.allow-circular-references=true
spring.jpa.database = MYSQL
# Show or not log for each sql query
spring.jpa.show-sql = true
# Hibernate ddl auto (create, create-drop, update,validate,none)
spring.jpa.hibernate.ddl-auto = none
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
# stripped before adding them to the entity manager)
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
这里要改成none否则启动的时候默认是主数据源,会报错表缺失,因为有些表是在另一个数据源
spring.jpa.hibernate.ddl-auto = none
2、新建数据源属性类(读取多个数据源属性)
我习惯新建一个package叫做config,来存放启动时候需要加载的配置
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
@Component
public class DataSourceProperties {
@Value("${spring.datasource.operation.url}")
private String operationUrl;
@Value("${spring.datasource.operation.username}")
private String operationUserName;
@Value("${spring.datasource.operation.password}")
private String operationPassword;
@Value("${spring.datasource.operation.driverClassName}")
private String operationDriverClass;
@Value("${spring.datasource.zhiyi.url}")
private String zhiyiUrl;
@Value("${spring.datasource.zhiyi.username}")
private String zhiyiUserName;
@Value("${spring.datasource.zhiyi.password}")
private String zhiyiPassword;
@Value("${spring.datasource.zhiyi.driverClassName}")
private String zhiyiDriverClass;
public String getOperationUrl() {
return operationUrl;
}
public void setOperationUrl(String operationUrl) {
this.operationUrl = operationUrl;
}
public String getOperationUserName() {
return operationUserName;
}
public void setOperationUserName(String operationUserName) {
this.operationUserName = operationUserName;
}
public String getOperationPassword() {
return operationPassword;
}
public void setOperationPassword(String operationPassword) {
this.operationPassword = operationPassword;
}
public String getOperationDriverClass() {
return operationDriverClass;
}
public void setOperationDriverClass(String operationDriverClass) {
this.operationDriverClass = operationDriverClass;
}
public String getZhiyiUrl() {
return zhiyiUrl;
}
public void setZhiyiUrl(String zhiyiUrl) {
this.zhiyiUrl = zhiyiUrl;
}
public String getZhiyiUserName() {
return zhiyiUserName;
}
public void setZhiyiUserName(String zhiyiUserName) {
this.zhiyiUserName = zhiyiUserName;
}
public String getZhiyiPassword() {
return zhiyiPassword;
}
public void setZhiyiPassword(String zhiyiPassword) {
this.zhiyiPassword = zhiyiPassword;
}
public String getZhiyiDriverClass() {
return zhiyiDriverClass;
}
public void setZhiyiDriverClass(String zhiyiDriverClass) {
this.zhiyiDriverClass = zhiyiDriverClass;
}
}
3、新建动态数据源上下文管理类
import java.util.ArrayList;
import java.util.List;
public class DataSourceContextHolder {
// 存放当前线程使用的数据源类型
private static final ThreadLocal<DataSourceTypeEnum> contextHolder = new ThreadLocal<>();
//存放数据源id
public static List<String> dataSourceIds = new ArrayList<String>();
// 设置数据源
public static void setDataSource(DataSourceTypeEnum type) {
contextHolder.set(type);
}
// 获取数据源
public static DataSourceTypeEnum getDataSource() {
return contextHolder.get();
}
// 清除数据源
public static void clearDataSource() {
contextHolder.remove();
}
//判断当前数据源是否存在
public static boolean isContainsDataSource(String dataSourceId) {
return dataSourceIds.contains(dataSourceId);
}
}
4、新建动态数据源类
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSource();
}
}
5、新建动态数据源配置类
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataSourceConfig {
@Autowired
DataSourceProperties dataSourceProperties;
@Bean(name = "operation")
public DataSource operation() {
HikariDataSource operation = new HikariDataSource();
operation.setJdbcUrl(dataSourceProperties.getOperationUrl());
operation.setDriverClassName(dataSourceProperties.getOperationDriverClass());
operation.setUsername(dataSourceProperties.getOperationUserName());
operation.setPassword(dataSourceProperties.getOperationPassword());
operation.setPoolName("HikariPool-operation");
operation.setAutoCommit(true);
operation.setReadOnly(false);
operation.setConnectionTestQuery("SELECT 1;");
return operation;
}
@Bean(name = "zhiyi")
public DataSource zhiyi() {
HikariDataSource zhiyi = new HikariDataSource();
zhiyi.setJdbcUrl(dataSourceProperties.getZhiyiUrl());
zhiyi.setDriverClassName(dataSourceProperties.getZhiyiDriverClass());
zhiyi.setUsername(dataSourceProperties.getZhiyiUserName());
zhiyi.setPassword(dataSourceProperties.getZhiyiPassword());
zhiyi.setPoolName("HikariPool-zhiyi");
zhiyi.setAutoCommit(true);
zhiyi.setReadOnly(false);
zhiyi.setConnectionTestQuery("SELECT 1;");
return zhiyi;
}
@Bean(name = "operationJdbcTemplate")
public JdbcTemplate operationJdbcTemplate(@Qualifier("operation") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "zhiyiJdbcTemplate")
public JdbcTemplate zhiyiJdbcTemplate(@Qualifier("zhiyi") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Primary
@Bean("dynamicDataSource")
public DataSource dynamicDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>(2);
targetDataSources.put(DataSourceTypeEnum.operation, operation());
targetDataSources.put(DataSourceTypeEnum.zhiyi, zhiyi());
// 添加数据源名称到列表
DataSourceContextHolder.dataSourceIds.add(DataSourceTypeEnum.operation.name());
DataSourceContextHolder.dataSourceIds.add(DataSourceTypeEnum.zhiyi.name());
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 如果没有指定数据源自动切换主数据源
dynamicDataSource.setDefaultTargetDataSource(operation());
dynamicDataSource.setTargetDataSources(targetDataSources);
return dynamicDataSource;
}
@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
JpaTransactionManager jpaTransactionManager = new JpaTransactionManager(entityManagerFactory);
jpaTransactionManager.setDataSource(dynamicDataSource());
return jpaTransactionManager;
}
}
6、自定义DS注解(标注DataSource)
import java.lang.annotation.*;
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface DS {
DataSourceTypeEnum value() default DataSourceTypeEnum.operation;
}
7、使用AOP的切面来切换数据源
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Aspect
@Order(-10) // 保证该AOP在@Transactional之前执行
@Component
public class DynamicDataSourceAspect {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
@Before(value = "@annotation(source)")
public void changeDataSource(JoinPoint point, DS source) throws Exception {
DataSourceTypeEnum currentSource = source.value();
logger.info("Change DataSource To:[" + currentSource + "]");
DataSourceContextHolder.setDataSource(currentSource);
}
@After(value = "@annotation(source)")
public void restoreDataSource(JoinPoint point, DS source) {
// 方法执行完毕之后,销毁当前数据源信息,进行垃圾回收。
DataSourceContextHolder.clearDataSource();
logger.info("Clear Change DataSource...");
}
}
8、在Service实现类上标注@DS来实现数据源的切换
@DS(DataSourceTypeEnum.operation)
@Override
public OrderDiagnoseModel saveOrUpdate(OrderDiagnoseModel orderDiagnoseModel) {
return diagnoseDao.save(orderDiagnoseModel);
}
9、配置数据源的名称枚举
public enum DataSourceTypeEnum {
operation, zhiyi
}
常见问题
1、bean注入失败,名称冲突
两个数据源都有订单表t_order 实体类名称都叫OrderModel,service都叫OrderService,控制器都叫OrderController 项目启动的时候会报错,bean的名称冲突
(1)、修改实体类
operation数据源:
@Entity
@Table(name = "t_order")
zhiyi数据源:
@Entity(name = "zhiyiOrderModel")
@Table(name = "t_order")
(2)、修改DAO层
operation数据源:
@Repository
zhiyi数据源:
@Repository("zhiyiOrderDao")
(3)、修改Service层,修改Servie的实现注解
operation数据源:
@Service
zhiyi数据源:
@Service("zhiyiOrderService")
(4)、修改Controller层
operation数据源:
@RestController
@RequestMapping("/operation/order")
zhiyi数据源:
@RestController("zhiyiOrderController")
@RequestMapping("/zhiyi/order")
在注入Dao 、注入Service的地方加上@Qualifier注解,例如
@Autowired
@Qualifier("zhiyiOrderService")
private OrderService orderService;
2、dao层查询方法失败
原因:可能有多个名称相同的实体类,所以Dao层在查询结果时候不知道是哪个同名的entity,解决方案是写出实体的完整路径
@Query("select h from com.dyt.operationappoint.model.zhiyi.HospitalModel h where h.status=1 and h.index is null order by h.sort desc")
List<HospitalModel> getAllHospital();