sharding jdbc 分库分表

---

sharding jdbc 分库分表

 

 

*******************************

示例

 

user：id、name、age

order：id、user_id

其中，user仅分表不分库，order分库分表

 

创建数据库、表：

db0：user_0、user_1，order_0、order_1

db1：order_0、order_1

 

 

*********************

配置文件

 

application.yml

spring:
  shardingsphere:
    datasource:
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
    sharding:
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: db$->{user_id%2}
      tables:
        user:
          actual-data-nodes: db0.user_${0..1}
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: user_$->{id%2}
        order:
          actual-data-nodes: db$->{0..1}.order_$->{0..1}
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: order_$->{id%2}

 

*********************

pojo 层

 

User

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="User对象", description="")
public class User extends Model<User> {

    private static final long serialVersionUID=1L;

    @TableId(value = "id", type = IdType.ID_WORKER)
    private Long id;

    private String name;

    private Integer age;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

 

Order

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="OrderItem对象", description="")
public class Order extends Model<OrderItem> {

    private static final long serialVersionUID=1L;

    @TableId(value = "id", type = IdType.ID_WORKER)
    private Long id;

    private Integer userId;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

 

*********************

controller 层

 

UserController

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;
    
    @RequestMapping("/save")
    public String save(){
        for(int i=0;i<10;i++){
            User user=new User();
            user.setName("瓜田李下"+i);
            user.setAge(i+10);
            
            userService.save(user);
        }
        
        return "success";
    }
}

 

OrderController

@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderService orderService;

    @RequestMapping("/save")
    public String save(){
        for(int i=0;i<10;i++){
            Order order=new Order();
            order.setUser(i+10);

            orderService.save(order);
        }

        return "success";
    }
}

 

 

****************************

插入测试

 

user表插入测试

                                

                                

 

order表插入测试