This tutorial only applies to the 4.x version, there are many configuration and compatibility issues in ShardingSphere’s iteration history, so it is important to pay attention to the version.

Build a SpringBoot project

The construction of the SpringBoot project will not be covered here, but the point to mention here is that when we build, there is little need to introduce dependencies, which will be added step by step

Database preparation

  • Build two libraries, library names installed ds0,ds1 to define
  • SQL > create table T_order1,t_order2; SQL > create table T_order2

The 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;
Copy the code

With the basic database and engineering setup completed, we are ready to complete our sharding JDBC integration

Shardingsphere and HikariCP connection pools are introduced

The Sharding Sphere introduced here is 4.1.1. Attention must be paid to the version problem, otherwise it may not succeed in the future. In addition to introducing Sharding Sphere, Web is also introduced here, which is convenient to write interfaces to call. POM files are 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>
Copy the code

Configure policies for tables and libraries

The main function here is to complete the shard table of the database, and since this is an example, there is only one table here.

server.port=10080

spring.shardingsphere.datasource.names=ds0,ds1
Configure the first database
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=
Configure the second database
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=
# configure the branch library policy
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# Configure a sub-table policy
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}
Copy the code

Writing test interfaces

Now that the basic operations are complete, we are ready to use Sharding JDBC to complete development. So let’s write some code and see if we can get to the database

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(a) throws SQLException {
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        return "success"; }}Copy the code

test

Start our project, then access the interface we wrote, and we can see the following on the console

conclusion

  • It is not difficult to integrate Sharding Sphere. The difficulty lies in the different understanding of each version
  • Encounter error, first look at the version of the problem