时间戳

首页 > 技术文章 > springboot mybatis/mybatis-plus 多数据源配置

springboot mybatis/mybatis-plus 多数据源配置

ssqswyf 2021-09-24 15:08 原文

最近需要在一个模块里使用两个数据库，因此要进行多数据源配置，此外，项目本身在用mybatis-plus，此处记录两种配置方法

一 springboot mybatis 多数据源

这种方法主要进行springboot mybatis 多数据源的配置，不适用于mybatis-plus，使用的是配置类的方法

1 数据库配置文件


spring:
  datasource:
    test1:
        jdbc-url: jdbc:mysql://localhost:3306/alice_test?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: xxx
    test2:
        jdbc-url: jdbc:mysql://localhost:3306/alice_test_two?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: xxx
 
mybatis:
  mapper-locations: classpath:*/mapper/**.xml

注意事项：url修改为jdbc-url，在单数据源配置中，使用的是url，不是jdbc-url。多数据源配置中使用url启动会报错（看版本）

2 配置类

主数据源配置类：

package com.alice.springboot.config;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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 javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.test", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSourceConfig1 {
 
    // 将这个对象放入Spring容器中
    @Bean(name = "test1DataSource")
    // 表示这个数据源是默认数据源
    @Primary
    // 读取application.properties中的配置参数映射成为一个对象
    // prefix表示参数的前缀
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource getDateSource1()
    {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "test1SqlSessionFactory")
    // 表示这个数据源是默认数据源
    @Primary
    // @Qualifier表示查找Spring容器中名字为test1DataSource的对象
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource datasource)
            throws Exception
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test/*.xml"));
        return bean.getObject();
    }
 
    @Bean("test1SqlSessionTemplate")
    // 表示这个数据源是默认数据源
    @Primary
    public SqlSessionTemplate test1SqlSessionTemplate(
            @Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}

次数据源配置类：

package com.alice.springboot.config;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.testTwo", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSourceConfig2 {
    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource getDateSource2()
    {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource datasource)
            throws Exception
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/testTwo/*.xml"));
        return bean.getObject();
    }
 
    @Bean("test2SqlSessionTemplate")
    public SqlSessionTemplate test2SqlSessionTemplate(
            @Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}

3 启动类——启动类需要取消加载数据源自动配置


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
 
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
//@MapperScan("com.alice.springboot.mapper.*")
public class AliceApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(AliceApplication.class, args);
    }
 
}

二 springboot mybatis-plus 多数据源(推荐)

引入“dynamic-datasource-spring-boot-starter”依赖，在yml中配置后，在使用从数据源的mapper上添加注释即可，非常方便。

1 项目结构

2 引入依赖(仅仅与mybatis-plus相关的，版本实测可用)

  <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>2.5.4</version>
        </dependency>

3 编写配置

application.yml

spring:
  mvc:
    static-path-pattern: /road/**
  resources:
    static-locations: file:${file.address.prefix}
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master，如果读者只是单数据源只需要注释掉slave相关配置即可，这里为了方便演示master与slave保持相同
      datasource:
        master:
          url: jdbc:postgresql://192.168.0.22:5432/ees_pdjgzx
          username: pdjgzx
          password: pdjgzx@sa
          driver-class-name: org.postgresql.Driver
        slave:
          url: jdbc:postgresql://192.168.0.22:5432/sys_authorization
          username: slifesys
          password: slifesyssa
          driver-class-name: org.postgresql.Driver
      initial-size: 10 # 以下是连接池配置
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      #validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        login-username: druid
        login-password: 123456
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          config:
            multi-statement-allow: true

#mybatis plus
mybatis-plus:
  mapper-locations: classpath:mapper/*/*.xml
  #实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.roadclean.api.*.*.entity
  check-config-location: true
  configuration:
    #是否开启自动驼峰命名规则（camel case）映射
    map-underscore-to-camel-case: true
    #全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存
    cache-enabled: false
    call-setters-on-nulls: true
    #配置JdbcTypeForNull, oracle数据库必须配置
    jdbc-type-for-null: 'null'
    #MyBatis 自动映射时未知列或未知属性处理策略 NONE：不做任何处理 (默认值), WARNING：以日志的形式打印相关警告信息, FAILING：当作映射失败处理，并抛出异常和详细信息
    auto-mapping-unknown-column-behavior: warning
  global-config:
    banner: false
    db-config:
      #主键类型  0:"数据库ID自增", 1:"未设置主键类型",2:"用户输入ID (该类型可以通过自己注册自动填充插件进行填充)", 3:"全局唯一ID (idWorker), 4:全局唯一ID (UUID), 5:字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: UUID
      #字段验证策略 IGNORED:"忽略判断", NOT_NULL:"非NULL判断", NOT_EMPTY:"非空判断", DEFAULT 默认的,一般只用于注解里(1. 在全局里代表 NOT_NULL,2. 在注解里代表 跟随全局)
      field-strategy: NOT_EMPTY
      #数据库大写下划线转换
      capital-mode: true
      #逻辑删除值
      logic-delete-value: 0
      #逻辑未删除值
      logic-not-delete-value: 1

4 启动类

@SpringBootApplication
@MapperScan("com.roadclean.api.*.mapper")
public class RoadcleanApplication {
    public static void main(String[] args) {
        SpringApplication.run(RoadcleanApplication.class,args);
    }
}

5 使用注释

使用主数据源的mapper不用管，在使用从数据源的mapper（interface）上添加注释

package com.roadclean.api.roadclean.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.roadclean.api.roadclean.entity.SysDepartmentinfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;


@DS("slave")
public interface SysDepartmentinfoMapper extends BaseMapper<SysDepartmentinfo> {

}

6 使用mapper

在service里使用mapper，主从数据源一致，注入后使用方法即可。

@Service
public class RoadCleanScenesServiceImpl implements RoadCleanScenesService {

    @Autowired
    private BaseMatunitinfoMapper baseMatunitinfoMapper;
    @Autowired
    private SysDepartmentinfoMapper sysDepartmentinfoMapper;

    @Override
    public Map<String, Object> getIndustryOverview() {

        Map<String, Object> map = new HashMap<>();
        List<BaseMatunitinfo> dataMList = new ArrayList<>();

        dataMList = baseMatunitinfoMapper.selectListt();

        List<SysDepartmentinfo> dataDList = new ArrayList<>();
        dataDList =  sysDepartmentinfoMapper.selectList(new QueryWrapper<>());


        map.put("m", dataMList);
        map.put("d", dataDList);

        return map;
    }
}

参考：https://blog.csdn.net/m0_37872413/article/details/91352507
https://blog.csdn.net/belonghuang157405/article/details/89708851

推荐阅读