SpringBoot + JPA + MySQL多数据源配置，附带常见问题处理_spring boot mysql+jpa+hikari配置

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

这里要改成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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

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);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

4、新建动态数据源类

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}
1
2
3
4
5
6
7
8
9

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;
    }


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

6、自定义DS注解（标注DataSource）

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })

public @interface DS {

    DataSourceTypeEnum value() default DataSourceTypeEnum.operation;
}
1
2
3
4
5
6
7
8
9
10

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...");
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

8、在Service实现类上标注@DS来实现数据源的切换

    @DS(DataSourceTypeEnum.operation)
    @Override
    public OrderDiagnoseModel saveOrUpdate(OrderDiagnoseModel orderDiagnoseModel) {
        return diagnoseDao.save(orderDiagnoseModel);
    }
1
2
3
4
5

9、配置数据源的名称枚举

public enum DataSourceTypeEnum {
    operation, zhiyi
}
1
2
3

常见问题

1、bean注入失败，名称冲突

两个数据源都有订单表t_order 实体类名称都叫OrderModel，service都叫OrderService，控制器都叫OrderController 项目启动的时候会报错，bean的名称冲突
（1）、修改实体类
operation数据源：

@Entity
@Table(name = "t_order")
1
2

zhiyi数据源：

@Entity(name = "zhiyiOrderModel")
@Table(name = "t_order")
1
2

（2）、修改DAO层
operation数据源：

@Repository
1

zhiyi数据源：

@Repository("zhiyiOrderDao")
1

（3）、修改Service层，修改Servie的实现注解
operation数据源：

@Service
1

zhiyi数据源：

@Service("zhiyiOrderService")
1

（4）、修改Controller层
operation数据源：

@RestController
@RequestMapping("/operation/order")
1
2

zhiyi数据源：

@RestController("zhiyiOrderController")
@RequestMapping("/zhiyi/order")
1
2

在注入Dao 、注入Service的地方加上@Qualifier注解，例如

    @Autowired
    @Qualifier("zhiyiOrderService")
    private OrderService orderService;
1
2
3

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();
1
2

鸣谢：大部分内容参考自CSDN博主：隔壁的老司机 的博客，原文链接：https://blog.csdn.net/chenxing1990/article/details/122808525