ShardingSphere-JDBC (一)：分库分表

ShardingSphere-JDBC(一)：分库分表

目录

ShardingSphere-JDBC(一)：分库分表

创建数据库

引入依赖

配置YML

实体类

Mapper

测试

• 创建数据库

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `order_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4



DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `order_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

• 引入依赖

<!-- mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
<!-- shardingSphere 分库分表 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.0</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

• 配置YML

server:
  port: 8080
spring:
  application:
    name: sharding
  shardingsphere:
    datasource:
      names: master
      record:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/test
        username: root
        password: 123456
    rules:
      sharding:
          t_order:
            actual-data-nodes: master.t_order_$->{1..2}
            tableStrategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: table-inline
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake

        sharding-algorithms:
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2 +1}
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      sql-show: true

• 实体类

package com.test.sharding.entity;

import java.math.BigDecimal;

/**
 * @author Say Hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
public class OrderPO {

    private Long orderId;

    private Long userId;

    private BigDecimal orderPrice;

    public OrderPO() {
    }

    public OrderPO(Long orderId, Long userId, BigDecimal orderPrice) {
        this.orderId = orderId;
        this.userId = userId;
        this.orderPrice = orderPrice;
    }

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public BigDecimal getOrderPrice() {
        return orderPrice;
    }

    public void setOrderPrice(BigDecimal orderPrice) {
        this.orderPrice = orderPrice;
    }
}

• Mapper

package com.test.sharding.dao;

import com.test.sharding.entity.OrderPO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @author Say Hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
@Mapper
public interface OrderMapper {


    /**
     *
     * 创建订单，雪花算法自动生成主键
     */
    @Insert("insert into t_order(order_price,user_id)values(#{order.orderPrice },#{order.userId})")
    int insertOrder(@Param("order") OrderPO po);   

}

• 测试

package com.test.sharding.dao;

import com.test.sharding.dao.OrderMapper;
import com.test.sharding.entity.OrderPO;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.math.BigDecimal;

/**
 * @author Say Hello
 * @version 1.0.0
 * @Date 2023/7/18
 * @Description
 */
@SpringBootTest
class OrderMapperTest {

    @Resource
    OrderMapper orderMapper;

    @Test
    void testInsert() {
        for (int i = 1; i < 10; i++) {
            OrderPO po = new OrderPO();
            po.setUserId((long) i);
            po.setOrderPrice(new BigDecimal(i));
            orderMapper.insertOrder(po);
        }
    }
}