Shulou(Shulou.com)06/03 Report--

This article is to share with you the content of the sample analysis of ShardingSphere subtables in Java. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

one。 Project requirements

When we do a project, the amount of data is relatively large, a single table of tens of millions of levels, the need for sub-library sub-table, so search the Internet for this aspect of the open source framework, the most common is mycat,sharding-sphere, and finally I choose the latter, it is easier to use it to do sub-library sub-table.

two。 Introduction to sharding-sphere

Official website address: https://shardingsphere.apache.org/

ShardingSphere is an ecological circle of open source distributed database middleware solutions, which consists of three independent products, Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned). They all provide standardized data sharding, distributed transaction and database governance capabilities, which can be applied to a variety of application scenarios, such as Java isomorphism, heterogeneous languages, containers, cloud natives, and so on.

ShardingSphere is positioned as relational database middleware, which aims to make full use of the computing and storage capacity of relational database in distributed scenarios, but not to achieve a new relational database. It is coexisting rather than mutually exclusive with NoSQL and NewSQL. As the forefront of new technology exploration, NoSQL and NewSQL are highly recommended for looking to the future and embracing change. On the contrary, we can also look at the problem in another way, look to the future, pay attention to the same things, and then grasp the essence of things. Relational database still occupies a huge market today, and it is the cornerstone of each company's core business, and it is difficult to shake it in the future. at this stage, we pay more attention to the increment on the original basis rather than subversion.

Sharding-jdbc is positioned as a lightweight Java framework that provides additional services at the JDBC layer of Java. It uses a client-side directly connected database and provides services in the form of jar packages without additional deployment and dependency. It can be understood as an enhanced version of the JDBC driver, fully compatible with JDBC and various ORM frameworks.

Suitable for any Java-based ORM framework, such as: JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC. Based on any third-party database connection pool, such as: DBCP, C3P0, BoneCP, Druid, HikariCP, etc. Supports any database that implements the JDBC specification. MySQL,Oracle,SQLServer and PostgreSQL are currently supported

three。 Project actual combat

This project is based on Spring Boot 2.1.5 and uses sharding-sphere + Mybatis-Plus to realize sub-database and sub-table.

1. Pom.xml introduces dependency

4.0.0 org.springframework.boot spring-boot-starter-parent 2.1.5.RELEASE com.xd spring-boot-sharding-table 0.0.1-SNAPSHOT spring-boot-sharding-table is based on Spring Boot 2.1.5 and uses sharding-sphere + Mybatis-Plus to implement sub-library and sub-table 1.8 org. Springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test mysql mysql-connector-java runtime com.baomidou mybatis-plus-boot-starter 3.1.1 io.shardingsphere sharding-jdbc-spring-boot-starter 3.1.0 io.shardingsphere sharding-jdbc-spring-namespace 3.1.0 org .projectlombok lombok 1.18.8 org.springframework.boot spring-boot-maven-plugin

two。 Create databases and tables

Ds0 ├── user_0 └── user_1 ds1 ├── user_0 └── user_1

Since it is a sub-database, the structure of the sub-table library must be consistent with the table structure.

Database: ds0

CREATE DATABASE IF NOT EXISTS `ds0` / *! 40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci * /; USE `ds0`; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- Table structure for user_0-DROP TABLE IF EXISTS `ds0` CREATE TABLE `user_ 0` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-Table structure for user_1-DROP TABLE IF EXISTS `user_ 1` CREATE TABLE `user_ 1` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1

Database: ds1

CREATE DATABASE IF NOT EXISTS `ds1` / *! 40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci * /; USE `ds1`; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- Table structure for user_0-DROP TABLE IF EXISTS `user_ 0` CREATE TABLE `user_ 0` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-Table structure for user_1-DROP TABLE IF EXISTS `user_ 1` CREATE TABLE `user_ 1` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1

3. Application.properties (emphasis) is basically configured in this file.

# data Source ds0,ds1sharding.jdbc.datasource.names=ds0 Ds1# first database sharding.jdbc.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcesharding.jdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driversharding.jdbc.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8&&serverTimezone=GMT%2B8sharding.jdbc.datasource.ds0.username=rootsharding.jdbc.datasource.ds0.password=root # second database sharding.jdbc.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcesharding.jdbc.datasource.ds1.driver -class-name=com.mysql.cj.jdbc.Driversharding.jdbc.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8&&serverTimezone=GMT%2B8sharding.jdbc.datasource.ds1.username=rootsharding.jdbc.datasource.ds1.password=root # horizontally split database (table) configuration sub-library + sub-table strategy row expression sharding policy # sub-library policy sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=idsharding.jdbc.config.sharding .default-database-strategy.inline.algorithm-expression=ds$- > {id% 2} # split table policy where user is a logical table split table mainly depends on the age line sharding.jdbc.config.tables.user.actual-data-nodes=ds$- > {0.1} .user _ $- > {0.1} sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=age# sharding algorithm expression sharding.jdbc.config.sharding.tables .user.table-strategy.inline.algorithm-expression=user_$- > {age% 2} # Primary key UUID 18 digits if distributed, there is also a setting to prevent the primary key from repeating the database executed by # sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id # printing and the statement sharding.jdbc.config.props..sql.show=truespring.main.allow-bean-definition-overriding=true

This time, I use configuration files to implement sub-libraries and sub-tables.

The above configuration instructions:

Logical table user

The general term for the same logical and data structure tables of a horizontally split database (table). Example: the user data is split into two tables, user0 to user1, according to the Mantissa of the primary key, and their logical table name is user.

Real table

A physical table that actually exists in a sharded database. User0 to user1 in the previous example

Slicing algorithm:

Hint fragmentation algorithm

Corresponding to HintShardingAlgorithm, which is used to handle scenarios that use Hint line slicing. It needs to be used with HintShardingStrategy.

Sharding strategy:

The line expression sharding strategy corresponds to InlineShardingStrategy. Use the expression of Groovy to provide sharding operation support for the = and IN in the SQL statement, only a single sharding key is supported. For simple slicing algorithms, you can use simple configuration to avoid tedious Java code development, such as: user$- > {id% 2} indicates that the user table is divided into two tables according to id module 2, and the table name is user0 to user_1.

Self-increasing primary key generation strategy

By generating the self-increasing primary key on the client side and replacing the primary key with the original self-increasing primary key in the database, the distributed primary key is not repeated. UUID.randomUUID () is used to generate distributed primary keys. Or SNOWFLAKE.

4. Entity class

Package com.zhang.shardingtable.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 / * @ Classname User * @ Description user entity Class * @ Author Zhang Guowen 13120739083@163.com * @ Date 2019-06-28 17:24 * @ Version 1.0 * / @ Data@EqualsAndHashCode (callSuper = true) @ Accessors (chain = true) @ TableName ("user") public class User extends Model {/ * * primary key Id * / private int id; / * * name * / private String name / * Age * / private int age;}

5. Dao layer

Package com.zhang.shardingtable.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.zhang.shardingtable.entity.User; / * user dao layer * @ author lihaodong * / public interface UserMapper extends BaseMapper {}

6. Service layer and implementation classes

UserService

Package com.zhang.shardingtable.service; import com.baomidou.mybatisplus.extension.service.IService;import com.zhang.shardingtable.entity.User; import java.util.List / * @ Classname UserService * @ Description user Service Class * @ Author Zhang Guowen 13120739083@163.com * @ Date 2019-06-28 17:31 * @ Version 1.0 * / public interface UserService extends IService {/ * Save user Information * @ param entity * @ return * / @ Override boolean save (User entity) / * query all user information * @ return * / List getUserList ();}

UserServiceImpl

Package com.zhang.shardingtable.service.Impl; import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import com.zhang.shardingtable.entity.User;import com.zhang.shardingtable.mapper.UserMapper;import com.zhang.shardingtable.service.UserService;import org.springframework.stereotype.Service; import java.util.List / * @ Classname UserServiceImpl * @ Description user service implementation class * @ Author Zhang Guowen 13120739083@163.com * @ Date 2019-06-28 17:32 * @ Version 1.0 * / @ Servicepublic class UserServiceImpl extends ServiceImpl implements UserService {@ Override public boolean save (User entity) {return super.save (entity);} @ Override public List getUserList () {return baseMapper.selectList (Wrappers.lambdaQuery ());}}

7. Control class

Package com.zhang.shardingtable.controller; import com.zhang.shardingtable.entity.User;import com.zhang.shardingtable.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController; import java.util.List / * * @ Classname UserController * @ Description user Test Control Class * @ Author Zhang Guowen 13120739083@163.com * @ Date 2019-06-28 17:36 * @ Version 1.0 * / @ RestControllerpublic class UserController {@ Autowired private UserService userService; @ GetMapping ("/ select") public List select () {return userService.getUserList () } @ GetMapping ("/ insert") public Boolean insert (User user) {return userService.save (user);}} four. test

Start the project

Open a browser to visit:

Http://localhost:8080/insert?id=1&name=lhd&age=12

Http://localhost:8080/insert?id=2&name=lhd&age=13

Http://localhost:8080/insert?id=3&name=lhd&age=14

Http://localhost:8080/insert?id=4&name=lhd&age=15

You can see the following display on the console, indicating that the insertion was successful.

According to the slicing algorithm and slicing strategy, the id and age models fall into different database tables to achieve the result of sub-database and sub-table.

Some people say, what should we do if we make a query? in fact, it also helps us to open the browser:

Http://localhost:8080/select

Query the results from two tables in ds0 database and two tables in ds1, and then summarize the results and return them.

Earlier, a friend asked me that the amount of data in a single table is up to 10 million. If I want to do horizontal segmentation, it is also possible to divide the database, right?

You can be very flexible as long as you modify the configuration of the configuration file.

As you can see from the code, my business layer code is the same as the usual single table operation, only need to introduce sh configuration and logical table to maintain the existing inconvenience, the use of non-intrusive our code can be changed on the basis of the original can be said to be very convenient

Thank you for reading! On the "Java ShardingSphere sub-library sub-table example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.