SpringBoot多数据源配置及测试

废话少说，直接上代码：（查阅资料，部分人总是喜欢把localhost:3306,username=root、password=root等信息打马赛克，很困惑啊，这些信息很敏感吗？怕别人访问你的库？打马赛克有毛用？谁能帮我解惑？谢谢！）

server.port=9000
custom.datasource.defaultname=default
custom.datasource.names=firstdb,seconddb

# 默认数据源
custom.datasource.driver-class-name=com.mysql.jdbc.Driver
custom.datasource.url=jdbc:mysql://localhost:3306/authmanage?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=UTC
custom.datasource.username=root
custom.datasource.password=root

# 更多数据源
custom.datasource.firstdb.driver-class-name=com.mysql.jdbc.Driver
custom.datasource.firstdb.url=jdbc:mysql://localhost:3306/authserver?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=UTC
custom.datasource.firstdb.username=root
custom.datasource.firstdb.password=root

custom.datasource.seconddb.driver-class-name=com.mysql.jdbc.Driver
custom.datasource.seconddb.url=jdbc:mysql://localhost:3306/blogs?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=UTC
custom.datasource.seconddb.username=root
custom.datasource.seconddb.password=root


custom.datasource.filters=stat
custom.datasource.maxActive=100
custom.datasource.initialSize=1
custom.datasource.minIdle=1
custom.datasource.timeBetweenEvictionRunsMillis=60000
custom.datasource.minEvictableIdleTimeMillis=300000
custom.datasource.validationQuery=select 'x'
custom.datasource.testWhileIdle=true
custom.datasource.testOnBorrow=false
custom.datasource.testOnReturn=false
custom.datasource.poolPreparedStatements=true
custom.datasource.maxOpenPreparedStatements=100

mybatis.type-aliases-package=com.item.*
mybatis.mapper-locations=classpath:mapper/**/*.xml

 

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源切换
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

package com.item.common.dynamicdb;

import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * 利用AOP切面实现数据源的动态切换
 */

@Aspect
@Order(-1)
@Component
public class DynamicDataSourceAspect {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    @Before("@annotation(targetDataSource)")
    public void changeDataSource(JoinPoint joinpoint, TargetDataSource targetDataSource) {

        String dataSourceId = targetDataSource.name();
        if (!DynamicDataSourceContextHolder.containsDataSource(dataSourceId)) {
            logger.error("数据源[{}]不存在,使用默认数据源 > {}", targetDataSource.name(), joinpoint.getSignature());
        } else {
            logger.debug("Use DataSource : {} > {}", dataSourceId, joinpoint.getSignature());
            DynamicDataSourceContextHolder.setDataSourceType(dataSourceId);
        }
    }

    @After("@annotation(targetDataSource)")
    public void restoreDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        logger.debug("Revert DataSource : {} > {}", targetDataSource.name(), joinPoint.getSignature());
        DynamicDataSourceContextHolder.clearDataSourceType();
    }
}

package com.item.common.dynamicdb;

import java.util.ArrayList;
import java.util.List;

/**
 * 动态切换数据源
 */
public class DynamicDataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<>();
    public static List<String> dataSourceIds = new ArrayList<>();

    /**
     * 设置数据源类型
     *
     * @param dataSourceType
     */
    public static void setDataSourceType(String dataSourceType) {
        contextHolder.set(dataSourceType);
    }

    /**
     * 获取数据源类型
     *
     * @return
     */
    public static String getDataSourceType() {
        return contextHolder.get();
    }

    /**
     * 清理数据源类型
     */
    public static void clearDataSourceType() {
        contextHolder.remove();
    }

    /**
     * 判断指定的数据源是否存在
     *
     * @param dataSourceId
     * @return
     */
    public static boolean containsDataSource(String dataSourceId) {
        return dataSourceIds.contains(dataSourceId);
    }
}

package com.item.common.dynamicdb;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.MutablePropertyValues;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.GenericBeanDefinition;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.ImportBeanDefinitionRegistrar;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotationMetadata;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

public class DynamicDataSourceRegister implements ImportBeanDefinitionRegistrar, EnvironmentAware {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceRegister.class);

    // 如配置文件中未指定数据源类型，使用该默认值
    private static final Object DATASOURCE_TYPE_DEFAULT = "com.alibaba.druid.pool.DruidDataSource";

    // 数据源
    private DataSource defaultDataSource;
    private Map<String, DataSource> customDataSources = new HashMap<>();

    private static String DB_NAME = "names";
    private static String DB_DEFAULT_VALUE = "custom.datasource";
    @Value("${custom.datasource.defaultname}")
    private String defaultDbname;

    @Override
    public void setEnvironment(Environment environment) {

        initDefaultDataSource(environment);
        initCustomDataSources(environment);
    }

    @Override
    public void registerBeanDefinitions(AnnotationMetadata importingClassMetadata, BeanDefinitionRegistry registry) {

        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        // 将主数据源添加到更多数据源中
        targetDataSources.put("dataSource", defaultDataSource);
        DynamicDataSourceContextHolder.dataSourceIds.add("dataSource");
        // 添加更多数据源
        targetDataSources.putAll(customDataSources);
        for (String key : customDataSources.keySet()) {
            DynamicDataSourceContextHolder.dataSourceIds.add(key);
        }
        // 创建DynamicDataSource
        GenericBeanDefinition beanDefinition = new GenericBeanDefinition();
        beanDefinition.setBeanClass(DynamicDataSource.class);
        beanDefinition.setSynthetic(true);
        MutablePropertyValues mpv = beanDefinition.getPropertyValues();
        mpv.addPropertyValue("defaultTargetDataSource", defaultDataSource);
        mpv.addPropertyValue("targetDataSources", targetDataSources);
        registry.registerBeanDefinition("dataSource", beanDefinition);

        logger.info("Dynamic DataSource Registry");
    }

    /**
     * 初始化默认数据源
     *
     * @param environment
     */
    private void initDefaultDataSource(Environment environment) {
        // 读取主数据源
        Map<String, Object> dsMap = new HashMap<>();
        dsMap.put("type", environment.getProperty(DB_DEFAULT_VALUE + "." + "type"));
        dsMap.put("driver-class-name", environment.getProperty(DB_DEFAULT_VALUE + "." + "driver-class-name"));
        dsMap.put("url", environment.getProperty(DB_DEFAULT_VALUE + "." + "url"));
        dsMap.put("username", environment.getProperty(DB_DEFAULT_VALUE + "." + "username"));
        dsMap.put("password", environment.getProperty(DB_DEFAULT_VALUE + "." + "password"));
        defaultDataSource = buildDataSource(dsMap);
    }

    // 初始化更多数据源
    private void initCustomDataSources(Environment env) {
        // 读取配置文件获取更多数据源，也可以通过defaultDataSource读取数据库获取更多数据源
        String dsPrefixs = env.getProperty(DB_DEFAULT_VALUE + "." + DB_NAME);
        for (String dsPrefix : dsPrefixs.split(",")) {// 多个数据源
            Map<String, Object> dsMap = new HashMap<>();

            dsMap.put("type", env.getProperty(DB_DEFAULT_VALUE + "." + dsPrefix + ".type"));
            dsMap.put("driver-class-name", env.getProperty(DB_DEFAULT_VALUE + "." + dsPrefix + ".driver-class-name"));
            dsMap.put("url", env.getProperty(DB_DEFAULT_VALUE + "." + dsPrefix + ".url"));
            dsMap.put("username", env.getProperty(DB_DEFAULT_VALUE + "." + dsPrefix + ".username"));
            dsMap.put("password", env.getProperty(DB_DEFAULT_VALUE + "." + dsPrefix + ".password"));

            DataSource ds = buildDataSource(dsMap);
            customDataSources.put(dsPrefix, ds);
        }
    }

    @SuppressWarnings("unchecked")
    public DataSource buildDataSource(Map<String, Object> dsMap) {
        try {
            Object type = dsMap.get("type");
            if (type == null) type = DATASOURCE_TYPE_DEFAULT;// 默认DataSource

            Class<? extends DataSource> dataSourceType;
            dataSourceType = (Class<? extends DataSource>) Class.forName((String) type);

            String driverClassName = dsMap.get("driver-class-name").toString();
            String url = dsMap.get("url").toString();
            String username = dsMap.get("username").toString();
            String password = dsMap.get("password").toString();

            DataSourceBuilder factory = DataSourceBuilder.create().driverClassName(driverClassName).url(url)
                    .username(username).password(password).type(dataSourceType);
            return factory.build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }
}

package com.item.common.dynamicdb;

import java.lang.annotation.*;

/**
 * 在方法上使用,用于指定使用哪个数据源
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {

    String name();
}

测试代码：

默认库某表的实体类：

package com.item.entity;

import com.item.common.entity.BaseEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 应用分类表
 * </p>
 *
 * @author liangsw
 * @since 2019-11-10
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class AppClassify extends BaseEntity {

    private static final long serialVersionUID = 1L;

    private Integer id;

    /**
     * 功能名称
     */
    private String name;

    /**
     * 图标地址
     */
    private String icon_url;

    /**
     * 描述
     */
    private String content;

    /**
     * 备注
     */
    private String remark;

    /**
     * 状态
     */
    private Integer status;
}

数据库A表的某实体类：

package com.item.entity;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import com.item.common.entity.BaseEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 用户表
 * </p>
 *
 * @author liangsw
 * @since 2019-10-22
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class SysUser extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /**
     * 用户编码
     */
    private String user_code;

    /**
     * 登录账号
     */
    private String login_code;

    /**
     * 用户昵称
     */
    private String user_name;

    /**
     * 登录密码
     */
    private String password;

    /**
     * 电子邮箱
     */
    private String email;

    /**
     * 手机号码
     */
    private String mobile;

    /**
     * 办公电话
     */
    private String phone;

    /**
     * 用户性别
     */
    private String sex;

    /**
     * 头像路径
     */
    private String avatar;

    /**
     * 个性签名
     */
    private String sign;

}

数据库B表的某实体类：

package com.item.entity;

import com.item.common.entity.BaseEntity;
import java.time.LocalDateTime;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 
 * </p>
 *
 * @author liangsw
 * @since 2019-10-22
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class User extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    private Integer id;

    /**
     * 昵称
     */
    private String username;

    /**
     * 密码
     */
    private String password;

    /**
     * 邮件
     */
    private String email;

    /**
     * 手机电话
     */
    private String mobile;

    /**
     * 积分
     */
    private Integer point;

    /**
     * 个性签名
     */
    private String sign;

    /**
     * 性别
     */
    private String gender;

    /**
     * 微信号
     */
    private String wechat;

    /**
     * vip等级
     */
    private Integer vip_level;

    /**
     * 生日
     */
    private LocalDateTime birthday;

    /**
     * 头像
     */
    private String avatar;

    /**
     * 内容数量
     */
    private Integer post_count;

    /**
     * 评论数量
     */
    private Integer comment_count;

    /**
     * 最后的登陆时间
     */
    private LocalDateTime lasted;

}

注意：三个实体类的**Mapper.xml、**Mapper(**DAO)、**Service及实现类相关的类此处省略！

默认库访问测试：

package com.item.controller;


import com.item.common.utils.R;
import com.item.entity.AppClassify;
import com.item.service.AppClassifyService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;
import com.item.common.controller.BaseController;

/**
 * <p>
 * 应用分类表 前端控制器
 * </p>
 *
 * @author liangsw
 * @since 2019-11-10
 */
@RestController
@RequestMapping("/appClassify")
public class AppClassifyController extends BaseController {

    @Autowired
    AppClassifyService appClassifyService;

    @RequestMapping(value = "/query/{id}")
    public R getAppClassifyInfo(@PathVariable Integer id) {

        AppClassify appClassify = this.appClassifyService.getById(id);
        return R.ok().put("appClassify", appClassify);
    }
}

数据库A测试：

package com.item.controller;


import com.item.common.dynamicdb.TargetDataSource;
import com.item.common.utils.R;
import com.item.entity.SysUser;
import com.item.service.SysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;
import com.item.common.controller.BaseController;

/**
 * <p>
 * 用户表 前端控制器
 * </p>
 *
 * @author liangsw
 * @since 2019-10-22
 */
@RestController
@RequestMapping("/sysUser")
public class SysUserController extends BaseController {

    @Autowired
    SysUserService sysUserService;

    @RequestMapping(value = "/query/{id}")
    @TargetDataSource(name = "firstdb")
    public R getAppClassifyInfo(@PathVariable Integer id) {

        SysUser sysUser = this.sysUserService.getById(id);
        return R.ok().put("sysUser", sysUser);
    }
}

数据库B实现：

package com.item.controller;

import com.item.common.dynamicdb.TargetDataSource;
import com.item.common.utils.R;
import com.item.entity.SysUser;
import com.item.entity.User;
import com.item.service.SysUserService;
import com.item.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.item.common.controller.BaseController;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author liangsw
 * @since 2019-10-22
 */
@RestController
@RequestMapping("/user")
public class UserController extends BaseController {

    @Autowired
    UserService userService;

    @RequestMapping(value = "/query/{id}")
    @TargetDataSource(name = "seconddb")
    public R getAppClassifyInfo(@PathVariable Integer id) {

        User user = this.userService.getById(id);
        return R.ok().put("user", user);
    }
}

代码中涉及的类R，其实就是一个Map，请不要迷惑！