SpringBoot 整合 ShardingSphere

前言

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

POM依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.4</version>
    </parent>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.11</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.com.xiaobai.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>

移除Drud自动配置

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class )
public class ShardingsphereApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingsphereApplication.class, args);
    }
}

水平分表

创建数据库lg_order，建表语句

-- ----------------------------
-- Table structure for pay_order_1
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_1`;
CREATE TABLE `pay_order_1`  (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for pay_order_2
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_2`;
CREATE TABLE `pay_order_2`  (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置代码

mybatis:
  configuration:
    map-underscore-to-camel-case: true
server:
  servlet:
    context-path: /
    encoding:
      charset: UTF-8
      enabled: true
      force: true
spring:
  application:
    description: 水平分表 （单库）
    name: sharding-jdbc-one
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      db1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: 123456;a
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/lg_order?characterEncoding=UTF-8&useSSL=false
        username: root
      names: db1
    props:
      sql:
        show: true
    sharding:
      tables:
        pay_order:
          actual-data-nodes: db1.pay_order_$->{1..2}
          key-generator:
            type: SNOWFLAKE
            column: order_id
          tablestrategy:
            inline:
              algorithm-expression: pay_order_$->{order_id % 2 + 1}
              sharding-column: order_id

编写Mapper

@Component
@Mapper
public interface OrderMapper {
    @Insert("insert into pay_order(user_id,product_name,count) values(#{userId},#{productName},#{count})")
    Long insert(OrderVO orderVO);

    /**
     * 查询订单
     */
    @Select({"<script>" +
            "select * from pay_order p where p.order_id in " +
            "<foreach collection='orderIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +
            "limit 1" +
            "</script>"})
    List<Map<String, Object>> findOrderByIds(@Param("orderIds") List<Long> orderIds);

    /**
     * 查询订单
     */
    @Select("select * from pay_order order by user_id asc")
    List<Map<String, Object>> list();
}

测试

@SpringBootTest()
public class OrderServiceTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void test1() {
        for (long i = 11; i < 20; i++) {
            OrderVO orderVO = new OrderVO();
            orderVO.setCount(1);
            orderVO.setUserId(i);
            orderVO.setProductName("产品" + i);

            orderMapper.insert(orderVO);
        }
    }

    @Test
    public void test2() {
        List<Long> ids = List.of(786541544898297857L,786541545091235841L,786541544923463680L);
        System.out.println(JSON.toJSONString(orderMapper.findOrderByIds(ids)));
    }

    @Test
    public void test3() {
        System.out.println(JSON.toJSONString(orderMapper.list()));
    }
}

insert结果

水平分库

创建数据库lg_order1, lg_order2

-- ----------------------------
-- Table structure for pay_order_1
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_1`;
CREATE TABLE `pay_order_1`  (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for pay_order_2
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_2`;
CREATE TABLE `pay_order_2`  (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置代码

mybatis:
  configuration:
    map-underscore-to-camel-case: true
server:
  servlet:
    context-path: /
    encoding:
      charset: UTF-8
      enabled: true
      force: true
spring:
  application:
    description: 水平分库
    name: sharding-jdbc-two
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      db1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: 123456;a
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/lg_order_1?characterEncoding=UTF-8&useSSL=false
        username: root
      db2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        password: 123456;a
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/lg_order_2?characterEncoding=UTF-8&useSSL=false
        username: root
      names: db1,db2
    props:
      sql:
        show: true
    sharding:
      tables:
        pay_order:
          databasestrategy:
            inline:
              algorithm-expression: db$->{user_id % 2 + 1}
              sharding-column: user_id
          actual-data-nodes: db$->{1..2}.pay_order_$->{1..2}
          key-generator:
            type: SNOWFLAKE
            column: order_id
          tablestrategy:
            inline:
              algorithm-expression: pay_order_$->{order_id % 2 + 1}
              sharding-column: order_id

测试

@SpringBootTest()
public class OrderServiceTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void test1() {
        for (long i = 11; i < 20; i++) {
            OrderVO orderVO = new OrderVO();
            orderVO.setCount(1);
            orderVO.setUserId(i);
            orderVO.setProductName("产品" + i);

            orderMapper.insert(orderVO);
        }
    }

    @Test
    public void test2() {
        List<Long> ids = List.of(786541544898297857L,786541545091235841L,786541544923463680L);
        System.out.println(JSON.toJSONString(orderMapper.findOrderByIds(ids)));
    }

    @Test
    public void test3() {
        System.out.println(JSON.toJSONString(orderMapper.list()));
    }
}

insert 结果

写累了，，，后面再写