记录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"));
}
}