SpringBoot多数据源配置
一、原因:
项目中需要把原来的两个系统的用户的信息clone到新的系统中,但是这三个系统的数据库分别在不同的数据库的,所以需要配置多数据源来读取不同数据库的信息。
二、配置方法:
配置方法参考了此文章:SpringBoot多数据源配置。
三、详细配置过程
本文实例所使用的IDE为IntelliJ IDEA
,如果是Eclipse
,仅做参考,不要全部照搬
1. 项目目录结构
2. 在 application.yml
中配置多个数据库
spring:
datasource:
ars:
url: jdbc:oracle:thin:@10.87.60.164:1521:orcl
username: gold_ars
password: gold_ars123
driverClassName: oracle.jdbc.OracleDriver
pas:
url: jdbc:oracle:thin:@10.87.60.164:1521:orcl
username: gold_pas
password: gold_pas123
driverClassName: oracle.jdbc.OracleDriver
ams:
url: jdbc:oracle:thin:@10.87.60.164:1521:orcl
username: gold_resource
password: gold_resource123
driverClassName: oracle.jdbc.OracleDriver
# type: com.alibaba.druid.pool.DruidDataSource
jpa:
# database: mysql
show-sql: true
hibernate:
ddl-auto: update
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
database-platform: org.hibernate.dialect.Oracle12cDialect
这里配置了 AMS, ARS, PAS
三个数据库的连接
3. 配置数据库连接属性
在AmsProperties
类中配置AmsProperties
数据库连接的属性
@ConfigurationProperties(prefix = "spring.datasource.ams")
@Component
@Data
public class AmsProperties {
private String url;
private String username;
private String password;
private String driverClassName;
}
在ArsProperties
类中配置ArsProperties
数据库连接的属性
@ConfigurationProperties(prefix = "spring.datasource.ars")
@Component
@Data
public class ArsProperties {
private String url;
private String username;
private String password;
private String driverClassName;
}
在PasProperties
类中配置PasProperties
数据库连接的属性
@ConfigurationProperties(prefix = "spring.datasource.pas")
@Component
@Data
public class PasProperties {
private String url;
private String username;
private String password;
private String driverClassName;
}
4. 数据库连接设置
新建DataSourceConfig
类,在其中设置两个数据库的连接数据,在本文章中使用的是springboot2.0
默认的Hikari
连接,如果使用的是alibaba的druid,把HikariDataSource
替换成DruidDataSource
即可
@Configuration
@Slf4j
public class DataSourceConfig {
@Autowired
private ArsProperties arsProperties;
@Autowired
private AmsProperties amsProperties;
@Autowired
private PasProperties pasProperties;
@Bean(name = "arsDataSource")
public DataSource arsDataSource(){
log.info("ARS DataSource初始化----111111");
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(arsProperties.getUrl());
dataSource.setUsername(arsProperties.getUsername());
dataSource.setPassword(arsProperties.getPassword());
dataSource.setDriverClassName(arsProperties.getDriverClassName());
return dataSource;
}
@Bean(name = "pasDataSource")
public DataSource pasDataSource(){
log.info("PAS DataSource初始化----222222222");
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(pasProperties.getUrl());
dataSource.setUsername(pasProperties.getUsername());
dataSource.setPassword(pasProperties.getPassword());
dataSource.setDriverClassName(pasProperties.getDriverClassName());
return dataSource;
}
@Primary //指定一个主要的数据库连接,不添加此注解会在运行时报错
//@Primary的意思是在众多相同的bean中,优先使用用@Primary注解的bean.
@Bean(name = "amsDataSource")
public DataSource amsDataSource(){
log.info("AMS DataSource初始化----33333");
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(amsProperties.getUrl());
dataSource.setUsername(amsProperties.getUsername());
dataSource.setPassword(amsProperties.getPassword());
dataSource.setDriverClassName(amsProperties.getDriverClassName());
return dataSource;
}
}
5. 配置数据源、连接工厂、事物管理器、repository目录
新建AmsDataBaseConfig
类
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryAms", // 配置连接工厂
transactionManagerRef = "transactionManagerAms", // 配置事物管理器
basePackages = {"com.pccw.data.migration.repository.ams"} // 设置dao所在位置
)
public class AmsDataBaseConfig {
// 配置数据源
@Autowired
//@Qualifier("amsDataSource")
private DataSource amsDataSource;
@Primary
@Bean(name = "entityManagerFactoryAms")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryAms(EntityManagerFactoryBuilder builder) {
return builder
// 设置数据源
.dataSource(amsDataSource)
//设置实体类所在位置.扫描所有带有 @Entity 注解的类
.packages("com.pccw.data.migration.entity.ams")
// Spring会将EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
// Repository就能用它来创建 EntityManager 了,然后 EntityManager 就可以针对数据库执行操作
.persistenceUnit("amsPersistenceUnit")
.build();
}
/**
* 配置事物管理器
*
* @param builder
* @return
*/
@Bean(name = "transactionManagerAms")
PlatformTransactionManager transactionManagerAms(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryAms(builder).getObject());
}
}
新建ArsDataBaseConfig
类
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryArs", // 配置连接工厂
transactionManagerRef = "transactionManagerArs", // 配置事物管理器
basePackages = {"com.pccw.data.migration.repository.ars"} // 设置dao所在位置
)
public class ArsDataBaseConfig {
// 配置数据源
@Autowired
private DataSource arsDataSource;
@Bean(name = "entityManagerFactoryArs")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryArs(EntityManagerFactoryBuilder builder) {
return builder
// 设置数据源
.dataSource(arsDataSource)
//设置实体类所在位置.扫描所有带有 @Entity 注解的类
.packages("com.pccw.data.migration.entity.ars")
// Spring会将EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
// Repository就能用它来创建 EntityManager 了,然后 EntityManager 就可以针对数据库执行操作
.persistenceUnit("arsPersistenceUnit")
.build();
}
/**
* 配置事物管理器
*
* @param builder
* @return
*/
@Bean(name = "transactionManagerArs")
PlatformTransactionManager transactionManagerArs(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryArs(builder).getObject());
}
}
新建PasDataBaseConfig
类
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryPas", // 配置连接工厂
transactionManagerRef = "transactionManagerPas", // 配置事物管理器
basePackages = {"com.pccw.data.migration.repository.pas"} // 设置dao所在位置
)
public class PasDataBaseConfig {
// 配置数据源
@Autowired
private DataSource pasDataSource;
@Bean(name = "entityManagerFactoryPas")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPas(EntityManagerFactoryBuilder builder) {
return builder
// 设置数据源
.dataSource(pasDataSource)
//设置实体类所在位置.扫描所有带有 @Entity 注解的类
.packages("com.pccw.data.migration.entity.pas")
// Spring会将EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
// Repository就能用它来创建 EntityManager 了,然后 EntityManager 就可以针对数据库执行操作
.persistenceUnit("pasPersistenceUnit")
.build();
}
/**
* 配置事物管理器
*
* @param builder
* @return
*/
@Bean(name = "transactionManagerPas")
PlatformTransactionManager transactionManagerPas(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryPas(builder).getObject());
}
}
@Primary
的意思是在众多相同的bean中,优先使用用@Primary
注解的bean.而@Qualifier
这个注解则指定某个bean有没有资格进行注入。
原文中在
AmsDataBaseConfig
类中在注入amsDataSource
的时候有添加了@Qualifier
注解的,但是我这边一开始添加的时候会在运行时候有报错,去掉了就可以了。
6. 配置实体类
配置AmsUser
实体类,其他两个类似,就不一一贴代码出来了
package com.pccw.data.migration.entity.ams;
import com.pccw.data.migration.constants.DataSourceSchema;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import java.io.Serializable;
import java.sql.Timestamp;
/**
* The persistent class for the COMMON_USER database table.
*
*/
@Entity
@Table(name="COMMON_USER",schema = "GOLD_RESOURCE")
//@NamedQuery(name="AmsUser.findAll", query="SELECT c FROM CommonUser c")
public class AmsUser implements Serializable {
private static final long serialVersionUID = 1L;
public static final String STATUS_PENDING = "P";
public static final String STATUS_ACTIVE = "A";
public static final String STATUS_DELETE = "D";
public static final String STATUS_BLOCK = "B";
public static final String STATUS_ALL = "ALL";
public static final int ID_GENERATION_LENGTH = 10;
@Id
@Column(name = "ID", nullable = false, unique = true)
private String id;
@Column(name="COUNT")
private Integer count;
@Column(name="CREATE_STAFF")
private String createStaff;
@Column(name="CREATE_TIMESTAMP")
private Timestamp createTimestamp;
private String email;
@Column(name="EMPLOYEE_ID")
private String employeeId;
private String enabled;
@Column(name="HASH_PASSWORD")
private String hashPassword;
@Column(name="LAST_LOGIN_TIME")
private Timestamp lastLoginTime;
@Column(name="LOGIN_NAME")
private String loginName;
private String mobile;
@Column(name="MODIFY_STAFF")
private String modifyStaff;
@Column(name="NICK_NAME")
private String nickName;
private String remark;
@Column(name="UPDATE_TIMESTAMP")
private Timestamp updateTimestamp;
private String username;
@Column(name = "STATUS")
private String status;
@Column(name = "LOGIN_STATE")
private String loginState;
public AmsUser() {
}
@Override
public String toString() {
return "AmsUser{" +
"id='" + id + '\'' +
", count=" + count +
", createStaff='" + createStaff + '\'' +
", createTimestamp=" + createTimestamp +
", email='" + email + '\'' +
", employeeId='" + employeeId + '\'' +
", enabled='" + enabled + '\'' +
", hashPassword='" + hashPassword + '\'' +
", lastLoginTime=" + lastLoginTime +
", loginName='" + loginName + '\'' +
", mobile='" + mobile + '\'' +
", modifyStaff='" + modifyStaff + '\'' +
", nickName='" + nickName + '\'' +
", remark='" + remark + '\'' +
", updateTimestamp=" + updateTimestamp +
", username='" + username + '\'' +
", status='" + status + '\'' +
", loginState='" + loginState + '\'' +
'}';
}
public String getId() {
return this.id;
}
public void setId(String id) {
this.id = id;
}
public Integer getCount() {
return this.count;
}
public void setCount(Integer count) {
this.count = count;
}
public String getCreateStaff() {
return this.createStaff;
}
public void setCreateStaff(String createStaff) {
this.createStaff = createStaff;
}
public Timestamp getCreateTimestamp() {
return this.createTimestamp;
}
public void setCreateTimestamp(Timestamp createTimestamp) {
this.createTimestamp = createTimestamp;
}
public String getEmail() {
return this.email;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmployeeId() {
return this.employeeId;
}
public void setEmployeeId(String employeeId) {
this.employeeId = employeeId;
}
public String getEnabled() {
return this.enabled;
}
public void setEnabled(String enabled) {
this.enabled = enabled;
}
public String getHashPassword() {
return this.hashPassword;
}
public void setHashPassword(String hashPassword) {
this.hashPassword = hashPassword;
}
public Timestamp getLastLoginTime() {
return this.lastLoginTime;
}
public void setLastLoginTime(Timestamp lastLoginTime) {
this.lastLoginTime = lastLoginTime;
}
public String getLoginName() {
return this.loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
public String getMobile() {
return this.mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getModifyStaff() {
return this.modifyStaff;
}
public void setModifyStaff(String modifyStaff) {
this.modifyStaff = modifyStaff;
}
public String getNickName() {
return this.nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getRemark() {
return this.remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public Timestamp getUpdateTimestamp() {
return this.updateTimestamp;
}
public void setUpdateTimestamp(Timestamp updateTimestamp) {
this.updateTimestamp = updateTimestamp;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getLoginState() {
return loginState;
}
public void setLoginState(String loginState) {
this.loginState = loginState;
}
}
7. 配置repository
配置AmsUserRepository
,其他两个类似,就不贴代码了
package com.pccw.data.migration.repository.ams;
import com.pccw.data.migration.entity.ams.AmsUser;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface AmsUserRepository extends JpaRepository<AmsUser, String> {
List<AmsUser> findAllByStatus(String status);
AmsUser findByUsername(String username);
}
ArsUserService
8. 编写Service类
public interface ArsUserService {
List<ArsUser> findAll();
}
ArsUserServiceImpl
@Service
public class ArsUserServiceImpl implements ArsUserService {
@Override
public List<ArsUser> findAll() {
return arsUserRepository.findAll();
}
}
9. 编写junit测试类测试
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class )
public class Test {
@Resource
AmsUserService amsUserService;
@Resource
ArsUserService arsUserService;
@Resource
IntegrationUserHandler userHandler;
@Resource
PasUserService pasUserService;
@org.junit.Test
public void testFindAllUser(){
System.out.println("--------------------------------- Junit test ---------------------------------");
List<AmsUser> users = amsUserService.getUserList("A");
System.out.println("amsUserList size: "+users.size());
System.out.println("--------------------------------- Junit test ---------------------------------");
List<ArsUser> userList = arsUserService.findAll();
System.out.println("arsUserList size: "+userList.size());
System.out.println("--------------------------------- Junit test ---------------------------------");
List<PasUser> pasUserList = pasUserService.findAll();
System.out.println("pasUserList size: "+pasUserList.size());
System.out.println("--------------------------------- Junit test ---------------------------------");
}
}
测试结果:
2020-09-03 11:58:26.184 INFO 8284 --- [ main] Test : Started Test in 9.074 seconds (JVM running for 10.45)
--------------------------------- Junit test ---------------------------------
2020-09-03 11:58:26.270 INFO 8284 --- [ main] c.p.d.m.service.ams.AmsUserServiceImpl : Start connecting DB to find all common user records
2020-09-03 11:58:26.555 INFO 8284 --- [ main] c.p.d.m.service.ams.AmsUserServiceImpl : End connecting DB to find all common user records
amsUserList size: 62
--------------------------------- Junit test ---------------------------------
arsUserList size: 31
--------------------------------- Junit test ---------------------------------
pasUserList size: 2
--------------------------------- Junit test ---------------------------------
四、错误排查
在配置过程中有出现过一些错误,不过都是一些不细心导致的,每一个数据库的实体类,repository都要一一对应,不要随便出现A的数据库配置了B的实体类。
如果出现错误说表或者视图不存在
,这种情况就很有可能是数据库的配置出错,详细排查下就可以了,要是还不行可以尝试清理idea的缓存,如下图所示:
五、源码
源码地址:
- 码云:https://gitee.com/liangwanquan/data_migration
- GitHub:https://github.com/liangwanquan/data_migration
六、总结
如果有疑问可以直接留言评论,如果觉得对你有帮助,可以小小的赞赏一杯奶茶钱,谢谢!!