Sharding-JDBC--整合MybatisPlus多数据源--实例/示例

骑猪看日落 2022-10-26 03:49 681阅读 0赞

原文网址：[Sharding-JDBC--整合MybatisPlus多数据源--实例/示例\_IT利刃出鞘的博客-CSDN博客][Sharding-JDBC--_MybatisPlus_--_IT_-CSDN]

# 简介 #

**说明**

本文用实例介绍Sharding-JDBC与MybatisPlus多数据源的整合。

**为什么有了sharding还要配置多数据源？**

ShardingSphere是将多个数据源合并为一个统一的逻辑数据源。因此即使不分库分表的部分，不配置分片规则ShardingSphere即无法精确的断定应该路由至哪个数据源。 ShardingSphere提供了下边两种变通的方式（本文介绍第二种）

**方法一：配置default-data-source**

凡是在默认数据源中的表可以无需配置在分片规则中，ShardingSphere将在找不到分片数据源的情况下将表路由至默认数据源。  
缺点：若表不在默认数据源中，还是需要将表配置到分片规则中。除非将所有不需要进行分片的表都放在默认数据源里，那么倒是可以这么做，否则你就得一个一个配置表的分片规则了。

**方法二：将不参与分库分表的数据源独立于ShardingSphere之外**

在应用中使用多个数据源分别处理分片和不分片的情况。

优点：将不分片的数据源独立出来，开发者可以通过多数据源自主选择处理不同情况，仅需要简单配置后加个注解即可。

**mybatis-plus的动态数据源**

本文使用**mybatis-plus的动态数据源**来进行演示。使用它还有如下优点：

sharding jdbc对一些语法不支持，[官方文档][Link 1]里说的比较笼统，如下图：

![format_png][]

像insert into ... select这些语法是不支持的（对于没有涉及到分表的语句，也有同样的限制）例如，项目里有个SQL：insert into user\_temp select \* from user;在集成了sharding jdbc后，即使user表没有配置分表，执行该SQL也会报错。

# 实战 #

说明：本处实战与此文（[Sharding-JDBC系列--分库分表\_IT利刃出鞘的博客-CSDN博客][Sharding-JDBC_--_IT_-CSDN] ）用的同一个工程。

## 建库建表 ##

**建库**

创建两个库：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;

**结果**

![20210218112026702.png][]

## **依赖** ##

<?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/> 
        </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>
                
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.1</version>
            </dependency>
    
            
            <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和applicaiton-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

**applicaiton-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

**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

## 代码 ##

**配置**

将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]

数据库结果

![20210617003349462.png][]

**默认有异常的操作（正常回滚）**

先清空表。

访问：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]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.3.jar:5.3.3]
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.3.jar:5.3.3]
    	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.3.jar:5.3.3]
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.3.jar:5.3.3]
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.3.jar:5.3.3]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.3.jar:5.3.3]
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.3.jar:5.3.3]
    	at com.example.demo.controller.OrderController$$EnhancerBySpringCGLIB$$7d78795e.createOrderError(<generated>) ~[classes/:na]
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_201]
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_201]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_201]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_201]
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.3.jar:5.3.3]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.3.jar:5.3.3]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:888) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1597) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_201]
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_201]
    	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.41.jar:9.0.41]
    	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_201]

数据库结果

![20210617003731847.png][]

**指定数据库（写到指定数据库的不带后缀的表）**

访问：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]

数据库结果：

![20210617003957187.png][]

**使用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]

数据库结果：

![20210617004139602.png][]

再次访问：

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]

数据库结果：

![20210617004229509.png][]

# 其他网址 #

[ShardingSphere jdbc集成多数据源\_读钓的博客-CSDN博客\_shardingsphere 多数据源][ShardingSphere jdbc_-CSDN_shardingsphere]  
[SpringBoot集成ShardingJDBC系列【4】—— 集成多数据源动态切换\_Klayt的博客-CSDN博客][SpringBoot_ShardingJDBC_4_ _Klayt_-CSDN]  
[苞米豆多数据源管理\_雨不会一直下，但是一直会下雨-CSDN博客][-CSDN]

https://calm\_java.gitee.io/blog/2020/02/13/labs-springboot2-dynamic-datasource/

[Sharding-JDBC系列--分库分表\_IT利刃出鞘的博客-CSDN博客][Sharding-JDBC_--_IT_-CSDN]

[Sharding-JDBC--_MybatisPlus_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/113525692
[Link 1]: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/unsupported-items/
[format_png]: /images/20221024/89d2d1f9c9174575b933ad0ae2bb2284.png
[Sharding-JDBC_--_IT_-CSDN]: https://blog.csdn.net/feiying0canglang/article/details/113644602
[20210218112026702.png]: /images/20221024/82db2926db564cc38f1c0583ae973079.png
[20210617003349462.png]: /images/20221024/e7b040a2046249c4b0e95127be77cd22.png
[20210617003731847.png]: /images/20221024/248858a22bca44419ddc9bc8fa5fbaa5.png
[20210617003957187.png]: /images/20221024/60367e5f1b2f4079a0c2b0256080454d.png
[20210617004139602.png]: /images/20221024/9a0f998a580d46278363df4a8d32d2a3.png
[20210617004229509.png]: /images/20221024/d8976d38c06447a8bca577f30a39037e.png
[ShardingSphere jdbc_-CSDN_shardingsphere]: https://blog.csdn.net/youthsong/article/details/107743936
[SpringBoot_ShardingJDBC_4_ _Klayt_-CSDN]: https://blog.csdn.net/weixin_43260238/article/details/108411388
[-CSDN]: https://blog.csdn.net/weixin_42849706/article/details/108868208