ShardingJDBC sub-database sub-table practice

preface

The previous article explained the principle of ShardingJDBC sub-library sub-table, which is integrated into the project as a jar package to enhance JDBC. The execution of SQL is mainly through the parsing engine, rewriting engine, execution engine, sorting engine and merging engine to execute and optimize SQL and result processing. Let’s do this in practice and look at the logs to see what’s going on.

Prepare the environment

Create two new databases, DS0 and DS1, locally, and execute the table building sentence on both databases

CREATE TABLE `t_order2021` (
  `id` bigint(32) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  `day_date` char(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_order2022` (
  `id` bigint(32) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  `day_date` char(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Then create a SpringBoot project using IDEA to integrate Mybatis and ShardingJDBC.

<? The XML version = "1.0" encoding = "utf-8"? > < project XMLNS = "http://maven.apache.org/POM/4.0.0" XMLNS: xsi = "http://www.w3.org/2001/XMLSchema-instance" Xsi: schemaLocation = "http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd" > < modelVersion > 4.0.0 < / modelVersion > < the parent > < groupId > org. Springframework. Boot < / groupId > The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 2.5.1 < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>shardingjdbc</artifactId> <version>0.0.1-SNAPSHOT</version> <name> ShardingJDBC </name> <description> Sharding JDBC Demo Project for Spring Boot < / description > < properties > < Java version > 1.8 < / Java version > The < sharding - sphere. Version > 4.4.1 < / sharding - sphere. Version > < / properties > < dependencies > < the dependency > <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> <! -- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.12.0</version> </dependency> <! -- https://mvnrepository.com/artifact/com.zaxxer/HikariCP --> <dependency> <groupId>com.zaxxer</groupId> The < artifactId > HikariCP < / artifactId > < version > 4.0.3 < / version > < / dependency > <! -- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> < artifactId > mysql connector - Java < / artifactId > < version > 8.0.25 < / version > < / dependency > <! -- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> </dependencies> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the code

The core configuration

Now we want to achieve this effect by sorting the t_ORDER table according to the parity of the primary key ids, routing records with even ids to ds0 and records with odd ids to DS1. Table t_ORDER is divided according to whether the value of day_date is 2021 or 2022. Without further ado, go straight to configuration

The configuration file

Server port = 10080 spring. Shardingsphere. The datasource. Names = ds0, ds1 # configuration database first spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0 Spring. Shardingsphere. The datasource. The ds0. Username = root spring. Shardingsphere. The datasource. The ds0. The second database password = 123456 # configuration spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1 spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=123456 # Depots strategy spring configuration t_order tables. Shardingsphere. Sharding. Name t_order. Database - strategy. Standard, sharding - column = id # custom depots strategy spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.example.shardi NGJDBC. Config. # MyDbPreciseShardingAlgorithm configuration t_order table strategy spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0.. 1}.t_order$->{2021.. 2022} spring. Shardingsphere. Sharding. Tables. T_order. Table - strategy. Standard, sharding - column = day_date # custom table strategy spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingj DBC. Config. MyTablePreciseShardingAlgorithm # add t_order table id generation strategy spring.shardingsphere.sharding.tables.t_order.key-generator.column=id Spring. Shardingsphere. Sharding. Tables. T_order. Key - generator. Type = # SNOWFLAKE open SQL output log Spring. Shardingsphere. Props. SQL. Show = true # mybatis configuration mybatis. Mapper - locations = classpath: mapper / *. XML Mybatis. Type - aliases - package = com. Example. Shardingjdbc. Po # logging.level.com.echo.shardingjdbc.dao=DEBUG configuration log levelCopy the code

This is the configuration in application.properties, if yamL is used, as follows

tables: t_order: actualDataNodes: ds$->{0.. 1}.t_order$->{2021.. 2022} databaseStrategy: standard: preciseAlgorithmClassName: com.example.shardingjdbc.config.MyDbPreciseShardingAlgorithm shardingColumn: id keyGenerator: column: id type: SNOWFLAKE logicTable: t_order tableStrategy: standard: preciseAlgorithmClassName: com.example.shardingjdbc.config.MyTablePreciseShardingAlgorithm shardingColumn: day_dateCopy the code

Custom sub-library rule class/sub-table rule class

package com.example.shardingjdbc.config; import lombok.extern.slf4j.Slf4j; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue; import java.util.Collection; @ Slf4j public class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm < Long > {/ * * * * @ param shard strategy AvailableTargetNames All data sources * @param preciseShardingValue Shard value passed during SQL execution * @return return */ @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue < Long > PreciseShardingValue) {/ / real nodes availableTargetNames forEach (a - > enter the info (" actual node db:{}", a)); log.info("logic table name:{}, route column:{}" , preciseShardingValue.getLogicTableName(), preciseShardingValue.getColumnName()); / / the precise divided info (" the column name: {} ", preciseShardingValue. The getValue ()); for (String availableTargetName : availableTargetNames) { Long value = preciseShardingValue.getValue(); if (("ds"+value%2).equals(availableTargetName)) { return availableTargetName; } } return null; }}Copy the code

package com.example.shardingjdbc.config; import lombok.extern.slf4j.Slf4j; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue; import java.util.Collection; @ Slf4j public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm < String > {/ * * * * @ param custom table rules availableTargetNames * @param preciseShardingValue * @return */ @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue < String > PreciseShardingValue) {/ / real nodes availableTargetNames forEach (a - > enter the info (" actual node table:{}", a)); log.info("logic table name:{}, route column:{}", preciseShardingValue.getLogicTableName(), preciseShardingValue.getColumnName()); / / the precise divided info (" the column value: {} ", preciseShardingValue. The getValue ()); for (String availableTargetName : availableTargetNames) { if (("t_order"+preciseShardingValue.getValue()).equals(availableTargetName)) { return availableTargetName; } } return null; }}Copy the code

The validation test

From the bottom up we define mapper. XML, mapper interface, Service class, Controller class

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.example.shardingjdbc.dao.TOrderDao"> <resultMap id="BaseResultMap" type="com.example.shardingjdbc.po.TOrder"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="user_id" jdbcType="INTEGER" property="userId"/> <result column="order_id" jdbcType="INTEGER" property="orderId"/> <result column="cloumn" jdbcType="VARCHAR" property="cloumn"/> <result column="day_date" jdbcType="CHAR" property="dayDate"/> </resultMap> <sql id="Base_Column_List"> id, user_id, order_id, cloumn, day_date </sql> <insert id="insert" parameterType="com.example.shardingjdbc.po.TOrder"> insert into t_order (user_id, order_id, cloumn, day_date) value (#{userId}, #{orderId}, #{cloumn}, #{dayDate}) </insert> <select id="getList" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from t_order </select> <delete id="delete" parameterType="java.lang.Long"> delete from t_order where id = #{id,jdbcType=BIGINT} </delete> <update id="update" parameterType="com.example.shardingjdbc.po.TOrder"> update t_order set cloumn = #{cloumn,jdbcType=VARCHAR}, order_id = #{orderId,jdbcType=INTEGER}, user_id = #{userId,jdbcType=INTEGER} where id = #{id,jdbcType=BIGINT} </update> </mapper>Copy the code

package com.example.shardingjdbc.dao;

import com.example.shardingjdbc.po.TOrder;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TOrderDao {

    void insert(TOrder tOrder);

    List<TOrder> getList();

    void delete(Integer id);

    int update(TOrder tOrder);
}
Copy the code

package com.example.shardingjdbc.service; import com.example.shardingjdbc.dao.TOrderDao; import com.example.shardingjdbc.po.TOrder; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class TOrderService { @Autowired private TOrderDao tOrderDao; public void save(TOrder tOrder) { tOrderDao.insert(tOrder); } public void delete(Integer id) { tOrderDao.delete(id); } public int update(TOrder tOrder) { return tOrderDao.update(tOrder); } public List<TOrder> getList() { return tOrderDao.getList(); }}Copy the code

package com.example.shardingjdbc.controller; import com.example.shardingjdbc.po.TOrder; import com.example.shardingjdbc.service.TOrderService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.util.List; @RestController @RequestMapping("/order") public class TOrderController { @Autowired private TOrderService tOrderService; @PostMapping("/save") public String save(@RequestBody TOrder tOrder) { tOrderService.save(tOrder); return "success"; } @PostMapping("/delete") public String delete(@RequestParam(value = "id") Integer id) { tOrderService.delete(id); return "success"; } @PostMapping("/update") public int update(@RequestBody TOrder tOrder) { return tOrderService.update(tOrder); } @GetMapping("/getList") public List<TOrder> getList() { return tOrderService.getList(); }}Copy the code

Then we started the project and tuned the interface test in Postman

Insert table T_ORDER2022 into ds0 library

Let’s open up the database

And then we insert another one

Insert table T_ORDER2021 into ds1 library

When the query interface is called

In the absence of sharding keys, shardingJDBC fetches data from various data sources

conclusion

ShardingJDBC for library table or very simple, configuration can be correct, this article is using custom sharding rules, need to implement custom sharding class, if the sharding rules are relatively simple, then you can directly define sharding rules in the configuration, that is simpler.