SpringBoot-整合SpringDataJPA+MyBatisPlus多数据源配置
参考文献
- Springboot整合mybatis实现多数据源所遇到的问题
- springboot + mybatis-plus 分包实现多数据源配置
- required a bean of type ‘org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder’ that could not be found
- JPA之EntityManager踩坑及解决:更改PersistenceContext
背景
- 公司项目最初数据库访问层使用的是
Spring Data JPA
后期由于内部原因改换使用MyBatis Plus
,由于业务需要对接第三方数据需要直接访问第三方的数据库,故而要配置MyBatis Plus
多数据源.
环境以及项目技术栈
1 | SpringBoot 2.5.6 |
公共代码
Entity
1 | package com.holelin.mysql.entity; |
Service
1 | package com.holelin.mysql.service.impl; |
Controller
1 | package com.holelin.mysql.controller; |
启动类
1 | package com.holelin.mysql; |
application配置
1 | server: |
问题: 手动配置MyBatis数据源,JPA插入数据时,无法入库
-
MyBatis配置
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
90
91
92
93
94
95
96package com.holelin.mysql.mybatis.config;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @Description: MyBaits 配置类
* @Author: HoleLin
* @CreateDate: 2022/1/29 2:22 PM
* @UpdateUser: HoleLin
* @UpdateDate: 2022/1/29 2:22 PM
* @UpdateRemark: 修改内容
* @Version: 1.0
*/
public class MyBatisPlusConfig {
private String url;
private String username;
private String password;
private String driverClassName;
public DataSource dataSource() {
final DataSource dataSource = DataSourceBuilder.create().
url(url).
username(username).
password(password).
driverClassName(driverClassName).
build();
return dataSource;
}
/**
* 配置 SqlSessionFactory TransactionManager SqlSessionTemplate
*/
public SqlSessionFactory mybatisSqlSessionFactory( DataSource dataSource)throws Exception {
final MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
mybatisSqlSessionFactoryBean.setDataSource(dataSource);
// 指定XML配置路径
// 注 此处需要使用 getResources()
// getResources() 获取所有类路径下的指定文件
// getResource() 表示从当前类的根路径去查找资源,能获取到同一个包下的文件
final Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath*:/mapping/**/*.xml");
mybatisSqlSessionFactoryBean.setMapperLocations(resources);
// 配置分页插件
Interceptor[] plugins = new Interceptor[]{mybatisPlusInterceptor()};
mybatisSqlSessionFactoryBean.setPlugins(plugins);
return mybatisSqlSessionFactoryBean.getObject();
}
public DataSourceTransactionManager transactionManager( { DataSource dataSource)
return new DataSourceTransactionManager(dataSource);
}
public SqlSessionTemplate sqlSessionTemplate( { SqlSessionFactory sqlSessionFactory)
return new SqlSessionTemplate(sqlSessionFactory);
}
public MybatisPlusInterceptor mybatisPlusInterceptor() {
final MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
} -
现象为控制台未打印出插入日志(配置文件中
spring.jpa.show-sql: true
),按道理插入成功的话应该会打印插入日志;推测可能是手动配置了MyBatis Plus
数据源配置,导致JPA
失效了.既然JPA失效了,那也手动配置一下JPA
的数据源 -
处理方式: 补充
JPA
数据源手动配置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
58package com.holelin.mysql.jpa.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
public class JpaConfig {
private JpaProperties jpaProperties;
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder,
{ DataSource dataSource)
return builder.dataSource(dataSource).
properties(jpaProperties.getProperties()).
packages("com.holelin.mysql.entity").
build();
}
public EntityManager entityManager(EntityManagerFactoryBuilder builder,
{ DataSource dataSource)
final EntityManagerFactory managerFactory = entityManagerFactory(builder, dataSource).getObject();
return managerFactory.createEntityManager();
}
public PlatformTransactionManager transactionManager(
EntityManagerFactoryBuilder builder,
{ DataSource dataSource)
final EntityManagerFactory managerFactory = entityManagerFactory(builder, dataSource).getObject();
return new JpaTransactionManager(managerFactory);
}
} -
配置完成后,调用
POST /multiple-data-source/jpa
接口,会报出Table 'test_data.multipledatasources' doesn't exist
异常信息 -
分析:
-
异常信息显示
multipledatasources
这个表不存在,但是数据库中创建的表名为multiple_data_sources
,将Entity
类中的@Table(name = "MultipleDataSources")
修改为@Table(name = "multiple_data_sources")
后重新运行程序,报出Unknown column 'multipleDataSources' in 'field list'
异常 -
进一步推测应该是
JPA
中的命名策略(ImplicitNamingStrategy
)出现了问题; -
为了证明推测,添加断点进行调试.经过调试后发现程序的命令策略确实出现了问题
- 按道理应该走
SpringImplicitNamingStrategy
实现类,但是程序实际走的是PhysicalNamingStrategy
默认实现.
1
2
3
4
5
6
7
8
9org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl
-->
org.hibernate.boot.MetadataSources#getMetadataBuilder(org.hibernate.boot.registry.StandardServiceRegistry)
-->
org.hibernate.boot.internal.MetadataBuilderImpl
-->
org.hibernate.boot.internal.MetadataBuilderImpl.MetadataBuildingOptionsImpl
-->
org.hibernate.boot.registry.selector.spi.StrategySelector#resolveDefaultableStrategy(java.lang.Class<T>, java.lang.Object, java.util.concurrent.Callable<T>) - 按道理应该走
-
debug发现可以通过配置
hibernate.implicit_naming_strategy
和hibernate.physical_naming_strategy
进行修改 -
修改后的
application.yml
文件内容1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19server:
port: 8092
spring:
datasource:
url: jdbc:mysql://localhost:3306/test_data?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
username: root
password: holelin..
driver-class-name: com.mysql.cj.jdbc.Driver
jpa:
open-in-view: false
hibernate:
ddl-auto: update
# 补充下面的配置项
properties:
hibernate.implicit_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
hibernate.physical_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
show-sql: true
-
-
补充两个配置后,重新启动程序并重新调用创建数据接口,在控制台中出现插入语句日志
-
总结与建议
- 在使用
JPA
建议在@Table
注解中将数据表的名称书写规范,虽然Spring提供的命名策略来做处理,但是保不齐会出现上诉问题,以及在Entity
中字段建议都加上@Column
并在name
中指明在数据表中实际的值来避免意料之外的问题;
- 在使用
配置MyBaits Plus多数据源
-
配置多数据源比较简单,可以复制第一份MyBaits配置进行修改
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
90
91
92
93
94
95
96package com.holelin.mysql.mybatis.second.config;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @Description: MyBaits 配置类
* @Author: HoleLin
* @CreateDate: 2022/1/29 2:22 PM
* @UpdateUser: HoleLin
* @UpdateDate: 2022/1/29 2:22 PM
* @UpdateRemark: 修改内容
* @Version: 1.0
*/
public class MyBatisPlusSecondConfig {
private String url;
private String username;
private String password;
private String driverClassName;
public DataSource secondDataSource() {
final DataSource dataSource = DataSourceBuilder.create().
url(url).
username(username).
password(password).
driverClassName(driverClassName).
build();
return dataSource;
}
/**
* 配置 SqlSessionFactory TransactionManager SqlSessionTemplate
*/
public SqlSessionFactory secondSqlSessionFactory( DataSource dataSource)throws Exception {
final MybatisSqlSessionFactoryBean secondSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
secondSqlSessionFactoryBean.setDataSource(dataSource);
// 指定XML配置路径
// 注 此处需要使用 getResources()
// getResources() 获取所有类路径下的指定文件
// getResource() 表示从当前类的根路径去查找资源,能获取到同一个包下的文件
final Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath*:/mapping/second/*.xml");
secondSqlSessionFactoryBean.setMapperLocations(resources);
// 配置分页插件
Interceptor[] plugins = new Interceptor[]{secondPlusInterceptor()};
secondSqlSessionFactoryBean.setPlugins(plugins);
return secondSqlSessionFactoryBean.getObject();
}
public DataSourceTransactionManager secondTransactionManager( { DataSource dataSource)
return new DataSourceTransactionManager(dataSource);
}
public SqlSessionTemplate secondSqlSessionTemplate( { SqlSessionFactory sqlSessionFactory)
return new SqlSessionTemplate(sqlSessionFactory);
}
public MybatisPlusInterceptor secondPlusInterceptor() {
return new MybatisPlusInterceptor();
}
} -
若出现下面异常,则需要在
JpaConfg
中补充1
2
3
4
5
6
7
8
9
10
11
12***************************
APPLICATION FAILED TO START
***************************
Description:
Parameter 0 of method entityManagerFactory in com.holelin.mysql.jpa.config.JpaConfig required a bean of type 'org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder' that could not be found.
Action:
Consider defining a bean of type 'org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder' in your configuration.1
2
3
4
public EntityManagerFactoryBuilder entityManagerFactoryBuilder() {
return new EntityManagerFactoryBuilder(new HibernateJpaVendorAdapter(), new HashMap<>(), null);
} -
启动类排除
DataSourceAutoConfiguration
以及DataSourceTransactionManagerAutoConfiguration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21package com.holelin.mysql;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.transaction.annotation.EnableTransactionManagement;
//@SpringBootApplication
public class MysqlApplication {
public static void main(String[] args) {
SpringApplication.run(MysqlApplication.class, args);
}
} -
特别注意点: 在配置多数据源的时候
@MapperScan
中的sqlSessionFactoryRef
一定要写并进行区分,不然会出现无法绑定Mapping.xml中自己写的方法;
遇到的坑
-
问题: 配置多数据源时,使用以下代码以及配置会抛出
java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
异常-
代码
1
2
3
4
5
6
public DataSource dataSource() {
return DataSourceBuilder.create().build();
} -
配置
1
2
3
4
5
6spring:
datasource:
url: jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
username: root
password: holelin..
driver-class-name: com.mysql.cj.jdbc.Driver
-
-
原因:
spring.datasource.url
: 数据库的JDBC URLspring.datasource.jdbc-url
: 用来重写自定义连接池
-
解决方法:
- 配置添加
spring.datasource.jdbc-url
- 配置添加