[ShardingSphere] shardingjdbc entry case-springboot integration shardingjdbc

This tutorial is only applicable to the 4.x version. There are many configuration and compatibility problems in the iteration history of ShardingSphere. You must pay attention to the version when you start here.

Build a SpringBoot project

The construction of the SpringBoot project will not be repeated here. The point to be mentioned here is that when we build, we basically do not need to introduce dependencies. We will add them step by step later.

Database preparation

  • Build two libraries, the library name is defined by installing ds0, ds1
  • Create two tables t_order1 and t_order2 in the database, the table structure is the same, but the names are sorted by numbers

The corresponding SQL is as follows:

DROP TABLE IF EXISTS `t_order1`;
CREATE TABLE `t_order1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `t_order2`;
CREATE TABLE `t_order2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `cloumn` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
After completing the basic database and project setup, we can complete our integrated sharding jdbc

Introduce shardingsphere and HikariCP connection pool

The sharding sphere introduced here is 4.1.1. You must pay attention to version problems. Otherwise, there may be no way to succeed. In addition to the introduction of sharding sphere, web is also introduced here, which is convenient for writing interfaces to call.
The specific POM file is 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>

    <!-- 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>

    <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>

</dependencies>

Configuration table and library strategy

The main function here is to complete the sharding and table partitioning of the database, and because it is an example, only one table is written here. The
content of the specific application.properties configuration file is as follows:

server.port=10080

spring.shardingsphere.datasource.names=ds0,ds1

# 配置第一个数据库
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=

# 配置第二个数据库
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=

# 配置分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

# 配置分表策略
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}

Write test interface

So far, the basic operations have been completed, and we can use sharding jdbc to complete the development. So let's write some code to see if we can get the corresponding database to understand.

package com.echo.shardingjdbc;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author tang.sl
 */
@RestController
@RequestMapping(value = "/test")
public class Test {

    @Resource
    private DataSource dataSource;

    @GetMapping(value = "/test")
    public String test() throws SQLException {
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        return "success";
    }

}

test

Start our project, and then visit the interface we wrote, we can see the following in the console

Insert picture description here

to sum up

  • Integrating sharding sphere is actually not difficult, the difficulty lies in the different understanding of different versions
  • If you encounter an error, look at the version problem first