SpringBoot配置MybatisPlus多数据源

Lyndon1107

已于 2024-05-10 10:57:56 修改

阅读量2.4k

点赞数

分类专栏： SpringBoot 文章标签： spring boot

于 2019-12-31 16:54:36 首次发布

原文链接：https://blog.csdn.net/u014769528/article/details/87773426

版权

SpringBoot 专栏收录该内容

33 篇文章 2 订阅

订阅专栏

其他

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都是从缓存中拿的，因此我们怎么切换数据源也没用，因此就出现表不存在的报错，具体源码可参考下面截图部分: