简介
说明
本文用实例介绍Sharding-JDBC与MybatisPlus多数据源的整合。
为什么有了sharding还要配置多数据源？
ShardingSphere是将多个数据源合并为一个统一的逻辑数据源。因此即使不分库分表的部分，不配置分片规则ShardingSphere即无法精确的断定应该路由至哪个数据源。 ShardingSphere提供了下边两种变通的方式（本文介绍第二种）
方法一：配置default-data-source
凡是在默认数据源中的表可以无需配置在分片规则中，ShardingSphere将在找不到分片数据源的情况下将表路由至默认数据源。
缺点：若表不在默认数据源中，还是需要将表配置到分片规则中。除非将所有不需要进行分片的表都放在默认数据源里，那么倒是可以这么做，否则你就得一个一个配置表的分片规则了。
方法二：将不参与分库分表的数据源独立于ShardingSphere之外
在应用中使用多个数据源分别处理分片和不分片的情况。
优点：将不分片的数据源独立出来，开发者可以通过多数据源自主选择处理不同情况，仅需要简单配置后加个注解即可。
mybatis-plus的动态数据源
本文使用mybatis-plus的动态数据源来进行演示。使用它还有如下优点：
sharding jdbc对一些语法不支持，官方文档里说的比较笼统，如下图：
像insert into … select这些语法是不支持的（对于没有涉及到分表的语句，也有同样的限制）例如，项目里有个SQL：insert into user_temp select * from user;在集成了sharding jdbc后，即使user表没有配置分表，执行该SQL也会报错。
实战
说明：本处实战与此文（Sharding-JDBC-分库分表-使用 – 自学精灵 ）用的同一个工程。
源码下载
建库建表
建库
创建两个库：sharding-jdbc0、sharding-jdbc1
建表
在上边两个库里按下边这个结构创建表，以t_order_0和t_order_1命名。另外，在sharding-jdbc0中单独创建一个t_order，也用下边的建表语句。
CREATE TABLE `t_order` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `user_id` bigint(11) NULL DEFAULT NULL COMMENT '用户id', `product_id` bigint(11) NULL DEFAULT NULL COMMENT '产品id', `count` int(11) NULL DEFAULT NULL COMMENT '数量', `money` decimal(11, 0) NULL DEFAULT NULL COMMENT '金额', `status` int(1) NULL DEFAULT NULL COMMENT '订单状态：0：创建中；1：已完结', `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0), PRIMARY KEY (`id`) ) ENGINE = InnoDB;
结果
依赖
<?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>2.4.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<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>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!-- <version>8.0.21</version> 版本Spring-Boot-Parent中已带 -->
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置
说明
只使用下边的application.yml和application-mbp_dynamic.yml便可以通过@DS(“xxx”)注解来动态的选择数据源了。本处：xxx可以写master0或者master1。
如果想将Sharding数据源也加入到dynamic-datasource的管理中，加入application-sharding_jdbc.yml和DataSourceConfiguration.java。
application.yml
server:
port: 9011
spring:
application:
name: order
profiles:
include: sharding_jdbc,mbp_dynamic
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
application-mbp_dynamic.yml
spring:
datasource:
dynamic:
datasource:
master0:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 222333
master1:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 222333
primary: master0 # 默认数据源名称。默认是master
application-sharding_jdbc.yml
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 222333
type: com.zaxxer.hikari.HikariDataSource
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 222333
type: com.zaxxer.hikari.HikariDataSource
sharding:
tables:
t_order:
#key-generator:
# column: id
# type: SNOWFLAKE
actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
database-strategy:
inline:
sharding-column: id
algorithm‐expression: ds$->{id % 2}
table-strategy:
inline:
sharding-column: id
algorithm‐expression: t_order_$->{id % 2}
props:
sql:
show: true # 日志显示SQL
代码
配置
将Sharding数据源也加入到dynamic-datasource的管理中。
package com.example.demo.config;
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.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractDataSourceAdapter;
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.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源配置：
*
* 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解，切换数据源
*
* <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
*/
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
SpringBootConfiguration.class})
public class DataSourceConfiguration {
// 分表数据源名称
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
//动态数据源配置项
@Autowired
private DynamicDataSourceProperties properties;
/**
* shardingjdbc有四种数据源，需要根据业务注入不同的数据源
*
* <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>2. 主从数据源: masterSlaveDataSource;
* <p>3. 脱敏数据源：encryptDataSource;
* <p>4. 影子数据源：shadowDataSource
*/
@Lazy
@Resource(name = "shardingDataSource")
AbstractDataSourceAdapter shardingDataSource;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
*/
@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;
}
}
Controller
本处只贴controller，其他都是代码生成器生成的，没有啥业务逻辑。
package com.example.demo.controller;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.example.demo.entity.Order;
import com.example.demo.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/*
http://localhost:9100/order/createOrder?id=1&userId=1&productId=1&count=10&money=100
*/
@RestController
@RequestMapping("/order")
public class OrderController {
@Autowired
OrderService orderService;
@PostMapping("createOrder")
public String createOrder(Order order) {
orderService.save(order);
return "success";
}
@PostMapping("createOrderError")
@Transactional
public String createOrderError(Order order) {
orderService.save(order);
order.setId(order.getId() + 1);
orderService.save(order);
int i = 1 / 0;
return "success";
}
//指定数据库
@PostMapping("createOrderSpecific")
@DS("master0")
public String createOrderSpecific(Order order) {
orderService.save(order);
return "success";
}
//mybatis-plus 使用sharding-jdbc的分库分表配置
@PostMapping("createOrderSharding")
@DS("sharding")
public String createOrderSharding(Order order) {
orderService.save(order);
return "success";
}
}
测试
插入到默认数据库
默认无异常的操作。
访问：http://localhost:9100/order/createOrder?id=1&userId=1&productId=1&count=10&money=100
后端结果：
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a126d70] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@1154849346 wrapping com.mysql.cj.jdbc.ConnectionImpl@1515d74a] will not be managed by Spring ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 1(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a126d70]
数据库结果
默认有异常的操作（正常回滚）
先清空表。
访问：http://localhost:9100/order/createOrderError?id=1&userId=1&productId=1&count=10&money=100
后端结果：
Creating a new SqlSession Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] JDBC Connection [HikariProxyConnection@1684993668 wrapping com.mysql.cj.jdbc.ConnectionImpl@3ff0faf0] will be managed by Spring ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 1(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null <== Updates: 1 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] from current transaction ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 2(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null <== Updates: 1 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ce91272] 2021-06-17 00:36:54.004 ERROR 9264 --- [nio-9100-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.ArithmeticException: / by zero] with root cause java.lang.ArithmeticException: / by zero at com.example.demo.controller.OrderController.createOrderError(OrderController.java:33) ~[classes/:na] at com.example.demo.controller.OrderController$$FastClassBySpringCGLIB$$1fa10612.invoke(<generated>) ~[classes/:na] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.3.jar:5.3.3] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.3.jar:5.3.3] ...
数据库结果（数据库正常回滚，没有插入数据）
指定数据库
写到指定数据库的不带后缀的表。
访问：http://localhost:9100/order/createOrderSpecific?id=1&userId=1&productId=1&count=10&money=100
后端结果：
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f1fbf3b] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@1382212482 wrapping com.mysql.cj.jdbc.ConnectionImpl@5221693d] will not be managed by Spring ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 1(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f1fbf3b]
数据库结果：
sharding-jdbc的分库分表
访问：
http://localhost:9100/order/createOrderSharding?id=1&userId=1&productId=1&count=10&money=100
后端结果：
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@381ee8b2] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@57c19961] will not be managed by Spring ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 1(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null 2021-06-17 00:40:53.194 INFO 13244 --- [nio-9100-exec-2] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) 2021-06-17 00:40:53.195 INFO 13244 --- [nio-9100-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@7684bcdf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2c361094), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2c361094, columnNames=[id, user_id, product_id, count, money, create_time, update_time], insertValueContexts=[InsertValueContext(parametersCount=7, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=117, stopIndex=117, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=120, stopIndex=120, parameterMarkerIndex=6)], parameters=[1, 1, 1, 10, 100, null, null])], generatedKeyContext=Optional.empty) 2021-06-17 00:40:53.195 INFO 13244 --- [nio-9100-exec-2] ShardingSphere-SQL : Actual SQL: ds1 ::: INSERT INTO t_order_1 ( id, user_id, product_id, count, money, create_time, update_time ) VALUES (?, ?, ?, ?, ?, ?, ?) ::: [1, 1, 1, 10, 100, null, null] <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@381ee8b2]
数据库结果：
再次访问：
http://localhost:9100/order/createOrderSharding?id=2&userId=1&productId=1&count=10&money=100
后端结果：
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a4fa52c] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@14885815] will not be managed by Spring ==> Preparing: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: 2(Long), 1(Long), 1(Long), 10(Integer), 100(BigDecimal), null, null 2021-06-17 00:41:56.473 INFO 13244 --- [nio-9100-exec-6] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id, user_id, product_id, count, money, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) 2021-06-17 00:41:56.473 INFO 13244 --- [nio-9100-exec-6] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@7684bcdf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@10b7ce5c), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@10b7ce5c, columnNames=[id, user_id, product_id, count, money, create_time, update_time], insertValueContexts=[InsertValueContext(parametersCount=7, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=117, stopIndex=117, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=120, stopIndex=120, parameterMarkerIndex=6)], parameters=[2, 1, 1, 10, 100, null, null])], generatedKeyContext=Optional.empty) 2021-06-17 00:41:56.473 INFO 13244 --- [nio-9100-exec-6] ShardingSphere-SQL : Actual SQL: ds0 ::: INSERT INTO t_order_0 ( id, user_id, product_id, count, money, create_time, update_time ) VALUES (?, ?, ?, ?, ?, ?, ?) ::: [2, 1, 1, 10, 100, null, null] <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a4fa52c]
数据库结果：
请先 ！