微服务里的springBoot+MybatisPlus+jdbc+多数据源配置+动态表空间的配置写法

记录springBoot、MybatisPlus框架、多数据源的配置使用
因为我这边的微服务里有含seat微服务的全局事务管理，所以，我这边的多数据源配置暂时都没有配置事务相关的配置，若有需要事务还得再研究下。

废话不多说，，，，开干：

首先pom.xml引入数据库相关依赖：

<!--	数据库操作开始	-->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
	<version>2.2.9.RELEASE</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.1.10</version>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.47</version>
</dependency>
<!--	数据库操作结束	-->
<!-- mybatis-plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.2</version>
</dependency>
<!-- oracle数据库依赖 -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.1.0</version>
</dependency>

自定义动态数据源类：DynamicDataSource.java

package com.test.config.mutidatasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源
 *
 * @author xyp
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		return DataSourceContextHolder.getDataSourceType();
	}

}

设置datasource的上下文，DataSourceContextHolder.java

package com.test.config.mutidatasource;

/**
 * datasource的上下文
 *
 * @author xyp
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /**
     * 设置数据源类型
     *
     * @param dataSourceType 数据库类型
     */
    public static void setDataSourceType(String dataSourceType) {
        contextHolder.set(dataSourceType);
    }

    /**
     * 获取数据源类型
     */
    public static String getDataSourceType() {
        return contextHolder.get();
    }

    /**
     * 清除数据源类型
     */
    public static void clearDataSourceType() {
        contextHolder.remove();
    }
}

接下来就是MybatisPlusConfig的配置

package com.test.config;

import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.core.annotation.Order;
import org.springframework.context.annotation.Scope;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;


@Configuration
@MapperScan(basePackages = {"com.test.*"})
@Slf4j
@Order(-1)
public class MybatisPlusConfig {
	/**
     * 全局mybatis配置
     */
	@Bean("mybatisplusConfiguration")
	@ConfigurationProperties(prefix = "mybatis-plus.configuration")
	@Scope("prototype")
	public MybatisConfiguration globalConfiguration(){
		return new MybatisConfiguration();
	}
	/**
     * 全局配置参数设置，用于动态表空间配置使用，不用可不用写
     */
	@Bean("configurationProperties")
	@ConfigurationProperties(prefix = "mybatis-plus.configuration-properties")
	public Properties configurationProperties(){
		return new Properties();
	}
/以下是动态表空间配置写法

	@Value("${test_db_schema}")
    private String test_db_schema;
    @Value("${app_db_schema}")
    private String app_db_schema;
    @Value("${sys_db_schema}")
    private String sys_db_schema;
    @Value("${log_db_schema}")
    private String log_db_schema;

    // 最新版
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));

        DynamicTableNameInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInterceptor();
        Map<String,TableNameHandler> map = new HashMap<>();
        map.put("${test_db_schema}", new TableNameHandler() {
            @Override
            public String dynamicTableName(String sql, String tableName) {
                return test_db_schema;
            }
        });
        map.put("${app_db_schema}",new TableNameHandler() {
            @Override
            public String dynamicTableName(String sql, String tableName) {
                return app_db_schema;
            }
        });
        map.put("${sys_db_schema}",new TableNameHandler() {
            @Override
            public String dynamicTableName(String sql, String tableName) {
                return sys_db_schema;
            }
        });
        map.put("${log_db_schema}",new TableNameHandler() {
            @Override
            public String dynamicTableName(String sql, String tableName) {
                return log_db_schema;
            }
        });
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }

}

自定义创建多数据源枚举

package com.test.common.enums;

/**
 * 
 * 多数据源的枚举
 *
 * @author xyp
 */
public interface DatasourceEnum {

	String DATA_SOURCE_DEFAULT= "defaultDataSource";			//默认数据源
	
	String DATA_SOURCE_BIZ = "dataSourceTest";			//其他业务的数据源
}

接下来是重点的多数据源配置写法

package com.test.config;


import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
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.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Properties;

@Configuration
public class DynamicDataSourceConfig{
	/**
     * 默认的数据源
     */
	@Bean(name = "defaultDataSource")
	@ConfigurationProperties(prefix = "spring.mr.datasource")
	public DataSource defaultDataSource(){
		return DataSourceBuilder.create().build();
	}
	
	/**
     * 单数据源连接池配置
     */
    @Bean
    @ConditionalOnProperty(prefix = "spring", name = "muti-datasource-open", havingValue = "false")
    public DataSource singleDatasource() throws NacosException {
        return defaultDataSource();
    }
	/**
     * 另一个的数据源
     */
	@Bean(name = "testDataSource")
	@ConfigurationProperties(prefix = "spring.test.datasource")
	public DataSource testDataSource(){
		return DataSourceBuilder.create().build();
	}

	/**
     * 多数据源连接池配置
     */
    @Bean
    @ConditionalOnProperty(prefix = "test", name = "muti-datasource-open", havingValue = "true")
    public DynamicDataSource mutiDataSource(@Qualifier("defaultDataSource") DataSource defaultDataSource,@Qualifier("testDataSource") DataSource testDataSource,) throws NacosException {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        HashMap<Object, Object> hashMap = new HashMap<>();
        hashMap.put(DatasourceEnum.DATA_SOURCE_DEFAULT, defaultDataSource);
        hashMap.put(DatasourceEnum.DATA_SOURCE_TEST, testDataSource);
        dynamicDataSource.setTargetDataSources(hashMap);
        dynamicDataSource.setDefaultTargetDataSource(dataSourceSguav);
        return dynamicDataSource;
    }

	@Bean
	@Primary
    public sqlSessionFactory sqlSessionFactory(@Qualifier("mutiDataSource") DynamicDataSource mutiDataSource,@Qualifier("mybatisplusConfiguration") MybatisConfiguration configuration,@Qualifier("configurationProperties") Properties properties,@Qualifier("mybatisPlusInterceptor") MybatisPlusInterceptor mybatisPlusInterceptor) throws Exception {
		MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(mutiDataSource);
		sqlSessionFactoryBean.setConfiguration(configuration);
		sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/*Mapper.xml"));
		sqlSessionFactoryBean.setTypeAliasesPackage("com.test.**.entity");
		sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);
		return sqlSessionFactoryBean.getObject();
	}

	@Bean
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory){
		return new SqlSessionTemplate(sqlSessionFactory);
	}

	@Bean(name = "jdbcTemplate")
	public JdbcTemplate defaultJdbcTemplate(@Qualifier("defaultDataSource") DataSource defaultDataSource){
		return new JdbcTemplate(defaultDataSource);
	}
}

aop类

package com.test.config.mutidatasource.aop;

import com.test.config.mutidatasource.DataSourceContextHolder;
import com.test.config.mutidatasource.annotion.DataSource;
import com.test.config.mutidatasource.config.MutiDataSourceProperties;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;

/**
 * 多数据源切换的aop
 *
 * @author xyp
 * @date 2024年04月30日 下午15:22:16
 */
@Aspect
@Component
@ConditionalOnProperty(prefix = "spring", name = "muti-datasource-open", havingValue = "true")
public class MultiSourceExAop implements Ordered {

    private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    MutiDataSourceProperties mutiDataSourceProperties;

    @Pointcut(value = "@annotation(com.test.config.mutidatasource.annotion.DataSource)")
    private void cut() {

    }

    @Around("cut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {

        Signature signature = point.getSignature();
        MethodSignature methodSignature = null;
        if (!(signature instanceof MethodSignature)) {
            throw new IllegalArgumentException("该注解只能用于方法");
        }
        methodSignature = (MethodSignature) signature;

        Object target = point.getTarget();
        Method currentMethod = target.getClass().getMethod(methodSignature.getName(), methodSignature.getParameterTypes());

        DataSource datasource = currentMethod.getAnnotation(DataSource.class);
        if (datasource != null) {
            DataSourceContextHolder.setDataSourceType(datasource.name());
            log.debug("设置数据源为：" + datasource.name());
            System.out.println("============设置数据源为：" + datasource.name());
        } else {
            DataSourceContextHolder.setDataSourceType(mutiDataSourceProperties.getDefaultDataSourceName());
            log.debug("设置数据源为：dataSourceCurrent");
            System.out.println("dataSourceCurrent============设置数据源为：" + mutiDataSourceProperties.getDefaultDataSourceName());
        }

        try {
            return point.proceed();
        } finally {
            log.debug("清空数据源信息！");
            DataSourceContextHolder.clearDataSourceType();
        }
    }


    /**
     * aop的顺序要早于spring的事务
     */
    @Override
    public int getOrder() {
        return 1;
    }
}

接下来是多数据源自定义注解类

package com.uav.config.mutidatasource.annotion;

import java.lang.annotation.*;

/**
 * 
 * 多数据源标识
 *
 * @author xyp
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD , ElementType.TYPE, ElementType.PARAMETER})
public @interface DataSource {

	String name() default "";
}

以下是动态表空间的实现类重写，没用到可不用管

package com.test.common.config;

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.TableNameParser;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.test.common.util.StringHelper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 重写实现动态表空间的方法
 * @author XYP
 * @version 1.0
 * @date 2023/10/17
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings({"rawtypes"})
public class DynamicTableNameInterceptor extends DynamicTableNameInnerInterceptor {

    private Map<String, TableNameHandler> tableNameHandlerMap;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
        mpBs.sql(this.changeTable(mpBs.sql()));
    }

    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
            PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(this.changeTable(mpBs.sql()));
        }
    }

    protected String changeTable(String sql) {
        TableNameParser parser = new TableNameParser(sql);
        List<TableNameParser.SqlToken> names = new ArrayList<>();
        parser.accept(names::add);
        StringBuilder builder = new StringBuilder();
        int last = 0;
        for (TableNameParser.SqlToken name : names) {
            int start = name.getStart();
            if (start != last) {
                builder.append(sql, last, start);
                String value = name.getValue();
                String value2 = "";
                //含有"$"符号的，进行替换
                if (name.getValue().indexOf("$") >= 0){
                    int dNum = name.getValue().lastIndexOf(".");
                    value = name.getValue().substring(0,dNum);
                    if (dNum >= 0 && name.getValue().length() > dNum){
                        value2 = name.getValue().substring(dNum + 1);
                    }
                }
                TableNameHandler handler = tableNameHandlerMap.get(value);
                if (handler != null) {
                    builder.append(handler.dynamicTableName(sql, value) + (StringHelper.isBlank(value2)?"":("." + value2)));
                } else {
                    builder.append(value);
                }
            }
            last = name.getEnd();
        }
        if (last != sql.length()) {
            builder.append(sql.substring(last));
        }
        return builder.toString();
    }
}

最后配置文件里.properties的配置：

#默认数据源配置：
spring.mr.datasource.hikari.minimum-idle=50
spring.mr.datasource.hikari.maximum-pool-size=1000
spring.mr.datasource.hikari.auto-commit=true
spring.mr.datasource.hikari.idle-timeout=40000
spring.mr.datasource.hikari.pool-name=UserHikariCP
spring.mr.datasource.hikari.max-lifetime=180000
spring.mr.datasource.hikari.connection-timeout=40000
spring.mr.datasource.hikari.connection-test-query=select version()
spring.mr.datasource.default-dataSource-name=defaultDataSource
spring.mr.datasource.jdbc-url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.mr.datasource.username=root
spring.mr.datasource.password=root
spring.mr.datasource.driver-class-name=com.mysql.jdbc.Driver

#另外一个数据源配置：
spring.test.datasource.jdbc-url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.test.datasource.username=root
spring.test.datasource.password=root
spring.test.datasource.driver-class-name=oracle.jdbc.OracleDriver

到此以上多数据源配置结束，接下来是调用例子：

实体类照常写，无论是默认库的映射的实体类，还是另外一个库的实体类都是一样

package com.test.entity;

@TableName(value = "test",schema="${test_db_schema}")
@Data
public class TestDemo {
	 @TableId(value = "id",type = IdType.ASSIGN_UUID)
    private String id;
    @TableField(value = "name")
    private String name;// 名称
}

dao类也是一样，照常写

package com.test.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.test.entity.TestDemo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TestDemoMapper extends BaseMapper<TestDemo> {
}

重点在于服务实现类，默认数据源的服务实现类照常写，以下重点列出另外一个数据源的服务实现类写法

另外一个数据源的服务实现列：

package com.test.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.test.dao.TestDemoMapper;
import com.test.entity.TestDemo;
import java.util.List;
import java.util.Map;

/**
 * 多数据源切换，切换数据库，选择查询的数据库
 */
@Service
@Primary
public class TestDemoServiceImpl implements ITestDemoService {

	@Autowired
    private TestDemoMapper testDemoMapper;

    @Override
	@DataSource(name = DatasourceEnum.DATA_SOURCE_TEST)
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public List<TestDemo> findList(String name){
		QueryWrapper<TestDemo> queryWrapper = new QueryWrapper<>();
		queryWrapper.like("name",name);
		List<TestDemo> list = testDemoMapper.selectList(queryWrapper);
		return list;
	}
}

package com.test.service;

public interface ITestDemoService {
	List<TestDemo> findList(String name);
}

正常业务调用：
默认的service调用

package com.test.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.test.service.ITestDemoService;
import java.util.*;

@Service
public class TestServiceImpl implements ITestService {
	@Autowired
    private ITestDemoService testDemoService;

	@Override
    public Map<String,Object> queryAll(String name) {
		Map<String,Object> map = new HashMap<>();
		//默认数据源查找
		QueryWrapper<TestDemo> queryWrapper = new QueryWrapper<>();
		queryWrapper.like("name",name);
		List<TestDemo> list = testDemoMapper.selectList(queryWrapper);
		//兼容查询另外库同表的数据
		List<TestDemo> list1 = testDemoService.findList(name);
		map.put("list",list);
        map.put("list1",list1);
        return map;
	}
}

package com.test.service;

public interface ITestService {
	Map<String,Object> queryAll(String name);
}

最后控制类输出：

package com.test.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.test.service.ITestService;

@RestController
@RequestMapping("/test")
public class TestController {
	@Autowired
    private ITestService testService;

	/**
     * 测试接口
     * @param params
     * @return
     */
    @RequestMapping(value = "queryAll",name = "测试接口")
    public Map<String,Object> queryAll(@RequestBody Map<String, String> params){
        return testService.queryAll(params.get("name"));
    }
}