SpringBoot：多数据源配置——注解+AOP

迷南。 2022-04-01 03:16 343阅读 0赞

\* maven依赖

<parent>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-parent</artifactId>
    	<version>2.1.1.RELEASE</version>
    	<relativePath/> 
    </parent>
    
    <dependencies>
    	<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-web</artifactId>
    	</dependency>
    
    	
    	<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-freemarker</artifactId>
    	</dependency>
    
    	
    	<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-log4j</artifactId>
    	</dependency>
    	
    	<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-aop</artifactId>
    	</dependency>
    	
    	<dependency>
    		<groupId>com.alibaba</groupId>
    		<artifactId>fastjson</artifactId>
    		<version>1.2.32</version>
    	</dependency>
    	
    	<dependency>
    		<groupId>org.projectlombok</groupId>
    		<artifactId>lombok</artifactId>
    	</dependency>
    	
    	<dependency>
    		<groupId>org.mybatis.spring.boot</groupId>
    		<artifactId>mybatis-spring-boot-starter</artifactId>
    		<version>1.1.1</version>
    	</dependency>
    	
    	<dependency>
    		<groupId>mysql</groupId>
    		<artifactId>mysql-connector-java</artifactId>
    	</dependency>
    </dependencies>
    
    <build>
    	<plugins>
    		<plugin>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-maven-plugin</artifactId>
    		</plugin>
    	</plugins>
    </build>

一，多数据源配置——注解+AOP

前一篇基于拆包配置维度对多数据源配置进行了简单实现。两种方式对比来看，拆包方式规范性更强，而注解方式更加注重灵活性。通过AOP方式，直接反射获取自定义注解，解析注解值进行数据源动态添加，实现多数据源配置。

二，基于AOP配置流程；相对拆包流程比较复杂，先对流程进行梳理，然后按照流程一步步实现

\* Java整体结构

![20181228213400121.png][]

\* 动态多数据源配置

-- DataSourceConfig

\* 创建线程持有数据库上下文

-- DynamicDataSourceHolder

\* 基于Spring提供的AbstractRoutingDataSource，动态添加数据源（事务下可能存在问题）

-- DynamicDataSource

\* 自定义注解，标识数据源

-- TargetDataSource

\* AOP前后置拦截解析类，对Mapper方法代用进行拦截

-- DataSourceAspect

\* 三层代码架构处理

-- DataSourceAOPController，DataSourceAOPService，DataSourceMapper

三，代码变现

0，application.properties

\* 不同于分数据源配置，单一数据源配置，jdbc-url为url

### mapper存储路径_AOP
    mybatis.mapper-locations=classpath:com.gupao.springboot.*.mapper/*.xml
    
    ### MYSQL_First数据源配置
    spring.datasource.first.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.first.jdbc-url=jdbc:mysql://localhost:3306/first?characterEncoding=utf-8&serverTimezone=GMT%2B8
    spring.datasource.first.username=root
    spring.datasource.first.password=123456
    
    ### MYSQL_First数据源配置
    spring.datasource.second.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.second.jdbc-url=jdbc:mysql://localhost:3306/second?characterEncoding=utf-8&serverTimezone=GMT%2B8
    spring.datasource.second.username=root
    spring.datasource.second.password=123456

1，动态多数据源配置

package com.gupao.springboot.datasourceaop.config;
    
    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.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.PlatformTransactionManager;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    
    /**
     * 配置数据源
     * @author pj_zhang
     * @create 2018-12-28 12:03
     **/
    @Configuration
    public class DataSourceConfig {
    
        /**
         * First数据源
         * @return
         */
        @Bean(name = "firstAopDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.first")
        public DataSource firstDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        /**
         * Second数据源
         * @return
         */
        @Bean(name = "secondAopDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.second")
        public DataSource secondDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        /**
         * 获取动态数据源
         * @return
         */
        @Bean(name = "dynamicDataSource")
        @Primary
        public DataSource dynamicDataSource() {
            DynamicDataSource dynamicDataSource = new DynamicDataSource();
            // 设置默认数据源为first数据源
            dynamicDataSource.setDefaultTargetDataSource(firstDataSource());
            // 配置多数据源, 
            // 添加数据源标识和DataSource引用到目标源映射
            Map<Object, Object> dataSourceMap = new HashMap<>();
            dataSourceMap.put("firstAopDataSource", firstDataSource());
            dataSourceMap.put("secondAopDataSource", secondDataSource());
            dynamicDataSource.setTargetDataSources(dataSourceMap);
            return dynamicDataSource;
        }
    
        @Bean
        public PlatformTransactionManager transactionManager() {
            return new DataSourceTransactionManager(dynamicDataSource());
        }
    
    }

2，创建线程持有数据库上下文，添加数据源到ThreadLocal中

package com.gupao.springboot.datasourceaop.context;
    
    /**
     * 线程持有数据源上下文
     *
     * @author pj_zhang
     * @create 2018-12-28 12:00
     **/
    public class DynamicDataSourceHolder {
    
        private static final ThreadLocal<String> THREAD_LOCAL = new ThreadLocal<String>();
    
        /**
         * 设置线程持有的DataSource, 底层以map形式呈现, key为当前线程
         *
         * @param dataSource
         */
        public static void setDataSource(String dataSource) {
            THREAD_LOCAL.set(dataSource);
        }
    
        /**
         * 获取线程持有的当前数据源
         *
         * @return
         */
        public static String getDataSource() {
            return THREAD_LOCAL.get();
        }
    
        /**
         * 清除数据源
         */
        public static void clear() {
            THREAD_LOCAL.remove();
        }
    
    }

3，基于Spring提供的AbstractRoutingDataSource，动态添加数据源（事务下可能存在问题）

package com.gupao.springboot.datasourceaop.config;
    
    import com.gupao.springboot.datasourceaop.context.DynamicDataSourceHolder;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    /**
     * spring为我们提供了AbstractRoutingDataSource，即带路由的数据源。
     * 继承后我们需要实现它的determineCurrentLookupKey()，
     * 该方法用于自定义实际数据源名称的路由选择方法，
     * 由于我们将信息保存到了ThreadLocal中，所以只需要从中拿出来即可。
     * @author pj_zhang
     * @create 2018-12-28 12:04
     **/
    @Slf4j
    public class DynamicDataSource extends AbstractRoutingDataSource  {
        @Override
        protected Object determineCurrentLookupKey() {
            // 直接从ThreadLocal中获取拿到的数据源
            log.info("DynamicDataSource.determineCurrentLookupKey curr data source :" + DynamicDataSourceHolder.getDataSource());
            return DynamicDataSourceHolder.getDataSource();
        }
    }

4，自定义注解，标识数据源

package com.gupao.springboot.datasourceaop.annotations;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * @author pj_zhang
     * @create 2018-12-28 12:13
     **/
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.METHOD)
    public @interface TargetDataSource {
        // 数据源名称
        String value() default "";
    }

5，AOP前后置拦截解析类，对Mapper方法代用进行拦截

package com.gupao.springboot.datasourceaop.aspect;
    
    import com.gupao.springboot.datasourceaop.annotations.TargetDataSource;
    import com.gupao.springboot.datasourceaop.context.DynamicDataSourceHolder;
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.JoinPoint;
    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.core.annotation.Order;
    import org.springframework.stereotype.Component;
    
    import java.lang.reflect.Method;
    
    /**
     * 多数据源配置, 拦截器配置
     * @author pj_zhang
     * @create 2018-12-28 12:15
     **/
    @Aspect
    @Component
    @Slf4j
    // 优先级, 1标识最先执行
    @Order(1)
    public class DataSourceAspect {
    
        private final String DEFAULT_DATA_SOURCE = "firstAopDataSource";
    
        @Pointcut("execution(public * com.gupao.springboot.*.mapper.*.*(..))")
        public void dataSourcePoint() {}
    
        @Before("dataSourcePoint()")
        public void before(JoinPoint joinPoint) {
            Object target = joinPoint.getTarget();
            MethodSignature methodSignature = (MethodSignature)joinPoint.getSignature();
            // 执行方法名
            String methodName = methodSignature.getName();
            // 方法参数
            Class[] parameterTypes = methodSignature.getParameterTypes();
            try {
                // 获取方法, 直接getClass获取对象可能为代理对象
                Method method = target.getClass().getInterfaces()[0].getMethod(methodName, parameterTypes);
                // 添加默认数据源
                String dataSource = DEFAULT_DATA_SOURCE;
                if (null != method && method.isAnnotationPresent(TargetDataSource.class)) {
                    TargetDataSource targetDataSource = method.getAnnotation(TargetDataSource.class);
                    dataSource = targetDataSource.value();
                }
                // 此处添加线程对应的数据源到上下文
                // 在AbstractRoutingDataSource子类中拿到数据源, 加载后进行配置
                DynamicDataSourceHolder.setDataSource(dataSource);
                log.info("generate data source : " + dataSource);
            } catch (Exception e) {
                log.info("error", e);
            }
    
        }
    
        /**
         * 清除数据源, 方法执行完成后, 清除数据源
         */
        @After("dataSourcePoint()")
        public void after(JoinPoint joinPoint) {
            DynamicDataSourceHolder.clear();
        }
    
    }

6，Controller层

package com.gupao.springboot.datasourceaop.controller;
    
    import com.gupao.springboot.datasourceaop.service.IDataSourceAOPService;
    import com.gupao.springboot.entitys.UserVO;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /**
     * @author pj_zhang
     * @create 2018-12-28 10:42
     **/
    @Slf4j
    @RestController
    public class DataSourceAOPController {
    
        @Autowired
        private IDataSourceAOPService dataSourceAOPService;
    
        @RequestMapping("/firstAOPInsert")
        public Integer firstInsert(String userName, String password) {
            UserVO userVO = new UserVO();
            userVO.setUserName(userName);
            userVO.setPassword(password);
            return dataSourceAOPService.insertFirstUserLst(userVO);
        }
    
        @RequestMapping("/secondAOPInsert")
        public Integer secondInsert(String userName, String password) {
            UserVO userVO = new UserVO();
            userVO.setUserName(userName);
            userVO.setPassword(password);
            return dataSourceAOPService.insertSecondUserLst(userVO);
        }
    
        @RequestMapping("/firstAOPSelect")
        public List<UserVO> findFirstData() {
            return dataSourceAOPService.findFirstData();
        }
    
        @RequestMapping("/secondAOPSelect")
        public List<UserVO> findSecondData() {
            return dataSourceAOPService.findSecondData();
        }
    
        @RequestMapping("/insertFirstAndSecond")
        public Integer insertFirstAndSecond(String userName, String password) {
            UserVO userVO = new UserVO();
            userVO.setUserName(userName);
            userVO.setPassword(password);
            return dataSourceAOPService.insertFirstAndSecond(userVO);
        }
    
    }

7，Service层

\* Service接口

package com.gupao.springboot.datasourceaop.service;
    
    import com.gupao.springboot.entitys.UserVO;
    
    import java.util.List;
    
    /**
     * @author pj_zhang
     * @create 2018-12-28 10:50
     **/
    public interface IDataSourceAOPService {
    
        /**
         * 新增用户到FIRST
         * @param userVO
         * @return
         */
        Integer insertFirstUserLst(UserVO userVO);
    
        /**
         * 新增用户到SECEND
         * @param userVO
         * @return
         */
        Integer insertSecondUserLst(UserVO userVO);
    
        /**
         * 查找数据FIRST
         * @return
         */
        List<UserVO> findFirstData();
    
        /**
         * 查找数据SECOND
         * @return
         */
        List<UserVO> findSecondData();
    
        /**
         * 新增数据到FIRST_SECOND
         * @param userVO
         * @return
         */
        Integer insertFirstAndSecond(UserVO userVO);
    }

\* Service.Impl

package com.gupao.springboot.datasourceaop.service.impl;
    
    import com.gupao.springboot.datasourceaop.mapper.DataSourceMapper;
    import com.gupao.springboot.datasourceaop.service.IDataSourceAOPService;
    import com.gupao.springboot.entitys.UserVO;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * @author pj_zhang
     * @create 2018-12-28 10:43
     **/
    @Slf4j
    @Service
    public class DataSourceAOPService implements IDataSourceAOPService {
    
        @Autowired
        private DataSourceMapper dataSourceMapper;
    
        @Override
        public Integer insertFirstUserLst(UserVO userVO) {
            return dataSourceMapper.insertFirstUser(userVO);
        }
    
        @Override
        public Integer insertSecondUserLst(UserVO userVO) {
            return dataSourceMapper.insertSecondUser(userVO);
        }
    
        @Override
        public List<UserVO> findFirstData() {
            return dataSourceMapper.findFirstData();
        }
    
        @Override
        public List<UserVO> findSecondData() {
            return dataSourceMapper.findSecondData();
        }
    
        @Override
        public Integer insertFirstAndSecond(UserVO userVO) {
            dataSourceMapper.insertFirstUser(userVO);
            dataSourceMapper.insertSecondUser(userVO);
            return 1;
        }
    
    }

8，Mapper层

package com.gupao.springboot.datasourceaop.mapper;
    
    import com.gupao.springboot.datasourceaop.annotations.TargetDataSource;
    import com.gupao.springboot.entitys.UserVO;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    /**
     * 动态加载数据源
     * value值为DataSource源数据配置map映射的key值
     * @author pj_zhang
     * @create 2018-12-28 10:43
     **/
    @Mapper
    public interface DataSourceMapper {
    
        /**
         * 注解为FIRST数据库
         * @param userVO
         * @return
         */
        @TargetDataSource("firstAopDataSource")
        Integer insertFirstUser(UserVO userVO);
    
        /**
         * 注解为SECOND数据库
         * @param userVO
         * @return
         */
        @TargetDataSource("secondAopDataSource")
        Integer insertSecondUser(UserVO userVO);
    
        @TargetDataSource("firstAopDataSource")
        List<UserVO> findFirstData();
    
        @TargetDataSource("secondAopDataSource")
        List<UserVO> findSecondData();
    }

<?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.gupao.springboot.datasourceaop.mapper.DataSourceMapper">
        <insert id="insertFirstUser" parameterType="com.gupao.springboot.entitys.UserVO">
            INSERT INTO
              USER_T(USER, PASSWORD)
            VALUES (
              #{userName, jdbcType=VARCHAR},
              #{password, jdbcType=VARCHAR}
            )
        </insert>
    
        <insert id="insertSecondUser" parameterType="com.gupao.springboot.entitys.UserVO">
            INSERT INTO
              USER_T(USER, PASSWORD)
            VALUES (
              #{userName, jdbcType=VARCHAR},
              #{password, jdbcType=VARCHAR}
            )
        </insert>
    
        <select id="findFirstData" resultType="com.gupao.springboot.entitys.UserVO">
            SELECT
              user as userName,
              password as password
            from
            USER_T
        </select>
    
        <select id="findSecondData" resultType="com.gupao.springboot.entitys.UserVO">
            SELECT
            user as userName,
            password as password
            from
            USER_T
        </select>
    
    </mapper>

9，启动入库

package com.gupao.springboot;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    // 去除SpringBoot自动配置, 采用自定义数据源配置
    @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
    public class GupaoSpringbootApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(GupaoSpringbootApplication.class, args);
        }
    
    }

10，测试

\* FIRST入库数据

![20181228222052246.png][]

\* 注意两条日志打印顺序；先通过AOP变更了数据源，再通过实现类进行数据源加载，后续同！

![20181228222119449.png][]

\* SECOND入库数据

![20181228222234635.png][]

\* FIRST+SECOND入库数据

![20181228222339861.png][]

\* FIRST查询数据

![2018122822235776.png][]

\* SECOND查询数据![20181228222407248.png][]

11，数据库数据

\* FIRST

![20181228222441413.png][]

\* SECOND

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70][]

四，存在问题

1，踩过的一个坑，数据库连接错误，非正常错误

error ：java.sql.SQLException: The server time zone value 'ÖÐ¹ú±ê×¼Ê±¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone val

这个问题可能是数据库时区问题导致的，具体解决措施在jdbc-url后面加上参数，如下

spring.datasource.first.jdbc-url=jdbc:mysql://localhost:3306/first?characterEncoding=utf-8&serverTimezone=GMT%2B8

2，AOP配置引起的数据源加载问题

\* 基于AbstractRoutingDataSource实现类配置的数据源动态加载，依赖于程序的执行顺序。先通过Mapper方法调用变更DynamicDataSourceHolder上下文持有的DataSource再进行数据源加载时，此时配置没有任何问题；

\* 但是单纯添加了声明式事务后，因为事务执行流程影响，AbstractRoutingDataSource实现类会先于Mapper方法执行，此时DynamicDataSourceHolder上下文并不持有数据源，则数据源为DataSourceConfig中配置的默认数据源；

\* 鉴于上面存在的问题，有的配置方式不基于AbstractRoutingDataSource实现类去动态加载数据源，而是在AOP前置拦截方法中，拦截到注解的数据源后，直接从Spring容器中获取DataSource并进行更改，直接跳过执行顺序可能存在的影响，该配置后续会继续完善在后面！

[20181228213400121.png]: /images/20220401/ce758c5f97764538a892a17205766000.png
[20181228222052246.png]: /images/20220401/99fade1e0c3a41679906c55ba42cdac9.png
[20181228222119449.png]: /images/20220401/52bb27c1135241e4a78762e229f2284f.png
[20181228222234635.png]: /images/20220401/9fa7cb02195342a5834dcc292fc82af7.png
[20181228222339861.png]: /images/20220401/3f9d644b6c2b4d2da1aedd12664d14db.png
[2018122822235776.png]: /images/20220401/ce6c6dc728e148efbcdfd1b6da89d337.png
[20181228222407248.png]: /images/20220401/8cdad44582234b7b8d8977b20769f9f6.png
[20181228222441413.png]: /images/20220401/3d29a3c414614841a05ff73a396a9bb7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70]: /images/20220401/5c7ca007ebe942509156b2a998da047f.png