如何一个项目配置多个数据库-手赚鸭

我们在项目中经常会遇到配置多个数据的情况，之前我有写过通过配置mapper.xml的不同地址来实现同一个项目连接到多个数据库。如果用这种方式可以参考，下面一片文章。

https://blog.csdn.net/fajing_feiyue/article/details/100604300

这篇文档与上面一种方式相比，是通过注解来实现和切面来配置实现数据库，这种方式需要能够更深刻来理解mybatis与spring的结合，这里将这种方式展示出来供大家参考。

1、引入maven依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.0.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.17</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
            <version>2.0.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>1.5.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

2、配置application.properties

server.port=8080


default.datasource.type=com.alibaba.druid.pool.DruidDataSource
default.datasource.url=jdbc:mysql://127.0.0.1:3306/pingyougou?serverTimezone=UTC
default.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
default.datasource.username=root
default.datasource.password=123

custom.datasource.names=custom1
custom.datasource.custom1.type=com.alibaba.druid.pool.DruidDataSource
custom.datasource.custom1.url=jdbc:mysql://127.0.0.1:3306/pinyougoudb?serverTimezone=UTC
custom.datasource.custom1.driver-class-name=com.mysql.cj.jdbc.Driver
custom.datasource.custom1.username=root
custom.datasource.custom1.password=123


#配置初始化大小，最小，最大
common.datasource.initialSize=10
common.datasource.minIdle=10
common.datasource.maxActive=50
#配置连接等待超时时间
common.datasource.maxWait=15000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
common.datasource.timeBetweenEvictionRunsMillis=60000
#配置一个连接在池中最小生存时间，一个小时
common.datasource.minEvictableIdleTimeMillis=3600000
#连接空闲时是否进行有效性验证（即是否还和数据库连通的）
common.datasource.testWhileIdle=true
#取得连接时是否进行有效性验证（即是否还和数据库连通的）【防止取到连接不可用】
common.datasource.testOnBorrow=true
#回连接时是否进行有效性验证（即是否还和数据库连通的）
common.datasource.testOnReturn=false
#配置提交方式，默认是true，可以不用配置
common.datasource.defultAutoCommint=true


#logging.config=classpath:config/log4j2.xml

#配置.xml文件路径
#mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.yin.dynamic_datasource.dto

3、通过实现ImportBeanDefinitionRegistrar动态注册DataSource，实现EnvironmentAware 来读取application.properties里面的内容。由自己定义DynamicDataSourceContextHolder来保存目前正在数据库的信息
和每个线程所使用的数据库。并且设置由自己定义的DynamicDataSource来切换数据库。

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 = "default.datasource";
    private static String DB_CUSTOM_VALUE = "custom.datasource";
    private static String DB_COMMON_VALUE = "common.datasource";


    public DynamicDataSourceRegister() {
    }



    @Override
    public void setEnvironment(Environment env) {
        initDefaultDataSource(env);
        initCustomDataSources(env);

    }

    private void initCustomDataSources(Environment env) {
        String dsPrefixs = env.getProperty(DB_CUSTOM_VALUE + "." + DB_NAME);
        String[] var3 = dsPrefixs.split(",");
        int var4 = var3.length;

        for(int var5 = 0; var5 < var4; ++var5) {
            String dsPrefix = var3[var5];
            Map<String, Object> dsMap = new HashMap();
            dsMap.put("type", env.getProperty(DB_CUSTOM_VALUE + "." + dsPrefix + ".type"));
            dsMap.put("driver-class-name", env.getProperty(DB_CUSTOM_VALUE + "." + dsPrefix + ".driver-class-name"));
            dsMap.put("url", env.getProperty(DB_CUSTOM_VALUE + "." + dsPrefix + ".url"));
            dsMap.put("username", env.getProperty(DB_CUSTOM_VALUE + "." + dsPrefix + ".username"));
            dsMap.put("password", env.getProperty(DB_CUSTOM_VALUE + "." + dsPrefix + ".password"));
            DataSource ds = this.buildDataSource(dsMap,env);
            this.customDataSources.put(dsPrefix, ds);
        }
    }

    private void initDefaultDataSource(Environment env) {

        HashMap<String, Object> dsMap = new HashMap<>();
        dsMap.put("type", env.getProperty(DB_DEFAULT_VALUE + ".type"));
        dsMap.put("driver-class-name", env.getProperty(DB_DEFAULT_VALUE + ".driver-class-name"));
        dsMap.put("url", env.getProperty(DB_DEFAULT_VALUE + ".url"));
        dsMap.put("username", env.getProperty(DB_DEFAULT_VALUE + ".username"));
        dsMap.put("password", env.getProperty(DB_DEFAULT_VALUE + ".password"));
        //创建数据源
        defaultDataSource = buildDataSource(dsMap, env);
    }

    private DataSource buildDataSource(Map<String, Object> dsMap,Environment env) {
        Object type = dsMap.get("type");
        if (type == null) {
            type = DATASOURCE_TYPE_DEFAULT;
        }
        Class<? extends DataSource> dataSourceType;
        try {
            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();

            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            dataSource.setDriverClassName(driverClassName);

            dataSource.setInitialSize(Integer.valueOf(env.getProperty(DB_COMMON_VALUE + ".initialSize")));
            dataSource.setMinIdle(Integer.valueOf(env.getProperty(DB_COMMON_VALUE + ".minIdle")));
            dataSource.setMaxActive(Integer.valueOf(env.getProperty(DB_COMMON_VALUE + ".maxActive")));
            dataSource.setMaxWait(Integer.valueOf(env.getProperty(DB_COMMON_VALUE + ".maxWait")));
            dataSource.setTimeBetweenEvictionRunsMillis(Integer.valueOf(env.getProperty(DB_COMMON_VALUE + ".timeBetweenEvictionRunsMillis")));
            dataSource.setMinEvictableIdleTimeMillis(Long.valueOf(env.getProperty(DB_COMMON_VALUE + ".minEvictableIdleTimeMillis")));
            dataSource.setTestWhileIdle(Boolean.valueOf(env.getProperty(DB_COMMON_VALUE + ".testWhileIdle")));
            dataSource.setTestOnBorrow(Boolean.valueOf(env.getProperty(DB_COMMON_VALUE + ".testOnBorrow")));
            dataSource.setTestOnReturn(Boolean.valueOf(env.getProperty(DB_COMMON_VALUE + ".testOnReturn")));
            dataSource.setDefaultAutoCommit(Boolean.valueOf(env.getProperty(DB_COMMON_VALUE + ".defultAutoCommint")));
            dataSource.setValidationQuery("SELECT 1");
            return dataSource;


        } catch (ClassNotFoundException ex) {
            logger.error(ex.getMessage(), ex);
        }
        return null;
    }

    @Override
    public void registerBeanDefinitions(AnnotationMetadata annotationMetadata, BeanDefinitionRegistry registry) {
        Map<Object, Object> targetDataSources = new HashMap();
        targetDataSources.put("dataSource", this.defaultDataSource);
        DynamicDataSourceContextHolder.dataSourceIds.add("dataSource");
        targetDataSources.putAll(this.customDataSources);
        Iterator var4 = this.customDataSources.keySet().iterator();

        while(var4.hasNext()) {
            String key = (String)var4.next();
            DynamicDataSourceContextHolder.dataSourceIds.add(key);
        }

        GenericBeanDefinition beanDefinition = new GenericBeanDefinition();
        beanDefinition.setBeanClass(DynamicDataSource.class);
        beanDefinition.setSynthetic(true);
        MutablePropertyValues mpv = beanDefinition.getPropertyValues();
        mpv.addPropertyValue("defaultTargetDataSource", this.defaultDataSource);
        mpv.addPropertyValue("targetDataSources", targetDataSources);
        registry.registerBeanDefinition("dataSource", beanDefinition);
        logger.info("Dynamic DataSource Registry");
    }
}

4、将DynamicDataSourceRegister 注册类交由启动类加载

@SpringBootApplication
@Import(DynamicDataSourceRegister.class)
public class DynamicDatasourceApplication {

    public static void main(String[] args) {
        SpringApplication.run(DynamicDatasourceApplication.class, args);
    }

}

5、定义DynamicDataSourceContextHolder类来保存所有用到数据库和每个线程所使用到数据库

public class DynamicDataSourceContextHolder {
    /**
     * 保存每个线程对应数据信息
     */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
    /**
     * 保存所有数据库的信息
     */
    public static List<String> dataSourceIds = new CopyOnWriteArrayList<>();

    public static void setContextHolder(String dataSource){
        contextHolder.set(dataSource);
    }


    public static String getContextHolder(){
        return contextHolder.get();
    }

    public static  boolean isContainsDatasource(String dataSourceId){
        return dataSourceIds.contains(dataSourceId);
    }
//这里在使用完数据库进行.remove()防止内存泄漏从而导致内存溢出
    public static void clearDataSourceType(){
        contextHolder.remove();
    }


}

6、定义DynamicDataSource来切换数据库

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getContextHolder();
    }
}

7、定义注解TargetDataSource 来实现，对数据库指定

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    String name();
}

8、定义切面对使用到这个注解的方法前后对用DynamicDataSourceContextHolder 保存和移除每个线程所使用到的数据库信息。之所以要用完移除主要是防止内存泄漏从而导致的内存溢出。

@Aspect
//保证该aop在@Transaction之前执行
@Order(-1)
@Component
public class DynamicDataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    public DynamicDataSourceAspect() {
    }

    @Before("@annotation(ds)")
    public void changeDataSource(JoinPoint point, TargetDataSource ds) throws Throwable {
        String dsId = ds.name();
        if (!DynamicDataSourceContextHolder.isContainsDatasource(dsId)) {
            logger.error("数据源[{}]不存在，使用默认数据源 > {}", ds.name(), point.getSignature());
        } else {
            logger.debug("Use DataSource : {} > {}", dsId, point.getSignature());
            DynamicDataSourceContextHolder.setContextHolder(dsId);
        }

    }

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

9、测试（以下为测试内容）

package com.yin.dynamic_datasource.controller;

import com.yin.dynamic_datasource.dto.AreaDto;
import com.yin.dynamic_datasource.service.DynamicService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.Mapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author yin
 * @Date 2020/1/28 10:35
 * @Method
 */
@RestController
@RequestMapping("/dynamic/test")

public class DynamicController {
    @Autowired
    private DynamicService dynamicService;

    @GetMapping("/insert/default")
    public AreaDto insertDefault(){
        AreaDto areaDto = new AreaDto();
        areaDto.setArea("defaultArea1");
        areaDto.setAreaId("defaultId1");
        areaDto.setCityid("defaultCityId1");
        dynamicService.insertDefaultArea(areaDto);
        return areaDto;
    }

    @GetMapping("/insert/custom1")
    public AreaDto insertCustom1(){
        AreaDto areaDto = new AreaDto();
        areaDto.setArea("custom1Area1");
        areaDto.setAreaId("custom1Id1");
        areaDto.setCityid("custom1CityId1");
        dynamicService.insertCustom1Area(areaDto);
        return areaDto;
    }

}

service

public interface DynamicService {
    void insertArea(AreaDto areaDto);

    void insertDefaultArea(AreaDto areaDto);

    void insertCustom1Area(AreaDto areaDto);
}

serviceImpl

@Service
public class DynamicServiceImpl implements DynamicService {
    @Autowired
    private DynamicMapper dynamicMapper;

    @Override
    public void insertArea(AreaDto areaDto) {
        dynamicMapper.insertArea(areaDto);
    }

    @Override
    public void insertDefaultArea(AreaDto areaDto) {
        dynamicMapper.insertArea(areaDto);
    }

    @Override
    @TargetDataSource(name = "custom1")
    public void insertCustom1Area(AreaDto areaDto) {
        dynamicMapper.insertArea(areaDto);
    }
}

mapper.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.yin.dynamic_datasource.dao.DynamicMapper">

    <!-- 插入记录-->
    <insert id="insertArea" useGeneratedKeys="true" keyProperty="id"
            parameterType="com.yin.dynamic_datasource.dto.AreaDto">

		  INSERT INTO tb_areas(areaid, area, cityid) values (#{areaId}, #{area}, #{cityid})

    </insert>


</mapper>

声明：本站所有文章，如无特殊说明或标注，均为网络收集发布。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

技术文章