SpringBoot实现多数据源配置的四种方案

<?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>3.2.5</version>
    </parent>
    <groupid>com.example</groupid>
    <artifactid>multi-ds-manual</artifactid>
    <version>1.0.0</version>
    <properties>
        <java.version>17</java.version>
        <druid.version>1.2.21</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</artifactid>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupid>org.mybatis.spring.boot</groupid>
            <artifactid>mybatis-spring-boot-starter</artifactid>
            <version>3.0.3</version>
        </dependency>
        <!-- druid（spring boot 3 用这个） -->
        <dependency>
            <groupid>com.alibaba</groupid>
            <artifactid>druid-spring-boot-3-starter</artifactid>
            <version>${druid.version}</version>
        </dependency>
        <!-- mysql 驱动 -->
        <dependency>
            <groupid>com.mysql</groupid>
            <artifactid>mysql-connector-j</artifactid>
            <version>8.3.0</version>
        </dependency>
        <!-- postgresql 驱动 -->
        <dependency>
            <groupid>org.postgresql</groupid>
            <artifactid>postgresql</artifactid>
            <version>42.7.3</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>
    </dependencies>
</project>

server:
  port: 8080
spring:
  datasource:
    type: com.alibaba.druid.pool.druiddatasource
    # ============ 数据源1：本地 mysql ============
    primary:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://localhost:3306/db_primary?useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=asia/shanghai&allowpublickeyretrieval=true
      username: root
      password: root123456
      initial-size: 5
      min-idle: 5
      max-active: 30
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall,slf4j
    # ============ 数据源2：postgresql ============
    secondary:
      driver-class-name: org.postgresql.driver
      url: jdbc:postgresql://localhost:5432/db_secondary?currentschema=public&stringtype=unspecified
      username: postgres
      password: pg123456
      initial-size: 3
      min-idle: 3
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall,slf4j
    # ============ 数据源3：阿里云 rds mysql ============
    third:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com:3306/db_cloud?useunicode=true&characterencoding=utf-8&usessl=true&servertimezone=asia/shanghai
      username: cloud_user
      password: cloud@2024!
      initial-size: 10
      min-idle: 10
      max-active: 50
      max-wait: 30000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: true
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall,slf4j
      keep-alive: true
      phy-timeout-millis: 1800000
logging:
  level:
    com.example.mapper: debug

package com.example;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourceautoconfigure;
import org.springframework.boot.springapplication;
import org.springframework.boot.autoconfigure.springbootapplication;

// 关键：排除 druid 自动配置，否则会和手动配置冲突
@springbootapplication(exclude = {druiddatasourceautoconfigure.class})
public class manualdsapplication {
    public static void main(string[] args) {
        springapplication.run(manualdsapplication.class, args);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import org.apache.ibatis.session.sqlsessionfactory;
import org.mybatis.spring.sqlsessionfactorybean;
import org.mybatis.spring.sqlsessiontemplate;
import org.mybatis.spring.annotation.mapperscan;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.context.annotation.primary;
import org.springframework.core.io.support.pathmatchingresourcepatternresolver;
import org.springframework.jdbc.datasource.datasourcetransactionmanager;
import org.springframework.transaction.platformtransactionmanager;

import javax.sql.datasource;

@configuration
@mapperscan(
    basepackages = "com.example.mapper.primary",
    sqlsessionfactoryref = "primarysqlsessionfactory",
    sqlsessiontemplateref = "primarysqlsessiontemplate"
)
public class primarydatasourceconfig {

    @primary
    @bean("primarydatasource")
    @configurationproperties(prefix = "spring.datasource.primary")
    public datasource primarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @primary
    @bean("primarysqlsessionfactory")
    public sqlsessionfactory primarysqlsessionfactory(
            @qualifier("primarydatasource") datasource ds) throws exception {
        sqlsessionfactorybean bean = new sqlsessionfactorybean();
        bean.setdatasource(ds);
        bean.setmapperlocations(new pathmatchingresourcepatternresolver()
                .getresources("classpath:mapper/primary/*.xml"));
        org.apache.ibatis.session.configuration cfg =
                new org.apache.ibatis.session.configuration();
        cfg.setmapunderscoretocamelcase(true);
        cfg.setlogimpl(org.apache.ibatis.logging.slf4j.slf4jimpl.class);
        bean.setconfiguration(cfg);
        return bean.getobject();
    }

    @primary
    @bean("primarytransactionmanager")
    public platformtransactionmanager primarytxmanager(
            @qualifier("primarydatasource") datasource ds) {
        return new datasourcetransactionmanager(ds);
    }

    @primary
    @bean("primarysqlsessiontemplate")
    public sqlsessiontemplate primarysqlsessiontemplate(
            @qualifier("primarysqlsessionfactory") sqlsessionfactory sf) {
        return new sqlsessiontemplate(sf);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import org.apache.ibatis.session.sqlsessionfactory;
import org.mybatis.spring.sqlsessionfactorybean;
import org.mybatis.spring.sqlsessiontemplate;
import org.mybatis.spring.annotation.mapperscan;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.core.io.support.pathmatchingresourcepatternresolver;
import org.springframework.jdbc.datasource.datasourcetransactionmanager;
import org.springframework.transaction.platformtransactionmanager;

import javax.sql.datasource;

@configuration
@mapperscan(
    basepackages = "com.example.mapper.secondary",
    sqlsessionfactoryref = "secondarysqlsessionfactory",
    sqlsessiontemplateref = "secondarysqlsessiontemplate"
)
public class secondarydatasourceconfig {

    @bean("secondarydatasource")
    @configurationproperties(prefix = "spring.datasource.secondary")
    public datasource secondarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("secondarysqlsessionfactory")
    public sqlsessionfactory secondarysqlsessionfactory(
            @qualifier("secondarydatasource") datasource ds) throws exception {
        sqlsessionfactorybean bean = new sqlsessionfactorybean();
        bean.setdatasource(ds);
        bean.setmapperlocations(new pathmatchingresourcepatternresolver()
                .getresources("classpath:mapper/secondary/*.xml"));
        org.apache.ibatis.session.configuration cfg =
                new org.apache.ibatis.session.configuration();
        cfg.setmapunderscoretocamelcase(true);
        cfg.setlogimpl(org.apache.ibatis.logging.slf4j.slf4jimpl.class);
        bean.setconfiguration(cfg);
        return bean.getobject();
    }

    @bean("secondarytransactionmanager")
    public platformtransactionmanager secondarytxmanager(
            @qualifier("secondarydatasource") datasource ds) {
        return new datasourcetransactionmanager(ds);
    }

    @bean("secondarysqlsessiontemplate")
    public sqlsessiontemplate secondarysqlsessiontemplate(
            @qualifier("secondarysqlsessionfactory") sqlsessionfactory sf) {
        return new sqlsessiontemplate(sf);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import org.apache.ibatis.session.sqlsessionfactory;
import org.mybatis.spring.sqlsessionfactorybean;
import org.mybatis.spring.sqlsessiontemplate;
import org.mybatis.spring.annotation.mapperscan;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.core.io.support.pathmatchingresourcepatternresolver;
import org.springframework.jdbc.datasource.datasourcetransactionmanager;
import org.springframework.transaction.platformtransactionmanager;

import javax.sql.datasource;

@configuration
@mapperscan(
    basepackages = "com.example.mapper.third",
    sqlsessionfactoryref = "thirdsqlsessionfactory",
    sqlsessiontemplateref = "thirdsqlsessiontemplate"
)
public class thirddatasourceconfig {

    @bean("thirddatasource")
    @configurationproperties(prefix = "spring.datasource.third")
    public datasource thirddatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("thirdsqlsessionfactory")
    public sqlsessionfactory thirdsqlsessionfactory(
            @qualifier("thirddatasource") datasource ds) throws exception {
        sqlsessionfactorybean bean = new sqlsessionfactorybean();
        bean.setdatasource(ds);
        bean.setmapperlocations(new pathmatchingresourcepatternresolver()
                .getresources("classpath:mapper/third/*.xml"));
        org.apache.ibatis.session.configuration cfg =
                new org.apache.ibatis.session.configuration();
        cfg.setmapunderscoretocamelcase(true);
        cfg.setlogimpl(org.apache.ibatis.logging.slf4j.slf4jimpl.class);
        bean.setconfiguration(cfg);
        return bean.getobject();
    }

    @bean("thirdtransactionmanager")
    public platformtransactionmanager thirdtxmanager(
            @qualifier("thirddatasource") datasource ds) {
        return new datasourcetransactionmanager(ds);
    }

    @bean("thirdsqlsessiontemplate")
    public sqlsessiontemplate thirdsqlsessiontemplate(
            @qualifier("thirdsqlsessionfactory") sqlsessionfactory sf) {
        return new sqlsessiontemplate(sf);
    }
}

package com.example.config;

import com.alibaba.druid.support.http.statviewservlet;
import com.alibaba.druid.support.http.webstatfilter;
import org.springframework.boot.web.servlet.filterregistrationbean;
import org.springframework.boot.web.servlet.servletregistrationbean;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;

import java.util.hashmap;
import java.util.map;

@configuration
public class druidmonitorconfig {

    @bean
    public servletregistrationbean<statviewservlet> statviewservlet() {
        servletregistrationbean<statviewservlet> bean =
                new servletregistrationbean<>(new statviewservlet(), "/druid/*");
        map<string, string> params = new hashmap<>();
        params.put("loginusername", "admin");
        params.put("loginpassword", "admin123");
        params.put("allow", "");
        params.put("resetenable", "false");
        bean.setinitparameters(params);
        return bean;
    }

    @bean
    public filterregistrationbean<webstatfilter> webstatfilter() {
        filterregistrationbean<webstatfilter> bean =
                new filterregistrationbean<>(new webstatfilter());
        bean.addurlpatterns("/*");
        bean.addinitparameter("exclusions", ".js,.gif,.jpg,.png,.css,.ico,/druid/*");
        return bean;
    }
}

package com.example.entity;

import lombok.data;
import java.time.localdatetime;

@data
public class user {
    private long id;
    private string username;
    private string email;
    private localdatetime createtime;
}

@data
public class order {
    private long id;
    private long userid;
    private string orderno;
    private java.math.bigdecimal amount;
    private integer status;
    private localdatetime createtime;
}

@data
public class product {
    private long id;
    private string productname;
    private java.math.bigdecimal price;
    private integer stock;
}

package com.example.mapper.primary;

import com.example.entity.user;
import org.apache.ibatis.annotations.param;
import java.util.list;

public interface usermapper {
    list<user> selectall();
    user selectbyid(@param("id") long id);
    int insert(user user);
}

package com.example.mapper.secondary;

import com.example.entity.order;
import org.apache.ibatis.annotations.param;
import java.util.list;

public interface ordermapper {
    list<order> selectall();
    list<order> selectbyuserid(@param("userid") long userid);
    int insert(order order);
}

package com.example.mapper.third;

import com.example.entity.product;
import org.apache.ibatis.annotations.param;
import java.util.list;

public interface productmapper {
    list<product> selectall();
    product selectbyid(@param("id") long id);
    int insert(product product);
}

<?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.example.mapper.primary.usermapper">
    <select id="selectall" resulttype="com.example.entity.user">
        select id, username, email, create_time from t_user
    </select>
    <select id="selectbyid" resulttype="com.example.entity.user">
        select id, username, email, create_time from t_user where id = #{id}
    </select>
    <insert id="insert" usegeneratedkeys="true" keyproperty="id">
        insert into t_user(username, email, create_time)
        values(#{username}, #{email}, now())
    </insert>
</mapper>

<?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.example.mapper.secondary.ordermapper">
    <select id="selectall" resulttype="com.example.entity.order">
        select id, user_id, order_no, amount, status, create_time from t_order
    </select>
    <select id="selectbyuserid" resulttype="com.example.entity.order">
        select id, user_id, order_no, amount, status, create_time
        from t_order where user_id = #{userid}
    </select>
    <insert id="insert" usegeneratedkeys="true" keyproperty="id">
        insert into t_order(user_id, order_no, amount, status, create_time)
        values(#{userid}, #{orderno}, #{amount}, #{status}, now())
    </insert>
</mapper>

<?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.example.mapper.third.productmapper">
    <select id="selectall" resulttype="com.example.entity.product">
        select id, product_name, price, stock from t_product
    </select>
    <select id="selectbyid" resulttype="com.example.entity.product">
        select id, product_name, price, stock from t_product where id = #{id}
    </select>
    <insert id="insert" usegeneratedkeys="true" keyproperty="id">
        insert into t_product(product_name, price, stock)
        values(#{productname}, #{price}, #{stock})
    </insert>
</mapper>

package com.example.service;

import com.example.entity.;
import com.example.mapper.primary.usermapper;
import com.example.mapper.secondary.ordermapper;
import com.example.mapper.third.productmapper;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.service;
import org.springframework.transaction.annotation.transactional;
import java.util.list;

@service
public class businessservice {

    @autowired
    private usermapper usermapper;
    @autowired
    private ordermapper ordermapper;
    @autowired
    private productmapper productmapper;

    public list<user> getusers() { return usermapper.selectall(); }
    public list<order> getorders() { return ordermapper.selectall(); }
    public list<product> getproducts() { return productmapper.selectall(); }

    @transactional(transactionmanager = "primarytransactionmanager")
    public void adduser(user user) { usermapper.insert(user); }

    @transactional(transactionmanager = "secondarytransactionmanager")
    public void addorder(order order) { ordermapper.insert(order); }

    @transactional(transactionmanager = "thirdtransactionmanager")
    public void addproduct(product product) { productmapper.insert(product); }
}

package com.example.controller;

import com.example.service.businessservice;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.;
import java.util.;

@restcontroller
@requestmapping("/api")
public class testcontroller {

    @autowired
    private businessservice service;

    @getmapping("/test")
    public map<string, object> test() {
        map<string, object> result = new linkedhashmap<>();
        result.put("本地mysql_用户", service.getusers());
        result.put("postgresql_订单", service.getorders());
        result.put("阿里云rds_商品", service.getproducts());
        return result;
    }
}

-- ========== 本地 mysql: db_primary ==========
create database if not exists db_primary default charset utf8mb4;
use db_primary;
create table t_user (
    id bigint auto_increment primary key,
    username varchar(50) not null,
    email varchar(100),
    create_time datetime default current_timestamp
);
insert into t_user(username, email) values('张三','zhangsan@test.com');
insert into t_user(username, email) values('李四','lisi@test.com');
-- ========== postgresql: db_secondary ==========
-- create database db_secondary;
create table t_order (
    id bigserial primary key,
    user_id bigint not null,
    order_no varchar(64) not null,
    amount numeric(10,2) default 0,
    status int default 0,
    create_time timestamp default current_timestamp
);
insert into t_order(user_id, order_no, amount, status)
values(1, 'ord20240101001', 99.90, 1);
insert into t_order(user_id, order_no, amount, status)
values(2, 'ord20240101002', 199.00, 0);
-- ========== 阿里云 rds mysql: db_cloud ==========
create database if not exists db_cloud default charset utf8mb4;
use db_cloud;
create table t_product (
    id bigint auto_increment primary key,
    product_name varchar(200) not null,
    price decimal(10,2) default 0,
    stock int default 0
);
insert into t_product(product_name, price, stock) values('iphone 15', 7999.00, 100);
insert into t_product(product_name, price, stock) values('macbook pro', 14999.00, 50);

server:
  port: 8081
spring:
  datasource:
    type: com.alibaba.druid.pool.druiddatasource
    primary:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://localhost:3306/db_primary?useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=asia/shanghai
      username: root
      password: root123456
      initial-size: 5
      min-idle: 5
      max-active: 30
      max-wait: 60000
      validation-query: select 1
      test-while-idle: true
      filters: stat,wall,slf4j
    secondary:
      driver-class-name: org.postgresql.driver
      url: jdbc:postgresql://localhost:5432/db_secondary?currentschema=public
      username: postgres
      password: pg123456
      initial-size: 3
      min-idle: 3
      max-active: 20
      max-wait: 60000
      validation-query: select 1
      test-while-idle: true
      filters: stat,wall,slf4j
    third:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com:3306/db_cloud?usessl=true&servertimezone=asia/shanghai
      username: cloud_user
      password: cloud@2024!
      initial-size: 5
      min-idle: 5
      max-active: 40
      max-wait: 30000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: true
      filters: stat,wall,slf4j
      keep-alive: true
logging:
  level:
    com.example: debug

@springbootapplication(exclude = {druiddatasourceautoconfigure.class})
public class dynamicdsapplication {
    public static void main(string[] args) {
        springapplication.run(dynamicdsapplication.class, args);
    }
}

package com.example.dynamic;

public class datasourcecontextholder {

    public static final string primary = "primary";
    public static final string secondary = "secondary";
    public static final string third = "third";

    private static final threadlocal<string> holder = new threadlocal<>();

    public static void set(string ds) {
        holder.set(ds);
    }

    public static string get() {
        return holder.get();
    }

    public static void clear() {
        holder.remove();
    }
}

package com.example.dynamic;

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

public class dynamicdatasource extends abstractroutingdatasource {

    @override
    protected object determinecurrentlookupkey() {
        return datasourcecontextholder.get();
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import com.example.dynamic.datasourcecontextholder;
import com.example.dynamic.dynamicdatasource;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
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;

@configuration
public class dynamicdatasourceconfig {

    @bean("primarydatasource")
    @configurationproperties(prefix = "spring.datasource.primary")
    public datasource primarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("secondarydatasource")
    @configurationproperties(prefix = "spring.datasource.secondary")
    public datasource secondarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("thirddatasource")
    @configurationproperties(prefix = "spring.datasource.third")
    public datasource thirddatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @primary
    @bean("dynamicdatasource")
    public datasource dynamicdatasource(
            @qualifier("primarydatasource") datasource primary,
            @qualifier("secondarydatasource") datasource secondary,
            @qualifier("thirddatasource") datasource third) {

        dynamicdatasource dynamicds = new dynamicdatasource();

        map<object, object> targetmap = new hashmap<>();
        targetmap.put(datasourcecontextholder.primary, primary);
        targetmap.put(datasourcecontextholder.secondary, secondary);
        targetmap.put(datasourcecontextholder.third, third);

        dynamicds.settargetdatasources(targetmap);
        dynamicds.setdefaulttargetdatasource(primary);  // 默认走 primary

        return dynamicds;
    }
}

package com.example.config;

import org.apache.ibatis.session.sqlsessionfactory;
import org.mybatis.spring.sqlsessionfactorybean;
import org.mybatis.spring.annotation.mapperscan;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.core.io.support.pathmatchingresourcepatternresolver;
import org.springframework.jdbc.datasource.datasourcetransactionmanager;
import org.springframework.transaction.platformtransactionmanager;

import javax.sql.datasource;

@configuration
@mapperscan(basepackages = "com.example.mapper")
public class mybatisconfig {

    @bean
    public sqlsessionfactory sqlsessionfactory(
            @qualifier("dynamicdatasource") datasource ds) throws exception {
        sqlsessionfactorybean bean = new sqlsessionfactorybean();
        bean.setdatasource(ds);
        bean.setmapperlocations(new pathmatchingresourcepatternresolver()
                .getresources("classpath:mapper/*/*.xml"));
        org.apache.ibatis.session.configuration cfg =
                new org.apache.ibatis.session.configuration();
        cfg.setmapunderscoretocamelcase(true);
        cfg.setlogimpl(org.apache.ibatis.logging.slf4j.slf4jimpl.class);
        bean.setconfiguration(cfg);
        return bean.getobject();
    }

    @bean
    public platformtransactionmanager transactionmanager(
            @qualifier("dynamicdatasource") datasource ds) {
        return new datasourcetransactionmanager(ds);
    }
}

package com.example.dynamic;

import java.lang.annotation.;

@target({elementtype.method, elementtype.type})
@retention(retentionpolicy.runtime)
@documented
public @interface ds {
    string value() default datasourcecontextholder.primary;
}

package com.example.dynamic;

import org.aspectj.lang.proceedingjoinpoint;
import org.aspectj.lang.annotation.around;
import org.aspectj.lang.annotation.aspect;
import org.aspectj.lang.reflect.methodsignature;
import org.springframework.core.annotation.order;
import org.springframework.stereotype.component;

import java.lang.reflect.method;

@aspect
@component
@order(-1)  // 必须在 @transactional 之前执行
public class datasourceaspect {

    @around("@annotation(com.example.dynamic.ds) || @within(com.example.dynamic.ds)")
    public object around(proceedingjoinpoint point) throws throwable {
        string dskey = determinedatasource(point);
        try {
            datasourcecontextholder.set(dskey);
            return point.proceed();
        } finally {
            datasourcecontextholder.clear();
        }
    }

    private string determinedatasource(proceedingjoinpoint point) {
        methodsignature signature = (methodsignature) point.getsignature();
        method method = signature.getmethod();

        // 优先取方法上的注解
        ds ds = method.getannotation(ds.class);
        if (ds != null) {
            return ds.value();
        }
        // 再取类上的注解
        ds = point.gettarget().getclass().getannotation(ds.class);
        if (ds != null) {
            return ds.value();
        }
        return datasourcecontextholder.primary;
    }
}

package com.example.mapper;

import com.example.entity.user;
import java.util.list;

public interface usermapper {
    list<user> selectall();
}

package com.example.mapper;

import com.example.entity.order;
import java.util.list;

public interface ordermapper {
    list<order> selectall();
}

package com.example.mapper;

import com.example.entity.product;
import java.util.list;

public interface productmapper {
    list<product> selectall();
}

package com.example.service;

import com.example.dynamic.ds;
import com.example.dynamic.datasourcecontextholder;
import com.example.entity.;
import com.example.mapper.;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.service;

import java.util.list;

@service
public class dynamicservice {

    @autowired
    private usermapper usermapper;
    @autowired
    private ordermapper ordermapper;
    @autowired
    private productmapper productmapper;

    // 方式1：注解切换
    @ds(datasourcecontextholder.primary)
    public list<user> getusers() {
        return usermapper.selectall();
    }

    @ds(datasourcecontextholder.secondary)
    public list<order> getorders() {
        return ordermapper.selectall();
    }

    @ds(datasourcecontextholder.third)
    public list<product> getproducts() {
        return productmapper.selectall();
    }

    // 方式2：手动切换（适合复杂逻辑）
    public object querybysource(string source) {
        try {
            datasourcecontextholder.set(source);
            if ("primary".equals(source)) return usermapper.selectall();
            if ("secondary".equals(source)) return ordermapper.selectall();
            if ("third".equals(source)) return productmapper.selectall();
            return "未知数据源";
        } finally {
            datasourcecontextholder.clear();
        }
    }
}

package com.example.controller;

import com.example.service.dynamicservice;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.;
import java.util.;

@restcontroller
@requestmapping("/api")
public class dynamiccontroller {

    @autowired
    private dynamicservice service;

    @getmapping("/all")
    public map<string, object> all() {
        map<string, object> map = new linkedhashmap<>();
        map.put("primary_用户", service.getusers());
        map.put("secondary_订单", service.getorders());
        map.put("third_商品", service.getproducts());
        return map;
    }

    @getmapping("/switch")
    public object switchds(@requestparam string source) {
        return service.querybysource(source);
    }
}

<?xml version="1.0" encoding="utf-8"?>
<project>
    <parent>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-starter-parent</artifactid>
        <version>3.2.5</version>
    </parent>
    <groupid>com.example</groupid>
    <artifactid>multi-ds-dynamic</artifactid>
    <version>1.0.0</version>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</artifactid>
        </dependency>
        <!-- mybatis-plus（dynamic-datasource 最佳搭档） -->
        <dependency>
            <groupid>com.baomidou</groupid>
            <artifactid>mybatis-plus-spring-boot3-starter</artifactid>
            <version>3.5.6</version>
        </dependency>
        <!-- 多数据源核心 -->
        <dependency>
            <groupid>com.baomidou</groupid>
            <artifactid>dynamic-datasource-spring-boot3-starter</artifactid>
            <version>4.3.0</version>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupid>com.alibaba</groupid>
            <artifactid>druid-spring-boot-3-starter</artifactid>
            <version>1.2.21</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupid>com.mysql</groupid>
            <artifactid>mysql-connector-j</artifactid>
            <version>8.3.0</version>
        </dependency>
        <!-- postgresql -->
        <dependency>
            <groupid>org.postgresql</groupid>
            <artifactid>postgresql</artifactid>
            <version>42.7.3</version>
        </dependency>
        <dependency>
            <groupid>org.projectlombok</groupid>
            <artifactid>lombok</artifactid>
            <optional>true</optional>
        </dependency>
    </dependencies>
</project>

server:
  port: 8082

spring:
  datasource:
    dynamic:
      # 默认数据源
      primary: master
      # 严格模式（未匹配到数据源直接报错）
      strict: false
      # 懒加载（按需初始化数据源）
      lazy: true

      # ========== 全局 druid 配置 ==========
      druid:
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: select 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat,wall,slf4j
        # 监控
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/
          login-username: admin
          login-password: admin123
          allow: ""
        web-stat-filter:
          enabled: true
          url-pattern: /*
          exclusions: ".js,.gif,.jpg,.png,.css,.ico,/druid/*"
        filter:
          stat:
            enabled: true
            log-slow-sql: true
            slow-sql-millis: 2000
            merge-sql: true
          wall:
            enabled: true
            config:
              multi-statement-allow: true

      # ========== 数据源定义 ==========
      datasource:
        # 本地 mysql
        master:
          driver-class-name: com.mysql.cj.jdbc.driver
          url: jdbc:mysql://localhost:3306/db_primary?useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=asia/shanghai
          username: root
          password: root123456
          druid:
            initial-size: 10
            max-active: 50

        # postgresql
        postgres:
          driver-class-name: org.postgresql.driver
          url: jdbc:postgresql://localhost:5432/db_secondary?currentschema=public
          username: postgres
          password: pg123456
          druid:
            initial-size: 3
            max-active: 15

        # 阿里云 rds mysql
        aliyun:
          driver-class-name: com.mysql.cj.jdbc.driver
          url: jdbc:mysql://rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com:3306/db_cloud?usessl=true&servertimezone=asia/shanghai
          username: cloud_user
          password: cloud@2024!
          druid:
            initial-size: 10
            max-active: 50
            test-on-borrow: true
            keep-alive: true

mybatis-plus
mybatis-plus:
  mapper-locations: classpath:mapper/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.stdoutimpl

logging:
  level:
    com.baomidou.dynamic: debug

package com.example;

import org.mybatis.spring.annotation.mapperscan;
import org.springframework.boot.springapplication;
import org.springframework.boot.autoconfigure.springbootapplication;

@springbootapplication
@mapperscan("com.example.mapper")
public class dynamicdsapplication {
    public static void main(string[] args) {
        springapplication.run(dynamicdsapplication.class, args);
    }
}

package com.example.entity;

import com.baomidou.mybatisplus.annotation.;
import lombok.data;
import java.time.localdatetime;

@data
@tablename("t_user")
public class user {
    @tableid(type = idtype.auto)
    private long id;
    private string username;
    private string email;
    @tablefield(fill = fieldfill.insert)
    private localdatetime createtime;
}

@data
@tablename("t_order")
public class order {
    @tableid(type = idtype.auto)
    private long id;
    private long userid;
    private string orderno;
    private java.math.bigdecimal amount;
    private integer status;
    private localdatetime createtime;
}

@data
@tablename("t_product")
public class product {
    @tableid(type = idtype.auto)
    private long id;
    private string productname;
    private java.math.bigdecimal price;
    private integer stock;
}

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.basemapper;
import com.example.entity.user;

public interface usermapper extends basemapper<user> {
}

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.basemapper;
import com.example.entity.order;

public interface ordermapper extends basemapper<order> {
}

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.basemapper;
import com.example.entity.product;

public interface productmapper extends basemapper<product> {
}

package com.example.service;

import com.baomidou.dynamic.datasource.annotation.ds;
import com.baomidou.mybatisplus.extension.service.impl.serviceimpl;
import com.example.entity.;
import com.example.mapper.;
import org.springframework.stereotype.service;

import java.util.list;

// ========== 用户服务 → master（本地mysql） ==========
@service
@ds("master")
public class userservice extends serviceimpl<usermapper, user> {

    public list<user> listall() {
        return this.list();
    }
}

// ========== 订单服务 → postgres ==========
@service
@ds("postgres")
public class orderservice extends serviceimpl<ordermapper, order> {

    public list<order> listall() {
        return this.list();
    }

    public list<order> listbyuserid(long userid) {
        return this.lambdaquery().eq(order::getuserid, userid).list();
    }
}

// ========== 商品服务 → aliyun ==========
@service
@ds("aliyun")
public class productservice extends serviceimpl<productmapper, product> {

    public list<product> listall() {
        return this.list();
    }
}

package com.example.service;

import com.baomidou.dynamic.datasource.annotation.ds;
import com.example.entity.user;
import com.example.mapper.usermapper;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.service;
import java.util.list;

@service
public class mixedservice {

    @autowired
    private usermapper usermapper;

    // 默认走 master
    public list<user> getusers() {
        return usermapper.selectlist(null);
    }

    // 方法级别切换到 postgres
    @ds("postgres")
    public list<user> getusersfrompostgres() {
        return usermapper.selectlist(null);
    }

    // 方法级别切换到 aliyun
    @ds("aliyun")
    public list<user> getusersfromaliyun() {
        return usermapper.selectlist(null);
    }
}

package com.example.controller;

import com.example.service.;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.;
import java.util.;

@restcontroller
@requestmapping("/api")
public class testcontroller {

    @autowired
    private userservice userservice;
    @autowired
    private orderservice orderservice;
    @autowired
    private productservice productservice;

    @getmapping("/all")
    public map<string, object> all() {
        map<string, object> map = new linkedhashmap<>();
        map.put("master_本地mysql", userservice.listall());
        map.put("postgres_pg", orderservice.listall());
        map.put("aliyun_阿里云", productservice.listall());
        return map;
    }

    @postmapping("/user")
    public boolean adduser(@requestbody com.example.entity.user user) {
        return userservice.save(user);
    }

    @getmapping("/orders/{userid}")
    public object orders(@pathvariable long userid) {
        return orderservice.listbyuserid(userid);
    }
}

<?xml version="1.0" encoding="utf-8"?>
<project>
    <parent>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-starter-parent</artifactid>
        <version>3.2.5</version>
    </parent>
    <groupid>com.example</groupid>
    <artifactid>multi-ds-jpa</artifactid>
    <version>1.0.0</version>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</artifactid>
        </dependency>
        <!-- jpa -->
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-data-jpa</artifactid>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupid>com.alibaba</groupid>
            <artifactid>druid-spring-boot-3-starter</artifactid>
            <version>1.2.21</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupid>com.mysql</groupid>
            <artifactid>mysql-connector-j</artifactid>
            <version>8.3.0</version>
        </dependency>
        <!-- postgresql -->
        <dependency>
            <groupid>org.postgresql</groupid>
            <artifactid>postgresql</artifactid>
            <version>42.7.3</version>
        </dependency>
        <dependency>
            <groupid>org.projectlombok</groupid>
            <artifactid>lombok</artifactid>
            <optional>true</optional>
        </dependency>
    </dependencies>
</project>

server:
  port: 8083
spring:
  datasource:
    type: com.alibaba.druid.pool.druiddatasource
    # 本地 mysql
    primary:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://localhost:3306/db_primary?usessl=false&servertimezone=asia/shanghai
      username: root
      password: root123456
      initial-size: 5
      min-idle: 5
      max-active: 30
      max-wait: 60000
      validation-query: select 1
      test-while-idle: true
      filters: stat,wall,slf4j
    # postgresql
    secondary:
      driver-class-name: org.postgresql.driver
      url: jdbc:postgresql://localhost:5432/db_secondary
      username: postgres
      password: pg123456
      initial-size: 3
      min-idle: 3
      max-active: 20
      max-wait: 60000
      validation-query: select 1
      test-while-idle: true
      filters: stat,wall,slf4j
    # 阿里云 rds
    third:
      driver-class-name: com.mysql.cj.jdbc.driver
      url: jdbc:mysql://rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com:3306/db_cloud?usessl=true&servertimezone=asia/shanghai
      username: cloud_user
      password: cloud@2024!
      initial-size: 5
      min-idle: 5
      max-active: 40
      max-wait: 30000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: true
      filters: stat,wall,slf4j
      keep-alive: true
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org.hibernate.sql: debug

@springbootapplication(exclude = {druiddatasourceautoconfigure.class})
public class jpamultidsapplication {
    public static void main(string[] args) {
        springapplication.run(jpamultidsapplication.class, args);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import jakarta.persistence.entitymanagerfactory;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.boot.orm.jpa.entitymanagerfactorybuilder;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.context.annotation.primary;
import org.springframework.data.jpa.repository.config.enablejparepositories;
import org.springframework.orm.jpa.jpatransactionmanager;
import org.springframework.orm.jpa.localcontainerentitymanagerfactorybean;
import org.springframework.transaction.platformtransactionmanager;
import org.springframework.transaction.annotation.enabletransactionmanagement;

import javax.sql.datasource;
import java.util.hashmap;
import java.util.map;

@configuration
@enabletransactionmanagement
@enablejparepositories(
    basepackages = "com.example.repository.primary",
    entitymanagerfactoryref = "primaryentitymanagerfactory",
    transactionmanagerref = "primarytransactionmanager"
)
public class primaryjpaconfig {

    @primary
    @bean("primarydatasource")
    @configurationproperties(prefix = "spring.datasource.primary")
    public datasource primarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @primary
    @bean("primaryentitymanagerfactory")
    public localcontainerentitymanagerfactorybean primaryentitymanagerfactory(
            entitymanagerfactorybuilder builder,
            @qualifier("primarydatasource") datasource ds) {

        map<string, object> properties = new hashmap<>();
        properties.put("hibernate.dialect", "org.hibernate.dialect.mysqldialect");
        properties.put("hibernate.hbm2ddl.auto", "none");
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.format_sql", "true");

        return builder
                .datasource(ds)
                .packages("com.example.entity.primary")  // entity 扫描包
                .persistenceunit("primary")
                .properties(properties)
                .build();
    }

    @primary
    @bean("primarytransactionmanager")
    public platformtransactionmanager primarytransactionmanager(
            @qualifier("primaryentitymanagerfactory") entitymanagerfactory emf) {
        return new jpatransactionmanager(emf);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import jakarta.persistence.entitymanagerfactory;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.boot.orm.jpa.entitymanagerfactorybuilder;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.data.jpa.repository.config.enablejparepositories;
import org.springframework.orm.jpa.jpatransactionmanager;
import org.springframework.orm.jpa.localcontainerentitymanagerfactorybean;
import org.springframework.transaction.platformtransactionmanager;
import org.springframework.transaction.annotation.enabletransactionmanagement;

import javax.sql.datasource;
import java.util.hashmap;
import java.util.map;

@configuration
@enabletransactionmanagement
@enablejparepositories(
    basepackages = "com.example.repository.secondary",
    entitymanagerfactoryref = "secondaryentitymanagerfactory",
    transactionmanagerref = "secondarytransactionmanager"
)
public class secondaryjpaconfig {

    @bean("secondarydatasource")
    @configurationproperties(prefix = "spring.datasource.secondary")
    public datasource secondarydatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("secondaryentitymanagerfactory")
    public localcontainerentitymanagerfactorybean secondaryentitymanagerfactory(
            entitymanagerfactorybuilder builder,
            @qualifier("secondarydatasource") datasource ds) {

        map<string, object> properties = new hashmap<>();
        properties.put("hibernate.dialect", "org.hibernate.dialect.postgresqldialect");
        properties.put("hibernate.hbm2ddl.auto", "none");
        properties.put("hibernate.show_sql", "true");

        return builder
                .datasource(ds)
                .packages("com.example.entity.secondary")
                .persistenceunit("secondary")
                .properties(properties)
                .build();
    }

    @bean("secondarytransactionmanager")
    public platformtransactionmanager secondarytransactionmanager(
            @qualifier("secondaryentitymanagerfactory") entitymanagerfactory emf) {
        return new jpatransactionmanager(emf);
    }
}

package com.example.config;

import com.alibaba.druid.spring.boot3.autoconfigure.druiddatasourcebuilder;
import jakarta.persistence.entitymanagerfactory;
import org.springframework.beans.factory.annotation.qualifier;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.boot.orm.jpa.entitymanagerfactorybuilder;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.data.jpa.repository.config.enablejparepositories;
import org.springframework.orm.jpa.jpatransactionmanager;
import org.springframework.orm.jpa.localcontainerentitymanagerfactorybean;
import org.springframework.transaction.platformtransactionmanager;
import org.springframework.transaction.annotation.enabletransactionmanagement;

import javax.sql.datasource;
import java.util.hashmap;
import java.util.map;

@configuration
@enabletransactionmanagement
@enablejparepositories(
    basepackages = "com.example.repository.third",
    entitymanagerfactoryref = "thirdentitymanagerfactory",
    transactionmanagerref = "thirdtransactionmanager"
)
public class thirdjpaconfig {

    @bean("thirddatasource")
    @configurationproperties(prefix = "spring.datasource.third")
    public datasource thirddatasource() {
        return druiddatasourcebuilder.create().build();
    }

    @bean("thirdentitymanagerfactory")
    public localcontainerentitymanagerfactorybean thirdentitymanagerfactory(
            entitymanagerfactorybuilder builder,
            @qualifier("thirddatasource") datasource ds) {

        map<string, object> properties = new hashmap<>();
        properties.put("hibernate.dialect", "org.hibernate.dialect.mysqldialect");
        properties.put("hibernate.hbm2ddl.auto", "none");
        properties.put("hibernate.show_sql", "true");

        return builder
                .datasource(ds)
                .packages("com.example.entity.third")
                .persistenceunit("third")
                .properties(properties)
                .build();
    }

    @bean("thirdtransactionmanager")
    public platformtransactionmanager thirdtransactionmanager(
            @qualifier("thirdentitymanagerfactory") entitymanagerfactory emf) {
        return new jpatransactionmanager(emf);
    }
}

package com.example.entity.primary;

import jakarta.persistence.;
import lombok.data;
import java.time.localdatetime;

@data
@entity
@table(name = "t_user")
public class user {
    @id
    @generatedvalue(strategy = generationtype.identity)
    private long id;

    @column(nullable = false, length = 50)
    private string username;

    @column(length = 100)
    private string email;

    @column(name = "create_time")
    private localdatetime createtime;

    @prepersist
    public void prepersist() {
        this.createtime = localdatetime.now();
    }
}

package com.example.entity.secondary;

import jakarta.persistence.;
import lombok.data;
import java.math.bigdecimal;
import java.time.localdatetime;

@data
@entity
@table(name = "t_order")
public class order {
    @id
    @generatedvalue(strategy = generationtype.identity)
    private long id;

    @column(name = "user_id", nullable = false)
    private long userid;

    @column(name = "order_no", nullable = false, length = 64)
    private string orderno;

    @column(precision = 10, scale = 2)
    private bigdecimal amount;

    private integer status;

    @column(name = "create_time")
    private localdatetime createtime;

    @prepersist
    public void prepersist() {
        this.createtime = localdatetime.now();
    }
}

package com.example.entity.third;

import jakarta.persistence.;
import lombok.data;
import java.math.bigdecimal;

@data
@entity
@table(name = "t_product")
public class product {
    @id
    @generatedvalue(strategy = generationtype.identity)
    private long id;

    @column(name = "product_name", nullable = false, length = 200)
    private string productname;

    @column(precision = 10, scale = 2)
    private bigdecimal price;

    private integer stock;
}

package com.example.repository.primary;

import com.example.entity.primary.user;
import org.springframework.data.jpa.repository.jparepository;
import org.springframework.stereotype.repository;
import java.util.list;

@repository
public interface userrepository extends jparepository<user, long> {
    list<user> findbyusernamecontaining(string keyword);
}

com.example.repository.secondary.orderrepository

package com.example.repository.secondary;

import com.example.entity.secondary.order;
import org.springframework.data.jpa.repository.jparepository;
import org.springframework.stereotype.repository;
import java.util.list;

@repository
public interface orderrepository extends jparepository<order, long> {
    list<order> findbyuserid(long userid);
    list<order> findbystatus(integer status);
}

package com.example.repository.third;

import com.example.entity.third.product;
import org.springframework.data.jpa.repository.jparepository;
import org.springframework.data.jpa.repository.modifying;
import org.springframework.data.jpa.repository.query;
import org.springframework.data.repository.query.param;
import org.springframework.stereotype.repository;

@repository
public interface productrepository extends jparepository<product, long> {

    @modifying
    @query("update product p set p.stock = :stock where p.id = :id")
    int updatestock(@param("id") long id, @param("stock") integer stock);
}

package com.example.service;

import com.example.entity.primary.user;
import com.example.entity.secondary.order;
import com.example.entity.third.product;
import com.example.repository.primary.userrepository;
import com.example.repository.secondary.orderrepository;
import com.example.repository.third.productrepository;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.service;
import org.springframework.transaction.annotation.transactional;

import java.util.list;

@service
public class jpabusinessservice {

    @autowired
    private userrepository userrepository;
    @autowired
    private orderrepository orderrepository;
    @autowired
    private productrepository productrepository;

    // ===== 本地 mysql =====
    public list<user> getallusers() {
        return userrepository.findall();
    }

    @transactional(transactionmanager = "primarytransactionmanager")
    public user saveuser(user user) {
        return userrepository.save(user);
    }

    // ===== postgresql =====
    public list<order> getordersbyuserid(long userid) {
        return orderrepository.findbyuserid(userid);
    }

    @transactional(transactionmanager = "secondarytransactionmanager")
    public order saveorder(order order) {
        return orderrepository.save(order);
    }

    // ===== 阿里云 rds =====
    public list<product> getallproducts() {
        return productrepository.findall();
    }

    @transactional(transactionmanager = "thirdtransactionmanager")
    public void updatestock(long productid, integer stock) {
        productrepository.updatestock(productid, stock);
    }
}

package com.example.controller;

import com.example.entity.primary.user;
import com.example.service.jpabusinessservice;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.;
import java.util.*;

@restcontroller
@requestmapping("/api")
public class jpatestcontroller {

    @autowired
    private jpabusinessservice service;

    @getmapping("/all")
    public map<string, object> all() {
        map<string, object> map = new linkedhashmap<>();
        map.put("primary_mysql用户", service.getallusers());
        map.put("secondary_pg订单", service.getordersbyuserid(1l));
        map.put("third_阿里云商品", service.getallproducts());
        return map;
    }

    @postmapping("/user")
    public user adduser(@requestbody user user) {
        return service.saveuser(user);
    }
}

1. 排除自动配置
   @springbootapplication(exclude = {druiddatasourceautoconfigure.class})
   多数据源时必须排除，否则 druid 会尝试自动创建单个数据源

2. 事务不能跨库
   一个 @transactional 只能管一个数据源
   跨库一致性 → seata / 消息最终一致性

3. 阿里云 rds 注意
   - 开启 ssl：usessl=true
   - 开启 test-on-borrow（网络抖动多）
   - 开启 keep-alive（防止连接被 rds 防火墙断开）
   - 白名单配置你的服务器 ip

4. postgresql 注意
   - validation-query 用 select 1
   - 注意 schema 配置：currentschema=public
   - 自增用 bigserial / identity

5. druid 监控安全
   - 生产环境限制 allow ip
   - 修改默认账号密码
   - 关闭 resetenable