spring boot Mybatis多数据源配置

关于

有时候，随着业务的发展，项目关联的数据来源会变得越来越复杂，使用的数据库会比较分散，这个时候就会采用多数据源的方式来获取数据。另外，多数据源也有其他好处，例如分布式数据库的读写分离，集成多种数据库等等。

下面分享我在实际项目中配置多数据源的案例。话不多说了，来一起看看详细的介绍吧

步骤

1.application.yml文件中，配置数据库源。这里primary是主库，secondary是从库。

 1 server:
 2  port: 8089
 3  
 4 # 多数据源配置
 5 #primary
 6 spring:
 7  primary:
 8  datasource:
 9   url: jdbc:mysql://127.0.0.1:3306/database1?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
10   username: root
11   password: ******
12   driver-class-name: com.mysql.jdbc.Driver
13  
14  #secondary
15  secondary:
16  datasource:
17   url: jdbc:mysql://127.0.0.1:3306/database1?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
18   username: root
19   password: ******
20   driver-class-name: com.mysql.jdbc.Driver
21  
22  jpa:
23  hibernate:
24   primary-dialect: org.hibernate.dialect.MySQL5Dialect
25   secondary-dialect: org.hibernate.dialect.MySQL5Dialect
26  open-in-view: true
27  show-sql: true

application.yml

2.创建一个Spring配置类，其中spring.primary.datasource的路径参考yml文件的配置。

 1 @Configuration
 2 public class DataSourceConfig {
 3  
 4  @Bean(name = "primaryDataSource")
 5  @Qualifier("primaryDataSource")
 6  @ConfigurationProperties(prefix="spring.primary.datasource")
 7  public DataSource primaryDataSource() {
 8   return DataSourceBuilder.create().build();
 9  }
10  
11  @Bean(name = "secondaryDataSource")
12  @Qualifier("secondaryDataSource")
13  @Primary
14  @ConfigurationProperties(prefix="spring.secondary.datasource")
15  public DataSource secondaryDataSource() {
16   return DataSourceBuilder.create().build();
17  }
18  
19 }

DataSourceConfig

3.分别创建主库、从库的配置类。

注意：entity包和dao包的配置，以及@Primary注解指定主库。

主库配置类：

 1 @Configuration
 2 @EnableTransactionManagement
 3 @EnableJpaRepositories(
 4   entityManagerFactoryRef = "entityManagerFactoryPrimary",
 5   transactionManagerRef = "transactionManagerPrimary",
 6   basePackages = {"com.infinitus.yunxiao_data.dao.primary"}) //设置Repository所在位置
 7 public class PrimaryConfig {
 8  @Autowired
 9  private JpaProperties jpaProperties;
10  
11  @Autowired
12  @Qualifier("primaryDataSource")
13  private DataSource primaryDataSource;
14  
15  @Primary
16  @Bean(name = "entityManagerPrimary")
17  public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
18   return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
19  }
20  
21  @Primary
22  @Bean(name = "entityManagerFactoryPrimary")
23  public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
24   return builder
25     .dataSource(primaryDataSource)
26     .properties(getVendorProperties(primaryDataSource))
27     .packages("com.infinitus.yunxiao_data.entity.primary") //设置实体类所在位置
28     .persistenceUnit("primaryPersistenceUnit")
29     .build();
30  }
31  
32  
33  private Map getVendorProperties(DataSource dataSource) {
34   return jpaProperties.getHibernateProperties(dataSource);
35  }
36  
37  @Primary
38  @Bean(name = "transactionManagerPrimary")
39  public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
40   return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
41  }
42 }

主库配置

从库的配置类：

 1 @Configuration
 2 @EnableTransactionManagement
 3 @EnableJpaRepositories(
 4   entityManagerFactoryRef = "entityManagerFactorySecondary",
 5   transactionManagerRef = "transactionManagerSecondary",
 6   basePackages = {"com.infinitus.yunxiao_data.dao.secondary"}) //设置Repository所在位置
 7 public class SecondaryConfig {
 8  @Autowired
 9  private JpaProperties jpaProperties;
10  
11  @Autowired
12  @Qualifier("secondaryDataSource")
13  private DataSource secondaryDataSource;
14  
15  @Bean(name = "entityManagerSecondary")
16  public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
17   return entityManagerFactorySecondary(builder).getObject().createEntityManager();
18  }
19  
20  @Bean(name = "entityManagerFactorySecondary")
21  public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
22   return builder
23     .dataSource(secondaryDataSource)
24     .properties(getVendorProperties(secondaryDataSource))
25     .packages("com.infinitus.yunxiao_data.entity.secondary") //设置实体类所在位置
26     .persistenceUnit("primaryPersistenceUnit")
27     .build();
28  }
29  
30  
31  private Map getVendorProperties(DataSource dataSource) {
32   return jpaProperties.getHibernateProperties(dataSource);
33  }
34  
35  @Bean(name = "transactionManagerSecondary")
36  PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
37   return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
38  }
39  
40 }

从库配置

4.分别创建主、从库dao类。

主dao：

1 @Repository
2 public interface PrimaryRepository extends JpaRepository<PrimaryEntity, Long> {
3  
4  @Query(value = "SELECT p FROM PrimaryEntity p")
5  List<PrimaryEntity> queryList();
6  
7 }

主dao

从dao：

1 @Repository
2 public interface SecondaryRepository extends JpaRepository<SecondaryEntity, Long> {
3  
4  @Query(value = "SELECT p FROM SecondaryEntity p")
5  List<SecondaryEntity> queryList();
6  
7 }

从dao

5.分别创建主、从库entity类。

主entity：

 1 @Entity
 2 @Table(name = "holiday_scheme")
 3 @EntityListeners(AuditingEntityListener.class)
 4 public class PrimaryEntity extends AbstractPersistable<Long> {
 5  @Column(name = "date")
 6  public String date;
 7  @Column(name = "hour")
 8  public String hour;
 9  @Column(name = "holiday")
10  public String holiday;
11  @Column(name = "holiday_explain")
12  public String holiday_explain;
13  
14  public String getDate() {
15   return date;
16  }
17  
18  public void setDate(String date) {
19   this.date = date;
20  }
21  
22  public String getHour() {
23   return hour;
24  }
25  
26  public void setHour(String hour) {
27   this.hour = hour;
28  }
29  
30  public String getHoliday() {
31   return holiday;
32  }
33  
34  public void setHoliday(String holiday) {
35   this.holiday = holiday;
36  }
37  
38  public String getHoliday_explain() {
39   return holiday_explain;
40  }
41  
42  public void setHoliday_explain(String holiday_explain) {
43   this.holiday_explain = holiday_explain;
44  }
45  
46  @Override
47  public String toString() {
48   return "PrimaryEntity{" +
49     "date='" + date + '\'' +
50     ", hour='" + hour + '\'' +
51     ", holiday='" + holiday + '\'' +
52     ", holiday_explain='" + holiday_explain + '\'' +
53     '}';
54  }
55 }

主Entity

从entity：

 1 @Entity
 2 @Table(name = "active_dashboards")
 3 @EntityListeners(AuditingEntityListener.class)
 4 public class SecondaryEntity extends AbstractPersistable<Long> {
 5  
 6  @Column(name = "dashboard_id")
 7  public String dashboard_id;
 8  @Column(name = "user_id")
 9  public String user_id;
10  @Column(name = "order_index")
11  public String order_index;
12  
13  public String getDashboard_id() {
14   return dashboard_id;
15  }
16  
17  public void setDashboard_id(String dashboard_id) {
18   this.dashboard_id = dashboard_id;
19  }
20  
21  public String getUser_id() {
22   return user_id;
23  }
24  
25  public void setUser_id(String user_id) {
26   this.user_id = user_id;
27  }
28  
29  public String getOrder_index() {
30   return order_index;
31  }
32  
33  public void setOrder_index(String order_index) {
34   this.order_index = order_index;
35  }
36  
37  @Override
38  public String toString() {
39   return "SecondaryEntity{" +
40     "dashboard_id='" + dashboard_id + '\'' +
41     ", user_id='" + user_id + '\'' +
42     ", order_index='" + order_index + '\'' +
43     '}';
44  }
45 }

从Entity

6.controller请求获取不同数据库的数据。

 1 @RestController
 2 @RequestMapping("/database")
 3 public class MailController {
 4  private final Logger logger = LoggerFactory.getLogger(this.getClass());
 5  
 6  @Autowired
 7  PrimaryRepository primaryRepository;
 8  @Autowired
 9  SecondaryRepository secondaryRepository;
10  
11  @RequestMapping("/primary")
12  @ResponseBody
13  public String primary() {
14   return primaryRepository.queryList().toString();
15  }
16  
17  @RequestMapping("/secondary")
18  @ResponseBody
19  public String secondary() {
20   return secondaryRepository.queryList().toString();
21  }
22  
23 }

Controller

注意

下面提两个在配置多数据源时遇到的坑点，一不注意就掉坑了。

1.Application类不需要配置@EnableJpaRepositories注解，会报如下错误。

A component required a bean named 'entityManagerFactory' that could not be f

2.注意检查dao类，获取数据的方法上格式是否正确，有没有某个字段是表中不存在的，避免启动异常。如下，SecondaryEntity表中是不存在job_name字段的，所以注释掉才能启动成功等。

1 //@Query(value = "SELECT p FROM SecondaryEntity p where p.job_name = ?1")
2 //List<SecondaryEntity> queryOdcRecord(String job_name);

View Code

转自：http://blog.csdn.net/yuechang5/article/details/53471743

https://baomidou.oschina.io/mybatis-plus-doc/#/multi-datasource

https://blog.csdn.net/husong_/article/details/80103497

https://www.cnblogs.com/hdwang/p/7041096.html

https://www.jb51.net/article/154301.htm

spring boot Mybatis多数据源配置

推荐阅读