SpringBoot整合MyBatisPlus多数据源

2024-11-24 来源：个人技术集锦

方法一：一种简单的通过MP来实现双数据源的方式

1.添加配置

这里使用的是hikari数据源，需要注意一下url配置，使用hikari配置成jdbc-url，如果用druid配置成url。

spring:
	datasource:
		mysql:
			jdbc-url: jdbc:mysql://localhost:3306/datebase1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
			username: root
			password: root
			driver-class-name: com.mysql.cj.jdbc.Driver
			type: com.zaxxer.hikari.HikariDataSource
			# 连接池最大连接数，默认是 10
			maximum-pool-size: 100
			# 链接超时时间，默认 30000(30 秒)
			connection-timeout: 60000
			# 空闲连接存活最大时间，默认 600000(10 分钟)
			idle-timeout: 600000
			# 连接将被测试活动的最大时间量
			validation-timeout: 3000
			# 此属性控制池中连接的最长生命周期，值 0 表示无限生命周期，默认 1800000(30 分钟)
			max-lifetime: 1800000
			# 连接到数据库时等待的最长时间(秒)
			login-timeout: 5
			# 池中维护的最小空闲连接数
			minimum-idle: 10
			# 以下为解决 No operations allowed after connection closed 的主要配置
			# 验证连接是否有效
			validation-query: SELECT 1 
			# 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个
			test-on-borrow: false 
			# 指明连接是否被空闲连接回收器(如果有)进行检验；如果检测失败,则连接将被从池中去除。
			test-while-idle: true
			# 指定空闲连接检查、废弃连接清理、空闲连接池大小调整之间的操作时间间隔
			time-between-eviction-runs-millis: 18800
		clickhouse:
			jdbc-url: jdbc:clickhouse://localhost:8123/default
			username: name
			password: 123456
			driver-class-name: com.clickhouse.jdbc.ClickHouseDriver
			type: com.zaxxer.hikari.HikariDataSource
			# 连接池最大连接数，默认是 10
			maximum-pool-size: 100
			# 链接超时时间，默认 30000(30 秒)
			connection-timeout: 60000
			# 空闲连接存活最大时间，默认 600000(10 分钟)
			idle-timeout: 600000
			# 连接将被测试活动的最大时间量
			validation-timeout: 3000
			# 此属性控制池中连接的最长生命周期，值 0 表示无限生命周期，默认 1800000(30 分钟)
			max-lifetime: 1800000
			# 连接到数据库时等待的最长时间(秒)
			login-timeout: 5
			# 池中维护的最小空闲连接数
			minimum-idle: 10
			# 以下为解决 No operations allowed after connection closed 的主要配置
			# 验证连接是否有效
			validation-query: SELECT 1 
			# 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个
			test-on-borrow: false 
			# 指明连接是否被空闲连接回收器(如果有)进行检验；如果检测失败,则连接将被从池中去除。
			test-while-idle: true
			# 指定空闲连接检查、废弃连接清理、空闲连接池大小调整之间的操作时间间隔
			time-between-eviction-runs-millis: 18800

2.编写配置类

MySql的配置类：

package com.example.Config;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.LocalCacheScope;
import org.apache.ibatis.session.SqlSessionFactory;
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.cloud.context.config.annotation.RefreshScope;
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;

/**
 * @Description: mysql数据源配置类
 */
@Configuration
// 单独扫描mysql的mapper
@MapperScan(basePackages = "com.example.mapper.mysql", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MySqlConfig {

    @RefreshScope
    @Primary
    @Bean("mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource getMysqlDataSource() {
        //使用默认的Hikari连接池时，用默认的DataSourceBuilder
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean("mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml"));
        // mp的配置
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setMapUnderscoreToCamelCase(true);
        mybatisConfiguration.setCacheEnabled(true);
        mybatisConfiguration.setLocalCacheScope(LocalCacheScope.SESSION);
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        return sqlSessionFactoryBean.getObject();
    }

    @Primary
    @Bean("mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Clickhouse的配置类：

package com.example.Config;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.LocalCacheScope;
import org.apache.ibatis.session.SqlSessionFactory;
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.cloud.context.config.annotation.RefreshScope;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/**
 * @Description: clickhouse配置类
 */
@Configuration
// 单独扫描clickhouse的mapper
@MapperScan(basePackages = "com.example.mapper.clickhouse", sqlSessionFactoryRef = "clickHouseSqlSessionFactory")
public class ClickHouseConfig {

    @RefreshScope
    @Bean("clickHouseDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse")
    public DataSource getClickHouseDataSource() {
        //使用默认的Hikari连接池时，用默认的DataSourceBuilder
        return DataSourceBuilder.create().build();
    }

    @Bean("clickHouseSqlSessionFactory")
    public SqlSessionFactory clickHouseSqlSessionFactory(@Qualifier("clickHouseDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        // 不要用原生的org.mybatis.spring.SqlSessionFactoryBean，否则MyBatisPlus提供的BaseMapper不生效
//        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/clickhouse/*.xml"));
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setMapUnderscoreToCamelCase(true);
        mybatisConfiguration.setCacheEnabled(true);
        mybatisConfiguration.setLocalCacheScope(LocalCacheScope.SESSION);
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        // 相应的不要用原生的org.apache.ibatis.session.Configuration
        /*org.apache.ibatis.session.Configuration configuration = factory.getConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(true);
        configuration.setLocalCacheScope(LocalCacheScope.SESSION);*/
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("clickHouseSqlSessionTemplate")
    public SqlSessionTemplate clickHouseSqlSessionTemplate(@Qualifier("clickHouseSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

3.使用

如下分别是对应mysql和clickhouse表的mapper接口，定义好之后直接使用即可，不需要指定数据源，现在配置类的MapperScan注解中已经添加了扫描。

package com.example.mapper.mysql;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.bean.DO.UserMySqlDO;

public interface UserMySqlMapper extends BaseMapper<UserMySqlDO> {
}

package com.example.mapper.clickhouse;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.bean.DO.OrderClickhouseDO;

public interface OrderClickhouseMapper extends BaseMapper<OrderClickhouseDO> {
}

方法二：通过dynamic-datasource-spring-boot-starter实现

通过MybatisPlus的dynamic-datasource-spring-boot-starter组件可以很方便的实现多数据源配置和切换，推荐使用这种方式。

1.添加依赖

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

2.添加配置

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari: 
      # 连接池最大连接数，默认是 10
      maximum-pool-size: 100
      # 链接超时时间，默认 30000(30 秒)
      connection-timeout: 60000
      # 空闲连接存活最大时间，默认 600000(10 分钟)
      idle-timeout: 600000
      # 连接将被测试活动的最大时间量
      validation-timeout: 3000
      # 此属性控制池中连接的最长生命周期，值 0 表示无限生命周期，默认 1800000(30 分钟)
      max-lifetime: 1800000
      # 连接到数据库时等待的最长时间(秒)
      login-timeout: 5
      # 池中维护的最小空闲连接数
      minimum-idle: 10
      # 以下为解决 No operations allowed after connection closed 的主要配置
      # 验证连接是否有效
      validation-query: SELECT 1 
      # 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个
      test-on-borrow: false 
      # 指明连接是否被空闲连接回收器(如果有)进行检验；如果检测失败,则连接将被从池中去除。
      test-while-idle: true
      # 指定空闲连接检查、废弃连接清理、空闲连接池大小调整之间的操作时间间隔
      time-between-eviction-runs-millis: 18800
    dynamic:
      #默认主数据源
      primary: mysql
      datasource:
        # mysql数据源
        mysql:
          url: jdbc:mysql://localhost:3306/datebase1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
        # clickhouse数据源
        clickhouse:
          url: jdbc:clickhouse://localhost:8123/default
          username: name
          password: 123456
          driver-class-name: com.clickhouse.jdbc.ClickHouseDriver

Mybatis/MP的配置：这里仍然是原始的配置即可

mybatis:
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    local-cache-scope: session
  mapper-locations: classpath:mapper/*.xml

3.Mapper层使用

使用dynamic-datasource-spring-boot-starter不需要特地分开不同数据源的mapper接口，分开也无所谓，只要能够扫描到就行。
不添加DS注解则使用默认的数据源。

package com.example.mapper.mysql;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.bean.DO.UserMySqlDO;

public interface UserMySqlMapper extends BaseMapper<UserMySqlDO> {
}

添加@DS("clickhouse")指定数据源。

package com.example.mapper.clickhouse;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.bean.DO.OrderClickhouseDO;

@DS("clickhouse")
public interface OrderClickhouseMapper extends BaseMapper<OrderClickhouseDO> {
}

显示全文

全部栏目