• 多数据源切换

    多数据源切换

    项目中经常会有多个数据源，那么如何处理呢
    有4种方法
    准备：
    创建两个数据库

    CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 ;
CREATE SCHEMA `school` DEFAULT CHARACTER SET utf8mb4 ;

    • 1
    • 2

    test数据库创建user表，school数据库创建student表

    --
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL COMMENT '用户名',
  `real_name` varchar(200) DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(2000) NOT NULL COMMENT '密码',
  `sex` tinyint(1) NOT NULL COMMENT '性别：0-男，1-女',
  `birthday` date NOT NULL COMMENT '生日',
  `card_id` varchar(20) NOT NULL COMMENT '身份证号',
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_id` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'liming','黎明','123456',1,'2000-09-09','623001200010014433','13099890032'),(3,'miaoshanshan','苗姗姗','123456',0,'2001-10-12','314002200110125523','13509327765');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Host: localhost    Database: school
-- ------------------------------------------------------
-- Server version	8.0.29

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(100) NOT NULL COMMENT '学生姓名',
  `student_no` varchar(20) DEFAULT NULL COMMENT '学号',
  `password` varchar(200) NOT NULL COMMENT '密码',
  `sex` tinyint(1) NOT NULL COMMENT '性别：0-男，1-女',
  `birthday` date NOT NULL COMMENT '生日',
  `card_id` varchar(20) NOT NULL COMMENT '身份证号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `student_no` (`student_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'黎明','202110010101','123456',1,'2000-09-09','623001200010014433'),(2,'李艳','202110010102','123456',1,'2000-03-19',''),(3,'苗姗姗','202110010302','123456',0,'2001-10-12','314002200110125523'),(4,'李媛','202110010301','123456',0,'2002-11-22','314002200211225523');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66

    1.jdbcTemplate

    不需要特殊的jar，只需配置即可
    pom文件

    <?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>jdbc-test</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.5.RELEASE</version>
    </parent>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

    </dependencies>
</project>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    配置多个数据源

    server:
  port: 10010
spring:
  application:
    name: dynamic-datasource-jdbc
  datasource:
    master:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root
    school:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/school?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    对数据源进行配置

    package com.test.jdbc.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-09 20:51
 */
@Configuration
public class DataSourceConfig {

    @Primary
    @Bean("master")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("school")
    @ConfigurationProperties("spring.datasource.school")
    public DataSource schoolDataSource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("master")
    public JdbcTemplate masterTemplate(@Qualifier("master") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean("schoolTemplate")
    public JdbcTemplate schoolTemplate(@Qualifier("school") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    启动类，因为用knife做测试，所以需要@EnableOpenApi

    package com.test.jdbc;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.oas.annotations.EnableOpenApi;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-11 20:33
 */
@SpringBootApplication
@EnableOpenApi
public class JdbcService {
    public static void main(String[] args) {
        SpringApplication.run(JdbcService.class,args);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    分别写实体，接口及实现类进行测试
    实体类

    package com.test.jdbc.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;

import java.io.Serializable;
import java.sql.Date;

/**
 * 学生表
 * @TableName student
 */
@Data
public class Student implements Serializable {
    /**
     * id
     */
    @TableId(type = IdType.AUTO)
    private Integer id;

    /**
     * 学生姓名
     */
    private String studentName;

    /**
     * 学号
     */
    private String studentNo;

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

    /**
     * 性别：0-男，1-女
     */
    private Boolean sex;

    /**
     * 生日
     */
    private Date birthday;//java.sql.Date对应数据库date类型，只显示年月日

    /**
     * 身份证号
     */
    private String cardId;

    /**
     * 手机号
     */
    @TableField(exist = false)
    @JsonIgnore
    private String phone;

}


package com.test.jdbc.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

import java.io.Serializable;
import java.sql.Date;

/**
 * 用户表
 * @TableName user
 */
@Data
public class User implements Serializable {
    /**
     * id
     */
    @TableId(type = IdType.AUTO)
    private Integer id;

    /**
     * 用户名
     */
    private String username;

    /**
     * 真实姓名
     */
    private String realName;

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

    /**
     * 性别：0-男，1-女
     */
    private Boolean sex;

    /**
     * 生日
     */
    private Date birthday;//java.sql.Date对应数据库date类型，只显示年月日

    /**
     * 身份证号
     */
    private String cardId;

    /**
     * 手机号
     */
    private String phone;

    private static final long serialVersionUID = 1L;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121

    接口

    package com.test.jdbc.controller;

import com.test.jdbc.domain.Student;
import com.test.jdbc.domain.User;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-11 20:31
 */
@RestController
public class TestController {

    @Autowired
    private JdbcTemplate masterTemplate;

    @Autowired
    private JdbcTemplate schoolTemplate;


    @GetMapping("getUserList")
    public List<User> getUserList(){
        List<User> list = masterTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        return list;
    }

    @GetMapping("getStudentList")
    public List<Student> getStudentList(){
        List<Student> students = schoolTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));
        return students;
    }

    @GetMapping("getStudent")
    public List<Student> getStudent(){
        String sql = "select * from student";
        List<Student> list = schoolTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
        list.stream().forEach(student -> {
            if (StringUtils.isNotBlank(student.getCardId())){
                String phone = masterTemplate.queryForObject("select phone from user where card_id = ?",String.class);
                student.setPhone(phone);
            }
        });
        return list;
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    浏览器输入http://localhost:10010/doc.html
    在这里插入图片描述
    在这里插入图片描述

    2.使用切面

    需要额外写注解
    pom

    <?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>aop-test</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.5.RELEASE</version>
    </parent>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60

    配置文件

    server:
  port: 10011
spring:
  application:
    name: dynamic-datasource-aop
  datasource:
    master:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root
    school:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/school?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    启动类

    package com.test.aop;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import springfox.documentation.oas.annotations.EnableOpenApi;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-09 20:37
 */
@EnableOpenApi
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)//排除数据源自动注入，不然会报错
public class AopSourceService {
    public static void main(String[] args) {
        SpringApplication.run(AopSourceService.class,args);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    自定义注解

    package com.test.aop.annonation;

import java.lang.annotation.*;

@Target({ElementType.PARAMETER,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSourceSwitch {

    public String dataSourceName() default "";
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    自定义注解的切面实现

    package com.test.aop.aop;

import com.test.aop.annonation.DataSourceSwitch;
import com.test.aop.config.DynamicDataSourceHolder;
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.stereotype.Component;

import java.lang.reflect.Method;

@Aspect
@Component
public class DataSourceAspect {

    @Pointcut("@annotation(com.test.aop.annonation.DataSourceSwitch)")
    public void pointCut(){
    }

    @Before("pointCut()")
    public void before(JoinPoint joinpoint){
        MethodSignature signature = (MethodSignature)joinpoint.getSignature();
        Method method = signature.getMethod();
        DataSourceSwitch annotation = method.getAnnotation(DataSourceSwitch.class);
        DynamicDataSourceHolder.setDatasourcePool(annotation.dataSourceName());
    }

    @After("pointCut()")
    public void after(){
        DynamicDataSourceHolder.removeDatasourcePool();
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    数据源配置类

    package com.test.aop.config;

import com.test.aop.enums.DataSourceType;
import com.zaxxer.hikari.HikariDataSource;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-09 20:51
 */
@Configuration
public class DataSourceConfig {

    @Bean("masterDataSource")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean("schoolDataSource")
    @ConfigurationProperties("spring.datasource.school")
    public DataSource schoolDataSource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean("dynamicDataSource")
    public DynamicDataSource dynamicDataSource(){
        Map<Object,Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.TEST.name(),masterDataSource());
        targetDataSources.put(DataSourceType.SCHOOL.name(),schoolDataSource());
        return new DynamicDataSource(masterDataSource(),targetDataSources);
    }

    /*@Bean("masterTemplate")
    public JdbcTemplate masterTemplate(@Qualifier("masterDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean("schoolTemplate")
    public JdbcTemplate schoolTemplate(@Qualifier("schoolDataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }*/
}


package com.test.aop.config;

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

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

public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object,Object> targetDataSource){
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSource);
        super.afterPropertiesSet();
    }
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDatasourcePool();
    }
}


package com.test.aop.config;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-09 20:54
 */
public class DynamicDataSourceHolder {

    public static ThreadLocal<String> DATASOURCE_POOL = new ThreadLocal<>();

    /**
     * 获取数据源变量
     * @return
     */
    public static String getDatasourcePool(){
        return DATASOURCE_POOL.get();
    }

    /**
     * 设置数据源变量
     * @param name
     */
    public static void setDatasourcePool(String name){
        DATASOURCE_POOL.set(name);
    }

    public static void removeDatasourcePool(){
        DATASOURCE_POOL.remove();
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110

    数据源类型枚举

    package com.test.aop.enums;

public enum DataSourceType {
    TEST,
    SCHOOL,
    ;
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    接口或实现类使用注解

    package com.test.aop.controller;

import com.test.aop.annonation.DataSourceSwitch;
import com.test.aop.domain.Student;
import com.test.aop.domain.User;
import com.test.aop.service.StudentService;
import com.test.aop.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-09 20:58
 */

@RestController
public class TestController {

    @Autowired
    private UserService userService;

    @Autowired
    private StudentService studentService;

    @GetMapping("user/list")
    public List<User> userList(){
        return userService.list();
    }

    @GetMapping("student/list")
    @DataSourceSwitch(dataSourceName = "SCHOOL")
    public List<Student> studentList(){
        return studentService.list();
    }

    @PostMapping("user/save")
    public void saveUser(@RequestBody User user){
        userService.save(user);
    }

    @PostMapping("student/save")
    @DataSourceSwitch(dataSourceName = "SCHOOL")
    public void saveStudent(@RequestBody Student student){
        studentService.save(student);
    }

    @GetMapping("registerToUser")
    public void registerToUser(Integer id,String username,String phone,String cardId){
        userService.registerTiUser(id,username,phone,cardId);
    }

    /**
     *
     * @return
     */
    @GetMapping("list")
    public List<Student> list(){
        return studentService.getPhone();
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    3.mybatis层次的多数据源

    需要每个库写一个配置类
    pom

    server:
  port: 10012
spring:
  application:
    name: dynamic-datasource-mybatis
  datasource:
    master:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root
    school:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/school?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
      username: root
      password: root


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    启动类

    package com.test.mybatis;

import com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import springfox.documentation.oas.annotations.EnableOpenApi;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-11 21:06
 */
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, MybatisPlusAutoConfiguration.class})
@EnableOpenApi
public class MybatisService {
    public static void main(String[] args) {
        SpringApplication.run(MybatisService.class,args);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    数据源配置文件

    package com.test.mybatis.config;

import com.sun.tracing.ProbeName;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.support.TransactionTemplate;

import javax.sql.DataSource;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-11 21:14
 */
@Configuration
@MapperScan(basePackages = "com.test.mybatis.mapper.test",sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDatasource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public SqlSessionFactory masterSqlSessionFactory()throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(masterDatasource());
        return bean.getObject();
    }

    @Primary
    @Bean
    public DataSourceTransactionManager masterTransactionManager(){
        DataSourceTransactionManager manager = new DataSourceTransactionManager();
        manager.setDataSource(masterDatasource());
        return manager;
    }

    @Bean
    public TransactionTemplate masterTemplate(){
        return new TransactionTemplate(masterTransactionManager());
    }

}

package com.test.mybatis.config;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;

/**
 * @author清梦
 * @site www.xiaomage.com
 * @company xxx公司
 * @create 2023-11-11 21:14
 */
@Configuration
@MapperScan(basePackages = "com.test.mybatis.mapper.school",sqlSessionFactoryRef = "schoolSqlSessionFactory")
public class SchoolDataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.school")
    public DataSource schoolDatasource(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    public SqlSessionFactory schoolSqlSessionFactory()throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(schoolDatasource());
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager schoolTransactionManager(){
        DataSourceTransactionManager manager = new DataSourceTransactionManager();
        manager.setDataSource(schoolDatasource());
        return manager;
    }

    @Bean
    public TransactionTemplate schoolTemplate(){
        return new TransactionTemplate(schoolTransactionManager());
    }

}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109

    4.spring的dynamic自动注入

    需要引入dynamic-datasource-spring-boot-starter，实现类使用注解@DS(“数据源”)即可
    pom

    <?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.5.RELEASE</version>
    </parent>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>2.5.6</version>
        </dependency>

    </dependencies>
</project>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    配置文件有点不同

    server:
  port: 10013
spring:
  application:
    name: dynamic-datasource-spring
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
          username: root
          password: root
        school:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/school?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
          username: root
          password: root


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    启动类

    @SpringBootApplication
@EnableOpenApi
public class SpringAutoService{

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在实现类使用注解

    package com.test.spring.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.test.spring.domain.Student;
import com.test.spring.mapper.StudentMapper;
import com.test.spring.mapper.UserMapper;
import com.test.spring.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
*
*/
@Service
@DS("school")
public class StudentServiceImpl extends ServiceImpl<StudentMapper,Student> implements StudentService{


    @Autowired
    private UserMapper userMapper;

    @Override
    public List<Student> getPhone() {
        List<Student> list = list();
        list.stream().filter(student -> StringUtils.isNotBlank(student.getCardId())).forEach(student -> {
            String phone = userMapper.selectPhoneByCardId(student.getCardId());
            student.setPhone(phone);
        });
        return list;
    }

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    package com.test.spring.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.test.spring.domain.Student;
import com.test.spring.domain.User;
import com.test.spring.mapper.StudentMapper;
import com.test.spring.mapper.UserMapper;
import com.test.spring.service.StudentService;
import com.test.spring.service.UserService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


/**
*
*/
@Service
@DS("master")
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private StudentMapper studentMapper;

    @Override
    public void registerTiUser(Integer id, String username, String phone, String cardId) {
        Student student = studentMapper.selectById(id);
        if (null == student){
            throw new RuntimeException("该学生不存在");
        }
        User user = new User();
        user.setRealName(student.getStudentName());
        BeanUtils.copyProperties(student,user);
        user.setUsername(username);
        user.setPhone(phone);
        user.setCardId(cardId);
        userMapper.insert(user);
    }

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

    源码地址：https://gitee.com/qfp17393120407/dynamic-datasource-new.git

  • 相关阅读:
    ［Linux］进程概念
    教你用API插件开发一个AI快速处理图片小助手
    ELK8.1从零搭建
    电商数据的采集标准
    论文笔记——BiFormer
    PTA题目 计算工资
    git设置并记忆用户名密码
    2.0 Python 数据结构与类型
    一线互联网大厂普遍使用的Docker，掌握这套面试题，让领导主动涨薪
    Docker常用应用部署
  • 原文地址：https://blog.csdn.net/weixin_44860141/article/details/134321766