本文是在springboot整合分库分表的基础上添加了多数据源，建议先看上一篇shardingsphere5.x整合springboot分库分表实战_任人人人呢的博客-CSDN博客

pom.xml配置：

<!--shardingsphere分库分表依赖-->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
	<version>5.1.1</version>
</dependency>

<!--mybatis-plus依赖-->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.4.0</version>
</dependency>

<!-- dynamic多数据源 -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
	<version>3.1.1</version>
</dependency>

yml配置：

#shardingsphere5.x
spring:
  datasource:
    # 动态数据源配置
    dynamic:
      datasource:
        singleDb1:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/singleDb1?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
          username: root
          password: root
        singleDb2:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/singleDb2?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
          username: root
          password: root
      # 指定默认数据源名称
      primary: singleDb1
  shardingsphere:
    datasource:
      common:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      names: db0,db1
      db0:
        url: jdbc:mysql://localhost:3306/db0
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
      db1:
        url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
    #默认数据源，未分片的表默认执行库
    sharding:
        default-data-source-name: db1
    rules:
     sharding:
        key-generators:
          #此处必须要配置，否则会导致报错，因为shardingsphere-jdbc-core-spring-boot-starter需要加载此项配置，官网的demo例子有错
          #分布式序列算法：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/keygen/
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123
        sharding-algorithms:
          #分片算法：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
          table-inline:
            type: MOD
            props:
              sharding-count: 3
        tables:
          orders:
            # 配置orders表的分表的规则
            actual-data-nodes: db0.orders_$->{0..2}
            table-strategy:
              standard:
                sharding-column: order_no
                sharding-algorithm-name: table-inline
    enabled: true
    # 展示修改以后的sql语句
    props:
      sql-show: true


mybatis:
    configuration:
        map-underscore-to-camel-case: true
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    type-aliases-package: com.example.data.migrate.entity
    mapper-locations: classpath:mapper/*.xml

添加多数据源配置类：

package com.example.demo;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;

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


@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfiguration {

    /**
     * 动态数据源配置项
     * 这里会根据yml文件的配置自动加载配置，将多个数据源信息放到datasourceMap中
     */
    @Autowired
    private DynamicDataSourceProperties properties;

    /**
     * 使用shardingSphereDataSource 自动装载的 DataSource
     * 5.1.1版本自动装载的shardingSphereDataSource beanName="shardingSphereDataSource"
     * 要加@Lazy
     */
    @Lazy
    @Autowired
    private DataSource shardingSphereDataSource;


    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                // 这里createDataSourceMap是new的AbstractDataSourceProvider类中的方法
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(DBConstants.SHARDING, shardingSphereDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

添加多数据源常量类：

package com.example.demo;


public class DBConstants {

    /**
     * 数据源分组 - 单库1
     * 对应yml中的 spring.datasource.dynamic.datasource.singleDb1
     */
    public static final String SINGLE_DB1 = "singleDb1";

    /**
     * 数据源分组 - 单库2
     * 对应yml中的 spring.datasource.dynamic.datasource.singleDb2
     */
    public static final String SINGLE_DB2 = "singleDb2";

    /**
     * 数据源分组 - 分库分表
     */
    public static final String SHARDING = "sharding";
}

Mapper文件


public interface ConfigProductMapper {

    Map<String, Object> selectByPrimaryKey(Long id);
}



public interface FreeContractMapper {

    @DS(value = DBConstants.SINGLE_DB2)
    Map<String, Object> selectByPrimaryKey(Long id);
}



@DS(value = DBConstants.SHARDING)
public interface OrdersMapper extends BaseMapper<Orders> {

	Orders getOrderByNo(String orderNo);

	List<Orders> getOrderList();
}



public interface UsersMapper {

    @DS(value = DBConstants.SHARDING)
    Map<String, Object> selectByPrimaryKey(Long id);
}
@Slf4j
@RestController
public class TestController {

	@Resource
	private ConfigProductMapper configProductMapper;
	@Resource
	private FreeContractMapper freeContractMapper;
	@Resource
	private OrdersMapper ordersMapper;
	@Resource
	private UsersMapper usersMapper;

	@RequestMapping("/test")
	public String test() {
		//singleDb1
		Map<String, Object> product = configProductMapper.selectByPrimaryKey(103L);
		log.info("product:{}", JSONObject.toJSONString(product));

		//singleDb2
		Map<String, Object> freeContract = freeContractMapper.selectByPrimaryKey(4L);
		log.info("freeContract:{}", JSONObject.toJSONString(freeContract));

		//db0
		Orders order = ordersMapper.getOrderByNo("426246356356363457");
		log.info("order:{}", JSONObject.toJSONString(order));

		//db1
		Map<String, Object> user = usersMapper.selectByPrimaryKey(1L);
		log.info("user:{}", JSONObject.toJSONString(user));
		return "SUCCESS";
	}
}
@SpringBootApplication
@MapperScan(basePackages = "com.example.demo.mapper")
public class ShardingApplication {

	public static void main(String[] args) {
		SpringApplication.run(ShardingApplication.class, args);
		System.out.println("==========项目启动完成=========");
	}
}
CREATE TABLE `singleDb1`.`config_product` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `product_code` varchar(32) DEFAULT NULL COMMENT '编号',
    `product_name` varchar(250) DEFAULT NULL COMMENT '名称',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品配置表';


CREATE TABLE `singleDb2`.`free_contract` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `proposal_no` varchar(64) NOT NULL COMMENT '投保单号',
    `contract_status` char(1) NOT NULL COMMENT '签约状态，1：未签约 0：已签约',
    `signpksub_id` varchar(64) NOT NULL COMMENT '签约编号',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='免密签约信息表'


CREATE TABLE `db0`.`orders_0` (
    `id` bigint NOT NULL COMMENT '主键',
    `order_no` varchar(255) NOT NULL COMMENT '订单号',
    `user_id` bigint NOT NULL COMMENT '用户id',
    `pay_amount` decimal(20,2) NOT NULL DEFAULT '0' COMMENT '支付金额',
    `order_status` int NOT NULL COMMENT '订单状态 1-已预约 2-已取车 3-已还车 4-已取消',
    `pay_status` int NOT NULL DEFAULT '0' COMMENT '支付状态 0-未支付 1-支付成功',
    `pay_method` int DEFAULT NULL COMMENT '支付方式 1-微信 2-支付宝',
    `pay_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付时间',
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `db0`.`orders_1` (
    `id` bigint NOT NULL COMMENT '主键',
    `order_no` varchar(255) NOT NULL COMMENT '订单号',
    `user_id` bigint NOT NULL COMMENT '用户id',
    `pay_amount` decimal(20,2) NOT NULL DEFAULT '0' COMMENT '支付金额',
    `order_status` int NOT NULL COMMENT '订单状态 1-已预约 2-已取车 3-已还车 4-已取消',
    `pay_status` int NOT NULL DEFAULT '0' COMMENT '支付状态 0-未支付 1-支付成功',
    `pay_method` int DEFAULT NULL COMMENT '支付方式 1-微信 2-支付宝',
    `pay_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付时间',
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `db0`.`orders_2` (
    `id` bigint NOT NULL COMMENT '主键',
    `order_no` varchar(255) NOT NULL COMMENT '订单号',
    `user_id` bigint NOT NULL COMMENT '用户id',
    `pay_amount` decimal(20,2) NOT NULL DEFAULT '0' COMMENT '支付金额',
    `order_status` int NOT NULL COMMENT '订单状态 1-已预约 2-已取车 3-已还车 4-已取消',
    `pay_status` int NOT NULL DEFAULT '0' COMMENT '支付状态 0-未支付 1-支付成功',
    `pay_method` int DEFAULT NULL COMMENT '支付方式 1-微信 2-支付宝',
    `pay_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付时间',
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';


CREATE TABLE `db1`.`users` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `real_name` varchar(32) NOT NULL COMMENT '真实姓名',
    `password` varchar(200) NOT NULL COMMENT '密码',
    `mobile` varchar(100) NOT NULL DEFAULT '' COMMENT '手机号',
    `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

# spring boot # mybatis # java
Logo
2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

cover

2026年AI写长篇小说工具终极测评：5款热门工具横评，长篇选手到底选哪个

avatar 2048 AI社区
cover

Agent 上下文管理

avatar 2048 AI社区
cover

关于ISACA第五届数字信任大会两大权威文件

avatar 2048 AI社区