Sharding-JDBC Demo含源码

关注

原创最新推荐文章于 2026-02-17 00:45:34 发布 575 阅读 AI 写同款· GEO 优化 ›

Sharding-JDBC快速入门demo 一.需求描述使用Sharding-JDBC完成对订单表的水平分表，通过快速入门程序的开发，快速体验Sharding-JDBC的使用方法；通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过Sharding-Jdbc 查询数据，根据 SQL语句的内容从t_order_1或t_order_2查询数据。二.环境搭建 1.sql CREATE DATABASE `order_db` CHARACTER SET 'u 阅读详情

花了两天时间学习了分库分表Sharding-JDBC，在bilibili看了教学视频，源码主要是博客https://blog.youkuaiyun.com/yangxiang_Younger/article/details/113122070#commentBox的，博客内存在bug，主要是注解“@Primary”的问题，@Primary注解在ds0上则使用的是原生的dataSource，此时使用JPA不可分库分表，排查了一段时间“@Primary”需注解在 shardingDataSource。修改后亲测可用。针对多数据源情况，需要在工程SpringBootApplication注解中排除自动配置类，如下

@SpringBootApplication(exclude={
        DataSourceAutoConfiguration.class,
//        HibernateJpaAutoConfiguration.class, //（如果使用Hibernate时，需要加）
        DataSourceTransactionManagerAutoConfiguration.class,
})
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

此Demo使用pom.xml配置如下

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.funtest</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>

代码正确的配置如下：

package com.funtest.shard.database.policy;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @Author: flathead
 * @Date: 2021/4/30 17:20
 */
@Component
@Slf4j
public class CustomerDataSourceFactory {

    @Value("${shard.jdbc.ds0.url}")
    private String dsOUrl;
    @Value("${shard.jdbc.ds1.url}")
    private String ds1Url;

    @Value("${mysql.username}")
    private String mysqlUserName;

    @Value("${mysql.password}")
    private String mysqlPassword;

    @Value("${mysql.driver.class}")
    private String driverClassName;

    @Value("${spring.jpa.hibernate.ddl-auto}")
    private String ddlDef;


    /**
     * 设置数据源ds0
     */
    @Bean(name = "ds0", initMethod = "init", destroyMethod = "close")
    public DruidDataSource getDs0Source() throws SQLException {
        DruidDataSource dataSource0 = new DruidDataSource();
        dataSource0.setDriverClassName(driverClassName);
        dataSource0.setUrl(dsOUrl);
        dataSource0.setUsername(mysqlUserName);
        dataSource0.setPassword(mysqlPassword);
        dataSource0.setInitialSize(5);
        dataSource0.setMaxActive(20);
        dataSource0.setMinIdle(10);
        dataSource0.setMaxWait(30000);
        dataSource0.setFilters("stat,wall");
        Properties properties = new Properties();
        properties.setProperty("connectTimeout", "2000");
        properties.setProperty("socketTimeout", "600000");
        properties.setProperty("spring.jpa.hibernate.ddl-auto", ddlDef);
        dataSource0.setConnectProperties(properties);
        return dataSource0;
    }

    /**
     * 设置数据源ds1
     */
    @Bean(name = "ds1", initMethod = "init", destroyMethod = "close")
    public DruidDataSource getDs1Source() throws SQLException {
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName(driverClassName);
        dataSource1.setUrl(ds1Url);
        dataSource1.setUsername(mysqlUserName);
        dataSource1.setPassword(mysqlPassword);
        dataSource1.setInitialSize(5);
        dataSource1.setMaxActive(20);
        dataSource1.setMinIdle(10);
        dataSource1.setMaxWait(30000);
        dataSource1.setFilters("stat,wall");
        Properties properties = new Properties();
        properties.setProperty("connectTimeout", "2000");
        properties.setProperty("socketTimeout", "600000");
        properties.setProperty("spring.jpa.hibernate.ddl-auto", ddlDef);
        dataSource1.setConnectProperties(properties);
        return dataSource1;
    }

    /**
     * 分片逻辑名称前缀 shard 分表
     */
    @Primary
    @Bean(name = "shardingDataSource")
    public DataSource shardingDataSource(@Qualifier("ds0") DruidDataSource ds0, @Qualifier("ds1") DruidDataSource ds1) {
        // 获取数据源对象
        Properties properties = new Properties();
        properties.setProperty("max.connections.size.per.query", "10");
//        if (devMode.equals("dao-dev")) {
//            // dev  环境开启此参数
//            properties.setProperty("sql.show", "true");
//        }
        DataSource dataSource = null;
        try {
            Map<String, DataSource> targetDataSource = new HashMap<>();
            targetDataSource.put("ds0", ds0);
            targetDataSource.put("ds1", ds1);
            //设置分表规则，订单后两位，路由至0-1，2张表
            ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
            //必须设置默认数据源
            shardingRuleConfig.setDefaultDataSourceName("ds0");
            shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig());
            //shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfig());
            //设置分库规则，订单/2，偶數ds0，奇數ds1
            shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", ShardingDBAlgorithm.DATABASE_SHARDING_ALGORITHM));
            //绑定表
            //shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
            shardingRuleConfig.getBindingTableGroups().add("t_order");
            //创建sharding数据源
            dataSource = ShardingDataSourceFactory.createDataSource(targetDataSource, shardingRuleConfig, properties);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("MYSQL shardconfig 数据源初始化失败");
        }
        log.info("MYSQL shardconfig 数据源初始化成功");
        return dataSource;
    }

    /**
     * 设置订单表分表规则
     */
    private TableRuleConfiguration orderRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order_${0..1}");
        StandardShardingStrategyConfiguration tableRule = new StandardShardingStrategyConfiguration("order_id", new OrderCheckShardingTableAlgorithm());
        tableRuleConfig.setTableShardingStrategyConfig(tableRule);
        return tableRuleConfig;
    }

    /**
     * 设置订单商品表分表规则（该规则未使用）
     */
    private TableRuleConfiguration orderItemRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order_item", "ds0.t_order_item_${0..1}");
        StandardShardingStrategyConfiguration tableRule = new StandardShardingStrategyConfiguration("order_id", new OrderCheckShardingTableAlgorithm());
        tableRuleConfig.setTableShardingStrategyConfig(tableRule);
        return tableRuleConfig;
    }



}

分库规则

package com.funtest.shard.database.policy;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * @Author: flathead
 * @Date: 2021/5/3 11:01
 */
@Slf4j
public class ShardingDBAlgorithm {
    public static final DatabaseShardingAlgorithm DATABASE_SHARDING_ALGORITHM = new DatabaseShardingAlgorithm();
    /**
     * 分库规则
     */
    static class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
        @Override
        public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
            Long orderId = shardingValue.getValue();
            long index = orderId % 2;
            String dataSource = "ds"+index;
//            dataSource="ds0";
            log.info("数据库：{}",dataSource);
            return dataSource;
        }
    }

}

分表规则

package com.funtest.shard.database.policy;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * @Author: flathead
 * @Date: 2021/5/3 10:52
 */
@Slf4j
public class OrderCheckShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
        StringBuilder buf = new StringBuilder();
        Long orderId = shardingValue.getValue();
        String orderIdStr = orderId.toString();
//        String orderIdSuffix = orderIdStr.substring(orderIdStr.length() - 2);
//        String logicTableName = shardingValue.getLogicTableName();
//        buf.append(logicTableName).append("_").append(orderIdSuffix);
//        if(tableNames.contains(buf.toString())) {
//            return buf.toString();
//        } else {
//            String errMsg = String.format("取模精确分片策略：没找到与分片键匹配的表名! %s : %s = %s", shardingValue.getLogicTableName(), shardingValue.getColumnName(), shardingValue.getValue());
//            log.info(errMsg);
//            throw new UnsupportedOperationException(errMsg);
//        }
//        if(tableNames.contains(orderId%2)) {
//            return buf.toString();
//        } else {
//            String errMsg = String.format("取模精确分片策略：没找到与分片键匹配的表名! %s : %s = %s", shardingValue.getLogicTableName(), shardingValue.getColumnName(), shardingValue.getValue());
//            log.info(errMsg);
//            throw new UnsupportedOperationException(errMsg);
//        }
        for (String tableName:tableNames){
            if (tableName.endsWith(String.valueOf(orderId%2))){
                return tableName;
            }
        }
        throw new UnsupportedOperationException("xxxx");
    }

}

代码放在了 https://gitee.com/flathead/sharding-jdbc-demo.git (git clone 可直接下载)

数据库需要手动创建（看下源码不难的）