1.在项目开发过程中,随着项目不断的更新迭代,数据量越来越大,项目越来重,为了提高接口响应速度以及缓解数据库压力,可能会涉及到分库分表,分库的概念比较明确,集群、主从复制等,将修改和更新数据的操作全部由主库处理,查询数据由从库处理。分表有垂直分表和水平分表,垂直分表是将列按照业务相关以及使用频繁程度来进行划分。水平分表就是多个相同的表,根据用户相关信息使用算法或者取模等规则,决定当前数据处于那个表中。
2.多法人的实现场景也可以见解分表分库思想实现,缺点就是成本较大,每新增一个法人就得新加一个数据库。
一、使用自定义注解实现多数据源切换
- 话不多说,直接上项目。新建SpringBoot项目,引入mybatis-plus和mysql依赖。
- pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.explame</groupId>
<artifactId>datasourcedemo</artifactId>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.8</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<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.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.4</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
<!-- swagger -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.github.caspar-chen</groupId>
<artifactId>swagger-ui-layer</artifactId>
<version>1.1.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!-- 打包跳过测试 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.18.1</version>
<configuration>
<skipTests>true</skipTests>
</configuration>
</plugin>
</plugins>
</build>
</project>
3.yam文件配置
spring:
datasource:
db1:
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/bus?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: 用户名
password: 数据库密码
db2:
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/nacos?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: 用户名
password: 数据库密码
server:
port: 8081
- 新建数据源配置类
package com.example.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 数据源配置类
*/
@Configuration
public class MyDataSourceConfig {
@Bean(name = "db1")
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db2")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
/**
* 配置自定义的类DynamicDataSource,这里是把两个(多个)数据源放到DynamicDataSource中,
* 同时设置一个默认的数据源
*/
@Bean("dynamicDataSource")
public MyDataSource dynamicDataSource(@Qualifier("db1") DataSource db1,
@Qualifier("db2") DataSource db2) {
Map<Object, Object> targetDataSource = new HashMap<>();
targetDataSource.put("db1", db1);
targetDataSource.put("db2", db2);
MyDataSource myDataSource = new MyDataSource();
myDataSource.setTargetDataSources(targetDataSource);
myDataSource.setDefaultTargetDataSource(db1);
return myDataSource;
}
/**
* 把动态数据源放到SqlSessionFactory
*
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("db1") DataSource db1,
@Qualifier("db2") DataSource db2) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource(db1, db2));
//没有xml文件时会报错
// sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return sqlSessionFactoryBean.getObject();
}
}
5.继承Spring的抽象类AbstractRoutingDataSource ,并重新determineCurrentLookupKey方法,决定Spring容器连接那个数据源
package com.example.config;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 扩展 Spring 的 AbstractRoutingDataSource 抽象类,重写 determineCurrentLookupKey 方法
* 动态数据源
* determineCurrentLookupKey() 方法决定使用哪个数据源
*/
@Slf4j
public class MyDataSource extends AbstractRoutingDataSource {
//使用线程局部缓存存储数据源信息,保证每个线程互不影响
public static final ThreadLocal<String> holder = new ThreadLocal<>();
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String type) {
holder.set(type);
}
public static String getDataSource(){
return holder.get();
}
public static void removeDataSource(){
holder.remove();
}
}
6.新建自定义注解,并使用Aspect对注解进行拦截
package com.example.util;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSourceNote {
String value() default "";
}
package com.example.aspect;
import com.example.config.MyDataSource;
import com.example.util.DataSourceNote;
import org.aspectj.lang.ProceedingJoinPoint;
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.springframework.stereotype.Component;
import java.lang.reflect.Method;
@Aspect
@Component
public class MyDataSourceAspect {
@Pointcut("execution(* com.example.mapper.*.*(..))")
public void pointcut() {
}
@Around("pointcut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Method method = signature.getMethod();
DataSourceNote dataSourceNote = method.getAnnotation(DataSourceNote.class);
if (dataSourceNote != null) {
String value = dataSourceNote.value();
MyDataSource.setDataSource(value);
}
try {
return joinPoint.proceed();
} finally {
//操作完成,移除指定数据源,还原默认数据源
MyDataSource.removeDataSource();
}
}
}
7.新建mapper文件,创建接口,进行测试
package com.example.mapper;
import com.example.util.DataSourceNote;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserMapper {
@Select("select name from student where id=1")
@DataSourceNote("db1")
String getFristDbUserName();
@DataSourceNote("db2")
@Select("select name from student where id=2")
String getSecondDbUserName();
}
8.在两个数据库中分别创建student表。调用接口进行测试。
测试成功啦!!!!!!!
10.上述写两个方法只是为了给大家更好的展示效果,实际开发过程中肯定不能这么写,肯定是使用用户信息或者当前操作的类型来决定使用那个数据源,这里就不给大家演示了,自行研究吧。
二、使用包路径隔离
- 这种方法跟方法一原理一致,区别在于方法一是在使用过程中进行切换,而方法二是项目启动就决定了,该方法开发成本更大,不予推荐,不做过多介绍,贴上代码。
2.分别新建数据源配置类,指定其扫描包路径即可
package com.example.config;
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.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.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.io.IOException;
@Configuration
@MapperScan(basePackages = "com.example.othermapper", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class OtherDataSourceConfig {
@Bean("seconddatesource")
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource createDataSource(){
return DataSourceBuilder.create().build();
}
@Bean("secondSqlSessionFactory")
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("seconddatesource") DataSource oterdatesource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(oterdatesource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:othermapper/*.xml"));
return bean.getObject();
}
@Bean(name = "secondTransactionManager")
public DataSourceTransactionManager secondTransactionManager( @Qualifier("seconddatesource") DataSource otherDataSource) {
return new DataSourceTransactionManager(otherDataSource);
}
@Bean(name = "secondSqlSessionTemplate")
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory otherSqlSessionFactory) throws Exception {
return new SqlSessionTemplate(otherSqlSessionFactory);
}
}
package com.example.config;
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.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.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.example.mapper", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class FirstDataSourceConfig {
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
@Primary
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "primarySqlSessionFactory")
@Primary
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//mapper下面没有文件时会报错
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return bean.getObject();
}
@Bean(name = "primaryTransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "primarySqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
三、使用Mybatis拦截器Interceptor实现分表查询
1.Mybatis拦截器Interceptor介绍
- MyBatis允许使用者在映射语句执行过程中的某一些指定的节点进行拦截调用,通过织入拦截器,在不同节点修改一些执行过程中的关键属性,从而影响SQL的生成、执行和返回结果,如:来影响Mapper.xml到SQL语句的生成、执行SQL前对预编译的SQL执行参数的修改、SQL执行后返回结果到Mapper接口方法返参POJO对象的类型转换和封装等。
根据上面的对Mybatis拦截器作用的描述,可以分析其可能的用途;最常见的就是Mybatis自带的分页插件PageHelper或Rowbound参数,通过打印实际执行的SQL语句,发现我们的分页查询之前,先执行了COUNT(*)语句查询数量,然后再执行查询时修改了SQL语句即在我们写的SQL语句后拼接上了分页语句LIMIT(offset, pageSize);
2.Mybatis核心对象介绍
Configuration 初始化基础配置,比如MyBatis的别名等,一些重要的类型对象,如,插件,映射器,ObjectFactory和typeHandler对象,MyBatis所有的配置信息都维持在Configuration对象之中
SqlSessionFactory SqlSession工厂
SqlSession 作为MyBatis工作的主要顶层API,表示和数据库交互的会话,完成必要数据库增删改查功能
Executor MyBatis执行器,是MyBatis 调度的核心,负责SQL语句的生成和查询缓存的维护
StatementHandler 封装了JDBC Statement操作,负责对JDBC statement 的操作,如设置参数、将Statement结果集转换成List集合。
ParameterHandler 负责对用户传递的参数转换成JDBC Statement 所需要的参数,
ResultSetHandler 负责将JDBC返回的ResultSet结果集对象转换成List类型的集合;
TypeHandler 负责java数据类型和jdbc数据类型之间的映射和转换
MappedStatement MappedStatement维护了一条<select|update|delete|insert>节点的封装,
SqlSource 负责根据用户传递的parameterObject,动态地生成SQL语句,将信息封装到BoundSql对象中,并返回BoundSql 表示动态生成的SQL语句以及相应的参数信息
2.简单使用
- 知道它有什么用了以后,就知道能帮我们做什么。最经典的应用场景就是分页插件,说白了就是重写sql。同样的道理,除了分页以外会不会有其他需求场景?比如对数据增删改查的时候默认要拼接一个逻辑删除的字段,亦或是给表起别名,或者更改表名等。本章节主要测试更改表名和拼接其他字段查询的效果。话不多说直接上代码。
- 首先实现Mybatis拦截器,
package com.example.config;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlASTVisitor;
import com.example.entiy.UserDto;
import com.example.util.UserLocalUtil;
import org.apache.ibatis.cache.CacheKey;
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.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.lang.reflect.Method;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
@Intercepts(
{
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})),
}
)
public class MyInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
//args对应Signature注解里的args
Object[] args = invocation.getArgs();
Executor executor = (Executor)invocation.getTarget();
//这里对应Signature注解里的method
Method method = invocation.getMethod();
MappedStatement mappedStatement = (MappedStatement)args[0];
//args[2]这里面是我们sql的参数
BoundSql boundSql = mappedStatement.getBoundSql(args[2]);
String sql = boundSql.getSql();
System.out.println("拦截前sql :" + sql);
MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sql);
SQLStatement statement =mySqlStatementParser.parseStatement();
TableMySqlASTVisitorAdapter visitor = new TableMySqlASTVisitorAdapter();
statement.accept(visitor);
BoundSql bs = new BoundSql(mappedStatement.getConfiguration(),statement.toString(),boundSql.getParameterMappings(),args[1]);
System.out.println("拦截后sql :" + statement);
MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(bs));
for (ParameterMapping mapping : boundSql.getParameterMappings()) {
String prop = mapping.getProperty();
if (boundSql.hasAdditionalParameter(prop)) {
bs.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}
args[0] = newMs;
return invocation.proceed();
}
/***
* 这里不知道为啥要复制,还没有研究,
* @param ms
* @param newSqlSource
* @return
*/
private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
builder.keyProperty(ms.getKeyProperties()[0]);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
}
//重新构建sql要用的对象
public static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
private void changeTableName(String sql){
}
}
2.注册拦截器
可能有人看到这里会问为啥不用注解对Bean进行装载(呜呜呜,难受,当时用了另一种方法,死活注册不了,不知道为啥,也没找到原因,再说了活人还能让尿给憋死么,所以就换了一种方式,不过方法有很多,不限这一种)
package com.example.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.util.Iterator;
import java.util.List;
@Component
public class InterceptorConfig implements ApplicationRunner {
@Autowired
private List<SqlSessionFactory> sqlSessionFactory;
@Override
public void run(ApplicationArguments args) throws Exception {
MyInterceptor myInterceptor = new MyInterceptor();
Iterator<SqlSessionFactory> iterator = this.sqlSessionFactory.iterator();
while (iterator.hasNext()){
SqlSessionFactory sqlSessionFactory1 = (SqlSessionFactory)iterator.next();
sqlSessionFactory1.getConfiguration().addInterceptor(myInterceptor);
}
}
}
3.实现durid的MySqlASTVisitorAdapter的相关方法,可以解析sql,添加字段等功能
package com.example.config;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.expr.SQLExistsExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlTableIndex;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlASTVisitorAdapter;
import com.example.entiy.UserDto;
import com.example.util.UserLocalUtil;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TableMySqlASTVisitorAdapter extends MySqlASTVisitorAdapter {
public static final Map<String,String> tableMap=new HashMap<>();
static {
tableMap.put("1","_one");
}
@Override
public boolean visit(SQLExprTableSource sqlExistsExpr){
//这里使用静态数据代替算法、取模等逻辑
UserDto user = UserLocalUtil.getUser();
String suffix = tableMap.get(user.getUserId());
String tableName = sqlExistsExpr.getTableName();
changeName((MySqlSelectQueryBlock) sqlExistsExpr.getParent());
sqlExistsExpr.setExpr(sqlExistsExpr.getExpr()+suffix);
return true;
}
private void changeName(MySqlSelectQueryBlock mySqlSelectQueryBlock){
List<SQLSelectItem> selectList = mySqlSelectQueryBlock.getSelectList();
mySqlSelectQueryBlock.addCondition("s.PHONE=13724889158");
}
}
4.编写sql进行测试
sql修改成功
四.总结
- 需求是千变万化的,只有你想不到,没有做不到。在上述demo测试过程中有几个疑问点还没搞明白是什么原因,给大家罗列一下后续会更新,也欢迎大家留言。
- Signature注解里type和args里的属性值应该有什么关系,当时测试的时候当type=StatementHandler时会报错,调了好几次,由于时间原因没有研究
- 上述案例中有两个Signature,当我注释调一个的时候拦截器就拦截不到sql,这个问题回头再研究研究,可能是我哪里的属性写的不对。
- 案例写的比较简单,对很多东西也只是一知半解,有的地方可能写的不是很正确,欢迎大家指点