Sharding-JDBC Demo含源码

花了两天时间学习了分库分表Sharding-JDBC，在bilibili看了教学视频，源码主要是博客https://blog.csdn.net/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 可直接下载)

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

Sharding-JDBC Demo含源码

Sharding-JDBC Demo含源码

继续阅读

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method