SpringBoot JPA 多数据源切换_jpa切换数据源-CSDN博客

本文链接：https://blog.csdn.net/m0_72989210/article/details/140817662

多数据源切换 - JDBC

SQL 脚本

-- 创建数据库ds1
CREATE
DATABASE `ds1`;

-- 使用ds1数据库
USE
ds1;

-- 创建user表
CREATE TABLE `user`
(
    `id`       INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
    `username` VARCHAR(50) COMMENT '用户名',
    `gender`   TINYINT(1) COMMENT '性别:0男，1女'
);

-- 向user表插入数据
INSERT INTO user (username, gender)
VALUES ('张三', 1),
       ('李四', 0),
       ('王五', 1);

-- 创建数据库ds2
CREATE
DATABASE `ds2`;

-- 使用ds2数据库
USE
ds2;

-- 创建user表
CREATE TABLE `user`
(
    `id`       INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
    `username` VARCHAR(50) COMMENT '用户名',
    `gender`   TINYINT(1) COMMENT '性别:0男，1女'
);

-- 向user表插入数据
INSERT INTO user (username, gender)
VALUES ('赵六', 1),
       ('陈七', 0),
       ('宝国', 1);

application.yml

spring:
  #动态数据源配置
  datasource:
    ds1:
      driverClassName: com.mysql.cj.jdbc.Driver
      jdbcUrl: jdbc:mysql://192.168.179.130:3306/ds1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
      username: root
      password: root
    ds2:
      driverClassName: com.mysql.cj.jdbc.Driver
      jdbcUrl: jdbc:mysql://192.168.179.130:3306/ds2?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
      username: root
      password: root

logging:
  level:
    demo_21_dynamicdata: debug

Demo21DynamicDataApplication

package org.example.demo_21_dynamicdata;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Demo21DynamicDataApplication {

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

}

UserService

package org.example.demo_21_dynamicdata.service;


import org.example.demo_21_dynamicdata.common.DataSourceContextHolder;
import org.example.demo_21_dynamicdata.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insertDs1(User user) {
        try {
            DataSourceContextHolder.setDataSource("ds1");
            String sql = "insert into user(username,gender) values(?,?)";
            jdbcTemplate.update(sql, user.getUsername(), user.getGender());
        } finally {
            DataSourceContextHolder.clearDataSource();
        }
    }

    public void insertDs2(User user) {
        try {