1,业务说明
进行了分库分表,分库中书籍放入了不同的表中,书籍都有一个唯一的编号num,在主库中存储编号所在的库和表。根据编号num查询书籍时,会在注解中进行处理,先查询编号所在的库和表,然后再去精准查询。需要进行多数据源的切换,可以在注解中进行自动设置切换或者手动设置进行切换。
分库:
主库:
2,配置文件,配置多个数据源
server:
port: 8080
#默认数据库(不需要了,会默认使用数据库列表中dataBaseType=1的为主库)
#spring:
# datasource:
# driver-class-name: com.mysql.jdbc.Driver
# url: jdbc:mysql://localhost:3306/elin_invoice?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
# username: root
# password: 123456
#数据库配置列表
databaseHolder:
databaseList[0]:
dbId: 0
dataBaseType: 1 #1为主库
url: jdbc:mysql://localhost:3306/elin_invoice?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
maxActive: 100
InitialSize: 5
testWhileIdle: true
testOnBorrow: false
validationQuery: SELECT 1 FROM DUAL
timeBetweenEvictionRunsMillis: 50000
minEvictableIdleTimeMillis: 200000
databaseList[1]:
dbId: 1
dataBaseType: 2
url: jdbc:mysql://localhost:3306/subdata?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
maxActive: 100
InitialSize: 5
testWhileIdle: true
testOnBorrow: false
validationQuery: SELECT 1 FROM DUAL
timeBetweenEvictionRunsMillis: 50000
minEvictableIdleTimeMillis: 200000
#mybatis
mybatis:
mapper-locations: classpath*:mapper/*Mapper.xml
type-aliases-package: com.example.demo.entity
#mapper打印sql语句
logging:
level:
com:
example:
demo:
mapper: debug
2,配置类
使用DataBaseHolder 获取数据源配置
package com.example.demo.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* 类描述: 动态数据源的配置类
*
* @author zhanglei
* @date 2019/3/8 9:10
**/
@Component
@ConfigurationProperties(prefix = "database-holder")
@RefreshScope
public class DataBaseHolder {
private List<DataBaseInfo> databaseList;
public List<DataBaseInfo> getDatabaseList() {
return databaseList;
}
public void setDatabaseList(List<DataBaseInfo> databaseList) {
this.databaseList = databaseList;
}
public boolean existDatabase(Integer dbID){
for(DataBaseInfo dataBaseInfo:databaseList){
if(dataBaseInfo.getDbId().equals(dbID)){
return true;
}
}
return false;
}
}
数据源配置DataBaseConfiguration ,所有数据源放入一个map中dynamicDataSourceMap,将 dataBaseType为1的数据源作为默认指定的数据源。
dynamicRoutingDataSource.setDefaultTargetDataSource(dataSourceMap.get(defaultDataSourceID));
dynamicRoutingDataSource.setTargetDataSources(dynamicDataSourceMap);
package com.example.demo.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.cloud.context.config.annotation.RefreshScope;
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 org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 类描述:
*
* @author zhanglei
* @date 2019/3/8 10:40
**/
@Configuration
@RefreshScope
public class DataBaseConfiguration {
private Integer defaultDataSourceID = null;
@Bean
@RefreshScope
public HashMap<Integer, DataSource> dataSourceMap(DataBaseHolder dataBaseHolder) {
HashMap<Integer, DataSource> dataSourceMap = new HashMap<>();
List<DataBaseInfo> dataBaseInfos = dataBaseHolder.getDatabaseList();
if(CollectionUtils.isEmpty(dataBaseInfos)){
return dataSourceMap;
}
for (DataBaseInfo database : dataBaseInfos) {
if(defaultDataSourceID == null &&
database.getDataBaseType().equals(DataBaseInfo.DATABASE_TYPE_PRIMARY)){
defaultDataSourceID = database.getDbId();
}
DataSource datasource = new DataSource();
datasource.setPoolProperties(database);
datasource.setDriverClassName("com.mysql.jdbc.Driver");
dataSourceMap.put(database.getDbId(), datasource);
}
return dataSourceMap;
}
@Bean("dynamicDataSource")
@Primary
@RefreshScope
public javax.sql.DataSource dynamicDataSource(Map<Integer, DataSource> dataSourceMap) {
DynamicDataSource dynamicRoutingDataSource = new DynamicDataSource();
Map<Object,Object> dynamicDataSourceMap = new HashMap<>();
dynamicDataSourceMap.putAll(dataSourceMap);
// 将 primary 数据源作为默认指定的数据源
dynamicRoutingDataSource.setDefaultTargetDataSource(dataSourceMap.get(defaultDataSourceID));
dynamicRoutingDataSource.setTargetDataSources(dynamicDataSourceMap);
return dynamicRoutingDataSource;
}
@Bean
@RefreshScope
public SqlSessionFactory sqlSessionFactory(
@Qualifier("dynamicDataSource") javax.sql.DataSource dynamicDataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dynamicDataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:mapper/**/*.xml"));
// bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml"));
return bean.getObject();
}
@Bean(name = "sqlSessionTemplate")
@RefreshScope
public SqlSessionTemplate sqlSessionTemplate(
@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean(name = "invoiceJdbcTemplate")
@RefreshScope
public JdbcTemplate invoiceJdbcTemplate(@Qualifier("dynamicDataSource") javax.sql.DataSource dynamicDataSource) {
return new JdbcTemplate(dynamicDataSource);
}
}
3,自定义注解@NeedRoute,在注解切面中进行数据源的切换
package com.example.demo.sharding.annotation;
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 NeedRoute {
}
注解切面的处理:创建一个类RouteModel ,可以直接设置要查的库ID和表,也可以根据关键字段num,到数据库中查询所在的库和表。
package com.example.demo.sharding.filter;
import com.example.demo.basic.DataBaseContextHolder;
import com.example.demo.config.DataBaseHolder;
import com.example.demo.entity.AutoDatabaseRouteByBookNum;
import com.example.demo.mapper.AutoDatabaseRouteByBookNumMapper;
import com.example.demo.sharding.RouteModel;
import com.example.demo.sharding.annotation.NeedRoute;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.Signature;
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.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* 类描述:@NeedRoute注解处理
* 切换数据源的切面;
*
* @author zhanglei
* @date 2019/3/8 14:10
**/
@Component
@Aspect
@Order(0)
@Slf4j
public class NodeRouteInterceptor {
@Autowired
AutoDatabaseRouteByBookNumMapper autoDatabaseRouteByBookNumMapper;
@Autowired
DataBaseHolder dataBaseHolder;
@Pointcut("@annotation(com.example.demo.sharding.annotation.NeedRoute)")
public void aopPoint() {
System.out.println("aop...");
}
@After("aopPoint()")
public void afterRoute() {
DataBaseContextHolder.clear();
}
@Before("aopPoint()")
public void doRoute(JoinPoint jp) throws Exception {
Object target = jp.getTarget();
Signature signature = jp.getSignature();
if (!(signature instanceof MethodSignature)) {
throw new Exception("请求路径异常,signature instanceof MethodSignature = false");
}
MethodSignature ms = (MethodSignature) signature;
Method method = target.getClass().getMethod(signature.getName(), ms.getParameterTypes());
NeedRoute annotation = method.getAnnotation(NeedRoute.class);
if (annotation == null) {
throw new Exception("切面异常,未发现指定注解,annotation: " + annotation);
}
Object[] args = jp.getArgs();
if (args == null || args.length < 1) {
throw new Exception("路由方法参数不正确");
}
Object firstArg = args[0];
if ((firstArg instanceof RouteModel) == false) {
throw new Exception("路由方法参数类型不正确");
}
RouteModel routeModel = (RouteModel) firstArg;
dynamicRoute(routeModel);
log.debug("routeInterceptor, firstArg: {} , targetTableID : {} ", routeModel, routeModel.getTableID());
}
private void dynamicRoute(RouteModel routeModel) throws Exception {
// 动态路由已存在时,不重复获取
if (routeModel.getDbID() != null && routeModel.getTableName() != null) {
return;
}
if (routeModel.getDbID() != null && routeModel.getTableID() != null) {
routeModel.setTableName(routeModel.getTablePrefix() + "_" + routeModel.getTableID());
return;
}
//根据num查询所在数据库和表
String num = routeModel.getNum();
AutoDatabaseRouteByBookNum routeInfo = autoDatabaseRouteByBookNumMapper.selectByNum(num);
if (routeInfo == null) {
log.info("没有找到该编号的路由信息:" + num);
DataBaseContextHolder.clear();
throw new Exception("企业资质未配置完全,没有找到该编号的路由信息:" + num);
} else {
//判断数据源是否存在,如果不存在,直接抛出异常;
if (dataBaseHolder.existDatabase(routeInfo.getDbId()) == false) {
throw new Exception("没有找到该数据源,dbID = " + routeInfo.getDbId());
}
DataBaseContextHolder.setDBID(routeInfo.getDbId());
DataBaseContextHolder.setTableID(routeInfo.getTableId());
routeModel.setTableID(routeInfo.getTableId());
routeModel.setTableName(routeModel.getTablePrefix() + "_" + routeInfo.getTableId());
}
}
}
在需要分库查询的dao层加上自定义的注解
如果不加注解,会自动查询主库,也可以进行手动设置数据源。
package com.example.demo.dao;
import com.example.demo.entity.Book;
import com.example.demo.mapper.BookMapper;
import com.example.demo.sharding.RouteModel;
import com.example.demo.sharding.annotation.NeedRoute;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class BookDaoImpl implements BookDao {
@Autowired
private BookMapper bookMapper;
@Override
public List<Book> list() {
return bookMapper.list();
}
@Override
@NeedRoute
public Book findByRout(RouteModel routeModel) {
return bookMapper.findByRout(routeModel);
}
}
mapper文件,从参数中获取要查的表名,在指定的数据源中进行查询操作。
<?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.example.demo.mapper.BookMapper">
<resultMap id="BaseMap" type="com.example.demo.entity.Book">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="num" property="num"/>
<result column="auther_id" property="autherId"/>
<result column="author_name" property="authorName"/>
<result column="press" property="press"/>
</resultMap>
<select id="list" resultMap="BaseMap">
select * from book;
</select>
<select id="listByRout" resultMap="BaseMap">
select * from ${routeModel.tableName};
</select>
<select id="findByRout" resultMap="BaseMap">
select * from ${routeModel.tableName} where num = #{routeModel.num};
</select>
</mapper>
因为项目的处理类比较多,没有全部贴出来,可以下载我的项目demo,是springboot项目,里面有建表的sql语句,创建表后修改数据库配置,启动后访问swagger地址进行接口访问
swagger地址:localhost:8080/swagger-ui.html