其他
SpringBoot + MybatisPlus 实现多数据源事务
https://blog.csdn.net/guzhangyu12345/article/details/108559810
https://zhuanlan.zhihu.com/p/612825647?utm_id=0
#### 用Dynamic Datasource配置多数据源
dynamic-datasource-spring-boot-starter 是一个基于springboot的快速集成多数据源的启动器
https://blog.csdn.net/u010833154/article/details/126991521
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/*.xml
DataSourceConfig
@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));
//动态表名
sessionFactory.setPlugins(mybatisPlusInterceptor());
// 多数据源控制台打印sql,配置任意一个数据源即可
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
sessionFactory.setConfiguration(mybatisConfiguration);
return secondBean.getObject();
}
//开启事务
@Bean
public DataSourceTransactionManager secondDataSourceTransactionManager() throws Exception{
return new DataSourceTransactionManager(secondDataSource);
}
}
SpringBoot 多模块、多数据源项目中Mybatis找不到子模块Mapper的解决办法
https://blog.csdn.net/qq_19636353/article/details/115449613
其他
https://blog.csdn.net/jbfx455l/article/details/88802374
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
https://blog.csdn.net/github_38924695/article/details/103006978
参考地址
SpringBoot数据源配置DataSource
https://blog.csdn.net/qq_32384855/article/details/89467492
SpringBoot配置属性之DataSource
https://blog.csdn.net/qq_24084925/article/details/53341790
SpringBoot集成Mybatis实现多数据源
https://blog.csdn.net/maoyeqiu/article/details/74011626
SpringBoot+Hirika 实现动态数据源
https://blog.csdn.net/u014769528/article/details/87773426
SpringBoot系列十八:整合Hikari
https://blog.csdn.net/lizhiqiang1217/article/details/90573759
Spring boot 实现动态数据源
https://blog.csdn.net/qq_16513911/article/details/82668682
Mybatis-plus多数据源配置
https://blog.csdn.net/asxyxxx/article/details/105726377
包
<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都是从缓存中拿的,因此我们怎么切换数据源也没用,因此就出现表不存在的报错,具体源码可参考下面截图部分: