【Sharding-JDBC】数据分片

一、使用规范

详细说明了在Sharding-JDBC中SQL和分页中支持项和不支持项：

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/

涉及到的库及表

CREATE DATABASE ds0 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
CREATE DATABASE ds1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

CREATE TABLE t_user(
  user_id BIGINT(20) PRIMARY KEY,
  user_name VARCHAR(40)
);

CREATE TABLE t_order(
  order_id BIGINT(20) PRIMARY KEY,
  user_id BIGINT(20),
  order_num VARCHAR(40)
);

二、不使用Spring

引入Maven依赖

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

这里使用到Mysql和dbcp2数据源

<!-- dbcp2 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.7.0</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.7.0</version>
</dependency>

<!-- mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

基于Java编码的规则配置

// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();

// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3305/ds0");
dataSource1.setUsername("root");
dataSource1.setPassword("123456");
dataSourceMap.put("ds0", dataSource1);

// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3305/ds1");
dataSource2.setUsername("root");
dataSource2.setPassword("123456");
dataSourceMap.put("ds1", dataSource2);

// 配置Order表规则
// 有ds0.t_order0，ds0.t_order1，ds1.t_order0，ds1.t_order1
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order${0..1}");

// 配置分库 + 分表策略
// 首先根据分库策略：user_id % 2 表达式有0和1两种情况，0的路由到ds0,1的路由到ds1。
// 其次根据分表策略：order_id % 2 表达式有0和1两种情况，0的路由到t_order0, 1的路由到t_order1。
// 结合分库分表策略，最终可以得到数据路由到了哪个库下的哪个表。
// 注意：如果分库策略不生效（即没有user_id，无法确认路由到ds0还是ds1），插入操作的时候会向所有的库进行插入。
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
Connection connection = dataSource.getConnection();

// 插入数据, t_order为逻辑表
ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
long orderId = ((Long) keyGenerator.generateKey()).longValue();
long userId = 10243L;
ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
String orderNum = (String) orderGenerator.generateKey();


String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(insertSql);
ps.setLong(1, orderId);
ps.setLong(2, userId);
ps.setString(3, orderNum);
int result = ps.executeUpdate();
System.out.println("执行结果数：" + result);

基于Yaml的规则配置

通过Yaml方式配置，与以上配置等价（sharddb.yml）：

dataSources:
  ds0: !!org.apache.commons.dbcp2.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3305/ds0
    username: root
    password: 123456
  ds1: !!org.apache.commons.dbcp2.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3305/ds1
    username: root
    password: 123456

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds${0..1}.t_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 2}
    t_order_item:
      actualDataNodes: ds${0..1}.t_order_item${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 2}

// org.springframework.core.io.ClassPathResource
ClassPathResource pathResource = new ClassPathResource("sharddb.yml");
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(pathResource.getFile());
Connection connection = dataSource.getConnection();

// 插入数据, t_order为逻辑表
ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
long orderId = ((Long) keyGenerator.generateKey()).longValue();
long userId = 10243L;
ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
String orderNum = (String) orderGenerator.generateKey();


String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(insertSql);
ps.setLong(1, orderId);
ps.setLong(2, userId);
ps.setString(3, orderNum);
int result = ps.executeUpdate();
System.out.println("执行结果数：" + result);

三、使用Spring

基于Spring boot的规则配置

① 引入Maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

这里使用到Mysql和dbcp2数据源

<!-- dbcp2 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.7.0</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.7.0</version>
</dependency>

<!-- mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

② application.properties配置

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3305/ds0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3305/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}

③ Java 代码

# 在使用的地方添加注解

@Resource
private DataSource dataSource;

注意不能使用注解@Autowired，因为有多个相同类型的DataSource。

# 代码操作

Connection connection = dataSource.getConnection();

// t_order为逻辑表
ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
long orderId = ((Long) keyGenerator.generateKey()).longValue();
long userId = 10244L;
ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
String orderNum = (String) orderGenerator.generateKey();

// 插入
String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
PreparedStatement updatePs = connection.prepareStatement(insertSql);
updatePs.setLong(1, orderId);
updatePs.setLong(2, userId);
updatePs.setString(3, orderNum);
int result = updatePs.executeUpdate();
System.out.println("执行的结果数：" + result);

// 查询
String selectSql = "select * from t_order";
PreparedStatement ps = connection.prepareStatement(selectSql);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()){
    String ud = resultSet.getString("user_id");
    String om = resultSet.getString("order_num");
    System.out.println(String.format("user_id = [%s], order_num = [%s]", ud, om));
}

④ （扩展）基于Spring boot + JNDI的规则配置

如果你计划使用Spring boot + JNDI的方式，在应用容器（如Tomcat）中使用Sharding-JDBC时，可使用spring.shardingsphere.datasource.${datasourceName}.jndiName来代替数据源的一系列配置。 如：

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.jndi-name=java:comp/env/jdbc/ds0
spring.shardingsphere.datasource.ds1.jndi-name=jdbc/ds1

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

基于Spring注解的规则配置

①  引入Maven依赖

<!-- for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.1.1</version>
</dependency>

② Configuration配置类

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class ShardingJdbcConfig {

    @Bean
    public BasicDataSource ds0(){
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3305/ds0");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public BasicDataSource ds1(){
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3305/ds1");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public InlineShardingStrategyConfiguration databaseStrategy(){
        return new InlineShardingStrategyConfiguration("user_id", "ds$->{user_id % 2}");
    }

    @Bean
    public InlineShardingStrategyConfiguration orderTableStrategy(){
        return new InlineShardingStrategyConfiguration("order_id", "t_order$->{order_id % 2}");
    }

    @Bean
    public DataSource shardingDataSource() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", ds0());
        dataSourceMap.put("ds1", ds1());
        // 配置分库分表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order${0..1}");
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(databaseStrategy());
        orderTableRuleConfig.setTableShardingStrategyConfig(orderTableStrategy());
        //......

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
        //......

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

    @Bean
    public JdbcTemplate shardingJdbcTemplate() throws SQLException {
        return new JdbcTemplate(shardingDataSource());
    }
}

③ 注入JdbcTemplate进行操作

@Resource
private JdbcTemplate shardingJdbcTemplate;
@GetMapping("queryData")
public void queryData(){
    List<Map<String, Object>> queryForList = shardingJdbcTemplate.queryForList("select * from t_order");
    if(null != queryForList && queryForList.size() > 0){
        for(Map<String, Object> item : queryForList){
            Long userId = (Long) item.get("user_id");
            String orderNum = (String) item.get("order_num");
            System.out.println(String.format("user_id = [%s], order_num = [%s]", userId, orderNum));
        }
    }
} 

基于Spring命名空间的规则配置

引入Maven依赖后，在Spring的xml文件中配置以下内容：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding 
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd 
                        ">
    <bean id="ds0" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3305/ds0" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
    <bean id="ds1" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3305/ds1" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
    
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds$->{user_id % 2}" />
    <sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order$->{order_id % 2}" />
    
    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="ds0,ds1">
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy" />
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
</beans>