其他
SpringBoot + MybatisPlus 实现多数据源事务
https://zhuanlan.zhihu.com/p/612825647?utm_id=0
#### 用Dynamic Datasource配置多数据源
dynamic-datasource-spring-boot-starter 是一个基于springboot的快速集成多数据源的启动器
https://jiuaidu.com/it/1930027/
springboot多数据源原理 https://blog.51cto.com/u_16099226/7026534概述
多数据源一般有两种解决方案：
1、通过显示模式声明多数据源，应用切换。优点是简单，缺点是不易于拓展，强耦合。
动态多数据源的场景
应用不拆，数据库拆
读写分离
SpringBoot集成Mybatis实现多数据源配置
application.yml中配置多数据源 master，second
指定数据源操作指定目录XML文件
该种方式需要操作的数据库的Mapper层和Dao层分别建立一个文件夹，分包放置。
结构图
Application.java
取消服务启动时对于数据源配置的自动导入。后面我们会手动配置多个数据源。如果有需要，也可把事务管理与mybatis的自动导入去掉。
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class, MybatisAutoConfiguration.class})
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}application.yml
spring:
datasource:
master:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://120.70.10.40:3306/wxtong-dev?useUnicode=true&characterEncoding=utf8
username: root
password: root
hikari:
minimum-idle: 5
idle-timeout: 180000
maximum-pool-size: 200
# 此属性控制池中连接的最长生命周期
max-lifetime: 1800000
connection-timeout: 30000
second:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://120.70.10.40:3306/wxt_staff?useUnicode=true&characterEncoding=utf8
username: root
password: root
hikari:
minimum-idle: 5
idle-timeout: 180000
maximum-pool-size: 200
max-lifetime: 1800000
connection-timeout: 30000
#mybatis:
#mapperLocations: classpath:mapper/second/*.xmlDataSourceConfig
@Configuration
public class DataSourceConfig
{
@Primary
@Bean(name="master")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource(){
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name="second")
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource secondDataSource(){
return DataSourceBuilder.create().build();
}
}master
DBMasterConfig.java
@Configuration
@MapperScan(basePackages = DBMasterConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DBMasterConfig
{
static final String PACKAGE = "cn.db.dao.master";
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
@Autowired
@Qualifier("master")
private DataSource masterDataSource;
@Bean(name="masterSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory()throws Exception{
SqlSessionFactoryBean masterBean = new SqlSessionFactoryBean();
masterBean.setDataSource(masterDataSource);
masterBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MAPPER_LOCATION));
return masterBean.getObject();
}
}second
DBSecondConfig.java
@Configuration
@MapperScan(basePackages = DBSecondConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class DBSecondConfig
{
static final String PACKAGE = "cn.db.dao.second";
static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";
@Bean(name = "secondSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("second") DataSource secondDataSource)throws Exception
{
SqlSessionFactoryBean secondBean = new SqlSessionFactoryBean();
secondBean.setDataSource(secondDataSource);
secondBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MAPPER_LOCATION));
return secondBean.getObject();
}
//开启事务
@Bean
public DataSourceTransactionManager secondDataSourceTransactionManager() throws Exception{
return new DataSourceTransactionManager(secondDataSource);
}
}SpringBoot 多模块、多数据源项目中Mybatis找不到子模块Mapper的解决办法
其他
Mybatis问题
解决 BindingException:Invalid bound statement (not found)
出现这个错误时，按以下步骤检查一般就会解决问题：
1：检查xml文件所在package名称是否和Mapper interface所在的包名一一对应；
2：检查xml的namespace是否和xml文件的package名称一一对应；
3：检查方法名称与xml的id是否对应；
4：去除xml文件中的中文注释；
5：随意在xml文件中加一个空格或者空行然后保存。HikariPool-1 - Failed to validate connection
Mybatis-plus多数据源配置
包
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>main
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class, MybatisPlusAutoConfiguration.class})
@SpringBootApplication
public class PhApplication {
public static void main(String[] args) {
SpringApplication.run(PhApplication .class, args);
}
}配置文件
spring:
datasource:
primary:
url: jdbc:mysql://192.168.3.202:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
second:
url: jdbc:mysql://192.168.3.202:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis-plus:
mapper-locations:
- classpath*:mapper/master/*.xml # *.xml的具体路径
mybatis-plus-second:
mapper-locations:
- classpath*:mapper/second/*.xml # *.xml的具体路径@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
@Primary
@Bean(name = "primaryMybatisPlusProperties")
@ConfigurationProperties("mybatis-plus")
public MybatisPlusProperties primaryMybatisPlusProperties(){
return new MybatisPlusProperties();
}
@Primary
@Bean(name = "secondMybatisPlusProperties")
@ConfigurationProperties("mybatis-plus-second")
public MybatisPlusProperties secondMybatisPlusProperties(){
return new MybatisPlusProperties();
}
}import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusPropertiesCustomizer;
import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandler;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;
@Configuration
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class SecondDataSourceConfig extends MybatisPlusAutoConfiguration {
static final String PACKAGE = "org.example.phdatagather.mapper.second";
static final String MAPPER_LOCATION = "classpath*:mapper/second/*.xml";
@Value("${spring.datasource.second.url}")
private String url;
@Value("${spring.datasource.second.username}")
private String user;
@Value("${spring.datasource.second.password}")
private String password;
@Value("${spring.datasource.second.driver-class-name}")
private String driverClass;
public SecondDataSourceConfig(@Autowired @Qualifier("secondMybatisPlusProperties") MybatisPlusProperties secondMybatisPlusProperties, ObjectProvider<Interceptor[]> interceptorsProvider, ObjectProvider<TypeHandler[]> typeHandlersProvider, ObjectProvider<LanguageDriver[]> languageDriversProvider, ResourceLoader resourceLoader, ObjectProvider<DatabaseIdProvider> databaseIdProvider, ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider, ObjectProvider<List<MybatisPlusPropertiesCustomizer>> mybatisPlusPropertiesCustomizerProvider, ApplicationContext applicationContext) {
super(secondMybatisPlusProperties, interceptorsProvider, typeHandlersProvider, languageDriversProvider, resourceLoader, databaseIdProvider, configurationCustomizersProvider, mybatisPlusPropertiesCustomizerProvider, applicationContext);
}
@Primary
@Bean(name = "secondDataSource")
public DataSource secondDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "secondTransactionManager")
@Primary
public DataSourceTransactionManager secondTransactionManager() {
return new DataSourceTransactionManager(secondDataSource());
}
@Bean(name = "secondSqlSessionFactory")
@Primary
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception{
return super.sqlSessionFactory(dataSource);
// MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
// sessionFactory.setDataSource(dataSource);
// sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
// return sessionFactory.getObject();
}
@Bean(name = "secondSqlSessionTemplate")
@Primary
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sessionFactory) throws Exception{
return new SqlSessionTemplate(sessionFactory);
}
}说明
在方案一中，我们针对每个DataSourece都创建对应的DataSourceTransactionManager实现，也可以看出DataSourceTransactionManager就是管理我们整体的事务的，当我们配置了事物管理器以及拦截Service中的方法后，每次执行Service中方法前会开启一个事务，并且同时会缓存DataSource、SqlSessionFactory、Connection，因为DataSource、Conneciton都是从缓存中拿的，因此我们怎么切换数据源也没用，因此就出现表不存在的报错，具体源码可参考下面截图部分:
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
