参考: https://blog.csdn.net/wodejiaAA/article/details/130654780
pom.xml依赖
这里springboot的版本为2.4.2
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!--spring-boot-web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.1</version>
</dependency>
<!-- druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.8</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
application.yml
#配置数据源
spring:
datasource:
druid:
# 初始连接数
initial-size: 5
# 最小连接数
min-idle: 15
# 最大连接数
max-active: 30
# eladmin库
eladmin:
url: jdbc:mysql://xxx:3306/eladmin?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: eladmin
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
# CRM库
crm:
url: jdbc:mysql://xxx:3306/crm?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: ygsw
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
# Lims库
lims:
url: jdbc:mysql://xxx:23260/lims?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: root
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
启动类修改
@SpringBootApplication(exclude = { MybatisPlusAutoConfiguration.class, DruidDataSourceAutoConfigure.class, DataSourceAutoConfiguration.class})
注意这里是排除了Mybatis Plus自动配置类、Druid自动配置类以及数据源自动配置类,因为这些配置都是我们手动配置的
Mybatis-Plus 配置
package com.hugo.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.hugo.enums.DataSourceEnum;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 配置文件
* Created by jinjin on 2020-09-21.
*/
@Configuration
@MapperScan(basePackages ={"com.hugo.**.mapper"})
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
MybatisPlusInterceptor paginationInterceptor = new MybatisPlusInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量，默认 500 条，-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
//paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
PaginationInnerInterceptor page = new PaginationInnerInterceptor();
paginationInterceptor.addInnerInterceptor(page);
return paginationInterceptor;
}
/**
* 多数据源的创建 有几个就写几个
*/
@Bean(name = "eladmin")
@ConfigurationProperties(prefix = "spring.datasource.druid.eladmin" )
public DataSource eladmin() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "crm")
@ConfigurationProperties(prefix = "spring.datasource.druid.crm" )
public DataSource crm() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "lims")
@ConfigurationProperties(prefix = "spring.datasource.druid.lims" )
public DataSource lims() {
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源配置
* @return
*/
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("eladmin") DataSource eladmin, @Qualifier("crm") DataSource crm, @Qualifier("lims") DataSource lims) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map< Object, Object > targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceEnum.ELADMIN.getValue(), eladmin);
targetDataSources.put(DataSourceEnum.CRM.getValue(), crm);
targetDataSources.put(DataSourceEnum.LIMS.getValue(), lims);
//添加数据源
dynamicDataSource.setTargetDataSources(targetDataSources);
//设置默认数据源
dynamicDataSource.setDefaultTargetDataSource(eladmin);
return dynamicDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(eladmin(),crm(),lims()));
// 指定所有的mapper.xml文件,否则在切换数据源时提示ibatis.binding.BindingException
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*Mapper.xml"));
MybatisConfiguration configuration = new MybatisConfiguration();
//configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
sqlSessionFactory.setPlugins(new Interceptor[]{
//PerformanceInterceptor(),OptimisticLockerInterceptor()
paginationInterceptor()
//添加分页功能
});
//sqlSessionFactory.setGlobalConfig(globalConfiguration());
return sqlSessionFactory.getObject();
}
@Bean
public GlobalConfig globalConfig() {
GlobalConfig globalConfig = new GlobalConfig();
globalConfig.setMetaObjectHandler(new MybatisPlusFillHandler());
return globalConfig;
}
}
多数据源配置
- 数据源枚举类
package com.hugo.enums;
public enum DataSourceEnum {
ELADMIN("eladmin"),CRM("crm"),LIMS("lims");
private String value;
DataSourceEnum(String value){this.value=value;}
public String getValue() {
return value;
}
}
- 数据源切面配置
package com.hugo.annotation;
import com.hugo.enums.DataSourceEnum;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author zhangmy
* @date 2023/7/4 13:49
* @description 自定义多数据源切换
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
public @interface DataSource {
DataSourceEnum value() default DataSourceEnum.ELADMIN;
}
package com.hugo.aspect;
import com.hugo.annotation.DataSource;
import com.hugo.utils.DynamicDataSourceContextHolder;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* @author zhangmy
* @date 2023/7/4 13:50
* @description
*/
@Aspect
@Order(1)
@Component
public class DataSourceAspect {
@Pointcut("@annotation(com.hugo.annotation.DataSource)"
+ "|| @within(com.hugo.annotation.DataSource)")
public void dsPc() {
}
@Before("dsPc() && @annotation(dataSource)")
public void doBefore(DataSource dataSource) {
DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().getValue());
}
@After("dsPc()")
public void doAfter() {
DynamicDataSourceContextHolder.clearDataSourceType();
}
}
- 动态数据源决策
package com.hugo.config;
import com.hugo.utils.DynamicDataSourceContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author zhangmy
* @date 2023/7/4 13:43
* @description 动态数据源决策
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
- 数据源切换工具类
package com.hugo.utils;
import lombok.extern.slf4j.Slf4j;
/**
* @author zhangmy
* @date 2023/7/4 13:43
* @description 数据源切换工具类
*/
@Slf4j
public class DynamicDataSourceContextHolder {
/**
* 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
* 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源的变量,参考DataSourceEnum.java类的取值
*/
public static void setDataSourceType(String dsType) {
log.info("切换到{}数据源", dsType);
CONTEXT_HOLDER.set(dsType);
}
/**
* 获得数据源的变量
*/
public static String getDataSourceType() {
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSourceType() {
CONTEXT_HOLDER.remove();
}
}
测试示例
原先eladmin数据源是有一套完整的mapper、service和controller的,这里我们加一个lims的测试
LimsMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hugo.modules.system.service.mapper.LimsMapper">
<!-- 根据ID查询样本信息 -->
<select id="getSampleById" resultType="map">
select * from gen_modual_sm where ID = #{ID}
</select>
</mapper>
LimsMapper
package com.hugo.modules.system.service.mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.Map;
/**
* @author zhangmy
* @date 2023/7/4 17:22
* @description
*/
@Repository
public interface LimsMapper {
/**
* 根据ID查询样本信息
* @param ID
* @return
*/
Map<String, Object> getSampleById(@Param("ID") String ID);
}
TestController
package com.hugo.modules.system.rest;
import com.alibaba.fastjson.JSONObject;
import com.hugo.annotation.rest.AnonymousGetMapping;
import com.hugo.modules.system.LimsService;
import com.hugo.modules.system.service.UserService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Map;
/**
* @author zhangmy
* @date 2023/7/4 17:20
* @description
*/
@RestController
@RequestMapping("/api/test")
@RequiredArgsConstructor
public class TestController {
private final UserService userService;
private final LimsService limsService;
@AnonymousGetMapping("/demo")
public void test() {
// 查询用户
System.out.println(userService.getByUsername("admin"));
Map<String, Object> sampleMap = limsService.getSampleById("00178a07-2f4b-4b8b-9f79-e29550dc62b1");
System.out.println(JSONObject.toJSON(sampleMap));
}
}
系统我们配置的默认是eladmin的数据源,测试代码中我们使用默认数据查询admin的账户信息,然后切换lims数据源查询样本信息,下面是运行结果
一些问题
系统启动的时候在ApplicationRunner使用了数据源报错ibatis.binding.BindingException
这里尤其注意MybatisPlusConfig中的sqlSessionFactory配置
不管是启动使用数据源还是启动后切换数据源报这个错,都是mapper绑定的关系出现了问题,由于需求比较简单,我们可以把mapper都放在一个包下面,然后如上图中配置mapper的位置即可
Failed to configure a DataSource: 'url' attribute is not specified and no em...
这个问题是因为我们配置了多数据源,就不能让系统自动配置数据源了,所以要在启动类上面排除DataSourceAutoConfiguration
评论区