What is the method of integrating sharding-jdbc with SpringBoot to realize custom sub-library and sub-table?

This article mainly explains the "SpringBoot integration sharding-jdbc to achieve custom sub-library sub-table method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "SpringBoot integration sharding-jdbc to achieve custom sub-library sub-table method is what" it!

I. Preface

SpringBoot integrates sharding-jdbc to realize the separation of database and table and the separation of read and write

Introduction 1. Sharding key

Key fields for database / table split

Ex: the user table is split into different databases according to user_id modeling

2. Slicing algorithm

Please refer to: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding

Accurate slicing algorithm

Range slicing algorithm

Compound slicing algorithm

Hint fragmentation algorithm

3. Sharding strategy (sharding key + sharding algorithm)

Line expression slicing strategy

Standard sharding strategy

Compound slicing strategy

Hint fragmentation strategy

Non-fragmentation strategy

You can view the source code org.apache.shardingsphere.core.yaml.config.sharding.YamlShardingStrategyConfiguration

Third, program realization

Warm Tip: for more information, please see the case demo source code

The complete application.yml configuration is posted here, and when each sharding policy is implemented, you can release the corresponding configuration.

# sharding-jdbc configuration spring: shardingsphere: # whether to enable SQL display props: sql: show: true # = = ↓ data source configuration ↓ = = datasource: names: ds-master-0,ds-slave-0-1 Ds-slave-1-2 # = = ↓ configuration the first master / slave library ↓ = # Master library 1 ds-master-0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL is in a higher version If you need to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # master library 1-slave library 1 ds-slave-0-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL in If the higher version needs to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # Master Library 1-Slave Library 2 ds-slave-0-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # = = ↓ configuration second master / slave library ↓ = # master library 2 ds-master-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc: Mysql://127.0.0.1:3306/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 1 ds-slave-1-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url : jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 2 ds-slave-1-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver Jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection problem in the higher version, then add & useSSL=false username: root password: root sharding: # = ↓ read-write separation configuration ↓ = = master-slave-rules: ds-master-0: # Master library masterDataSourceName: ds-master-0 # slave library slaveDataSourceNames:-ds-slave-0-1-ds-slave-0-2 # load balancing algorithm for querying data from slave library currently there are two algorithms round_robin (polling) and random (random) # algorithm interface org.apache.shardingsphere.spi .masterslave.MasterSlaveLoadBalanceAlgorithm # implements class RandomMasterSlaveLoadBalanceAlgorithm and RoundRobinMasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithmType: ROUND_ROBIN ds-master-1: masterDataSourceName: ds-master-1 slaveDataSourceNames:-ds-slave-1-1-ds-slave-1-2 loadBalanceAlgorithmType: ROUND_ROBIN # = = ↓ sub-library sub-table configuration ↓ = = tables: T_user: actual-data-nodes: ds-master-$- > {0.1} .t _ user$- > {0.1} # configuration attributes can be found in org.apache.shardingsphere.core.yaml.config.sharding.YamlShardingStrategyConfiguration # = ↓ line expression sharding policy ↓ = # using Groovy expressions in configuration Provides sharding operation support for the = and IN in the SQL statement. Only single sharding keys are supported. # # = ↓ sub-library ↓ = # database-strategy:# inline:# sharding-column: user_id # add data sub-library fields (according to which database to insert data into) # algorithm-expression: ds-master-$- > {user_id% 2} # according to User_id is split into different libraries # # = ↓ sub-table ↓ = # table-strategy:# inline:# sharding-column: sex # add data sub-table field (according to which table to insert data into) # algorithm-expression: tweak username-> {sex% 2 } # sharding algorithm expression = > split the model into different tables according to the user's gender # = ↓ standard sharding strategy ↓ = # precise slicing algorithm = > sql triggers computation logic when executing = and IN on the library / table key Otherwise, do not take sub-library / sub-table, full-database / full-table execution. # database-strategy:# standard:# sharding-column: user_id # keys used in sub-libraries # precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # custom sub-library algorithm implementation class # table-strategy:# standard:# sharding-column: sex # additions According to the sub-table field (insert data into that table ex:sex according to the field) # precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyTablePreciseShardingAlgorithm # custom sub-table algorithm to implement class # range slicing algorithm = > sql execute BETWEEN AND on the sub-library / sub-table key, >, =, > =, {user_id% 2} # split into different libraries according to user_id module # = ↓ sub-table ↓ = table-strategy: inline: sharding-column: sex # add data sub-table fields (according to which table the field inserts data into) algorithm-expression: tweak username-> {sex% 2} # sharding algorithm expression = > split into different tables according to the user's gender

2. Standard slicing strategy

A: accurate slicing algorithm

# precise sharding algorithm = > sql triggers the calculation logic when executing = and IN on the sub-library / sub-table key, otherwise the whole database / table is executed without going to the sub-library / sub-table. Database-strategy: standard: sharding-column: user_id # key used in sub-library precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # custom sub-library algorithm implementation class table-strategy: standard: sharding-column: sex # add data sub-table field (insert data into that table ex:sex according to the field) precise-algorithm-class-name: com.zhengqing.demo.config.sharding. Precise.MyTablePreciseShardingAlgorithm # custom sharding algorithm implementation class @ Slf4jpublic class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm {/ * sharding policy * * @ param dbNameList all data sources * @ param shardingValue SQL passed shard value * @ return data source name * / @ Override public String doSharding (Collection dbNameList) PreciseShardingValue shardingValue) {log.info ("[MyDbPreciseShardingAlgorithm] SQL passed in shard value: [{}]", shardingValue) / / split into different libraries according to user_id Long userId = shardingValue.getValue (); for (String dbNameItem: dbNameList) {if (dbNameItem.endsWith (userId% 2)) {return dbNameItem;}} return null } @ Slf4jpublic class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm {/ * sharding policy * * @ param tableNameList all table names * @ param shardingValue SQL shard value passed in during execution * @ return table name * / @ Override public String doSharding (Collection tableNameList, PreciseShardingValue shardingValue) {log.info ("[MyTablePreciseShardingAlgorithm] shard value passed during SQL execution: [{}]", shardingValue) / / split into different tables according to the user's gender Byte sex = shardingValue.getValue (); for (String tableNameItem: tableNameList) {if (tableNameItem.endsWith (String.valueOf (sex% 2) {return tableNameItem;}} return null;}}

B: range slicing algorithm

# range slicing algorithm = > sql executes BETWEEN AND, >, =, > =, = tableSize) {return tableNameResultList;}} return tableNameResultList;} on the library / table key

4. Hint slicing strategy

# = ↓ hint sharding policy ↓ = # Personalized configuration through Hint API = > you can see com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHintdatabase-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyDbHintShardingAlgorithmtable-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyTableHintShardingAlgorithm@Slf4jpublic class MyDbHintShardingAlgorithm implements HintShardingAlgorithm {@ Override public Collection doSharding (Collection dbNameList) HintShardingValue hintShardingValue) {log.info ("[MyDbHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue) Collection dbResultList = new ArrayList (); int dbSize = dbNameList.size (); for (String dbNameItem: dbNameList) {for (Integer shardingValue: hintShardingValue.getValues ()) {if (dbNameItem.endsWith (String.valueOf (shardingValue% 2) {dbResultList.add (dbNameItem) } if (dbResultList.size () > = dbSize) {return dbResultList;} return dbResultList;}} @ Slf4jpublic class MyTableHintShardingAlgorithm implements HintShardingAlgorithm {@ Override public Collection doSharding (Collection tableNameList, HintShardingValue hintShardingValue) {log.info ("[MyTableHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue) Collection tableResultList = new ArrayList (); int tableSize = tableNameList.size (); Collection hintShardingValueValueList = hintShardingValue.getValues (); for (String tableName: tableNameList) {for (Integer shardingValue: hintShardingValueValueList) {if (tableName.endsWith (String.valueOf (shardingValue% 2) {tableResultList.add (tableName) } if (tableResultList.size () > = tableSize) {return tableResultList;} return tableResultList;}}

When in use, the dynamic trigger is as follows:

Public IPage listPageForHint () {/ / clear the previous rule, otherwise an error will be reported HintManager.clear (); / / HintManager API tool class instance HintManager hintManager = HintManager.getInstance (); / / Library = > mainly transfers the value value to MyDbHintShardingAlgorithm for logical sub-library processing hintManager.addDatabaseShardingValue ("t_user", 100); hintManager.addDatabaseShardingValue ("t_user", 1000) / / specify the sharding key of the table = > specify to look up t_user0 hintManager.addTableShardingValue ("t_user", 0); / / hintManager.addTableShardingValue ("t_user", 1); / / read-write separation forces you to read the master library to avoid delay caused by master-slave replication hintManager.setMasterRouteOnly () / / query data Page result = this.userMapper.selectPage (new Page (1,10), new LambdaQueryWrapper () .eq (User::getSex, "0"). Between (User::getUserId, 1L, 1000L)); / / clear rule hintManager.close (); return result } Thank you for your reading, the above is the content of "what is the method of SpringBoot integrating sharding-jdbc to achieve custom sub-library sub-table". After the study of this article, I believe you have a deeper understanding of what is the method of SpringBoot integrating sharding-jdbc to achieve custom sub-library sub-table, and the specific use situation still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!