Java学习之Springboot动态调用多数据源

最新推荐文章于 2023-06-05 18:13:39 发布

偶遇急雨洗心尘

最新推荐文章于 2023-06-05 18:13:39 发布

阅读量213

点赞数

文章标签： java 学习 spring boot 数据库开发语言

本文链接：https://blog.csdn.net/bernardGao94/article/details/129458347

版权

应用背景：

本文只是简单的实现动态数据源，重点在于熟悉代码方面的流程，至于要满足于复杂的业务场景需要进一步扩展。

本文中的动态数据源切换需要满足两种业务场景：

提前知道并指定使用哪个数据源；
不知道使用哪个动态数据源，由逻辑判断匹配数据源(但是需要标识，例如第一个数据源中的表名为‘people’，第二个数据源中的表名为‘people2’)。

场景一：提前知道使用哪个数据源(由自己指定数据源)

1、数据准备：创建三个数据库(每个库里的表名重复)，往表里插入几条数据用于测试

dynamic1：

CREATE DATABASE `dynamic1` ;

USE `dynamic1`;

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic1'),(2,'lisi',1,'dynamic1'),(3,'wangwu',1,'dynamic1');

dynamic2：

CREATE DATABASE `dynamic2` ;

USE `dynamic2`;

DROP TABLE IF EXISTS `people`;

CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic2'),(2,'lisi',1,'dynamic2'),(3,'wangwu',1,'dynamic2');

dynamic3：

CREATE DATABASE `dynamic3` ;

USE `dynamic3`;

DROP TABLE IF EXISTS `people`;

CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic3'),(2,'lisi',1,'dynamic3'),(3,'wangwu',1,'dynamic3');

2、搭建Springboot项目(搭建过程参考：https://www.cnblogs.com/Bernard94/p/14123880.html)

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.demo</groupId>
    <artifactId>dynamic</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>dynamic</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--Web请求-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--开发帮助工具-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <!--数据库链接-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <!--日志-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--启动-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--AOP-->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.6</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

db.yml

driverClassName1 = com.mysql.jdbc.Driver
url1 = jdbc:mysql://127.0.0.1:3306/dynamic1?useUnicode=true&characterEncoding=utf8
userName1 = root
password1 = root

driverClassName2 = com.mysql.jdbc.Driver
url2 = jdbc:mysql://127.0.0.1:3306/dynamic2?useUnicode=true&characterEncoding=utf8
userName2 = root
password2 = root

driverClassName3 = com.mysql.jdbc.Driver
url3 = jdbc:mysql://127.0.0.1:3306/dynamic3?useUnicode=true&characterEncoding=utf8
userName3 = root
password3 = root

application.yml

server:
  port: 10086
mybatis:
  type-aliases-package: com.demo.dynamic.bean
  mapper-locations: classpath:mapper/*Mapper.xml

people(bean对象，未用到，可省略)

package com.demo.dynamic.bean;

import lombok.Data;

/**
 * @Classname People
 * @Description TODO
 * @Date 2022/4/27 18:09
 * @Created by BG
 */
@Data
public class People {
    private String id;
    private String name;
    private String status;
    private String content;
}

controller

package com.demo.dynamic.controller;

import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @Classname PeopleController
 * @Description TODO
 * @Date 2022/4/27 17:59
 * @Created by BG
 */
@RestController
@RequestMapping("/data")
public class PeopleController {

    @Autowired
    public PeopleService peopleService;

    @RequestMapping("/people1")
    public String getPeopleMaster() {
        return peopleService.getPeopleMaster();
    }

    @RequestMapping("/people2")
    public String getPeopleSlaver1() {
        return peopleService.getPeopleSlaver1();
    }

    @RequestMapping("/people3")
    public String getPeopleSlaver2() {
        return peopleService.getPeopleSlaver2();
    }
}

service

package com.demo.dynamic.service;

/**
 * @Classname PeopleService
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
public interface PeopleService {
    String getPeopleMaster();

    String getPeopleSlaver1();

    String getPeopleSlaver2();
}

package com.demo.dynamic.service.impl;

import com.demo.dynamic.mapper.PeopleMapper;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * @Classname PeopleServiceImpl
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
@Service
public class PeopleServiceImpl implements PeopleService {

    @Autowired
    public PeopleMapper peopleMapper;

    @Override
    public String getPeopleMaster() {
        List<Map<String, Object>> peopleList = peopleMapper.getPeopleMaster();
        return peopleList.toString();
    }

    @Override
    public String getPeopleSlaver1() {
        List<Map<String, Object>> peopleList = peopleMapper.getPeopleSlaver1();
        return peopleList.toString();
    }

    @Override
    public String getPeopleSlaver2() {
        List<Map<String, Object>> peopleList = peopleMapper.getPeopleSlaver2();
        return peopleList.toString();
    }
}

mapper

package com.demo.dynamic.mapper;

import com.demo.dynamic.dynamic.DynamicDB;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

/**
 * @Classname PeopleMapper
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
@Mapper
public interface PeopleMapper {
    @DynamicDB(dataSource = "master")
    List<Map<String, Object>> getPeopleMaster();

    @DynamicDB(dataSource = "slave1")
    List<Map<String, Object>> getPeopleSlaver1();

    @DynamicDB(dataSource = "slave2")
    List<Map<String, Object>> getPeopleSlaver2();
}

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.demo.dynamic.mapper.PeopleMapper">

    <select id="getPeopleMaster" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            people t
    </select>

    <select id="getPeopleSlaver1" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            people t
    </select>

    <select id="getPeopleSlaver2" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            people t
    </select>

</mapper>

DatasourceConfig(先配置各个数据源的基础配置)

package com.demo.dynamic.dynamic;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * @Classname DatasourceConfig
 * @Description 数据源链接配置类
 * @Date 2022/4/28 9:56
 * @Created by BG
 */
@Configuration
@Slf4j
@Data//此注解可以省去setter和getter方法
public class DatasourceConfig {
    //master
    private String url1;
    private String userName1;
    private String password1;
    private String driverClassName1;
    //slaver-1
    private String url2;
    private String userName2;
    private String password2;
    private String driverClassName2;
    //slaver-2
    private String url3;
    private String userName3;
    private String password3;
    private String driverClassName3;

    public DatasourceConfig() {
        Properties properties = DatasourceConfig.getProperties("db.yml");
        initDb(properties);
    }

    public void initDb(Properties properties) {
        if (null == properties) {
            initDefaultDb();
        } else {
            url1 = properties.getProperty("url1");
            userName1 = properties.getProperty("userName1");
            password1 = properties.getProperty("password1");
            driverClassName1 = properties.getProperty("driverClassName1");
            url2 = properties.getProperty("url2");
            userName2 = properties.getProperty("userName2");
            password2 = properties.getProperty("password2");
            driverClassName2 = properties.getProperty("driverClassName2");
            url3 = properties.getProperty("url3");
            userName3 = properties.getProperty("userName3");
            password3 = properties.getProperty("password3");
            driverClassName3 = properties.getProperty("driverClassName3");
        }
    }

    public void initDefaultDb() {
        url1 = "jdbc:mysql://127.0.0.1:3306/dynamic1?useUnicode=true&characterEncoding=utf8";
        userName1 = "root";
        password1 = "root";
        driverClassName1 = "com.mysql.jdbc.Driver";
    }

    public static Properties getProperties(String fileName) {
        try {
            //先读取config目录的
            String path = "DBConfig" + File.separator;
            Properties properties = new Properties();
            InputStream in = new FileInputStream(new File(path + fileName));
            properties.load(in);
            return properties;
        } catch (IOException e) {
            log.info("dataSource_init_failed:" + e.getMessage());
            return null;
        }
    }
}

DynamicDataSourceConfig(整合多个数据源用于动态切换)

package com.demo.dynamic.dynamic;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

/**
 * @Classname DynamicDataSourceConfig
 * @Description 多个数据源配置类
 * @Date 2022/4/28 10:24
 * @Created by BG
 */
@Configuration
public class DynamicDataSourceConfig {

    @Autowired
    private DatasourceConfig datasourceConfig;

    @Bean
    public DataSource master() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(datasourceConfig.getUserName1());
        druidDataSource.setPassword(datasourceConfig.getPassword1());
        druidDataSource.setUrl(datasourceConfig.getUrl1());
        //其他参数配置 省略
        return druidDataSource;
    }

    @Bean
    public DataSource slave1() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(datasourceConfig.getUserName2());
        druidDataSource.setPassword(datasourceConfig.getPassword2());
        druidDataSource.setUrl(datasourceConfig.getUrl2());
        //其他参数配置 省略
        return druidDataSource;
    }

    @Bean
    public DataSource slave2() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(datasourceConfig.getUserName3());
        druidDataSource.setPassword(datasourceConfig.getPassword3());
        druidDataSource.setUrl(datasourceConfig.getUrl3());
        //其他参数配置 省略
        return druidDataSource;
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource master, DataSource slave1, DataSource slave2) {
        Map<Object, Object> map = new HashMap<>(4);
        map.put("master", master);
        map.put("slave1", slave1);
        map.put("slave2", slave2);
        return new DynamicDataSource(master, map);
    }
}

DynamicDataSource(代码上调用的数据源对象)

package com.demo.dynamic.dynamic;

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

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

/**
 * @Classname DynamicDataSource
 * @Description 动态数据源
 * @Date 2022/4/28 10:27
 * @Created by BG
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        //设置默认的数据源
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        //设置多数据源
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        Object dataSource = getDataSource();
        return dataSource;
    }

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

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

    public static void clearDataSource() {
        contextHolder.remove();
    }
}

DynamicDB(自定义动态数据源注解)

package com.demo.dynamic.dynamic;

import java.lang.annotation.*;

/**
 * @Classname DynamicDB
 * @Description 动态数据源的自定义注解
 * @Date 2022/4/28 10:31
 * @Created by BG
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DynamicDB {
    //默认主数据源
    String dataSource() default "master";
}

DynamicDBAspect

package com.demo.dynamic.dynamic;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.lang.reflect.Method;

/**
 * @Classname DynamicDBAspect
 * @Description 动态数据源注解切面
 * @Date 2022/4/28 10:38
 * @Created by BG
 */
@Aspect
@Component
@Slf4j
public class DynamicDBAspect {

    //事务提交或回滚
    @Autowired
    private DataSourceTransactionManager transactionManager;

    //切点
    @Pointcut("@annotation(com.demo.dynamic.dynamic.DynamicDB)")
    public void dataSourcePointCut() {
    }

    //环绕加强
    @Around(value = "dataSourcePointCut()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        Object point = null;
        //是否动态的标识
        boolean dynamicFlag = false;//当前数据源
        String currDataSource = DynamicDataSource.getDataSource();
        //反射获取到注解‘对象’
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        Method method = signature.getMethod();
        DynamicDB dynamicDB = method.getAnnotation(DynamicDB.class);
        if (dynamicDB == null) {
            //如果获取不到注解对象就设置成主数据源
            DynamicDataSource.setDataSource("master");
        } else {
            //获取注解上的'dataSource'属性
            dynamicFlag = true;
            DynamicDataSource.setDataSource(dynamicDB.dataSource());
        }
　　　　　
        if (!dynamicFlag) {
            point = joinPoint.proceed();
            return point;
        }
        //如果当前存在事务，启动一个新的事务
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        transactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
        //开启事务
        TransactionStatus ts = transactionManager.getTransaction(transactionDefinition);
        try {
            //让方法执行
            point = joinPoint.proceed();
            //提交事务
            transactionManager.commit(ts);
        } catch (Throwable throwable) {
            //异常时回滚
            transactionManager.rollback(ts);
            throw throwable;
        } finally {
            //如果事务没有处理，默认进行回滚
            if (!ts.isCompleted()) {
                transactionManager.rollback(ts);
            }
            //恢复当前数据源
            DynamicDataSource.setDataSource(currDataSource);
        }
        return point;
    }
}

页面测试路径

1、http://localhost:10086/data/people1

2、http://localhost:10086/data/people2

3、http://localhost:10086/data/people3

说明确实按需求分别查了三个库里的数据！

场景二：不知道用哪个数据源，从配置变为逻辑判断(需根据业务中查询不同的表动态匹配对应的数据源)

1、数据准备：准备两个数据库“dynamic4”和“dynamic5”，注意表名要不重复(dynamic4：people、dynamic5：people2)

CREATE DATABASE `dynamic4` ;

USE `dynamic4`;

DROP TABLE IF EXISTS `people`;

CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT  INTO `people`(`id`,`name`,`status`,`content`) VALUES (1,'zhangsan',1,'dynamic4'),(2,'lisi',1,'dynamic4'),(3,'wangwu',1,'dynamic4');

CREATE DATABASE `dynamic5` ;

USE `dynamic5`;

DROP TABLE IF EXISTS `people2`;

CREATE TABLE `people2` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT  INTO `people2`(`id`,`name`,`status`,`content`) VALUES (1,'zhangsan',1,'dynamic5'),(2,'lisi',1,'dynamic5'),(3,'wangwu',1,'dynamic5');

2、Springboot项目构建

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.demo</groupId>
    <artifactId>dynamic</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>dynamic</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--Web请求-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--开发帮助工具-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <!--数据库链接-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <!--日志-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--启动-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--AOP-->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.6</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

db.yml

driverClassName1 = com.mysql.jdbc.Driver
url1 = jdbc:mysql://127.0.0.1:3306/dynamic4?useUnicode=true&characterEncoding=utf8
userName1 = root
password1 = root

driverClassName2 = com.mysql.jdbc.Driver
url2 = jdbc:mysql://127.0.0.1:3306/dynamic5?useUnicode=true&characterEncoding=utf8
userName2 = root
password2 = root

application.yml

server:
  port: 10086
mybatis:
  type-aliases-package: com.demo.dynamic.bean
  mapper-locations: classpath:mapper/*Mapper.xml

--数据源配置相关类--

DatasourceConfig

package com.demo.dynamic.dynamic;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * @Classname DatasourceConfig
 * @Description 数据源链接配置类
 * @Date 2022/4/28 9:56
 * @Created by BG
 */
@Configuration
@Slf4j
@Data//此注解可以省去setter和getter方法
public class DatasourceConfig {
    //master
    private String url1;
    private String userName1;
    private String password1;
    private String driverClassName1;
    //slaver-1
    private String url2;
    private String userName2;
    private String password2;
    private String driverClassName2;

    public DatasourceConfig() {
        Properties properties = DatasourceConfig.getProperties("db.yml");
        initDb(properties);
    }

    public void initDb(Properties properties) {
        if (null == properties) {
            initDefaultDb();
        } else {
            url1 = properties.getProperty("url1");
            userName1 = properties.getProperty("userName1");
            password1 = properties.getProperty("password1");
            driverClassName1 = properties.getProperty("driverClassName1");
            url2 = properties.getProperty("url2");
            userName2 = properties.getProperty("userName2");
            password2 = properties.getProperty("password2");
            driverClassName2 = properties.getProperty("driverClassName2");
        }
    }

    public void initDefaultDb() {
        url1 = "jdbc:mysql://127.0.0.1:3306/dynamic4?useUnicode=true&characterEncoding=utf8";
        userName1 = "root";
        password1 = "root";
        driverClassName1 = "com.mysql.jdbc.Driver";
    }

    public static Properties getProperties(String fileName) {
        try {
            //先读取config目录的
            String path = "DBConfig" + File.separator;
            Properties properties = new Properties();
            InputStream in = new FileInputStream(new File(path + fileName));
            properties.load(in);
            return properties;
        } catch (IOException e) {
            log.info("dataSource_init_failed:" + e.getMessage());
            return null;
        }
    }
}

DynamicDataSource

package com.demo.dynamic.dynamic;

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

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

/**
 * @Classname DynamicDataSource
 * @Description 动态数据源
 * @Date 2022/4/28 10:27
 * @Created by BG
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        //设置默认的数据源
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        //设置多数据源
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        Object dataSource = getDataSource();
        return dataSource;
    }

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

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

    public static void clearDataSource() {
        contextHolder.remove();
    }
}

DynamicDataSourceConfig

package com.demo.dynamic.dynamic;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

/**
 * @Classname DynamicDataSourceConfig
 * @Description 多个数据源配置类
 * @Date 2022/4/28 10:24
 * @Created by BG
 */
@Configuration
public class DynamicDataSourceConfig {

    @Autowired
    private DatasourceConfig datasourceConfig;

    @Bean
    public DataSource master() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(datasourceConfig.getUserName1());
        druidDataSource.setPassword(datasourceConfig.getPassword1());
        druidDataSource.setUrl(datasourceConfig.getUrl1());
        //其他参数配置 省略
        return druidDataSource;
    }

    @Bean
    public DataSource slave() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(datasourceConfig.getUserName2());
        druidDataSource.setPassword(datasourceConfig.getPassword2());
        druidDataSource.setUrl(datasourceConfig.getUrl2());
        //其他参数配置 省略
        return druidDataSource;
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource master, DataSource slave) {
        Map<Object, Object> map = new HashMap<>(4);
        map.put("master", master);
        map.put("slave", slave);
        return new DynamicDataSource(master, map);
    }
}

DynamicDB

package com.demo.dynamic.dynamic;

import java.lang.annotation.*;

/**
 * @Classname DynamicDB
 * @Description 动态数据源的自定义注解
 * @Date 2022/4/28 10:31
 * @Created by BG
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DynamicDB {
}

DynamicDBAspect

package com.demo.dynamic.dynamic;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.lang.reflect.Method;

/**
 * @Classname DynamicDBAspect
 * @Description 动态数据源注解切面
 * @Date 2022/4/28 10:38
 * @Created by BG
 */
@Aspect
@Component
@Slf4j
public class DynamicDBAspect {

    //事务提交或回滚
    @Autowired
    private DataSourceTransactionManager transactionManager;

    //切点
    @Pointcut("@annotation(com.demo.dynamic.dynamic.DynamicDB)")
    public void dataSourcePointCut() {
    }

    //环绕加强
    @Around(value = "dataSourcePointCut()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        Object point = null;
        //是否动态的标识
        boolean dynamicFlag = false;
        //当前数据源
        String currDataSource = DynamicDataSource.getDataSource();
        //参数
        Object[] args = joinPoint.getArgs();
        //通过参数逻辑判断是否动态数据源
        for (Object arg : args) {
            //判断mapper中是sql还是参数对象
            if (arg instanceof String) {
                dynamicFlag = switchDataSource((String) arg, "sql");
                break;
            } else if (arg instanceof DynamicParam) {
                dynamicFlag = switchDataSource(((DynamicParam) arg).getSqlOrTableName(), "tableName");
                break;
            }
        }
        if (!dynamicFlag) {
            point = joinPoint.proceed();
            return point;
        }
        //如果当前存在事务，启动一个新的事务
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        transactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
        //开启事务
        TransactionStatus ts = transactionManager.getTransaction(transactionDefinition);
        try {
            //让方法执行
            point = joinPoint.proceed();
            //提交事务
            transactionManager.commit(ts);
        } catch (Throwable throwable) {
            //异常时回滚
            transactionManager.rollback(ts);
            throw throwable;
        } finally {
            //如果事务没有处理，默认进行回滚
            if (!ts.isCompleted()) {
                transactionManager.rollback(ts);
            }
            //恢复当前数据源
            DynamicDataSource.setDataSource(currDataSource);
        }
        return point;
    }

    private static boolean switchDataSource(String arg, String type) {
        //sql语句中包含指定表名
        if (type.equals("sql") && arg.contains("people2")) {
            DynamicDataSource.setDataSource("slave");
            return true;
            //表名等于指定表名
        } else if (type.equals("tableName") && arg.equals("people2")) {
            DynamicDataSource.setDataSource("slave");
            return true;
        }
        return false;
    }
}

DynamicParam

package com.demo.dynamic.dynamic;

/**
 * @Classname DynamicParam
 * @Description 动态数据源参数基础接口
 * @Date 2022/4/28 13:58
 * @Created by BG
 */
public interface DynamicParam {
    String getSqlOrTableName();
}

--数据源配置-end--

PeopleParam

package com.demo.dynamic.bean;

import com.demo.dynamic.dynamic.DynamicParam;
import lombok.Data;

/**
 * @Classname People
 * @Description 满足匹配动态数据源的对象
 * @Date 2022/4/27 18:09
 * @Created by BG
 */
@Data
public class PeopleParam implements DynamicParam {
    private String id;
    private String name;
    private String status;
    private String content;
    private String tableName;

    @Override
    public String getSqlOrTableName() {
        return tableName;
    }
}

controller

package com.demo.dynamic.controller;

import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @Classname PeopleController
 * @Description TODO
 * @Date 2022/4/27 17:59
 * @Created by BG
 */
@RestController
@RequestMapping("/data")
public class PeopleController {

    @Autowired
    public PeopleService peopleService;


    @RequestMapping("/people1")
    public String getPeople1() {
        return peopleService.getPeople1();
    }

    @RequestMapping("/people2")
    public String getPeople2() {
        return peopleService.getPeople2();
    }

    @RequestMapping("/people3")
    public String getPeople3() {
        return peopleService.getPeople3();
    }

}

service

package com.demo.dynamic.service;

/**
 * @Classname PeopleService
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
public interface PeopleService {

    String getPeople1();

    String getPeople2();

    String getPeople3();
}

package com.demo.dynamic.service.impl;

import com.demo.dynamic.bean.PeopleParam;
import com.demo.dynamic.mapper.PeopleMapper;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * @Classname PeopleServiceImpl
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
@Service
public class PeopleServiceImpl implements PeopleService {

    @Autowired
    public PeopleMapper peopleMapper;


    @Override
    public String getPeople1() {
        List<Map<String, Object>> peopleList = peopleMapper.getPeople1();
        return peopleList.toString();
    }

    @Override
    public String getPeople2() {
        String sql = "select * from people2";
        List<Map<String, Object>> peopleList = peopleMapper.getPeople2(sql);
        return peopleList.toString();
    }

    @Override
    public String getPeople3() {
        PeopleParam param = new PeopleParam();
        param.setTableName("people2");
        List<Map<String, Object>> peopleList = peopleMapper.getPeople3(param);
        return peopleList.toString();
    }
}

mapper

package com.demo.dynamic.mapper;

import com.demo.dynamic.bean.PeopleParam;
import com.demo.dynamic.dynamic.DynamicDB;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @Classname PeopleMapper
 * @Description TODO
 * @Date 2022/4/27 18:00
 * @Created by BG
 */
@Mapper
public interface PeopleMapper {

    @DynamicDB
    List<Map<String, Object>> getPeople1();

    @DynamicDB
    List<Map<String, Object>> getPeople2(@Param("getDataSql") String sql);

    //限制：如果参数不是一条Sql则需要封装成实现‘DynamicParam’接口的对象
    //实际是需要用到‘getSqlOrTableName’获取到表名用来匹配数据源
    @DynamicDB
    List<Map<String, Object>> getPeople3(@Param("param") PeopleParam param);
}

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.demo.dynamic.mapper.PeopleMapper">

    <select id="getPeople1" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            people t
    </select>

    <select id="getPeople2" resultType="java.util.HashMap">
        ${getDataSql}
    </select>

    <select id="getPeople3" resultType="java.util.HashMap">
        SELECT
           *
        FROM
            ${param.tableName} t
    </select>

</mapper>

页面测试

1、http://localhost:10086/data/people1

2、http://localhost:10086/data/people2

3、http://localhost:10086/data/people3

说明和设想的一样：当查询表‘people’时去数据库dynamic4中查询数据，当查询表‘people2’时去数据库dynamic5中查询数据！

总结：

Springboot项目启动时会自动加载配置‘@Configuration’注解的配置类(DatasourceConfig、DynamicDataSourceConfig)，而在‘DynamicDataSourceConfig’中又对“DynamicDataSource”进行了设置，即完成了多个数据源配置的加载。在调用时，通过切面“DynamicDBAspect”获取到被自定义注解“DynamicDB”的方法，然后在切面中进行逻辑分析并动态切换成对应的数据源，完成多数据源的调用。

偶遇急雨洗心尘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java学习之Springboot动态调用多数据源

应用背景：本文只是简单的实现动态数据源，重点在于熟悉代码方面的流程，至于要满足于复杂的业务场景需要进一步扩展。本文中的动态数据源切换需要满足两种业务场景：提前知道并指定使用哪个数据源；不知道使用哪个动态数据源，由逻辑判断匹配数据源(但是需要标识，例如第一个数据源中的表名为‘people’，第二个数据源中的表名为‘people2’)。场景一：提前知道使用哪个数据源(由自己指定数据源)...
复制链接

扫一扫