SoFunction
Updated on 2025-03-04

SpringBoot integrated ShardingSphere to implement database tables

ShardingSphere Function Introduction

ShardingSphereIt is an open source distributed database middleware, aiming to provide applications with database sharding, read and write separation, distributed transactions and other functions. It can integrate seamlessly with existing databases and supports multiple databases (including MySQL, SQL Server, Oracle, etc.) without making too many changes to the application code.

Official website:/

ShardingSphere provides the following core features

1.Sharding：

  • Data is divided into multiple databases or tables through configuration rules. Common sharding methods include range-based, hashing, composite fields, etc.
  • Supports multiple sharding strategies, such as sharding by a field value (for example, based onuser_idororder_idfields), as well as sharding of tables and databases.

2. Read/Write Splitting：

The separation of read operations and write operations can be achieved through configuration. Generally speaking, write operations point to the main library, and read operations can be load-balanced read from multiple replica libraries.

3. Data Masking & Encryption：

Provides data encryption and desensitization functions to protect sensitive data in the database.

4.Distributed Transaction：

Supports distributed transactions to ensure consistency when operating across multiple databases.

5. Transparency and compatibility：

ShardingSphere is a database middleware that transparently applies sharding policies to the application layer. It supports integration with frameworks such as Spring Boot, Spring Cloud, MyBatis, JPA, etc., and no additional changes are required for application layer code.

6. Support multiple databases：

ShardingSphere supports common databases such as MySQL, PostgreSQL, SQL Server, Oracle, etc. You can flexibly select the database you need to support.

7. High scalability：

ShardingSphere provides expansion points to support flexible functional expansion and secondary development.

Using ShardingSphere to implement tables in Spring Boot

In Spring Boot, ShardingSphere is used to implement subtables, and supports SQL Server, MySQL, Oracle, PostgreSQL at the same time

existSpring BootUsed inShardingSphereImplement subtables and support them at the same timeSQL Server、MySQL、OracleandPostgreSQLThe following steps are required for database. By configurationShardingSphereThe sharding strategy, combined with the connection settings of different databases, can realize data sharding across multiple databases.

Step 1: Introduce the necessary dependencies

existIn the file, addSpring Boot、ShardingSphereand JDBC drivers for different databases.

<dependencies>
    <!-- Spring Boot Basic dependency -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- ShardingSphere-JDBC for Spring Boot -->
    <dependency>
        <groupId></groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>5.0.0</version> <!-- Please select the appropriate version as needed -->
    </dependency>

    <!-- MySQL drive -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- SQL Server drive -->
    <dependency>
        <groupId></groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>9.4.1.jre8</version>
    </dependency>

    <!-- Oracle drive -->
    <dependency>
        <groupId></groupId>
        <artifactId>ojdbc8</artifactId>
        <version>19.8.0.0</version>
    </dependency>

    <!-- PostgreSQL drive -->
    <dependency>
        <groupId></groupId>
        <artifactId>postgresql</artifactId>
        <version>42.5.0</version>
    </dependency>
</dependencies>

Step 2: Configure

exist, configure ShardingSphere-related data sources and sharding policies.

spring:
  datasource:
    # Configure MySQL data source    mysql:
      url: jdbc:mysql://localhost:3306/mysql_db
      username: root
      password: root
      driver-class-name: 

    # Configure SQL Server data source    sqlserver:
      url: jdbc:sqlserver://localhost:1433;databaseName=sqlserver_db
      username: sa
      password: password
      driver-class-name: 

    # Configure Oracle Data Source    oracle:
      url: jdbc:oracle:thin:@localhost:1521:orcl
      username: oracle
      password: oracle
      driver-class-name: 

    # Configure PostgreSQL data source    postgresql:
      url: jdbc:postgresql://localhost:5432/postgresql_db
      username: postgres
      password: password
      driver-class-name: 

  sharding:
    jdbc:
      config:
        data-source:
          mysql:
            url: jdbc:mysql://localhost:3306/mysql_db
            username: root
            password: root
            driver-class-name: 
          sqlserver:
            url: jdbc:sqlserver://localhost:1433;databaseName=sqlserver_db
            username: sa
            password: password
            driver-class-name: 
          oracle:
            url: jdbc:oracle:thin:@localhost:1521:orcl
            username: oracle
            password: oracle
            driver-class-name: 
          postgresql:
            url: jdbc:postgresql://localhost:5432/postgresql_db
            username: postgres
            password: password
            driver-class-name: 

      rules:
        sharding:
          tables:
            order:
              actual-data-nodes: mysql.order_${0..1},sqlserver.order_${0..1},oracle.order_${0..1},postgresql.order_${0..1}
              table-strategy:
                inline:
                  sharding-column: order_id
                  algorithm-expression: order_${order_id % 2}
            user:
              actual-data-nodes: mysql.user_${0..1},sqlserver.user_${0..1},oracle.user_${0..1},postgresql.user_${0..1}
              table-strategy:
                inline:
                  sharding-column: user_id
                  algorithm-expression: user_${user_id % 2}

explain:

  • data-source: Defines the data source for each database, including MySQL, SQL Server, Oracle, and PostgreSQL.
  • sharding: Configure sharding rules. Each table (e.g.orderanduser) There are actual data nodes in different databases, e.g.mysql.order_0, sqlserver.order_0wait.
  • table-strategy: useinlineStrategy, basedorder_idoruser_idto perform sharding, for exampleorder_id % 2Indicates that the data is fragmented intoorder_0ororder_1。

Step 3: Configure ShardingSphere data source and sharding rules

You need to write a Java configuration class to configure ShardingSphere data source and sharding rules.

：

@Configuration
@EnableTransactionManagement
public class ShardingConfig {

    @Bean
    public DataSource dataSource() throws SQLException {
        return (createDataSourceMap(), createShardingRuleConfig());
    }

    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        ("mysql", createDataSource("mysql"));
        ("sqlserver", createDataSource("sqlserver"));
        ("oracle", createDataSource("oracle"));
        ("postgresql", createDataSource("postgresql"));
        return dataSourceMap;
    }

    private DataSource createDataSource(String dataSourceName) {
        HikariDataSource dataSource = new HikariDataSource();
        switch (dataSourceName) {
            case "mysql":
                ("jdbc:mysql://localhost:3306/mysql_db");
                ("root");
                ("root");
                ("");
                break;
            case "sqlserver":
                ("jdbc:sqlserver://localhost:1433;databaseName=sqlserver_db");
                ("sa");
                ("password");
                ("");
                break;
            case "oracle":
                ("jdbc:oracle:thin:@localhost:1521:orcl");
                ("oracle");
                ("oracle");
                ("");
                break;
            case "postgresql":
                ("jdbc:postgresql://localhost:5432/postgresql_db");
                ("postgres");
                ("password");
                ("");
                break;
            default:
                throw new IllegalArgumentException("Unsupported DataSource");
        }
        return dataSource;
    }

    private ShardingRuleConfiguration createShardingRuleConfig() {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // Configure the sharding rules for order tables        ().add(createOrderTableRule());
        // Configure the sharding rules of the user table        ().add(createUserTableRule());

        // Set the default data source        ("mysql");

        return shardingRuleConfig;
    }

    private TableRuleConfiguration createOrderTableRule() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        ("order");
        ("mysql.order_${0..1},sqlserver.order_${0..1},oracle.order_${0..1},postgresql.order_${0..1}");
        (new InlineShardingStrategyConfiguration("order_id", "order_${order_id % 2}"));
        return orderTableRuleConfig;
    }

    private TableRuleConfiguration createUserTableRule() {
        TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration();
        ("user");
        ("mysql.user_${0..1},sqlserver.user_${0..1},oracle.user_${0..1},postgresql.user_${0..1}");
        (new InlineShardingStrategyConfiguration("user_id", "user_${user_id % 2}"));
        return userTableRuleConfig;
    }
}

illustrate:

  • createDataSourceMap()Method to register all data sources withShardingDataSourceFactory。
  • createShardingRuleConfig()The method defines the sharding rules, respectivelyorderanduserThe table specifies the sharding policy.
  • Sharding strategy passedInlineShardingStrategyConfigurationSet it by fieldorder_idoruser_idPerform sharding.

Step 4: Create an entity class

You need to define entity classes based on business needs. For example:

@Entity
public class Order {

    @Id
    private Long orderId;
    private String orderDetails;

    // getters and setters
}

@Entity
public class User {

    @Id
    private Long userId;
    private String userName;

    // getters and setters
}

Step 5: Test

Create some simple test cases to verify that ShardingSphere's sharding works as expected. You can check the sharding effect by performing some simple CRUD operations.

@RunWith()
@SpringBootTest
public class ShardingTest {

    @Autowired
    private OrderRepository orderRepository;

    @Test
    public void testOrderSharding() {
        Order order = new Order();
        (1L);
        ("Test order");
        (order);

        Optional<Order> savedOrder = (1L);
        assertTrue(());
    }
}

Summarize

Through the above configuration, ShardingSphere can implement sharding across multiple databases, including MySQL, SQL Server, Oracle, and PostgreSQL. In actual applications, you can adjust sharding rules as needed and combine data sources to achieve distributed data management.

The above is the detailed content of SpringBoot integrated ShardingSphere to implement database subtables. For more information about SpringBoot ShardingSphere database subtables, please pay attention to my other related articles!