Harding-introduction to jdbc usage and basic configuration

  Configuration, Framework

Author: Gao Yulong

Source: Yixin Institute of Technology

Technology Salon 001 |AI Middle Station: An Agile Intelligent Business Support Scheme | Yixin Technology Salon Live Online at 8pm on March 28, Click to Sign Up

1. what is Sharding-JDBC

Sharding-JDBC is positioned as a lightweight spring mvc, an additional service provided in Java’s JDBC layer. It uses a client-side direct connection database 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 and is fully compatible with JDBC and various ORM frameworks.

2. What can Sharding-JDBC do

Sub-library & Sub-table

read/write splitting

Distributed primary key

Distributed transaction

3. Applicable Project Framework

Sharding-JDBC applies to:

  • Any ORM framework based on Java, such as JPA, Hibernate, MyBatis, Spring JDBC
    Template or use JDBC directly.
  • Database connection pool based on any third party, e.g. dbcp, c3p0, bonecp, druid, hikaricp, etc.
  • Any database that implements JDBC specification is supported. MySQL, Oracle, SQLServer and PostgreSQL are currently supported.

4. Maven dependence

<!-- sharding jdbc 开始-->
<dependency>
 <groupId>io.shardingsphere</groupId>
 <artifactId>sharding-core</artifactId>
 <version>${sharding.version}</version>
</dependency>
<dependency>
 <groupId>io.shardingsphere</groupId>
 <artifactId>sharding-jdbc-spring-namespace</artifactId>
 <version>${sharding.version}</version>
</dependency>
<!—如果不配置分布式事务的话配置上边两个就够了 --> 
<!--分布式事务引用依赖-->
<dependency>
 <groupId>io.shardingsphere</groupId>
 <artifactId>sharding-transaction-2pc-xa</artifactId>
 <version>${sharding.version}</version>
</dependency>
<dependency>
 <groupId>io.shardingsphere</groupId>
 <artifactId>sharding-transaction-spring</artifactId>
 <version>${sharding.version}</version>
</dependency>
<!-- sharding jdbc 结束-->
<!--AspectJ AOP支持 -->
<dependency>
 <groupId>org.aspectj</groupId>
 <artifactId>aspectjweaver</artifactId>
 <version>${aspectjweaver.version}</version>
</dependency>

5. Separation of reading and writing

5.1 Data Source Configuration

Configure data sources first

You can also configure read-write separation

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

ParentDs is a common configuration for data sources, which is extracted to avoid writing duplicate codes.

<!-- ds0的主-->
<bean id="ds0_master" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" parent="parentDs"> 
<property name="driverClassName" value=""/>
 <property name="url" value=""/>
</bean>
<!-- ds0的从-->
<bean id="ds0_slave" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" parent="parentDs">
 <property name="driverClassName" value=""/>
 <property name="url" value="${sharding.connection.url.0}"/>
</bean>
<!-- ds1的主-->
<bean id="ds1_master" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" parent="parentDs">
 <property name="driverClassName" value=""/>
 <property name="url" value="${sharding.connection.url.1}"/>
</bean>
<!-- ds1的从-->
<bean id="ds1_slave" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" parent="parentDs">
 <property name="driverClassName" value=""/>
 <property name="url" value="${sharding.connection.url.1}"/>
</bean>

5.2 read-write separation configuration

The case of configuring only the master and never configuring the sub-library 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 master data source ID.

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

<master-slave:data-source id="masterSlaveDataSource" master-data-source-name="ds0_master, ds1_master" slave-data-source-names="ds0_slave, ds1_slave " >
 <master-slave:props>
 <prop key="sql.show">${sql_show}</prop>
 <prop key="executor.size">10</prop>
 <prop key="foo">bar</prop>
 </master-slave:props>
</master-slave:data-source>

5.3 read-write separation and allocation of libraries and tables

If the read-write separation is used together with the sub-library and sub-table, it is sufficient to configure the master-slave route under shardingdata-source.

Harding: the id of master-slave-rule is the name of the configured logical data source. if there are multiple slaves, load balancing can be configured by configuring strategy-ref.

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

Slave-data-source configures the ID of the data source from the library, with multiple numbers separated by commas.

<!-- sharding数据源-->
<sharding:data-source id="shardingDataSource">
 <!-- 读写分离的话要把所有的主从数据源都写在这里-->
 <sharding:sharding-rule
 data-source-names="ds0_master,ds0_slave,ds1_master,ds1_slave ">
 <!-- 读写分离的路由 一主一从配置 strategy-ref -->
 <sharding:master-slave-rules>
 <sharding:master-slave-rule id="ds0" master-data-source-name="ds0_master" slave-data-source-names="ds0_slave"/>
 <sharding:master-slave-rule id="ds1" master-data-source-name="ds1_master" slave-data-source-names="ds1_slave"/>
 </sharding:master-slave-rules>
 <!-- 读写分离配置 结束-->
 <sharding:table-rules>
 <!— 这里是分库分表路由的配置 -->
 </sharding:table-rules>
 <sharding:binding-table-rules>
 <!—- 绑定表的配置 --> 
 </sharding:binding-table-rules>
 </sharding:sharding-rule>
<sharding:props>
 <!-- 显示SQL -->
 <prop key="sql.show">true</prop>
 </sharding:props>
</sharding:data-source>

6. Data Fragmentation

6.1 Fragmentation Support

Sharding-JDBC provides five sharding strategies. Because sharding algorithm is closely related to service implementation, Sharding-JDBC does not provide built-in sharding algorithm, but refines various scenarios through sharding strategy, provides higher-level abstraction, and provides an interface for application developers to implement sharding algorithm by themselves.

StandardShardingStrategy

Standard fragmentation strategy. Provides support for slicing =, IN, and BETWEEN AND in SQL statements. StandardShardingStrategy only supports single slicing key, and provides two slicing algorithms: PreciseShardingAlgorithm and RangeShardingAlgorithm. PreciseShardINgAlgorithm is required to process the slices of = and in; Rangeshardinggalgorithm is optional and is used to process BETWEEN AND fragments. if rangeshardinggalgorithm is not configured, BETWEEN AND in SQL will be processed according to the whole library route.

ComplexShardingStrategy

Compound slicing strategy. Provides support for slicing =, IN, and BETWEEN AND in SQL statements. ComplexShardingStrategy supports multi-slice keys. Because of the complex relationship between multi-slice keys, Sharding-JDBC does not make too much encapsulation. Instead, the combination of slice key values and slice operators are directly delivered to the algorithm interface, which is completely implemented by application developers and provides maximum flexibility.

InlineShardingStrategy

Inline expression fragmentation strategy. Use Groovy’s Inline expression to provide support for slicing = and IN in SQL statements. InlineShardingStrategy only supports single slicing key. For simple slicing algorithms, it can be used through simple configuration, thus avoiding tedious Java code development. For example, tuser${user_id% 8} means that the t_user table is divided into 8 tables according to the modulo of user_id by 8, and the table names are t_user_0 to t_user_7.

HintShardingStrategy

The strategy of slicing through Hint instead of SQL parsing.

NoneShardingStrategy

The strategy of no fragmentation.

6.2 Fragmentation Configuration

Standard slice configuration

<!-- 标准分片策略。-->
<bean id="demoUserStandardStrategy" class="shard.strategy.DemoUserStandardStrategy"/>
<sharding:standard-strategy id="shardingDemoUserStandardStrategy"
 precise-algorithm-ref="demoUserStandardStrategy" sharding-column="id" range-algorithm-ref=""/>

DemoUserStandardStrategy standard shard is to implement the PreciseShardingAlgorithm interface. One of the two parameters of doSharding is cllection of all data sources. The other parameter is the value of the shard passed when executing SQL.

/**
 * 根据ID取
 * 标准分片策略
 * 用于处理=和IN的分片
 * @author yulonggao
 * @date 2019/1/31 14:35
 */
@Slf4j
public class DemoUserStandardStrategy implements PreciseShardingAlgorithm<Long> {
 @Override
 public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
 //这个里边有异常会被处理掉,然后导致拿不到分片。但出异常一般是业务代码写错了。
 //每条指定分片的操作都会调用此方法,如果是in 条件查询的话每个值会调用一次此方法,如果是批量插入也是每一条都要调用一次进行分片
 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 fragmentation

<!-- 强制路由分片策略-->
<bean id="demoUserHintStrategy" class="shard.strategy.DemoUserHintStrategy"/>
<!-- 强制路由例子使用-->
<sharding:hint-strategy id="shardingDemoUserHintStrategy" algorithm-ref="demoUserHintStrategy"/>
DemoUserHintStrategy 的Java 如下,强制分片要实现HintShardingAlgorithm接口。
/**
 * DemoUserHint强制路由分片策略,其实可以共用,只是例子
 * @author yulonggao
 * @date 2019/1/31 14:35
 */
@Slf4j
public class DemoUserHintStrategy implements HintShardingAlgorithm {
 @Override
 public Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {
 //availableTargetNames 这个参数是所有的dataSource的集合,shardingValue是HintManager传过来的分片信息
 log.info("DemoUserHintStrategy_availableTargetNames={}", availableTargetNames);
 log.info("DemoUserHintStrategy_shardingValue={}", shardingValue);
 ListShardingValue listShardingValue = (ListShardingValue) shardingValue;
 Collection shardingValueList = listShardingValue.getValues();
 //因为调用的时候分片是直接传的 DataSource的名称,所以直接返回就可以了,如果传其它值则要加业务逻辑进行分片筛选
 //返回结果只能是availableTargetNames 里边所包含的
 return shardingValueList;
 }
}

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

<!—主键生成 -->
<bean id="keyId" class="shard.key.DefaultKeyGenerator"/>
Sharding-JDBC 使用入门和基本配置

图片描述

7. Distributed Transaction

The following line of code is configured in spring. shardingTransaction.xml is included in the jar package.

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

<bean id="transactionManager"
 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
 <property name="dataSource" ref="shardingDataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
<!-- 事务支持-->
<import resource="classpath:META-INF/shardingTransaction.xml"/>

To configure a transaction with annotations, use both ShardingTransactionType and Transactional annotations.

/**
 * 注意:@ShardingTransactionType需要同Spring的@Transactional配套使用,事务才会生效。
 * @param param
 * @return
 */
@ShardingTransactionType(TransactionType.XA)
@Transactional(rollbackFor = Exception.class)
@Override
public int addParam(DemoParam param) {
log.info("addParam-param={}", param);
return demoParamDao.addParam(param);
}

7.1 Support Level

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

Cross-library transactions caused by logical exceptions are fully supported. For example, the same transaction is updated across two libraries. After the update is completed, empty pointers are thrown, and the contents of both libraries can be rolled back.

Supports rollback caused by database field constraints.

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

8. Other Issues

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

Harding-introduction to jdbc usage and basic configuration

图片描述

9. Reference Documents

https://shardingsphere.apache …

Yixin Institute of Technology