shardingsphere 集成springboot【水平分表】

创建sharding_sphere数据库

在数据库中创建两张表，t_order_1和t_order_2

分片规则：如果订单编号是偶数添加到t_order_1,如果是奇数添加到t_order_2

创建实体类

public class Order {  
  
    private Integer id;  
    private Integer orderType;  
    private Integer customerId;  
    private Double amount;  
  
    public Integer getId() {  
        return id;  
    }  
  
    public void setId(Integer id) {  
        this.id = id;  
    }  
  
    public Integer getOrderType() {  
        return orderType;  
    }  
  
    public void setOrderType(Integer orderType) {  
        this.orderType = orderType;  
    }  
  
    public Integer getCustomerId() {  
        return customerId;  
    }  
  
    public void setCustomerId(Integer customerId) {  
        this.customerId = customerId;  
    }  
  
    public Double getAmount() {  
        return amount;  
    }  
  
    public void setAmount(Double amount) {  
        this.amount = amount;  
    }  
  
    @Override  
    public String toString() {  
        return "Order{" +  
                "id=" + id +  
                ", orderType='" + orderType + '\'' +  
                ", customerId=" + customerId +  
                ", amount=" + amount +  
                '}';  
    }  
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

创建Mapper

@Repository  
@Mapper  
public interface OrderMapper {  
  
    @Insert("insert into t_order(order_type,customer_id,amount) values(#{orderType},#{customerId},#{amount})")  
    public void insert(Order orders);  
  
    @Select("select * from t_order where id = #{id}")  
    @Results({  
            @Result(property = "id",column = "id"),  
            @Result(property = "orderType",column = "order_type"),  
            @Result(property = "customerId",column = "customer_id"),  
            @Result(property = "amount",column = "amount")  
    })  
    public Order selectOne(Integer id);
}    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

创建配置文件

spring:  
  shardingsphere:  
    datasource: #数据源配置  
      names: ds1  
      ds1: #数据源0  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false  
        username: root  
        password: wsrbb  
    sharding:  
      tables:  
        t_order:  
          actual-data-nodes: ds1.t_order_${1..2}   #指定orders表的分布情况，配置表在哪个数据库中，表名称是什么  
          # 雪花算法  
          key-generator:  
            column: id  
            type: SNOWFLAKE  
            props:  
              worker-id: 123  
              max-vibration-offset: 3  
          ##指定分片策略。根据id的奇偶性来判断插入到哪个表  
          table-strategy:  
            inline:  
              sharding-column: id  
              algorithm-expression: t_order_${id % 2 +1}  
      #打开sql输出日志  
      props:  
        sql:  
          show: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

运行测试类即可

@Test  
public void addOrders(){  
    for (int i = 1; i <=10 ; i++) {  
        Order orders = new Order();  
        //orders.setId(i);  
        orders.setCustomerId(new Random().nextInt(10));  
        orders.setOrderType(i);  
        orders.setAmount(1000.0*i);  
        orderMapper.insert(orders);  
    }  
}

1
2
3
4
5
6
7
8
9
10
11
12