[ShardingSphere] springboot integrates shardingjdbc and uses the precise fragmentation algorithm to customize the sub database and sub table

The last two articles talked about two ways to divide databases and tables. These two ways can be summarized into one type, which are to divide data in the form of configuration. In this article, we continue to explain a new way to slice data. In addition to the form of configuration, sharding JDBC also supports self-defined rules through code.

Custom rules

Previously, we implemented id module and database and table by date. Here, we continue to divide the table by date in order to show the technology, but it is self-defined by code. Before we start to write code, we first define the rules of sub database and sub table.
Here we build two libraries DS0 and DS1. Create table t for each library_ order2021,t_order2022 has two tables. The statements are as follows:

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;

Construction works

When the basic environment is ready, we can start our project construction. Here, build a springboot project, and then integrate mybatis and sharding JDBC. Specific dependencies are as follows:

<properties>
    <java.version>1.8</java.version>
    <sharding-sphere.version>4.1.1</sharding-sphere.version>
</properties>
<dependencies>
    <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>
        <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>

Starting sharding configuration

Add configuration of mybatis and shardingjdbc

server.port=10080

spring.shardingsphere.datasource.names=ds0,ds1

# Configure the first database
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.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

# Configure the second database
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=root

# Configuration t_ Sub database strategy of order table
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=id
# Custom sub database policy
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.example.test.config.MyDbPreciseShardingAlgorithm

# Configuration t_ Table splitting strategy of order
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{2021..2022}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=day_date
# Custom table splitting policy
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.test.config.MyTablePreciseShardingAlgorithm

# Add t_ id generation strategy of order table
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:mapping/*.xml
mybatis.type-aliases-package=com.example.test.po

# Configure log level
logging.level.com.echo.shardingjdbc.dao=DEBUG

Add mapper scan configuration @ MapperScan("com.example.test.dao") of mybatis on the startup class

In the above configuration, we have defined the class path of custom configuration. Next, we will write the contents of these two custom configurations.

Writing custom rule classes

We have defined the rule at the beginning of the article. Now let's implement the rule. According to our rules, we can choose the accurate segmentation algorithm to implement. The specific code is as follows:

package com.example.test.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;

/**
 * Custom sub library rule class
 * @author echo
 * @date 2021/6/10 0010 10:09 am
 */
@Slf4j
public class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * Partition strategy
     *
     * @param availableTargetNames All data sources
     * @param shardingValue        SQL Fragment value passed in during execution
     * @return return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        // Real node
        availableTargetNames.forEach(item -> log.info("actual node db:{}", item));

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        //Accurate segmentation
        log.info("column value:{}", shardingValue.getValue());

        for (String each : availableTargetNames) {
            Long value = shardingValue.getValue();
            if (("ds" + value % 2).equals(each)) {
                return each;
            }
        }

        return null;
    }

}

package com.example.test.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;

/**
 * Custom table splitting rule class
 *
 * @author echo
 * @date 2021/6/10 0010 10:09 am
 */
@Slf4j
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        // Real node
        availableTargetNames.forEach(item -> log.info("actual node table:{}", item));

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        // Accurate segmentation
        log.info("column value:{}", shardingValue.getValue());

        for (String each : availableTargetNames) {
            if (("t_order" + shardingValue.getValue()).equals(each)) return each;
        }

        return null;
    }

}

Upper test code

According to the routine of the previous article, we write some test codes as follows:

package com.example.test.controller;

import com.example.test.po.TOrder;
import com.example.test.service.TOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @author echo
 * @date 2021/6/3 0003 16:37 PM
 */
@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") Long 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();
    }

}

public interface TOrderService {
    void save(TOrder tOrder);

    void delete(Long id);

    int update(TOrder tOrder);

    List<TOrder> getList();
}

@Service
public class TOrderServiceImpl implements TOrderService {

    @Autowired
    private TOrderDao tOrderDao;

    @Override
    public void save(TOrder tOrder) {
        tOrderDao.insert(tOrder);
    }

    @Override
    public void delete(Long id) {
        tOrderDao.delete(id);
    }

    @Override
    public int update(TOrder tOrder) {
        return tOrderDao.update(tOrder);
    }

    @Override
    public List<TOrder> getList() {
        return tOrderDao.getList();
    }

}

public interface TOrderDao {

    void insert(TOrder tOrder);

    List<TOrder> getList();

    void delete(Long id);

    int update(TOrder tOrder);
}

<?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.test.dao.TOrderDao">
    <resultMap id="BaseResultMap" type="com.example.test.po.TOrder">
        <id column="id" jdbcType="BIGINT" 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.test.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.test.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>

After that, we can test it

Call interface http://localhost:3306/order/save , we will find that our data enters the corresponding table according to our established rules

summary

During configuration, the version problem will have a certain impact on the configuration. Therefore, if the corresponding content is configured, pay attention to the official website configuration rules corresponding to the version information
Different rules correspond to different configuration rules. For example, the exact segmentation algorithm used here needs to find the configuration content of the corresponding exact segmentation algorithm, otherwise it will not take effect

Keywords: MySQL Spring shardingsphere shardingjdbc

Added by ltoso on Sun, 30 Jan 2022 13:26:27 +0200

Programming VIP