About sharding JDBC

Sharding JDBC is the first product of ShardingSphere and the predecessor of ShardingSphere. It is positioned as a lightweight Java framework that provides additional services in the JDBC layer of Java. It uses the client-side direct connection database to provide services in the form of jar package, without additional deployment and dependence. It can be understood as an enhanced version of jdbc driver, fully compatible with JDBC and various ORM frameworks.

• It is applicable to any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or using JDBC directly.
• Support any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
• Support any database that implements JDBC specification. At present, it supports MySQL, Oracle, SQL server, PostgreSQL and any database complying with SQL92 standard.

internal structure

Usage flow

1. Initialization process
2. Configure the Configuration object.
3. Converts a Configuration object to a Rule object through a Factory object.
4. Assemble the Rule object with the DataSource object through the Factory object.
5. Sharding JDBC uses DataSource objects for sub library.

Version dependency

	<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>4.0.0-RC2</version>
	</dependency>

Scene using demo

Create data source method

	public static DataSource createDataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://10.136.15.102:3306/otoc_weixin_user?useUnicode=true&characterEncoding=utf-8");
		dataSource.setUsername("dptest");
		dataSource.setPassword("111111");
		return dataSource;
	}

sharding custom class

package com.szeastroc.gateway;

import java.util.Collection;

import org.apache.commons.lang.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import com.szeastroc.common.utils.RouterUtils;

import lombok.extern.slf4j.Slf4j;

/**
 * hash Fragmentation rule
 * For =, > scenarios
 * @author LiangHao
 *
 */

@SuppressWarnings("rawtypes")
@Slf4j
public class HashShardingAlgorithm implements PreciseShardingAlgorithm{

	/**
	 * Table quantity
	 */
	private Integer tableNum;

	public HashShardingAlgorithm(Integer tableNum) {
		this.tableNum = tableNum;
	}

	@Override
	public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
		StringBuilder table = new StringBuilder();
		table.append(availableTargetNames.iterator().next()).append("_");

		String value = (String) shardingValue.getValue();

		table.append(StringUtils.leftPad(String.valueOf(RouterUtils.getResourceCode(value) % tableNum), 3, '0'));
        log.info("Slice table name:{}",table);
		
		return table.toString();
	}

}

package com.szeastroc.gateway;

import java.util.Arrays;
import java.util.Collection;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import lombok.extern.slf4j.Slf4j;
/**
 * Range query fragment is applicable to range statements such as between
 * @author LiangHao
 *
 */
@SuppressWarnings("rawtypes")
@Slf4j
public class RangeShardingAlgoithm implements RangeShardingAlgorithm{


	@Override
	public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
		 log.info("Range slice table name:{}",Arrays.asList("t_weixin_user_000","t_weixin_user_001"));
		return Arrays.asList("t_weixin_user_000","t_weixin_user_001");
	}



}

package com.szeastroc.gateway;

import java.util.Arrays;
import java.util.Collection;

import org.apache.commons.lang.StringUtils;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import com.alibaba.fastjson.JSON;
import com.szeastroc.common.utils.RouterUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm{

	private Integer tableNum;
	
	private String[] shardingCloumns;
	
	public ComplexShardingAlgorithm(Integer tableNum,String ...shardingCloumns) {
		this.tableNum=tableNum;
		this.shardingCloumns = shardingCloumns;
	}
	
	@Override
	public Collection doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
		
		 log.info("Composite field slice table name:{}",Arrays.asList("t_weixin_user_000","t_weixin_user_001"));
		return Arrays.asList("t_weixin_user_000","t_weixin_user_001");
	}
}

1. Standard fragmentation demo, applicable to single field fragmentation =, >

	public static void standardShardingTableDemo() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
		dataSourceMap.put("ds0", createDataSource());
		/**
		 * Configure table rules
		 */
		TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_weixin_user");

		orderTableRuleConfig.setTableShardingStrategyConfig(
				new StandardShardingStrategyConfiguration("openid", new HashShardingAlgorithm(10)));

		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
		shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

		DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
				new Properties());

		Connection connection = dataSource.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement("select * from t_weixin_user where openid=?");
		prepareStatement.setString(1, "2342");
		prepareStatement.execute();
		ResultSet result = prepareStatement.getResultSet();
		if (result.next()) {
			log.info("Standard fragment query data x_openid:{}", result.getString(1));
		}
		connection.close();
	}

2. Range fragmentation demo, applicable to between statements

/**
	 * Standard range fragmentation rule demo
	 * 
	 * @throws SQLException
	 */
	public static void standardRangeShardingTableDemo() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
		dataSourceMap.put("ds0", createDataSource());
		/**
		 * Configure table rules
		 */
		TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_weixin_user");

		orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("openid",
				new HashShardingAlgorithm(10), new RangeShardingAlgoithm()));

		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
		shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

		DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
				new Properties());

		Connection connection = dataSource.getConnection();
		PreparedStatement prepareStatement = connection
				.prepareStatement("select * from t_weixin_user where openid between ? and ?");
		prepareStatement.setString(1, "2342");
		prepareStatement.setString(2, "2342");
		prepareStatement.execute();
		ResultSet result = prepareStatement.getResultSet();
		if (result.next()) {
			log.info("Range fragment query data x_openid:{}", result.getString(1));
		}
		connection.close();
	}

3. Composite field demo, which is applicable to the combination and segmentation of multiple fields

/**
	 * Compound field rule demo
	 * 
	 * @throws SQLException
	 */
	public static void complexShardingTableDemo() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
		dataSourceMap.put("ds0", createDataSource());
		/**
		 * Configure table rules
		 */
		TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_weixin_user");

		orderTableRuleConfig.setTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration("openid,x_openid",
				new ComplexShardingAlgorithm(10, "openid", "x_openid")));

		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
		shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

		DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
				new Properties());

		Connection connection = dataSource.getConnection();
		PreparedStatement prepareStatement = connection
				.prepareStatement("select * from t_weixin_user where openid=? and x_openid=?");
		prepareStatement.setString(1, "2342");
		prepareStatement.setString(2, "234");
		prepareStatement.execute();
		ResultSet result = prepareStatement.getResultSet();
		if (result.next()) {
			log.info("Composite field fragment query data x_openid:{}", result.getString(1));
		}
		connection.close();
	}

4. Read write separation demo

    /**
	 * Read / write separation
	 * 
	 * @throws SQLException
	 */
	public static void masterSlaveDemo() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
		dataSourceMap.put("ds0", createDataSource());
		dataSourceMap.put("ds1", createDataSource());
		  
		MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("Read / write separation", "ds1", Arrays.asList("ds1", "ds_slave1"));
		    

		DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig,
				new Properties());

		Connection connection = dataSource.getConnection();
		PreparedStatement prepareStatement = connection
				.prepareStatement("select * from t_weixin_user_000 where openid=? and x_openid=?");
		prepareStatement.setString(1, "2342");
		prepareStatement.setString(2, "234");
		prepareStatement.execute();
		ResultSet result = prepareStatement.getResultSet();
		if (result.next()) {
			log.info("Read write separated query data x_openid:{}", result.getString(1));
		}
		connection.close();
	}