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

Editor to share with you how SpringBoot2 integrates Sharding-Jdbc middleware to achieve data sub-database and sub-table. I hope you will get something after reading this article. Let's discuss it together.

Horizontal division 1. Horizontal sub-library

1), concept:

Based on the field, according to a certain strategy, split the data in one library into multiple libraries.

2), result

The structure of each library is the same; the data is different

The union of all libraries is full data.

2. Horizontal subtable

1), concept

Split the data in one table into multiple tables based on the field and according to a certain strategy.

2), result

The structure of each table is the same; the data is different

The union of all tables is full data.

2. Shard-jdbc middleware 1, architecture diagram

2. Characteristics

1) Sharding-JDBC directly encapsulates JDBC API, and the cost of old code migration is almost zero.

2), suitable for any Java-based ORM framework, such as Hibernate, Mybatis, etc.

3), can be based on any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, etc.

4) provide services in the form of jar package, no proxy proxy layer, no additional deployment, no other dependencies.

5) the sharding strategy is flexible, which can support equal sign, between, in and other multi-dimensional shards, as well as multi-shard keys.

6) SQL parsing function is perfect, supporting aggregation, grouping, sorting, limit, or and other queries.

Project demonstration 1. Project structure

Springboot version 2.0 druid 1.1.13 sharding-jdbc 3.1 version 2, database configuration

One basic library mapping (shard_one) two libraries do sub-database sub-table (shard_two,shard_three). Table usage: table_one,table_two3, core code block

Data source profile

Spring:datasource: # data source: shard_one dataOne: type: com.alibaba.druid.pool.DruidDataSource druid: driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/shard_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false username: root password: 123 initial-size: 10 max-active: 100 min-idle: 10 max-wait: 60000 pool-prepared-statements: true max- Pool-prepared-statement-per-connection-size: 20 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 max-evictable-idle-time-millis: 60000 validation-query: SELECT 1 FROM DUAL # validation-query-timeout: 5000 test-on-borrow: false test-on-return: false test-while-idle: true connectionProperties: druid.stat.mergeSql=true Druid.stat.slowSqlMillis=5000 # data source: shard_two dataTwo: type: com.alibaba.druid.pool.DruidDataSource druid: driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/shard_two?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false username: root password: 123 initial-size: 10 max-active: 100 min-idle: 10 max-wait: 60000 pool-prepared-statements: true max -pool-prepared-statement-per-connection-size: 20 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 max-evictable-idle-time-millis: 60000 validation-query: SELECT 1 FROM DUAL # validation-query-timeout: 5000 test-on-borrow: false test-on-return: false test-while-idle: true connectionProperties: druid.stat.mergeSql=true Druid.stat.slowSqlMillis=5000 # data source: shard_three dataThree: type: com.alibaba.druid.pool.DruidDataSource druid: driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/shard_three?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false username: root password: 123 initial-size: 10 max-active: 100 min-idle: 10 max-wait: 60000 pool-prepared-statements: true max -pool-prepared-statement-per-connection-size: 20 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 max-evictable-idle-time-millis: 60000 validation-query: SELECT 1 FROM DUAL # validation-query-timeout: 5000 test-on-borrow: false test-on-return: false test-while-idle: true connectionProperties: druid.stat.mergeSql=true Druid.stat.slowSqlMillis=5000

Database sub-database strategy

/ * Database mapping calculation * / public class DataSourceAlg implements PreciseShardingAlgorithm {private static Logger LOG = LoggerFactory.getLogger (DataSourceAlg.class); @ Override public String doSharding (Collection names, PreciseShardingValue value) {LOG.debug ("partition algorithm parameters {}, {}", names,value); int hash = HashUtil.rsHash (String.valueOf (value.getValue (); return "ds_" + ((hash% 2) + 2);}}

Data Table 1 Sub-Table Strategy

/ * subtable algorithm * / public class TableOneAlg implements PreciseShardingAlgorithm {private static Logger LOG = LoggerFactory.getLogger (TableOneAlg.class); / * each database is divided into 5 tables * / @ Override public String doSharding (Collection names, PreciseShardingValue value) {LOG.debug ("partitioning algorithm parameters {}, {}", names,value); int hash = HashUtil.rsHash (String.valueOf (value.getValue () Return "table_one_" + (hash 531);}}

Data Table 2 Sub-Table Strategy

/ * subtable algorithm * / public class TableTwoAlg implements PreciseShardingAlgorithm {private static Logger LOG = LoggerFactory.getLogger (TableTwoAlg.class); / * each database is divided into 5 tables * / @ Override public String doSharding (Collection names, PreciseShardingValue value) {LOG.debug ("partitioning algorithm parameters {}, {}", names,value); int hash = HashUtil.rsHash (String.valueOf (value.getValue () Return "table_two_" + (hash 531);}}

Data source integration configuration

/ * Database sub-database sub-table configuration * / @ Configurationpublic class ShardJdbcConfig {/ / omitted druid configuration, / * Shard-JDBC sub-database configuration * / @ Bean public DataSource dataSource (@ Autowired DruidDataSource dataOneSource, @ Autowired DruidDataSource dataTwoSource, @ Autowired DruidDataSource dataThreeSource) throws Exception {ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration () ShardJdbcConfig.getTableRuleConfigs (). Add (getTableRule01 ()); shardJdbcConfig.getTableRuleConfigs (). Add (getTableRule02 ()); shardJdbcConfig.setDefaultDataSourceName ("ds_0"); Map dataMap = new LinkedHashMap (); dataMap.put ("ds_0", dataOneSource); dataMap.put ("ds_2", dataTwoSource); dataMap.put ("ds_3", dataThreeSource); Properties prop = new Properties () Return ShardingDataSourceFactory.createDataSource (dataMap, shardJdbcConfig, new HashMap (), prop);} / * * Shard-JDBC subtable configuration * / private static TableRuleConfiguration getTableRule01 () {TableRuleConfiguration result = new TableRuleConfiguration (); result.setLogicTable ("table_one"); result.setActualDataNodes ("ds_$ {2... 3} .table _ one_$ {1... 5}"); result.setDatabaseShardingStrategyConfig (new StandardShardingStrategyConfiguration ("phone", new DataSourceAlg () Result.setTableShardingStrategyConfig (new StandardShardingStrategyConfiguration ("phone", new TableOneAlg ()); return result;} private static TableRuleConfiguration getTableRule02 () {TableRuleConfiguration result = new TableRuleConfiguration (); result.setLogicTable ("table_two"); result.setActualDataNodes ("ds_$ {2... 3} .table _ two_$ {1... 5}"); result.setDatabaseShardingStrategyConfig (new StandardShardingStrategyConfiguration ("phone", new DataSourceAlg () Result.setTableShardingStrategyConfig (new StandardShardingStrategyConfiguration ("phone", new TableTwoAlg ()); return result;}}

Test the code execution process

@ RestControllerpublic class ShardController {@ Resource private ShardService shardService; / * 1, table creation process * / @ RequestMapping ("/ createTable") public String createTable () {shardService.createTable (); return "success";} / * 2, generate table table_one data * / @ RequestMapping ("/ insertOne") public String insertOne () {shardService.insertOne (); return "SUCCESS" } / * 3, generate table table_two data * / @ RequestMapping ("/ insertTwo") public String insertTwo () {shardService.insertTwo (); return "SUCCESS";} / * 4, query table table_one data * / @ RequestMapping ("/ selectOneByPhone/ {phone}") public TableOne selectOneByPhone (@ PathVariable ("phone") String phone) {return shardService.selectOneByPhone (phone) Query table table_one data * / @ RequestMapping ("/ selectTwoByPhone/ {phone}") public TableTwo selectTwoByPhone (@ PathVariable ("phone") String phone) {return shardService.selectTwoByPhone (phone) }} after reading this article, I believe you have a certain understanding of "how SpringBoot2 integrates Sharding-Jdbc middleware to achieve data sub-database and sub-table". If you want to know more about it, you are welcome to follow the industry information channel and thank you for your reading!

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.