Sharding-JDBC简单使用

前言

sharding-jdbc 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

提供了 4 中配置方式：

• JAVA API
• Yaml
• SpringBoot
• Spring 命名空间（xml 配置文件）

代码

环境

jdk8，maven3.3.9，mysql5.7，springboot2.5.0

依赖

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>2.1.4</version>
	</dependency>
	<dependency>
		<groupId>org.apache.shardingsphere</groupId>
		<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		<version>4.0.0</version>
	</dependency>
	<dependency>
		<groupId>cn.hutool</groupId>
		<artifactId>hutool-all</artifactId>
		<version>5.6.5</version>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<optional>true</optional>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
</dependencies>

配置文件

server:
  port: 8080
  tomcat:
    uri-encoding: utf-8
  servlet:
    encoding:
      charset: UTF-8

logging:
  level:
    com.jcl.sharing: info

spring:
  application:
    name: sharing-jdbc-sample
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    # 仅包含非空的属性值
    default-property-inclusion: non_null
  # sharding 配置
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/sharding-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8
        username: root
        password: 123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.1.6:3306/shard-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8
        username: root
        password: 123456
    sharding:
      # 分片表配置
      tables:
        # 逻辑表名
        # 下面的没有自动提示，可以点击配置属性，会跳转到具体的自动配置文件中
        # 然后就会看到源码对应的配置类是什么，就知道有哪些属性可以配置了
        # 比如 org.apache.shardingsphere.core.yaml.config.sharding.YamlTableRuleConfiguration
        t_order:
          # 实际节点
          actual-data-nodes: ds$->{0..1}.t_order_$->{1..2}
          # 数据库分片策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds$->{user_id % 2}
          # 表分片策略
          # 由于 雪花 ID 是一个 long 类型的，所以可以使用行内表达式
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: t_order_$->{id % 2 + 1}
          # 主键生成策略
#          key-generator:
    #            column: id
    #            type: SNOWFLAKE
    #            worker.id: 100

    # 打印 sharding 的 sql 信息
    props:
      sql.show: true
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    # 打印sql
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: /mapper/*.xml

创建数据库和表

分别在两个 mysql 中创建数据库

1. 名称：sharding-order、shard-order
2. 编码：utf8mb4

CREATE TABLE `t_order_1` (
  `id` int(11) NOT NULL,
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_status` int(1) NOT NULL COMMENT '订单状态',
  `user_id` int(11) NOT NULL COMMENT '用户 iD',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

01:
sharding-order
	t_order_1
    t_order_2
  
02:
shard-order
	t_order_1
  	t_order_2

我们的需求如下：

1. 数据库切分：使用 user_id 取模，偶数分到 01，奇数分到 02 上
2. 表切分：再用 ID 取模，偶数分到 t_order_1，奇数分到 t_order_2 中

实体

import java.math.BigDecimal;

public class Order {
    private Integer id;

    private BigDecimal orderAmount;

    private Integer orderStatus;

    private Integer userId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public BigDecimal getOrderAmount() {
        return orderAmount;
    }

    public void setOrderAmount(BigDecimal orderAmount) {
        this.orderAmount = orderAmount;
    }

    public Integer getOrderStatus() {
        return orderStatus;
    }

    public void setOrderStatus(Integer orderStatus) {
        this.orderStatus = orderStatus;
    }

    public Integer getUserId() {
        return userId;
    }

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

dao

package com.jcl.sharing.mapper;

import com.jcl.sharing.bean.Order;
import com.jcl.sharing.bean.OrderExample;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface OrderMapper {

    int deleteByPrimaryKey(Integer id);

    int insert(Order record);

    Order selectByPrimaryKey(Integer id);
}

测试

import com.jcl.sharing.bean.Order;
import com.jcl.sharing.bean.OrderExample;
import com.jcl.sharing.mapper.OrderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

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

@SpringBootTest
class SharingJdbcSampleApplicationTests {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void contextLoads() {
        Order order = new Order();
        // 数据库：userId 偶数分到 sharding-order，奇数分到 shard-order
        order.setUserId(20);
        // 表：id 偶数分到 t_order_1, 奇数分到 t_order_2
        order.setId(2);
        order.setOrderAmount(BigDecimal.TEN);
        order.setOrderStatus(1);
        orderMapper.insertSelective(order);
    }

    @Test
    public void testSelectOrder() {
        OrderExample example = new OrderExample();
        example.createCriteria().andIdEqualTo(2).andUserIdEqualTo(20);
        List<Order> orders = orderMapper.selectByExample(example);
        for (Order order : orders) {
            System.err.println("id=" + order.getId() + " , userId=" + order.getUserId());
        }
    }
}

根据我们上面的策略来看，这条语句期望是插入到：sharding-order.t_order_1 中

下面的查询结果为：

==>  Preparing: select id, order_amount, order_status, user_id from t_order WHERE ( id = ? and user_id = ? ) 
==> Parameters: 2(Integer), 20(Integer)
<==    Columns: id, order_amount, order_status, user_id
<==        Row: 2, 10.00, 1, 20
<==      Total: 1

# 上面是 mybatis 的打印信息
# 下面的是我们自己打印的信息
id=2 , userId=20

代码仓库

https://gitee.com/thirtyleo/java_training_ground/tree/master/sharing-jdbc-sample

未完待续。。。

如果有问题可在评论区回复，作者会收到通知的！