springboot+mybatisplus+mysql 多数据源

于 2023-10-27 10:10:28 发布
阅读量139 收藏
点赞数
文章标签： spring boot java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_45084064/article/details/134070349
版权

springboot+mybatisplus+mysql 多数据源

1.数据准备

测试数据

CREATE DATABASE `test2`CHARACTER SET utf8mb4 ;
CREATE DATABASE `test1`CHARACTER SET utf8mb4 ;

CREATE TABLE test1.`user` (
   `username` varchar(256) NOT NULL COMMENT '用户名',
   `sex` tinyint(4) DEFAULT 0 COMMENT '性别',
   `phone` varchar(256) NOT NULL COMMENT '手机号',
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
   `address` varchar(256) NOT NULL COMMENT '居住地',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表';
 
CREATE TABLE test2.`user` (
   `username` varchar(256) NOT NULL COMMENT '用户名',
   `sex` tinyint(4) DEFAULT 0 COMMENT '性别',
   `phone` varchar(256) NOT NULL COMMENT '手机号',
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
   `address` varchar(256) NOT NULL COMMENT '居住地',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表'


insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('秦修杰', 0, '13384620379', 1, '盐城');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('洪鸿涛', 0, '17622718907', 2, '葫芦岛');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('贺鸿涛', 1, '17684939394', 3, '胶南');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('龚熠彤', 0, '14563132069', 4, '中山');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('邵鑫鹏', 0, '17326641831', 5, '东营');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('韦涛', 1, '17137427755', 6, '重庆');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('薛修洁', 0, '17244034927', 7, '赤峰');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('崔志泽', 0, '17853386821', 8, '贵阳');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('胡弘文', 0, '15978653401', 9, '宜宾');
insert into test1.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('彭明辉', 0, '17229214351', 10, '德州');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('邵晋鹏', 0, '17186478354', 11, '德阳');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('陈明轩', 1, '17204116519', 12, '丽水');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('蔡炫明', 1, '17217611915', 13, '湘潭');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('傅航', 1, '15018327439', 14, '北海');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('许苑博', 1, '15801441475', 15, '贵阳');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('何彬', 0, '14718699574', 16, '肇庆');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('白展鹏', 1, '15022448510', 17, '营口');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('莫明', 1, '17839217296', 18, '太仓');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('冯懿轩', 0, '13628630821', 19, '威海');
insert into test2.`user` (`username`, `sex`, `phone`, `id`, `address`) values ('莫伟诚', 1, '15791417939', 20, '昆山');

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>com.it</groupId>
    <artifactId>dynamicdsdemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

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



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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis-spring-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>

<!--        &lt;!&ndash; PostgreSQL &ndash;&gt;-->
<!--        <dependency>-->
<!--            <groupId>org.postgresql</groupId>-->
<!--            <artifactId>postgresql</artifactId>-->
<!--        </dependency>-->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.16</version>
        </dependency>

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

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

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3.yaml文件

server:
  port: 8010

spring:
  application:
    name: dynamic-datasource

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource

    test1:
      jdbc-url: jdbc:mysql://localhost:3306/test1?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=true
      username: root
      password: root

    test2:
      jdbc-url: jdbc:mysql://localhost:3306/test2?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=true
      username: root
      password: root


mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:mapper/**/*.xml
  type-aliases-package: com.it.mapper

logging:
  level:
   com.it: info
   com.it.mapper: debug

4.启动类

package com.it;

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 org.springframework.context.annotation.EnableAspectJAutoProxy;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class}) // 排除自带的数据源自动装配
@EnableAspectJAutoProxy // 允许aop
@MapperScan("com.it.mapper") // mapper扫描路径
public class DynamicDSApplication {
    public static void main(String[] args) {
        SpringApplication.run(DynamicDSApplication.class,args);
    }
}

5.数据源枚举类

最好和yaml文件配置的数据源名称一一对应,避免产生歧义

package com.it.costant;

/**
 * 定义数据源信息
 */
public enum DataSourceName {

    Test1,
    Test2;

}

6.数据源配置

6.1.DataSource配置

分别声明数据源的对象注入IOC容器

package com.it.config;

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 javax.sql.DataSource;

@Configuration
public class MyDataSourceConfig {

    @Bean //默认beanName为方法名
    @ConfigurationProperties("spring.datasource.test1") 
    public DataSource test1(){
        return DataSourceBuilder.create().build();
    }


    @Bean
    @ConfigurationProperties("spring.datasource.test2")
    public DataSource test2(){
        return DataSourceBuilder.create().build();
    }
}

6.2.继承AbstractRoutingDataSource

package com.it.config;

import com.it.costant.DataSourceName;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;


@Component
@Primary
public class MyDataSourceManagement extends AbstractRoutingDataSource {

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

    @Resource
    private DataSource test1;

    @Resource
    private DataSource test2;


    @Override
    protected Object determineCurrentLookupKey() {
        return flag.get();
    }

    public MyDataSourceManagement(){
        flag.set(DataSourceName.Test1.name());
    }

    @Override
    public void afterPropertiesSet() {

        Map<Object,Object> targetDataSource = new ConcurrentHashMap<>();
        
        // map.put(test1标识,对应的test1数据源)
        targetDataSource.put(DataSourceName.Test1.name(),test1);
        targetDataSource.put(DataSourceName.Test2.name(),test2);
        
        super.setTargetDataSources(targetDataSource);
        // 设置默认数据源
        super.setDefaultTargetDataSource(test1);
        super.afterPropertiesSet();

    }
}

7.自定义注解

用于标识要使用数据源,如果没有改注解则表示使用的是默认数据源

package com.it.annotation;

import com.it.costant.DataSourceName;

import java.lang.annotation.*;

@Target({ElementType.METHOD}) // 作用范围: 方法
@Retention(RetentionPolicy.RUNTIME) // 作用时间: 运行
@Documented
public @interface Dynamic {

    DataSourceName value() default DataSourceName.Test1; //默认test1

}

8.AOP实现

通过获取方法上的注解的值,来切换

package com.it.aspect;


import com.it.annotation.Dynamic;
import com.it.config.MyDataSourceManagement;
import com.it.costant.DataSourceName;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;


@Slf4j
@Component
@Aspect
public class DynamicDSAspect {

    @Pointcut("@annotation(com.it.annotation.Dynamic)")
    public void point() {
    }

    @Before("point()")
    public void updateDataSource(JoinPoint joinPoint) {

        // 获取方法
        MethodSignature m = (MethodSignature) joinPoint.getSignature();

        Dynamic dynamic = m.getMethod().getAnnotation(Dynamic.class);
        // 获取注解的值,切换
        if (!dynamic.value().name().equals(DataSourceName.Test1.name())) {
            MyDataSourceManagement.flag.set(dynamic.value().name());
            log.info("当前数据源已切换为默认数据源: {}", MyDataSourceManagement.flag.get());
        } else {
            log.info("当前数据源为默认数据源");
        }

    }


    @After("point()")
    public void defaultDS(JoinPoint joinPoint) {
        // 获取方法
        MethodSignature m = (MethodSignature) joinPoint.getSignature();
        Dynamic dynamic = m.getMethod().getAnnotation(Dynamic.class);
        // 获取注解的值,切换为默认数据源
        if (!dynamic.value().name().equals(DataSourceName.Test1.name())) {
            MyDataSourceManagement.flag.set(DataSourceName.Test1.name());
            log.info("当前数据源已切换为默认数据源: {}", MyDataSourceManagement.flag.get());
        } else {
            log.info("当前数据源为默认数据源");
        }
        
    }

}

9.实体类

package com.it.model;


import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("user")
public class User implements Serializable {
    private static final long serialVersionUID = 5913428462570926954L;

    private Long id;

    private String username;

    private String sex;

    private String address;

    private String phone;
}

10.Mapper类

10.1. Test1Mapper

package com.it.mapper.test1;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.it.model.User;

public interface Test1Mapper extends BaseMapper<User> {
}

10.2. Test2Mapper

package com.it.mapper.test2;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.it.model.User;

public interface Test2Mapper extends BaseMapper<User> {
}

11.Service类

11.1. Test1Service以及实现类

Test1Service

package com.it.service;

import com.it.model.User;

import java.util.List;

public interface Test1Service {


    List<User> getList();

}

Test1ServiceImpl

package com.it.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.it.mapper.test1.Test1Mapper;
import com.it.model.User;
import com.it.service.Test1Service;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class Test1ServiceImpl extends ServiceImpl<Test1Mapper, User> implements Test1Service {


    @Override
//    @Dynamic
    public List<User> getList() {
        return this.list();
    }
}

11.2. Test2Service以及实现类

Test2Service

package com.it.service;

import com.it.model.User;

import java.util.List;

public interface Test2Service {
    List<User> getList();
}

Test2ServiceImpl

package com.it.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.it.annotation.Dynamic;
import com.it.costant.DataSourceName;
import com.it.mapper.test1.Test1Mapper;
import com.it.model.User;
import com.it.service.Test2Service;
import org.springframework.stereotype.Service;

import java.util.List;

@Service

public class Test2ServiceImpl extends ServiceImpl<Test1Mapper, User> implements Test2Service {

    @Override
    @Dynamic(DataSourceName.Test2)
    public List<User> getList() {
        return this.list();
    }
}

12.测试方法

package com.it.service;



import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

/**
 * 简单测试了一下是否可以从对应的数据库获取数据,以及数据获取完后是否切换为默认数据源
 */
@SpringBootTest
public class TestAll {

    @Resource
    private Test1Service test1Service;

    @Resource
    private Test2Service test2Service;

    @Test
    void getList(){
        test2Service.getList().forEach(System.out::println);
        test1Service.getList().forEach(System.out::println);
    }

}

13.总结

dataSource or dataSourceClassName or jdbcUrl is required

1.可能是yaml配置的数据源存在问题, 看了网络上的文档,是因为版本导致的.更改一下参数即可 jdbc-url / url

2.可能是配置类缺失注解 @ConfigurationProperties(“xx”) 导致没有获取到配置信息

java.lang.StackOverflowError
at java.lang.ThreadLocal.get(ThreadLocal.java:161)
at com.it.config.MyDataSourceManagement.determineCurrentLookupKey(MyDataSourceManagement.java:29)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.determineTargetDataSource(AbstractRoutingDataSource.java:226)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:194)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:194)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:194)

这个报错产生原因比较笨, 当时参考的文档存在缺漏, 导致没有创建对应的DataSource, 但是在其他使用了注入.后续补充即可

14.参考连接

https://blog.youkuaiyun.com/qq_45515182/article/details/126330084

感谢大佬的文档, 当时心血来潮准备尝试一下写个demo. 找了很多文档都有些问题.写一个备份一下.

流域吖
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值