mybatis-plus多数据源配置

SomeOtherTime

已于 2023-12-11 15:50:40 修改

阅读量8.2k

点赞数 7

分类专栏： mybatis 后端文章标签： mybatis java spring boot

于 2022-12-02 11:36:55 首次发布

本文链接：https://blog.youkuaiyun.com/yaoct/article/details/128145307

版权

后端同时被 2 个专栏收录

20 篇文章

订阅专栏

mybatis

5 篇文章

订阅专栏

这里结合了springboot的多数据源配置，主要是手动创建数据源，再手动设置配置一些配置参数。

其中DateSource和transactionManager是关于数据源和事务的配置。SqlSessionTemplate和sqlSessionFactory是mybatis-plus框架的配置，通过@MapperScan注解的属性关联。

多数据源事务参考多数据源事务（非分布式）_SomeOtherTime的博客-优快云博客_多数据源事务

参考源码分析 - MyBatis Plus 多数据源踩坑 - 知乎

mybatis多数据源的驼峰命名法_mybatis 多个数据源名称驼峰-优快云博客

yml：

## 部署环境
server:
  port: 8210

spring:
  datasource:
    #不同数据配置名会变，以下是HikariDataSource
    one:
      driver-class-name: org.postgresql.Driver
      jdbc-url: jdbc:postgresql://192.168.2.89:5435/la_water_river
      username: postgres
      password: 
      minimum-idle: 10
      maximum-pool-size: 5
    two:
      driver-class-name: com.kingbase8.Driver
      jdbc-url: jdbc:kingbase8://172.16.1.28:54321/la_water_monitor?currentSchema=PUBLIC
      username: kingbases
      password: 
      minimum-idle: 5
      maximum-pool-size: 20
  # redis 配置
  redis:
    host: 192.168.2.76
    port: 6379
    password: 
    database: 10
    timeout: 10s
  cache:
    type: redis
    redis:
      time-to-live: 20000 #缓存超时时间ms
      cache-null-values: false #是否缓存空值

management:
  endpoints:
    web:
      base-path: /actuator
      exposure:
        include: "*"
  endpoint:
    health:
      show-details: always
  security:
    enabled: false

swagger:
  enable: true

logging:
  config: classpath:logback-spring-dev.xml

mybatis-plus:
  type-handlers-package: com.zjzy.mapper.typehandler
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:mapper/**/*Mapper.xml #多数据源下该配置失效，配置在代码中

数据源1:

package com.wisdomcity.laian.river.config.datasource;

import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.wisdomcity.laian.river.config.MybatisPlusConfig;
import com.wisdomcity.laian.river.config.UpdateMetaObjectHandler;
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.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.wisdomcity.laian.river.mapper.water",sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne")
public class DatasourceConfigOne {
//
//    @Autowired
//    @Qualifier("globalConfig")
//    private GlobalConfig globalConfig;

    @Autowired
    MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean(name = "dataSourceOne")
    @ConfigurationProperties(prefix = "spring.datasource.one")
    @Primary
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "sqlSessionFactoryOne")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceOne") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/water/*.xml"));
//        bean.setGlobalConfig(globalConfig);
        bean.setGlobalConfig(globalConfig());
        bean.setPlugins(mybatisPlusInterceptor);

        //https://blog.youkuaiyun.com/weixin_41785851/article/details/119739897
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        // 配置打印sql语句
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        return bean.getObject();
    }

    @Primary
    @Bean(name = "transactionManagerOne")
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSourceOne") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "sqlSessionTemplateOne")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryOne") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

//    /** 全局自定义配置 */
//    @Bean(name = "globalConfigMP")
//    @ConfigurationProperties(prefix = "mybatis-plus")
//    public GlobalConfig globalConfig(){
//        return new GlobalConfig();
//    }

    /**
     * 自动插入创建和更新时间
     * 多数据源下要创建多个，或者不创建，不然saveBatch等批量操作方法会报错，因为save方法是获取baseMapper，而批量操作的saveBatch方法是从全局配置 GlobalConfig 里获取的。
     */
    @Bean("waterGlobalConfig")
    public GlobalConfig globalConfig() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new UpdateMetaObjectHandler());
        return globalConfig;
    }
}

数据源2：

package com.wisdomcity.laian.river.config.datasource;

import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.wisdomcity.laian.river.config.UpdateMetaObjectHandler;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.wisdomcity.laian.river.mapper.monitor",sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo")
public class DatasourceConfigTwo {

//    @Autowired
//    @Qualifier("globalConfig")
//    private GlobalConfig globalConfig;

    @Autowired
    MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean(name = "dataSourceTwo")
    @ConfigurationProperties(prefix = "spring.datasource.two")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactoryTwo")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceTwo") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/monitor/*.xml"));
//        bean.setGlobalConfig(globalConfig);
        bean.setGlobalConfig(globalConfig());
        bean.setPlugins(mybatisPlusInterceptor);
        bean.setDataSource(dataSource);

        //https://blog.youkuaiyun.com/weixin_41785851/article/details/119739897
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        // 配置打印sql语句
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        return bean.getObject();
    }

    @Bean(name = "transactionManagerTwo")
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSourceTwo") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 自动插入创建和更新时间
     * 多数据源下要创建多个，或者不创建，不然saveBatch等批量操作方法会报错，因为save方法是获取baseMapper，而批量操作的saveBatch方法是从全局配置 GlobalConfig 里获取的。
     */
    @Bean("monitorGlobalConfig")
    public GlobalConfig globalConfig() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new UpdateMetaObjectHandler());
        return globalConfig;
    }
}

分页配置

package com.wisdomcity.laian.river.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * MybatisPlus分页配置
 */
@Configuration
public class MybatisPlusConfig {
    /**
     * 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }

}

jpa配置思路类似

@Configuration
@EntityScan(basePackages = "com.zjzy.po.sqlserver")
//1、实体扫描
//2、实体管理ref
//3、事务管理
@EnableJpaRepositories(
        basePackages = "com.zjzy.repository.sqlserver",
        entityManagerFactoryRef = "secondaryEntityManagerFactoryBean",
        transactionManagerRef = "secondaryTransactionManager")
@EnableTransactionManagement
public class SqlDataSourceConfig {
    /**
     * sqlserver第二个数据源，可以不加Qualifier
     */
    @Autowired
    @Qualifier("sqlDataSource")
    private DataSource dataSource;

    /**
     * jpa其他参数配置
     */
    @Autowired
    private JpaProperties jpaProperties;

    /**
     *  实体管理工厂builder
     */
    @Autowired
    private EntityManagerFactoryBuilder factoryBuilder;

    /**
     * 配置第二个实体管理工厂的bean
     * @return
     */
    @Bean(name = "secondaryEntityManagerFactoryBean")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {
        return factoryBuilder.dataSource(dataSource)
                //这一行的目的是加入jpa的其他配置参数比如（ddl-auto: update等）
                //当然这个参数配置可以在事务配置的时候也可以
//                .properties(jpaProperties.getHibernateProperties(new HibernateSettings()))
                .packages("com.zjzy.po.sqlserver")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    /**
     * EntityManager不过解释，用过jpa的应该都了解
     * @return
     */
    @Bean(name = "secondaryEntityManager")
    public EntityManager entityManager() {
        return entityManagerFactoryBean().getObject().createEntityManager();
    }

    /**
     * jpa事务管理
     * @return
     */
    @Bean(name = "secondaryTransactionManager")
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setEntityManagerFactory(entityManagerFactoryBean().getObject());
        return jpaTransactionManager;
    }
}

dynamicDataSource的配置方法

@Configuration
//@MapperScan(basePackages = "com.zjzy.emergencyservice.dao")
@MapperScan(basePackages = "com.zjzy.emergencyservice.dao",sqlSessionFactoryRef = "sqlSessionFactoryZero",sqlSessionTemplateRef = "sqlSessionTemplateZero")
public class DataSourceConfig {
//
    @Bean(name = "sqlSessionFactoryZero")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
        //SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
//        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        //mybatis多数据源添加驼峰命名https://blog.youkuaiyun.com/qq_37752382/article/details/120769453
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);

        sessionFactoryBean.setConfiguration(configuration);
        String locationPattern = "classpath*:/mapper/*.xml";
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "transactionManagerZero")
    public DataSourceTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplateZero")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryZero") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean("dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1(){
        return DataSourceBuilder.create().build();
    }

    @Bean("dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource2(){
        return DataSourceBuilder.create().build();
    }

    @Bean("dataSource3")
    @ConfigurationProperties(prefix = "spring.datasource.db3")
    public DataSource dataSource3(){
        return DataSourceBuilder.create().build();
    }

    @Bean("dataSource4")
    @ConfigurationProperties(prefix = "spring.datasource.db4")
    public DataSource dataSource4(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     * @return
     */
//    @Primary
    @Bean(name = "dynamicDataSource")
    @Primary
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSource1());
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap();
        dsMap.put(DataSourceEnum.MASTER_DATA_SOURCE_NAME.getDataSource(), dataSource1());
        dsMap.put(DataSourceEnum.CLUSTER_DATA_SOURCE_NAME.getDataSource(), dataSource2());
        dsMap.put(DataSourceEnum.POSTGRES_DATA_SOURCE_NAME.getDataSource(), dataSource3());
        dsMap.put(DataSourceEnum.MYSQL_DATA_SOURCE_NAME.getDataSource(), dataSource4());
        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    /**
     * 配置@Transactional注解事物
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}