mybatis-plus+spring-boot配置双数据源mysql+sqlserver

最新推荐文章于 2024-04-12 17:37:13 发布

kaisens

最新推荐文章于 2024-04-12 17:37:13 发布

阅读量1.8k

点赞数 1

分类专栏：框架 mysql mybatis 文章标签： sqlserver mysql mybatis spring-boot mybatis-plus

本文链接：https://blog.csdn.net/kaisens/article/details/126592025

版权

mysql 同时被 3 个专栏收录

6 篇文章 0 订阅

订阅专栏

mybatis

6 篇文章 0 订阅

订阅专栏

框架

4 篇文章 1 订阅

订阅专栏

pom文件引入
原本项目用的是mysql,因为需要从另一个系统取数据,索性配成双数据库.
引入sqlserver引擎

<!-- sqlserver -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>

配置application.yml
spring:
  application:
    name: wds
  mvc:
    static-path-pattern: /**
    pathmatch:
      matching-strategy: ant_path_matcher
  datasource:
#    单数据库配置
#    type: com.alibaba.druid.pool.DruidDataSource
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    druid:
#      url: jdbc:mysql://127.0.0.1:3306/party?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
#      username: root
#      password:root
#      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 FROM DUAL
#      test-while-idle: true
#      test-on-borrow: false
#      test-on-return: false
#      stat-view-servlet:
#        enabled: true
#        url-pattern: /druid/*
#        login-username: admin
#        login-password: admin
#      filter:
#        stat:
#          log-slow-sql: true
#          slow-sql-millis: 1000
#          merge-sql: false
#        wall:
#          config:
#            multi-statement-allow: true
#####################################################################################
#		双数据库配置
    mysqldb:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      druid:
        url: jdbc:mysql://127.0.0.1:3306/party?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: root
        password:root
        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 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
          login-username: admin
          login-password: admin
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: false
          wall:
            config:
              multi-statement-allow: true
    sqlserverdb:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://127.0.0.1:1433
      username: sa
      password: 1
#mybatis plus 设置
mybatis-plus:
  mapper-locations: classpath*:cn/wantsong/base/modules/**/xml/*Mapper.xml
  global-config:
    # 关闭MP3.0自带的banner
    banner: false
    db-config:
      #主键类型  0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: ASSIGN_UUID
      # 默认数据库表下划线命名
      table-underline: true
  configuration:
    map-underscore-to-camel-case: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  configuration2:
    map-underscore-to-camel-case: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

原先同事搭的框架,写的configuration,不知道干啥的,干掉.注掉类名上面的注解就行.

import cn.base.utils.oConvertUtils;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

/**
 * 单数据源配置（datasource.open = false时生效）
 * @Author 
 *
 */
//@Configuration
//@MapperScan(value={"cn.base.**.mapper*"})
public class MybatisPlusSaasConfig {
    /**
     * tenant_id 字段名
     */
    private static final String TENANT_FIELD_NAME = "tenant_id";
    /**
     * 哪些表需要做多租户 表需要添加一个字段 tenant_id
     */
    private static final List<String> tenantTable = new ArrayList<String>();

    static {
        tenantTable.add("demo");

//        //角色、菜单、部门
//        tenantTable.add("sys_role");
//        tenantTable.add("sys_permission");
//        tenantTable.add("sys_depart");
    }


    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                String tenant_id = oConvertUtils.getString(TenantContext.getTenant(),"0");
                return new LongValue(tenant_id);
            }

            @Override
            public String getTenantIdColumn(){
                return TENANT_FIELD_NAME;
            }

            // 返回 true 表示不走租户逻辑
            @Override
            public boolean ignoreTable(String tableName) {
                for(String temp: tenantTable){
                    if(temp.equalsIgnoreCase(tableName)){
                        return false;
                    }
                }
                return true;
            }
        }));
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

//    /**
//     * 下个版本会删除，现在为了避免缓存出现问题不得不配置
//     * @return
//     */
//    @Bean
//    public ConfigurationCustomizer configurationCustomizer() {
//        return configuration -> configuration.setUseDeprecatedExecutor(false);
//    }
//    /**
//     * mybatis-plus SQL执行效率插件【生产环境可以关闭】
//     */
//    @Bean
//    public PerformanceInterceptor performanceInterceptor() {
//        return new PerformanceInterceptor();
//    }

}

写两个configuration,一个数据源一个.
mysql:

package cn.base.config.mybatis;


import cn.base.utils.oConvertUtils;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
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.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * @author kaisens
 * @time 2022/8/29 15:23
 * @description sqlserver 数据库
 **/
@Configuration
@MapperScan(basePackages = "cn.base.modules.**.mapper*",sqlSessionFactoryRef = "mysqldbSqlSessionFactory")
public class MybatisMySqlConfig {

    @Value("${spring.datasource.mysqldb.driver-class-name}")
    String driverClass;
    @Value("${spring.datasource.mysqldb.druid.url}")
    String url;
    @Value("${spring.datasource.mysqldb.druid.username}")
    String userName;
    @Value("${spring.datasource.mysqldb.druid.password}")
    String passWord;


    /**
     * tenant_id 字段名
     */
    private static final String TENANT_FIELD_NAME = "tenant_id";
    /**
     * 哪些表需要做多租户 表需要添加一个字段 tenant_id
     */
    private static final List<String> tenantTable = new ArrayList<String>();

    static {
        tenantTable.add("demo");

//        //角色、菜单、部门
//        tenantTable.add("sys_role");
//        tenantTable.add("sys_permission");
//        tenantTable.add("sys_depart");
    }

    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.configuration")
    public MybatisConfiguration mybatisConfiguration() {
        return new MybatisConfiguration();
    }





    @Primary
    @Bean(name = "mysqldbDataSource")
    @ConfigurationProperties("spring.datasource.mysqldb")
    public DataSource masterDataSource(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(passWord);
        return dataSource;
    }

    @Primary
    @Bean(name = "mysqldbSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqldbDataSource") DataSource dataSource,MybatisConfiguration mybatisConfiguration) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:cn/base/modules/**/mapper/xml/*Mapper.xml"));
        sessionFactoryBean.setConfiguration(mybatisConfiguration);
        return sessionFactoryBean.getObject();
    }

    @Primary
    @Bean(name = "mysqldbSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionFactoryTemplate(@Qualifier("mysqldbSqlSessionFactory")SqlSessionFactory sqlSessionFactory ) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                String tenant_id = oConvertUtils.getString(TenantContext.getTenant(),"0");
                return new LongValue(tenant_id);
            }

            @Override
            public String getTenantIdColumn(){
                return TENANT_FIELD_NAME;
            }

            // 返回 true 表示不走租户逻辑
            @Override
            public boolean ignoreTable(String tableName) {
                for(String temp: tenantTable){
                    if(temp.equalsIgnoreCase(tableName)){
                        return false;
                    }
                }
                return true;
            }
        }));
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

sqlserver:

package cn.base.config.mybatis;


import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.DriverManagerDataSource;

import javax.sql.DataSource;

/**
 * @author 
 * @time 2022/8/29 15:23
 * @description sqlserver 数据库
 **/
@Configuration
@MapperScan(basePackages = "cn.base.modules.**.maper*",sqlSessionFactoryRef = "sqlserverdbSqlSessionFactory")
public class MybatisSqlServerConfig {

    @Value("${spring.datasource.sqlserverdb.driver-class-name}")
    String driverClass;
    @Value("${spring.datasource.sqlserverdb.url}")
    String url;
    @Value("${spring.datasource.sqlserverdb.username}")
    String userName;
    @Value("${spring.datasource.sqlserverdb.password}")
    String passWord;

    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.configuration2")
    public MybatisConfiguration mybatisConfiguration2() {
        return new MybatisConfiguration();
    }



    @Bean(name = "sqlserverdbDataSource")
    @ConfigurationProperties("spring.datasource.sqlserverdb")
    public DataSource masterDataSource(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(passWord);
        return dataSource;
    }


    @Bean(name = "sqlserverdbSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("sqlserverdbDataSource") DataSource dataSource,MybatisConfiguration mybatisConfiguration2) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:cn/base/modules/**/maper/xml/*Mapper.xml"));
        sessionFactoryBean.setConfiguration(mybatisConfiguration2);
        return sessionFactoryBean.getObject();
    }


    @Bean(name = "sqlserverdbSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionFactoryTemplate(@Qualifier("sqlserverdbSqlSessionFactory")SqlSessionFactory sqlSessionFactory ) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

项目结构
目录结构

成功运行.
注意看几个扫描路径是否正确,还有数据库链接信息是否有误(这玩意不会主动报错,都是抓出来的).
总之就这几个点我调试了一天才搞定,主要还是因为新框架以前根本没用过mybatis-plus和boot.

kaisens

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
1
评论
mybatis-plus+spring-boot配置双数据源mysql+sqlserver

原本项目用的是mysql,因为需要从另一个系统取数据,索性配成双数据库.写两个configuration,一个数据源一个.引入sqlserver引擎。
复制链接

扫一扫