Sharding-jdbc tutorial: Spring Boot integration sharding-JDBC implementation of read and write separation

Sharding – JDBC introduction

Sharding-JDBC is a distributed database middleware solution. Sharding-jdbc, Sharding-Proxy and Sharding-Sidecar (planned) are three independent products, which constitute the ShardingSphere together. Sharding-jdbc is positioned as a lightweight Java framework. It uses the client to directly connect to the database, which can be understood as an enhanced VERSION of the JDBC driver, fully compatible with JDBC and various ORM frameworks.

• Works with any Java-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
• Database connection pool based on any third party such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
• Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer and PostgreSQL.

The architecture diagram is as follows:

Supports the following special effects:

• Depots table
• Reading and writing separation
• Flexible transaction
• Distributed primary key
• Distributed governance capability

Project preparation

The last article explained how to build read/write separation in Mysql5.7 in detail, and it’s done. Details are as follows:

Execute the following database initialization script in the main database:

USE `cool`;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx-username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

Copy the code

In the previous article, the master and slave databases were already set up, so after executing the above script, the two slave libraries should also have the User table.

Case on

In this article, we use Spring Boot 2.0.3+MyBatis+Druid+Sharding-JDBC+MySQL to separate read and write cases. Part of Mybatis code generation can refer to https://github.com/forezp/mybatis-generator, here Mybatis part of configuration and code would not elaborate on here, can view the source code for details. The engineering structure is shown in the figure below:

The following dependencies are introduced in the project POM file, including the Spring Boot Web startup dependent on spring-boot-starter Web, mybatis startup dependent on mybatis-spring-boot-starter, mysql connection machine, Druid-spring-boot-starter is used as the connection pool starter for druid and sharding-JDBC is used as the connection pool starter for sharding-jdbc-spring-boot-starter. The code is as follows:

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> < version > 1.3.2 < / version > < / dependency > < the dependency > < groupId > mysql < / groupId > <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> < artifactId > druid - spring - the boot - starter < / artifactId > < version > 1.1.10 < / version > < / dependency > < the dependency > <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> < version > 3.1.0. M1 < / version > < / dependency >Copy the code

Do the following in the Spring Boot project configuration file application.yml:

sharding:
  jdbc:
    dataSource:
      names: db-test0,db-test1,db-test2
      Configure the master library
      db-test0: #org.apache.tomcat.jdbc.pool.DataSource
        type: com. Alibaba. Druid. Pool. DruidDataSource driverClassName: com. Mysql.. JDBC Driver url: JDBC: mysql: / / 10.0.0.3:3306 / cool? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password:
        # Maximum number of connections
        maxPoolSize: 20
      db-test1: Configure the first slave library
        type: com. Alibaba. Druid. Pool. DruidDataSource driverClassName: com. Mysql.. JDBC Driver url: JDBC: mysql: / / 10.0.0.13:3306 / cool? useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password:
        maxPoolSize: 20
      db-test2: Configure the second slave library
        type: com. Alibaba. Druid. Pool. DruidDataSource driverClassName: com. Mysql.. JDBC Driver url: JDBC: mysql: / / 10.0.0.17:3306 / cool? useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password:
        maxPoolSize: 20
    config:
      masterslave: Configure read/write separation
        load-balance-algorithm-type: round_robin //random //round_robin round_robin
        name: db1s2
        master-data-source-name: db-test0
        slave-data-source-names: db-test1,db-test2
    props:
      sql: # enable SQL display, default: false, note: no log will be printed for read/write separation only!!
        show: true

Copy the code

Sharding, JDBC dataSource. The names of configuration is the name of the database, is the name of the multiple data sources. Sharding.jdbc.datasource Configures multiple data sources. The database name must be the same as that configured above. And data configuration, including the connection pool type, connector, database address, database account password information, and so on. Sharding. JDBC. Config. Masterslave. Load balance algorithm – – the type of the query load balance algorithm, there are two kinds of algorithms, round_robin (polling) and the random (random). Sharding. JDBC. Config. Masterslave. Master – data – the source – the name the main data source name. Sharding. JDBC. Config. Masterslave. Slave – data – the source – the names from the data source name, multiple use commas.

Case validation

Write 2 interfaces as follows:

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public Object list() {
        return userService.list();
    }

    @GetMapping("/add")
    public Object add(@RequestParam Integer id,@RequestParam String username,@RequestParam String  password) {
        User user = new User();
        user.setId(id);
        user.setUsername(username);
        user.setPassword(password);
        returnuserService.addUser(user); }}Copy the code

In the previous article, YOU turned on CRUD logging for the database in the /var/lib/mysql directory.

By calling two interfaces, you can view the logs of inserted data in the log directory corresponding to the host of the main library:

2019-06-20T02:50:25.183174Z	 2030 Query	select @@session.transaction_read_only
2019-06-20T02:50:25.193506Z	 2030 Query	INSERT INTO user (
          id, username, password
        )
        VALUES (
        134,
        'forezp134'.'1233edwd'
        )

Copy the code

View the logs of the query data from the log directory of the host corresponding to the library:

2019-06-20T2:41:28.450643z 7367 Query SELECT U.* FROM user uCopy the code

This shows that Sharding-JDBC implements read and write separation of the database.

Download the source code

Github.com/forezp/Spri…

The resources

Github.com/apache/incu…

Shardingsphere.apache.org/document/cu…

Github.com/apache/incu…