SpringBoot implementation example using ShardingSphere-Proxy

Using ShardingSphere-Proxy in Spring Boot is different from using ShardingSphere-JDBC. ShardingSphere-Proxy is an independent proxy layer that handles database partitioning, tables, routing, and load balancing functions, while applications connect to proxy services through JDBC instead of directly connecting to the database. Therefore, the way to integrate ShardingSphere-Proxy mainly involves configuring Spring Boot to connect to ShardingSphere-Proxy.

Here are the detailed steps on how to configure and use ShardingSphere-Proxy in Spring Boot.

1. ShardingSphere-Proxy deployment

First, make sure you have deployed ShardingSphere-Proxy. ShardingSphere-Proxy is an independent proxy service that can be deployed by downloading the official ShardingSphere-Proxy binary package, or through a Docker container.

Deploy ShardingSphere-Proxy

• Download ShardingSphere-Proxy:

  • VisitShardingSphere Official GitHubGet the latest version.
  • Download and unzip ShardingSphere-Proxy.

• Configure ShardingSphere-Proxy:

  • The configuration file is usually located inconfin the folderFiles, mainly configure data sources, sharding strategies, etc.
  • Here is a basic oneConfiguration example:

  server:
    port: 3307  # The port that the proxy service listens, Spring Boot connects to this port
  datasource:
    names: ds0, ds1
    ds0:
      url: jdbc:mysql://localhost:3306/db0
      username: root
      password: root
      driver-class-name: 
    ds1:
      url: jdbc:mysql://localhost:3306/db1
      username: root
      password: root
      driver-class-name: 
  
  sharding:
    tables:
      user:
        actualDataNodes: ds${0..1}.user${0..1}
        tableStrategy:
          inline:
            shardingColumn: user_id
            algorithmExpression: user${user_id % 2}
    defaultDatabaseStrategy:
      inline:
        shardingColumn: user_id
        algorithmExpression: ds${user_id % 2}
    defaultTableStrategy:
      inline:
        shardingColumn: user_id
        algorithmExpression: user${user_id % 2}
  

  • Data source configuration:ds0andds1are two database instances, pointing to different MySQL databases.
  • Sharding configuration:userTable passeduser_idThe fields are sharded, and the table and database will be based onuser_idShare the pieces.

• Start ShardingSphere-Proxy:

  • Start the proxy service via the command line:

    bin/
    

  • ShardingSphere-Proxy starts on the configured port, such as 3307.

2. Spring Boot Configuration

ShardingSphere-Proxy is already running as a standalone service, so in Spring Boot, the application connects to ShardingSphere-Proxy through JDBC, rather than directly connecting to the database.

2.1 Add dependencies

First, inAdd the JDBC driver and Spring Boot-related dependencies required for ShardingSphere-Proxy.

<dependencies>
    <!-- Spring Boot Starter DataSource -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!-- MySQL JDBC Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!-- HikariCP connection pool (optional) -->
    <dependency>
        <groupId></groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

2.2 Configuration

existConfigure Spring Boot to connect to ShardingSphere-Proxy.

spring:
  datasource:
    url: jdbc:mysql://localhost:3307  # ShardingSphere-Proxy proxy service address and port    username: root
    password: root
    driver-class-name: 
    hikari:
      maximum-pool-size: 10
   

In the configuration:

• Data source configuration: point toShardingSphere-ProxyThe JDBC URL of the service (for example:jdbc:mysql://localhost:3307）。
• ShardingSphere configuration: Configure sharding rules related to ShardingSphere in Spring Boot.

2.3 Configure DataSource connection pool (optional)

You can use HikariCP or any other connection pool to configure the data source. Spring Boot will automatically configure the connection pool for you.

spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5

2.4 Configure entities and repositories

Defining entity classes and Spring Data JPA repository interfaces is the same as usual. byUserAs an example, create entity classes and repository interfaces:

@Entity
public class User {
    @Id
    private Long userId;
    private String userName;

    // getters and setters
}

public interface UserRepository extends JpaRepository<User, Long> {
    User findByUserId(Long userId);
}

2.5 Using data in the controller

Then, you can use the data after the library and table in the controller like using normal Spring Data JPA:

@RestController
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("/user/{userId}")
    public User getUser(@PathVariable Long userId) {
        return (userId);
    }
}

3. ShardingSphere-Proxy access and debugging

Once you start the Spring Boot app and ShardingSphere-Proxy is running, your app can interact with multiple databases through a proxy. You can perform query operations by accessing the API provided by Spring Boot, and ShardingSphere-Proxy automatically routes and shards based on the configured library and table policy.

3.1 View Proxy Logs

You canShardingSphere-ProxyView SQL request and routing information in the console or log file to ensure that the data is correctly sharded through the proxy service.

4. Transaction Management

ShardingSphere-Proxy supports distributed transaction management, you can use Spring's transaction management as usual in Spring Boot. ShardingSphere ensures transaction consistency across multiple databases.

@Transactional
public void createUser(User user) {
    (user);
}

ShardingSphere-Proxy handles transaction commits and rollbacks between multiple data sources in the background.

Summarize

• ShardingSphere-Proxy is an independent database proxy layer that connects to the ShardingSphere-Proxy service through JDBC in Spring Boot, rather than directly connecting to the database.
• The key to configuring ShardingSphere-Proxy is setting upThe database connection address in  is the address of the proxy service (such asjdbc:mysql://localhost:3307）。
• ShardingSphere-Proxy is suitable for scenarios where centralized management and sharing database services across multiple applications, especially in microservice architectures.

The advantage of this approach is that you can centrally manage multiple database instances and handle complex logic such as sharding, routing, and transactions through a unified proxy service without the need for each application to embed the partition logic.

This is the end of this article about the implementation example of SpringBoot using ShardingSphere-Proxy. For more related content about SpringBoot using ShardingSphere-Proxy, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!