In the above article, we have implemented SpringBoot integration shardingJDBC + Mybatis, wherein we choose to divide the library and table according to the ID of the module to carry out the library and table. This is a classic way, but there are many other ways, such as dividing the database into tables by year, month and day. This article will elaborate on the breakdown table by year, month and day

Environment to prepare

In business development, there are requirements to put several years of data into a database, but the current database has many tables to indicate that it is built by year and month. In order to demonstrate the technology, we will build a database according to a year, and the database corresponding to a year will build 365 tables according to the date. I’ve set up two libraries here

The table structure for each library is as follows:

CREATE TABLE `test_20210601` (
  `id` bigint(32) NOT NULL,
  `year_value` char(4) DEFAULT NULL COMMENT 'Year value',
  `day_value` char(8) DEFAULT NULL COMMENT 'Date value',
  `content` varchar(255) DEFAULT NULL COMMENT 'content'.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8; .CREATE TABLE `test_20210610` (
  `id` bigint(32) NOT NULL,
  `year_value` char(4) DEFAULT NULL COMMENT 'Year value',
  `day_value` char(8) DEFAULT NULL COMMENT 'Date value',
  `content` varchar(255) DEFAULT NULL COMMENT 'content'.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

This is close to presentation, so only 10 tables are created

Configure a database and table division policy

server.port=10080

spring.shardingsphere.datasource.names=ds2021,ds2022
Configure the first database
spring.shardingsphere.datasource.ds2021.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2021.jdbc-url=jdbc:mysql://localhost:3306/ds2021
spring.shardingsphere.datasource.ds2021.username=root
spring.shardingsphere.datasource.ds2021.password=root
Configure the second database
spring.shardingsphere.datasource.ds2022.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2022.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2022.jdbc-url=jdbc:mysql://localhost:3306/ds2022
spring.shardingsphere.datasource.ds2022.username=root
spring.shardingsphere.datasource.ds2022.password=root
SQL > alter table test
spring.shardingsphere.sharding.tables.test.database-strategy.inline.sharding-column=year_value
spring.shardingsphere.sharding.tables.test.database-strategy.inline.algorithm-expression=ds$->{year_value}
SQL > alter table trans_channel
spring.shardingsphere.sharding.tables.test.actual-data-nodes=ds$->{2021.. 2022}.test_$->{20210601.. 20210610}
spring.shardingsphere.sharding.tables.test.table-strategy.inline.sharding-column=day_value
spring.shardingsphere.sharding.tables.test.table-strategy.inline.algorithm-expression=test_$->{day_value}
Add id generation policy for trans_channel table
spring.shardingsphere.sharding.tables.test.key-generator.column=id
spring.shardingsphere.sharding.tables.test.key-generator.type=SNOWFLAKE
Copy the code

test

We’re going to do what we did before, we’re going to generate some code, and we’re going to test it. The introduction of shardingjdbc and the integration of mybatis will not be repeated here, but you can check out the previous two articles if you are not clear

@RestController
@RequestMapping(value = "/test")
public class Test {

    @Autowired
    private TestService testService;

    @GetMapping(value = "/save")
    public String save(a) {
        testService.save();
        return "success"; }}public interface TestService {

    void save(a);

}

public class TestServiceImpl implements TestService {

    @Autowired
    private TestDao testDao;

    @Override
    public void save(a) {
        Test test = new Test();
        test.setDayValue("20210602");
        test.setYearValue("2021"); testDao.save(test); }}public interface TestDao {

    int save(Test test); } <? 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.echo.shardingjdbc.dao.TestDao">
    <resultMap id="BaseResultMap" type="com.echo.shardingjdbc.po.Test">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="year_value" jdbcType="CHAR" property="yearValue"/>
        <result column="day_value" jdbcType="CHAR" property="dayValue"/>
        <result column="content" jdbcType="VARCHAR" property="content"/>
    </resultMap>
    <sql id="Base_Column_List">
    id, year_value, day_value, content
  </sql>

    <insert id="save" parameterType="com.echo.shardingjdbc.po.Test">
    insert into test (year_value, day_value, content)
    values (#{yearValue,jdbcType=CHAR}, #{dayValue,jdbcType=CHAR}, #{content,jdbcType=VARCHAR})
  </insert>
</mapper>
Copy the code

Call interfacehttp://localhost:10080/test/save, we can see the final data entered ds2021 library 20210602 tables

We can change our save values according to the rules that we defined at the time of configuration, so that we can effectively store the data in different libraries, different tables.

conclusion

  • Test_ $->{day_value}, which represents the date value, is the suffix of our table
  • Actual-data-nodes describes a true value range. If none exists, an error is reported