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

This article introduces the relevant knowledge of "how SpringBoot uses Sharding-JDBC to achieve data fragmentation and read-write separation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

A brief introduction to Sharding-JDBC

Second, the specific way of implementation

1. Maven reference

2. Database preparation

3. Spring configuration

4. Java code of precise slicing algorithm and range slicing algorithm.

5. Test

A brief introduction to Sharding-JDBC

Sharding-JDBC is a product of Sharding-Sphere, which has three products, namely Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar, which provide standardized data fragmentation, read-write separation, flexible transactions and data governance functions. We use Sharding-JDBC here, so if you want to know more about the latter two products, you can check them on their official website.

Sharding-JDBC is a lightweight Java framework that uses client-side directly connected databases to provide services in the form of jar packages without additional deployment and dependency. It can be understood as an enhanced version of JDBC driver with strong compatibility. Applicable ORM frameworks include JPA, Hibernate, Mybatis, Spring JDBC Template or third-party database connection pools that directly use JDBC;, such as DBCP, C3P0, BoneCP, Druid, etc.; supported databases have MySQL,Oracle,SQLServer and PostgreSQL; diversified configuration files Java,yaml,Spring Boot, Spring namespaces. In fact, what is said here is nonsense, we can not look at it, let's start the formal configuration.

2. Specific implementation methods 1. Maven citation

The configuration I use here is the Spring namespace configuration, so I only need to refer to sharding-jdbc-spring-namespace. I should also note that I am not using Dangdang's sharding, and notice that groupId is io.shardingsphere. If you are using other configuration methods, you can go to the http://maven.aliyun.com/nexus/#nexus-search;quick~io.shardingsphere website to find the corresponding maven references.

Io.shardingsphere sharding-jdbc-spring-namespace 3.0.0.M12, database preparation

I use the mysql database here. According to the specific requirements of our project, I have prepared three master libraries and corresponding slave libraries. The name of the master library of the simulation is master, and there is no corresponding slave library for the time being, so the corresponding slave library still points to master;. The second master library has master_1, the third master library has master_1_slaver_1,master_1_slave_2;, and the third master library has master_2_slave_1,master_2_slave_2.

The tables in the database are also divided into tables, and the following is the corresponding mysql screenshot.

The master and slave libraries in this first picture should be on different servers, but here they are built on local servers only for simulation purposes. The write operation in our read-write separation will only occur on the master library, and the slave library will automatically synchronize the data on the master library and provide data for reading. The master-slave replication of the database is introduced in detail in the previous blog post. You can take a look at https://www.yisu.com/article/226077.htm.

This picture serves as our original main library, and the sub-libraries and tables below are based on the order tables in this library. So the tables in the sub-library are only order tables and order schedules.

The third picture is the second main library, in which the order and order schedule are divided into tables, and the specific slicing strategy and slicing algorithm are introduced below. The third master table is the same as the second master table, and all slave tables are consistent with the corresponding master table.

3. Spring configuration

The database information configuration file db.properties configuration can be configured in two copies, which can be divided into development version and beta version, as follows:

# masterMaster.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster.username=rootMaster.password=123456Slave.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueSlave.username=rootSlave.password=123456# maste_1Master_1.url=jdbc:mysql://localhost:3306/master_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_1.username=rootMaster_1.password=123456Master_1_Slave_1.url=jdbc:mysql://localhost:3306/master_1_slave_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_1 _ Slave_1.username=rootMaster_1_Slave_1.password=123456Master_1_Slave_2.url=jdbc:mysql://localhost:3306/master_1_slave_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_1_Slave_2.username=rootMaster_1_Slave_2.password=123456# master_2Master_2.url=jdbc:mysql://localhost:3306/master_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_2.username=rootMaster_2.password=123456Master_2_Slave_1.url=jdbc:mysql://localhost:3306/master_2_slave _ 1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_2_Slave_1.username=rootMaster_2_Slave_1.password=123456Master_2_Slave_2.url=jdbc:mysql://localhost:3306/master_2_slave_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=trueMaster_2_Slave_2.username=rootMaster_2_Slave_2.password=123456

Configuration for Spring:

Because the sharding keys temporarily used in our project are all user_id single keys, there is no compound sharding strategy, nor do we use Hint sharding strategy, line expression sharding strategy and non-sharding strategy.

4. Java code of precise slicing algorithm and range slicing algorithm.

Standard slicing strategy, accurate slicing algorithm

Package com.jihao.algorithm;import io.shardingsphere.core.api.algorithm.sharding.PreciseShardingValue;import io.shardingsphere.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;import java.util.Collection;import com.alibaba.fastjson.JSON / * Custom standard sharding policy, using precise sharding algorithm (= with IN) * @ author JiHao * * / public class PreciseModuleDatabaseShardingAlgorithm implements PreciseShardingAlgorithm {@ Override public String doSharding (Collection availableTargetNames, PreciseShardingValue preciseShardingValue) {System.out.println ("collection:" + JSON.toJSONString (availableTargetNames) + ", preciseShardingValue:" + JSON.toJSONString (preciseShardingValue) For (String name: availableTargetNames) {/ / = the value String value = String.valueOf (preciseShardingValue.getValue ()) corresponding to the sharding key in IN; / / the suffix int I = 1 of the sublibrary / / Recursive algorithm if (name.endsWith ("_" + countDatabaseNum (Long.parseLong (value), I)) {return name;}} throw new UnsupportedOperationException () } / * calculate the database in which the data of this magnitude is in * @ return * / private String countDatabaseNum (long columnValue, int I) {/ / ShardingSphereConstants the amount of data defined in each database long left = ShardingSphereConstants.databaseAmount * (iMel 1); long right = ShardingSphereConstants.databaseAmount * I If (left < columnValue & & columnValue

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.