This is the 11th day of my participation in the August More Text Challenge

Author: Tom Brother wechat official account: Micro technology

With the rapid development of the Internet, massive data storage is also a problem. It is difficult to support single database and single table of traditional relational database. How to store and access these data efficiently has become an urgent problem in the industry. The solution has two directions:

  • A NoSQL database is a non-relational database that naturally integrates functions similar to distributed sharding and supports massive data storage, but does not have transaction management

  • Divide database and table, integrate multiple single database and single table resources, and equip resource scheduling module, so as to form a logical table with massive data storage.

Today we mainly introduce, how to quickly integrate sub-library sub-table framework based on Springboot, as far as possible out of the box

Of course, in addition to ShardingSphere, there are other sub-library sub-table frameworks, such as Cobar, MyCat and so on

ShardingSphere introduction

ShardingSphere is an open source ecosystem of distributed database middleware solutions. It consists of three independent products that can be deployed and used together. They all provide standardized data sharding, distributed transactions, and database governance capabilities

ShardingSphere consists of three sub-projects that form a complete database solution.

Shardingsphere-jdbc: 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.

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.

2. Shardingsphere-proxy: positioned as a transparent database Proxy, it provides server version that encapsulates database binary protocol to support heterogeneous languages. MySQL/PostgreSQL is currently available, which can manipulate data using any MySQL/PostgreSQL compliant access client, making it more DBA-friendly.

3. Shardingsphere-sidecar (under planning) : positioned as the cloud native database agent of Kubernetes, it acts as a proxy for all access to the database in the form of Sidecar. The Database Mesh, also known as the data grid, is provided through a central-free, zero-intrusion solution to interact with the Database.

Advantage:

  • The solution is complete, which integrates the core functions of client sharding, proxy server, and distributed database.

  • Development-friendly, provides a friendly way of integration, business developers only need to introduce a JAR package can embed data sharding, read and write separation, distributed transactions, database governance and a series of functions in business code.

  • Pluggable system extensibility: Many of its core functions are provided in the form of plug-ins, allowing developers to permutations and combinations to customize their own unique systems.

Project example

First, create a project spring-boot-Bulking-Sharding-sphere and add the starter dependency package of sub-library sub-table to pom. XML file

<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> < version > 4.0.0 - RC1 < / version > < / dependency >Copy the code

Data Environment Preparation

Create two databases DS0 and DS1 respectively, and create two user tables user_0 and user_2 in the DS0 database

CREATE TABLE `user_0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, 'user_name' varchar(128) NOT NULL DEFAULT 'COMMENT ',' age 'int(11) NOT NULL COMMENT' age ', 'address' varchar(128) COMMENT 'address ', PRIMARY KEY (' id')) ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT=' user '; CREATE TABLE `user_2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, 'user_name' varchar(128) NOT NULL DEFAULT 'COMMENT ',' age 'int(11) NOT NULL COMMENT' age ', 'address' varchar(128) COMMENT 'address ', PRIMARY KEY (' id')) ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT=' user ';Copy the code

Create user_1 and user_3 user tables in ds1 database

CREATE TABLE `user_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, 'user_name' varchar(128) NOT NULL DEFAULT 'COMMENT ',' age 'int(11) NOT NULL COMMENT' age ', 'address' varchar(128) COMMENT 'address ', PRIMARY KEY (' id')) ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT=' user '; CREATE TABLE `user_3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, 'user_name' varchar(128) NOT NULL DEFAULT 'COMMENT ',' age 'int(11) NOT NULL COMMENT' age ', 'address' varchar(128) COMMENT 'address ', PRIMARY KEY (' id')) ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT=' user ';Copy the code

We adopt Mybatis as ORM framework and follow the development process of Mybatis. Firstly, we need to define the business entity class User, Mapper interface class file and the XML file of the corresponding SQL statement.

@Mapper
public interface UserMapper {

    Long addUser(User user);

    List<User> queryAllUser();

    User queryUserById(Long id);

    Page<User> querUserByPage();
}

Copy the code

The Spring Boot framework’s most shiny design is convention over configuration, scrapping the tedious XML form of defining Bean instances and migrating a series of framework configuration items to application.properties. With the help of EnableAutoConfiguration, the load is automatically completed, and the corresponding Bean instance is instantiated into the Spring container, which is uniformly managed by IOC.

For two database initialization DataSource objects, these two DataSource object will form a Map and passed to ShardingDataSourceFactory factory class, application, properties configuration file:

server.port=8090 application.name=spring-boot-bulking-sharding-sphere Mybatis. Config - location = classpath: config/mybatis - config. XML spring. Shardingsphere. The datasource. Names = ds0, ds1 # data source spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8 spring.shardingsphere.datasource.ds0.username=root spring.shardingsphere.datasource.ds0.password=111111 spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8 spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=111111Copy the code

Now that we have the data source, we need to set up the database and table splitting policies.

Application. Properties is not configured for the form alone. The default data source strategy. Spring shardingsphere. Sharding. Default - data - the source - name = ds1 # user table table configuration Spring. Shardingsphere. Sharding. Tables. The user. The actual data - nodes. = ds0 user_ $- > {} [0, 2], an user_ $- > {} [1, 3] # user library strategy (also can use the default)  spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id Spring. Shardingsphere. Sharding. Tables. User. Database - strategy. The inline. Algorithm - expression = ds $- > % 2} {id # user table strategy spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4} #spring.shardingsphere.sharding.tables.user.key-generator.column=id #spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKECopy the code

A group in ShardingSphere ShardingStrategyConfiguration, used here is based on the expression of InlineShardingStrategyConfiguration.

InlineShardingStrategyConfiguration contains two parameters, a column names is specified divided shardingColumn, another is to specify subdivision algorithm algorithmExpression expression. In the example above, which database should the data be stored in based on the modulo value of the ID column to 2

We have done a sub-database and sub-table for the user table, which is divided into four tables and belongs to two libraries respectively. The subtable key is the ID field.

Through the unit test, insert 10 user records, acceptance of the data insert situation ~

@Test public void addUser() { for (long i = 1; i < 11; I++) {User User = User. The builder (). The id (I). The userName (" TomGE "). The age (29), address (" hangzhou "). The build (); userMapper.addUser(user); System.out.println(" insert user successfully, uid=" + user.getid ()); }}Copy the code

Two records, id=4 and id=8, are inserted into user_0 table of ds0 library.

Program source code

Github.com/aalansehaiy…

Module: spring – the boot – bulking – sharding – sphere

Author introduction: Tom brother, computer graduate student, the school recruited ali, P7 technical expert, has a patent, CSDN blog expert. Responsible for e-commerce transactions, community fresh, flow marketing, Internet finance and other businesses, many years of first-line team management experience