所有分类
  • 所有分类
  • 未分类

Sharding-JDBC–整合MybatisPlus多数据源–方法/实例

简介

说明

本文用实例介绍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-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]

数据库结果

 默认有异常的操作(正常回滚)

先清空表。

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

数据库结果:

1

评论0

请先

显示验证码
没有账号?注册  忘记密码?

社交账号快速登录