spingboot通过DynamicDataSource来动态获取数据源,自定义注解进行切换

2022-09-04 13:36:24

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

git地址:https://gitee.com/itzhl/dynamic-data-source-demo.git

  • 作者:zhanglei500038
  • 原文链接:https://blog.csdn.net/zhanglei500038/article/details/109851474
    更新时间:2022-09-04 13:36:24