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

[technology Salon 002] data Center: construction practice of Credit Agile data Center | Credit Technology Salon will be broadcast live online at 8: 00 p.m. on May 23. Click to sign up.

What is Sharding-JDBC

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.

What can Sharding-JDBC do?

Sub-library & sub-table

Separation of reading and writing

Distributed primary key

Distributed transaction

III. Applicable project framework

Sharding-JDBC is 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, and currently supports MySQL,Oracle,SQLServer and PostgreSQL.

Maven relies on io.shardingsphere sharding-core ${sharding.version} io.shardingsphere sharding-jdbc-spring-namespace ${sharding.version} io.shardingsphere sharding-transaction-2pc-xa ${sharding.version} io.shardingsphere sharding-transaction-spring ${sharding.version} org.aspectj aspectjweaver ${aspectjweaver.version} V, read-write separation 5.1 data source configuration

Configure the data source first

You can also configure read-write separation

The following configuration is a total of four master and slave data sources for the ds0 and ds1 databases.

ParentDs is a common configuration of the data source, which is pulled out to avoid duplicate code.

5.2 read-write separation configuration

The case where only the master never configures the sub-database sub-table is as follows. If you want to configure the sub-library sub-table, you do not need the following configuration.

Master-data-source-name is the main data source ID

Slave-data-source-names is ID from the data source

${sql_show} 10 bar 5.3 read-write separation and sub-database sub-table configuration together

If read-write separation and sub-library sub-table are used together, then the master-slave route can be configured under shardingdata-source.

The id of sharding:master-slave-rule is the name of the configured logical data source. If there are multiple slaves, you can configure the load balancer by configuring strategy-ref.

Master-data-source is configured with the main library data source ID.

Slave-data-source is configured with ID from the library data source, multiple separated by commas.

True VI. Data sharding 6.1 sharding support

Sharding-JDBC provides five sharding strategies. Because sharding algorithm is closely related to business implementation, Sharding-JDBC does not provide built-in sharding algorithm. Instead, it abstracts various scenarios through sharding strategy, provides a higher level of abstraction, and provides an interface for application developers to implement sharding algorithm on their own.

StandardShardingStrategy

Standard slicing strategy. Provides slicing support for =, IN and BETWEEN AND in SQL statements. StandardShardingStrategy only supports a single sharding key and provides two sharding algorithms, PreciseShardingAlgorithm and RangeShardingAlgorithm. PreciseShardingAlgorithm is required and is used to handle = and IN shards; RangeShardingAlgorithm is optional and is used to handle BETWEEN AND shards. If BETWEEN AND in RangeShardingAlgorithm,SQL is not configured, it will be handled according to full library routing.

ComplexShardingStrategy

Compound slicing strategy. Provides slicing support for =, IN and BETWEEN AND in SQL statements. ComplexShardingStrategy supports multi-sharding keys. Due to the complex relationship between multi-sharding keys, Sharding-JDBC does not do too much encapsulation, but directly delivers sharding key-value combinations and sharding operators to the algorithm interface, which is completely implemented by application developers to provide maximum flexibility.

InlineShardingStrategy

Inline expression slicing strategy. Use Groovy's Inline expression to provide sharding support for = and IN in SQL statements. InlineShardingStrategy only supports a single sharding key. For simple sharding algorithms, you can use simple configuration to avoid tedious Java code development. For example, tuser$ {user_id% 8} means that the t_user table is divided into 8 tables according to user_id, and the table name is t_user_0 to t_user_7.

HintShardingStrategy

The strategy of sharding through Hint rather than SQL parsing.

NoneShardingStrategy

The strategy of not dividing into pieces.

6.2 sharding configuration

Standard shard configuration

In order to implement the PreciseShardingAlgorithm interface for DemoUserStandardStrategy standard slicing, one of the two parameters of doSharding is cllection. The other parameter is the value of the shard passed when the SQL is executed.

/ * * according to ID * standard sharding policy * used to deal with = and IN shards * @ author yulonggao * @ date 14:35 on 2019-1-31 * / @ Slf4jpublic class DemoUserStandardStrategy implements PreciseShardingAlgorithm {@ Override public String doSharding (Collection collection, PreciseShardingValue preciseShardingValue) {/ / this exception will be handled, and then the shard cannot be obtained. But when an exception occurs, the business code is usually written incorrectly. / / this method is called for every specified sharding operation. If it is an in conditional query, this method will be called once for each value, and for batch insertion, each item will be called for sharding log.info ("DemoUserStandardStrategy_preciseShardingValue= {}", preciseShardingValue); Long suffix= preciseShardingValue.getValue ()% 4; log.info ("suffix= {}", suffix). Final String targetDb = String.valueOf (Math.abs (suffix.intValue (); String shardingValue= collection.stream (). Filter (p-> p.endsWith (targetDb)). FindFirst (). Get (); log.info ("preciseShardingValue= {}, shardingValue= {}", preciseShardingValue, shardingValue); return shardingValue;}

Forced slicing

The Java of DemoUserHintStrategy is as follows, and the HintShardingAlgorithm API is required for sharding. / * DemoUserHint forced routing sharding strategy, which can actually be shared, only as an example * @ author yulonggao * @ date 14:35 on 2019-1-31 * / @ Slf4jpublic class DemoUserHintStrategy implements HintShardingAlgorithm {@ Override public Collection doSharding (Collection availableTargetNames, ShardingValue shardingValue) {/ / availableTargetNames this parameter is the collection of all dataSource, and shardingValue is the sharding information log.info ("DemoUserHintStrategy_availableTargetNames= {}", availableTargetNames) sent by HintManager. Log.info ("DemoUserHintStrategy_shardingValue= {}", shardingValue); ListShardingValue listShardingValue = (ListShardingValue) shardingValue; Collection shardingValueList = listShardingValue.getValues (); / / because the shard is the name of the DataSource passed directly when it is called, it can be returned directly. If you pass other values, you need to add business logic for sharding filtering / / the return result can only be the return shardingValueList; included in the availableTargetNames}}.

The configuration of the partial ID is generated, and the class that generates the primary key implements the KeyGenerator interface.

7. Distributed transaction

Configure the following line of code in spring, which comes with shardingTransaction.xml in the jar package.

The source code of the file has only two lines of configuration:

Use annotations to configure transactions with both ShardingTransactionType and Transactional annotations.

/ * Note: @ ShardingTransactionType needs to be used with @ Transactional of Spring for the transaction to take effect. * @ param param * @ return * / @ ShardingTransactionType (TransactionType.XA) @ Transactional (rollbackFor = Exception.class) @ Overridepublic int addParam (DemoParam param) {log.info ("addParam-param= {}", param); return demoParamDao.addParam (param);} 7.1 support

Non-cross-library transactions are fully supported, for example, only sub-tables or sub-libraries, but the result of routing is in a single library.

Cross-library transactions caused by logic exceptions are fully supported. For example, if you update across two libraries in the same transaction and throw a null pointer after the update, the contents of both libraries can be rolled back.

Rollback caused by database field constraints is supported.

Cross-database transactions caused by network and hardware anomalies are not supported. For example, if the first library crashes after updating across two libraries in the same transaction and before it is committed, only the second library data is committed.

VIII. Other questions

With regard to order by sorting, if the sorted field is not in the query result, the generated SQL will also be brought, but the result will not be returned to you.

IX. Reference documents

Https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/sharding/

Author: Gao Yulong

Source: Yixin Institute of Technology

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.