來源：haihui_yang 發(fā)布時間：2018-12-08 11:41:04 閱讀量：1218
SpringBoot 多數(shù)據(jù)源配置
最近在項目中需要連兩個 mysql 數(shù)據(jù)庫，即需要配置多數(shù)據(jù)源。
關于多數(shù)據(jù)源的配置網(wǎng)上還是有一大堆資料的，在搜尋一番過后，開始進行配置。雖然配置過程中也遇到過一些坑，但總體上還算比較簡單。
大體步驟如下：（文末附有項目 github 地址）
一、application.yml 中添加數(shù)據(jù)庫配置（兩個數(shù)據(jù)庫，分別為：primary、secondary）
spring:
datasource:
primary:
hikari:
driver-class-name: com.mysql.cj.jdbc.Driver
connection-test-query: SELECT 1 FROM DUAL
minimum-idle: 1
maximum-pool-size: 5
pool-name: bosPoolName
max-lifetime: 180000000
jdbcUrl: jdbc:mysql://${mysqlHost1:localhost}:3306/test1?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: ${mysqlUsername1:root}
password: ${mysqlPassword1:123456}
secondary:
hikari:
driver-class-name: com.mysql.cj.jdbc.Driver
connection-test-query: SELECT 1 FROM DUAL
minimum-idle: 1
maximum-pool-size: 5
pool-name: bosPoolName
max-lifetime: 180000000
jdbcUrl: jdbc:mysql://${mysqlHost2:localhost}:3306/test2?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: ${mysqlUsername2:root}
password: ${mysqlPassword2:123456}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
二、添加 PrimaryDataSourceConfig 和 SecondaryDataSourceConfig 配置類
PrimaryDataSourceConfig.java
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "primaryEntityManagerFactory",
transactionManagerRef = "primaryTransactionManager",
basePackages = {"com.yhh.primary.**.dao"}//primary數(shù)據(jù)庫對應dao所在的package
)
public class PrimaryDataSourceConfig {
@Bean(name = "primaryDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.primary.hikari")//primary數(shù)據(jù)庫配置
public DataSource getDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Primary
@Bean
public JdbcTemplate getJdbcTemplate() {
return new JdbcTemplate(getDataSource());
}
@Primary
@Bean(name = "primaryEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
@Qualifier("vendorProperties") Map<String, ?> vendorProperties) {//自己定義的Bean：vendorProperties
return builder
.dataSource(getDataSource())
.properties(vendorProperties)
.packages("com.yhh.primary.**.entity")//primary數(shù)據(jù)庫對應entity所在的package
.persistenceUnit("primary")//persistence unit，隨便給，須唯一
.build();
}
@Primary
@Bean(name = "primaryTransactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("primaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}
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
SecondaryDataSourceConfig.java
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "secondaryEntityManagerFactory",
transactionManagerRef = "secondaryTransactionManager",
basePackages = "com.yhh.secondary.**.dao"//secondary數(shù)據(jù)庫對應dao所在的package
)
public class SecondaryDataSourceConfig {
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary.hikari")//secondary數(shù)據(jù)庫配置
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate() {
return new JdbcTemplate(secondaryDataSource());
}
@Bean(name = "secondaryEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,
@Qualifier("vendorProperties") Map<String, ?> vendorProperties) {//自己定義的Bean：vendorProperties
return builder
.dataSource(secondaryDataSource())
.properties(vendorProperties)
.packages("com.yhh.secondary.**.entity")//secondary數(shù)據(jù)庫對應entity所在的package
.persistenceUnit("secondary")//persistence unit，隨便給，須唯一
.build();
}
@Bean(name = "secondaryTransactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("secondaryEntityManagerFactory") EntityManagerFactory entityManagerFactory
) {
return new JpaTransactionManager(entityManagerFactory);
}
}
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
三、vendorProperties Bean 配置
@Autowired
private JpaProperties jpaProperties;
@Bean(name = "vendorProperties")
public Map<String, Object> getVendorProperties() {
return jpaProperties.getHibernateProperties(new HibernateSettings());
}
1
2
3
4
5
6
7
實際上這里配置的是下面這三個屬性：
"hibernate.id.new_generator_mappings" -> "true"
1
"hibernate.physical_naming_strategy" -> "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy"
1
"hibernate.implicit_naming_strategy" -> "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy"
1
文末有兩個參考鏈接，按照這兩個鏈接基本上就可以將其配置出來，不過當數(shù)據(jù)庫字段名命名規(guī)則為下劃線命名法時會有問題。異常如下：
o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1054, SQLState: 42S22
o.h.engine.jdbc.spi.SqlExceptionHelper : Unknown column 'teacherdo0_.teacherName' in 'field list'
1
2
原因是代碼中是駝峰命名法，而數(shù)據(jù)庫中命名是下劃線命名法，二者無法相互映射，會報 Unknown column 異常。當時為這個問題找了好半天，后來通過添加 vendorProperties 解決。
四、添加 entity 及 dao （需要注意的是：entity 與 dao 的 package 位置須與前面配置一致）
StudentDO.java
package com.yhh.primary.entity;
import lombok.Data;
import javax.persistence.*;
@Data
@Entity
@Table(name = "student")
public class StudentDO {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String studentName;
private Integer age;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
StudentDao.java
package com.yhh.primary.dao;
import com.yhh.primary.entity.StudentDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface StudentDao extends JpaRepository<StudentDO, Integer> {
}
1
2
3
4
5
6
7
8
9
10
TeacherDO.java
package com.yhh.secondary.entity;
import lombok.Data;
import javax.persistence.*;
@Entity
@Data
@Table(name = "teacher")
public class TeacherDO {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String teacherName;
private Integer age;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
TeacherDao.java
package com.yhh.secondary.dao;
import com.yhh.secondary.entity.TeacherDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface TeacherDao extends JpaRepository<TeacherDO, Integer> {
}
1
2
3
4
5
6
7
8
9
10
五、測試與驗證（需要數(shù)據(jù)庫有與之對應的 table 及數(shù)據(jù)，在 github 項目的 README 文件中有現(xiàn)成的 sql 語句）
可以通過添加 IT 或 Controller 的方式驗證是否配置成功。
1、IT （集成測試），跑集成測試，兩個 dao 都可以查出數(shù)據(jù)。
package com.yhh.dao;
import com.yhh.primary.dao.StudentDao;
import com.yhh.primary.entity.StudentDO;
import com.yhh.secondary.dao.TeacherDao;
import com.yhh.secondary.entity.TeacherDO;
import lombok.extern.slf4j.Slf4j;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
@EnableAutoConfiguration
public class MutiDaoIT {
@Autowired
private StudentDao studentDao;
@Autowired
private TeacherDao teacherDao;
@Test
public void muti_dao_IT() {
List<TeacherDO> teacherDOList = teacherDao.findAll();
List<StudentDO> studentDOList = studentDao.findAll();
Assert.assertFalse(teacherDOList.isEmpty());
Assert.assertFalse(studentDOList.isEmpty());
}
}
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
2、Controller，通過啟動 SpringBoot 應用，請求 http://localhost:8888/api/muti-data 會得到一個 json 數(shù)組，里面有四條數(shù)據(jù)。
package com.yhh.rest;
import com.yhh.primary.dao.StudentDao;
import com.yhh.secondary.dao.TeacherDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.ArrayList;
import java.util.List;
@Controller
@RequestMapping(value = "/api")
@Slf4j
public class ShowController {
private final StudentDao studentDao;
private final TeacherDao teacherDao;
public ShowController(StudentDao studentDao, TeacherDao teacherDao) {
this.studentDao = studentDao;
this.teacherDao = teacherDao;
}
@GetMapping(value = "/muti-data")
public ResponseEntity queryMutiData() {
log.info("query muti-data.");
List result = new ArrayList<>();
result.addAll(studentDao.findAll());
result.addAll(teacherDao.findAll());
log.info("result size is {}.", result.size());
return ResponseEntity.ok(result);
}
}
---------------------
13450931319
微信登錄
QQ登錄
微博登錄
售前咨詢
