SpringBoot integrates sharding-jdbc to implement library and table operations

1. Introduction

Sharding-JDBC is an open source distributed database middleware designed to simplify the implementation of Sharding. It distributes data into multiple databases or tables in a transparent way, while providing a development experience consistent with native JDBC. Sharding-JDBC is one of the submodules of the Apache ShardingSphere project and is widely used in high concurrency and large data volume scenarios.

Core functions

1. Library and table:

Support horizontal library and horizontal tables, scattering data into multiple databases or tables, and improving system performance and storage capabilities.

Supports multiple sharding strategies, such as by range, hash, time, etc.

2. Reading and writing separation:

It supports read and write separation of master-slave architecture, routes read operations to slave libraries, and routes write operations to master libraries, reducing the pressure on master libraries.

3. Distributed transactions:

Provides distributed transaction support based on XA and flexible transactions (Saga, TCC) to ensure data consistency.

4. Data desensitization:

Supports encryption and desensitization of sensitive data to ensure data security.

5. Multi-data source management:

Supports dynamic data source configuration and management, which facilitates expansion and maintenance.

6. SQL Compatibility:

Supports most SQL syntax and is compatible with mainstream databases (such as MySQL, PostgreSQL, Oracle, etc.).

Core concept

1. Logic Table:

Virtual table names used in development, such as order.

2. Actual Table:

Tables that actually exist in the database, such as order_0, order_1.

3. Data Node:

The smallest unit of data sharding, consisting of the data source name and the real table, such as ds0.order_0.

4. Sharding Key:

Fields used for sharding, such as user_id in the order table.

5. Sharding Algorithm:

Defines how to route data to different databases or tables based on shard keys.

6. Binding Table:

Tables with the same sharding rules, such as order and order_item, can avoid cross-table queries.

Architectural design

Sharding-JDBC is designed with a lightweight architecture that is directly embedded in the application without the need for additional middleware deployment. Its core components include:

SQL parsing engine: parse SQL statements and extract shard keys.
Routing engine: Route SQL to the correct data node according to sharding rules.
Rewrite engine: Rewrite logical SQL to real SQL.
Execution Engine: Execute SQL and merge results.

Use scenarios

1. High concurrency scenario:

Improve the system's concurrent processing capabilities through library and table division.

2. Big data scenarios:

Solve the problem of excessive amount of data in a single table through data sharding.

3. Read and write separation scenario:

Improve the read performance of the database through read and write separation.

4. Multi-tenant scenario:

Multi-tenant data isolation is achieved through library and table separation.

2. Practical process

2.1.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0" xmlns:xsi="http:///2001/XMLSchema-instance"
         xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.">
  <modelVersion>4.0.0</modelVersion>

  <artifactId>spring-boot-demo-sharding-jdbc</artifactId>
  <version>1.0.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>spring-boot-demo-sharding-jdbc</name>
  <description>Demo project for Spring Boot</description>

  <parent>
    <groupId></groupId>
    <artifactId>spring-boot-demo</artifactId>
    <version>1.0.0-SNAPSHOT</version>
  </parent>

  <properties>
    <>UTF-8</>
    <>UTF-8</>
    <>1.8</>
  </properties>

  <dependencies>
    <dependency>
      <groupId></groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
      <groupId></groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId></groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <dependency>
      <groupId></groupId>
      <artifactId>sharding-jdbc-core</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId></groupId>
      <artifactId>hutool-all</artifactId>
    </dependency>

    <dependency>
      <groupId></groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
  </dependencies>

  <build>
    <finalName>spring-boot-demo-sharding-jdbc</finalName>
    <plugins>
      <plugin>
        <groupId></groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

2.2.

package ;

import ;
import ;

public class CustomSnowflakeKeyGenerator implements KeyGenerator {
    private Snowflake snowflake;

    public CustomSnowflakeKeyGenerator(Snowflake snowflake) {
         = snowflake;
    }
    
    @Override
    public Number generateKey() {
        return ();
    }
}

2.3.

@Configuration
public class DataSourceShardingConfig {
    private static final Snowflake snowflake = (1, 1);

    /**
      * Need to manually configure the transaction manager
      */
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // Set library policy        (new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        // Set the table for rule adaptation        ().add("t_order");
        // Set the table policy        ().add(orderTableRule());
        ("ds0");
        (new NoneShardingStrategyConfiguration());

        Properties properties = new Properties();
        ("", "true");

        return (dataSourceMap(), shardingRuleConfig, new ConcurrentHashMap&lt;&gt;(16), properties);
    }

    private TableRuleConfiguration orderTableRule() {
        TableRuleConfiguration tableRule = new TableRuleConfiguration();
        // Set logical table name        ("t_order");
        // ds${0..1}.t_order_${0..2} can also be written as ds$->{0..1}.t_order_$->{0..1}        ("ds${0..1}.t_order_${0..2}");
        (new InlineShardingStrategyConfiguration("order_id", "t_order_$-&gt;{order_id % 3}"));
        (customKeyGenerator());
        ("order_id");
        return tableRule;
    }

    private Map&lt;String, DataSource&gt; dataSourceMap() {
        Map&lt;String, DataSource&gt; dataSourceMap = new HashMap&lt;&gt;(16);

        // Configure the first data source        HikariDataSource ds0 = new HikariDataSource();
        ("");
        ("jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;serverTimezone=GMT%2B8");
        ("root");
        ("root");

        // Configure the second data source        HikariDataSource ds1 = new HikariDataSource();
        ("");
        ("jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;serverTimezone=GMT%2B8");
        ("root");
        ("root");

        ("ds0", ds0);
        ("ds1", ds1);
        return dataSourceMap;
    }

    /**
      * Custom primary key generator
      */
    private KeyGenerator customKeyGenerator() {
        return new CustomSnowflakeKeyGenerator(snowflake);
    }

}

2.4.

@Slf4j
@RunWith()
@SpringBootTest
public class SpringBootDemoShardingJdbcApplicationTests {
    @Autowired
    private OrderMapper orderMapper;

    /**
      * New tests
      */
    @Test
    public void testInsert() {
        for (long i = 1; i &lt; 10; i++) {
            for (long j = 1; j &lt; 20; j++) {
                Order order = ().userId(i).orderId(j).remark((20)).build();
                (order);
            }
        }
    }

    /**
      * Test update
      */
    @Test
    public void testUpdate() {
        Order update = new Order();
        ("Modify the comment information");
        (update, Wrappers.&lt;Order&gt;update().lambda().eq(Order::getOrderId, 2).eq(Order::getUserId, 2));
    }

    /**
      * Test removal
      */
    @Test
    public void testDelete() {
        (new QueryWrapper&lt;&gt;());
    }

    /**
      * Test query
      */
    @Test
    public void testSelect() {
        List&lt;Order&gt; orders = (Wrappers.&lt;Order&gt;query().lambda().in(Order::getOrderId, 1, 2));
        ("【orders】= {}", (orders));
    }
    
}

This is the article about SpringBoot integrating sharding-jdbc to implement sample code for sub-repository and table operations. For more related contents of SpringBoot sharding-jdbc sub-repository and tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!