spring boot 实现注解+自定义配置多数据库

配置多数据库 注解+AOP

你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

maven依赖

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- 整合freemarker -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

        <!-- log4j -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <!-- aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
         <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>      

多数据源配置

通过AOP方式，直接反射获取自定义注解，解析注解值进行数据源动态添加，实现多数据源配置。

• 动态多数据源配置
  – DataSourceConfig

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

    – DynamicDataSourceHolder

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

    – DynamicDataSource

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

    – TargetDataSource

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

    – DataSourceAspect

  • 三层代码架构处理

    – SpecificationController，SpecificationServiceImpl，SpecificationOptionDao合理的创建标题，有助于目录的生成

代码实现

1. application.properties

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

spring.datasource.db01.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db01.jdbc-url=jdbc:mysql://127.0.0.1:3306/test_db?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=GMT
spring.datasource.db01.username=
spring.datasource.db01.password=

spring.datasource.db02.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db02.jdbc-url=jdbc:mysql://127.0.0.1:3306/test_db1?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=GMT
spring.datasource.db02.username=
spring.datasource.db02.password=

3. 动态多数据源配置

package com.dexin.sellergoods.datasource;

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;


@Configuration
public class DataSourceConfig {
    /**
     * First数据源
     * @return
     */
    @Bean(name = "firstAopDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db01")
    public DataSource firstDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * Second数据源
     * @return
     */
    @Bean(name = "secondAopDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db02")
    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());
    }
}

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

package com.dexin.sellergoods.datasource;
/**
 * 线程持有数据源上下文
 */
public class DynamicDataSourceHolder {
    private static final ThreadLocal<String> THREAD_LOCAL = new ThreadLocal<String>();

    /**
     * 设置线程持有的DataSource, 底层以map形式呈现, key为当前线程
     */
    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();
    }
}

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

package com.dexin.sellergoods.datasource;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * spring为我们提供了AbstractRoutingDataSource，即带路由的数据源。
 * 继承后我们需要实现它的determineCurrentLookupKey()，
 * 该方法用于自定义实际数据源名称的路由选择方法，
 * 由于我们将信息保存到了ThreadLocal中，所以只需要从中拿出来即可。
 */
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        // 直接从ThreadLocal中获取拿到的数据源
        log.info("DynamicDataSource.determineCurrentLookupKey curr data source :" + DynamicDataSourceHolder.getDataSource());
        return DynamicDataSourceHolder.getDataSource();
    }
}

9. 自定义注解，标识数据源

package com.dexin.sellergoods.datasource;

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 TargetDataSource {
    // 数据源名称
    String value() default "";
}

10.AOP前后置拦截解析类，对Mapper（dao）方法代用进行拦截

package com.dexin.sellergoods.datasource;

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;


/**
 * 多数据源配置, 拦截器配置
 */
@Aspect
@Component
@Slf4j
// 优先级 1表示最先执行
@Order(1)
public class DataSourceAspect {
    private final String defultDataSource = "firstAopDataSource";
    // 拦截 路径配置  * [公共] com.dynamic.datasource.dao [包路径] .* [所有类] .* [所有方法]（…）[所有参数]
    @Pointcut("execution(public * com.dexin.pinyougou.sellergoods.dao.*.*(..))")
    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 = defultDataSource;
            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();
    }

}

11. Controller层

package com.dexin.sellergoods.controller;

import com.dexin.sellergoods.entity.Specification;
import com.dexin.sellergoods.service.ISpecificationService;
import com.dexin.sellergoods.vo.ResultVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/specify")
public class SpecificationController {

    @Autowired
    private ISpecificationService specificationService;
    
    /**
     * 获取规格数据
     */
    @GetMapping("/findAllSpecification")
    public ResultVO findAllSpecification(){
        try {
            List<Specification> specification = specificationService.findAllSpec();
            return ResultVO.success(specification);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultVO.error("find specification faill ");
        }
    }
    /**
     * 获取规格数据
     * id 规格id
     */
    @GetMapping("/findOneSpecification")
    public ResultVO addSpecificaiton(Long id){
        try {
            Specification specification = specificationService.findOne(id);
            return ResultVO.success(specification);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultVO.error("find specification faill ");
        }
    }
}

13. Service层

package com.dexin.sellergoods.service;

import com.dexin.sellergoods.entity.Specification;

import java.util.List;

public interface ISpecificationService {

    List<Specification> findAllSpec();

    Specification findOne(Long id);
}

15. Service.Impl

package com.dexin.sellergoods.service.impl;

import com.dexin.sellergoods.dao.SpecificationDao;
import com.dexin.sellergoods.entity.Specification;
import com.dexin.sellergoods.entity.TbSpecification;
import com.dexin.sellergoods.service.ISpecificationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class SpecificationServiceImpl implements ISpecificationService {

    @Autowired
    private SpecificationDao specificationDao;

    @Override
    public List<Specification> findAllSpec() {
        return specificationDao.findAll();
    }

    @Override
    public Specification findOne(Long id) {
        Specification specification = new Specification();
        TbSpecification tbSpecification = specificationDao.findOneSpec(id);
        specification.setSpecification(tbSpecification);
        return specification;
    }


}

17. Mapper（dao）层

package com.dexin.sellergoods.dao;

import com.dexin.pinyougou.sellergoods.datasource.TargetDataSource;
import com.dexin.pinyougou.sellergoods.entity.Specification;
import com.dexin.pinyougou.sellergoods.entity.TbSpecification;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SpecificationDao {


    TbSpecification findOneSpec(Long id);

    @TargetDataSource("secondAopDataSource")
    List<Specification> findAll();
}

19. mybatis xml配置

<?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.dexin.sellergoods.dao.SpecificationDao" >
    <resultMap id="TbSpecificationMapper" type="com.dexin.sellergoods.entity.TbSpecification">
        <result property="id" column="id"/>
        <result property="specName" column="spec_name"/>
    </resultMap>


    <select id="findOneSpec" resultMap="TbSpecificationMapper">
        select * from tb_specification
            where id = #{id}
    </select>

    <select id="findAll" resultMap="TbSpecificationMapper">
        select * from tb_specification
    </select>

</mapper>

21. 启动类需排查DataSourceAutoConfiguration.class

package com.dexin.sellergoods;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@MapperScan("com.dexin.sellergoods.dao")
// 去除SpringBoot自动配置, 采用自定义数据源配置
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class SellerGoodsApplication {
    public static void main(String[] args) {
        SpringApplication.run(SellerGoodsApplication.class, args);
    }
}

• 测试结果
  
  

存在问题

• java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
  

• 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.db01.jdbc-url=jdbc:mysql://localhost:3306/first?characterEncoding=utf-8&serverTimezone=GMT%2B8