Sharding-jdbc introduction

Sharding- JDBC is an open source database operation middleware; Positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.

Official Document Address:

Shardingsphere.apache.org/document/cu…

This article demo realizes the function of sub-table.

The author’s ability is limited, if there are mistakes, welcome to point out in the comments. Much appreciated!

Ii. Project structure

First, create a general Spring Boot project. The project adopts a three-tier architecture, with the structure diagram as follows:

! [](https://p1-tt-ipv6.byteimg.com/large/pgc-image/c86dfbc59b1547fb8bc74c41765a4c71)

The pom.xml file is as follows:

<? The XML version = "1.0" encoding = "utf-8"? > < project XMLNS = "http://maven.apache.org/POM/4.0.0" XMLNS: xsi = "http://www.w3.org/2001/XMLSchema-instance" Xsi: schemaLocation = "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" > < modelVersion > 4.0.0 < / modelVersion > < the parent > < groupId > org. Springframework. Boot < / groupId > The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 2.1.6. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <groupId>com.macky</groupId> < artifactId > spring - the boot - shardingjdbc < / artifactId > < version > 0.0.1 - the SNAPSHOT < / version > <name>spring-boot-shardingjdbc</name> <description>Demo project for spring-boot-shardingjdbc</description> <properties> <java.version>1.8</java.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> <! --mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <! --Mybatis-Plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> The < version > 3.1.1 < / version > < / dependency > <! --shardingsphere start--> <! -- for spring boot --> <dependency> <groupId>io.shardingsphere</groupId> < artifactId > sharding - JDBC - spring - the boot - starter < / artifactId > < version > 3.1.0 < / version > < / dependency > <! -- for spring namespace --> <dependency> <groupId>io.shardingsphere</groupId> < artifactId > sharding - JDBC - spring - the namespace < / artifactId > < version > 3.1.0 < / version > < / dependency > <! --shardingsphere end--> <! --lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the code

The entity class takes books as an example

package com.macky.springbootshardingjdbc.entity; import com.baomidou.mybatisplus.annotation.TableName; import com.baomidou.mybatisplus.extension.activerecord.Model; import groovy.transform.EqualsAndHashCode; import lombok.Data; import lombok.experimental.Accessors; /** * @author Macky * @Title class Book * @Description: Books are entity classes */ @data @EqualSandHashCode (callSuper = true) @Accessors(chain = true) @Tablename ("book") public class book extends Model<Book> { private int id; private String name; private int count; }Copy the code

Open save and query two interfaces, the code is as follows:

package com.macky.springbootshardingjdbc.controller; import com.macky.springbootshardingjdbc.entity.Book; import com.macky.springbootshardingjdbc.service.BookService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.util.List; /** * @author Macky * @Title class BookController * @Description: TODO */ @RestController public class BookController { @Autowired BookService bookService; @RequestMapping(value = "/book", method = RequestMethod.GET) public List<Book> getItems(){ return bookService.getBookList(); } @RequestMapping(value = "/book",method = RequestMethod.POST) public Boolean saveItem(Book book){ return bookService.save(book); }}Copy the code

BookServiceImpl.java

package com.macky.springbootshardingjdbc.service.impl; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.macky.springbootshardingjdbc.entity.Book; import com.macky.springbootshardingjdbc.mapper.BookMapper; import com.macky.springbootshardingjdbc.service.BookService; import org.springframework.stereotype.Service; import java.util.List; /** * @author Macky * @Title class BookServiceImpl * @Description: TODO * @date 2019/7/12 20:47 */ @Service public class BookServiceImpl extends ServiceImpl<BookMapper, Book> implements BookService { @Override public List<Book> getBookList() { return baseMapper.selectList(Wrappers.<Book>lambdaQuery()); } @Override public boolean save(Book book) { return super.save(book); }}Copy the code

BookMapper.java

package com.macky.springbootshardingjdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.macky.springbootshardingjdbc.entity.Book;

/**
 * @author Macky
 * @Title class BookMapper
 * @Description: TODO
 * @date 2019/7/12 20:46
 */
public interface BookMapper extends BaseMapper<Book> {
}
Copy the code

Create a database table with the following DDL statement

CREATE DATABASE IF NOT EXISTS 'db0'; USE `db0`; DROP TABLE IF EXISTS `book_0`; CREATE TABLE `book_0` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS `book_1`; CREATE TABLE `book_1` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; CREATE DATABASE IF NOT EXISTS `db1`; USE `db1`; DROP TABLE IF EXISTS `book_0`; CREATE TABLE `book_0` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS `book_1`; CREATE TABLE `book_1` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; CREATE DATABASE IF NOT EXISTS `db2`; USE `db2`; DROP TABLE IF EXISTS `book_0`; CREATE TABLE `book_0` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; DROP TABLE IF EXISTS `book_1`; CREATE TABLE `book_1` ( `id` INT ( 11 ) NOT NULL, `name` VARCHAR ( 255 ) DEFAULT NULL, `count` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;Copy the code

Application. Properties:

# data source db0 db1, db2 sharding.. JDBC datasource. Names = db0, db1, db2 # the first database sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&useJDBCCompliantTimezoneShift=true &useLegacyDatetimeCode=false&serverTimezone=UTC sharding.jdbc.datasource.db0.username=root Sharding, JDBC datasource. Db0. Password = Aa123456 # second database sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&useJDBCCompliantTimezoneShift=true &useLegacyDatetimeCode=false&serverTimezone=UTC sharding.jdbc.datasource.db1.username=root Sharding, JDBC datasource. Db1. Third database password = Aa123456 # sharding.jdbc.datasource.db2.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2?useUnicode=true&useJDBCCompliantTimezoneShift=true &useLegacyDatetimeCode=false&serverTimezone=UTC sharding.jdbc.datasource.db2.username=root Sharding, JDBC datasource. Db2. Password = # Aa123456 level of split database (table) configuration depots + table line strategy expression subdivision strategy # depots sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id Sharding. JDBC. Config. Sharding. Default - database - strategy. The inline. Algorithm - expression = db $- > % 3} {id # table strategy The book for the logical table Table depends mainly on line id sharding. JDBC. Config. Sharding. Tables. Book. Actual data - nodes = db $- > {0.. 2}.book_$->{0.. 1} sharding. JDBC. Config. Sharding. Tables. Book. The table - strategy. The inline. Sharding - column = # count subdivision algorithm expression Sharding. JDBC. Config. Sharding. Name the book. The table - strategy. The inline. Algorithm - expression = book_ $- > % {count} 2 # 18 primary key UUID digits If a set is distributed Prevent primary key repeat # sharding. JDBC. Config. Sharding. Name the user. The key - generator - column - name = id # print database of execution and statements sharding.jdbc.config.props.. SQL. Show = true spring. Main. Allow - bean - definition - the overriding = true separation sharding. #, speaking, reading and writing. The JDBC datasource. Dsmaster =Copy the code

Interface tests use Postman

Example:

GET request — — — — — – > http://localhost:8080/book POST request: — — — — — — — > http://localhost:8080/book? Id =1&name= Java programming thought &count=8

Github address of Demo:

Github.com/Macky-He/sp…

If you feel helpful, please give a star to encourage the bloggers, thank you!

Third, summary

Sub-database sub-table implementation in accordance with the official document to do a demo is the first step, if you need to further study the source code, research structure, research ideas; This document is only used as a starting point for demo construction. For further understanding, go to the official document.