SpringBoot整合Sharding-JDBC分库分表使用

SpringBoot整合Sharding-JDBC分库分表使用

最近在搞一个项目，数据量比较大，需要用到分库分表。于是看了一下这个Sharding-JDBC这个轻量级框架，使用的比较新的版本。百度的基本都是 3.x版本，我这边选择了 4.0.0-RC1的版本。根据官网的例子来改的。官网的文档确实有点坑，估计版本也是比较老的
Sharding-JDBC中文官网
项目地址

引入依赖

1. 连接池选择 dbcp2
2. mysql 的 驱动包
3. 持久层选择 Jpa
4. 分库分表选择 sharding-jdbc-core

<dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.5.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>persistence-api</artifactId>
            <version>1.0</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

创建数据库

数据库 ds0

-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
  `order_id` bigint(50) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(50) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8014416105477508505 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(50) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(50) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=997501360 DEFAULT CHARSET=utf8mb4;

数据库ds1

-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
  `order_id` bigint(50) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(50) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=996746277 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(50) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(50) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1562070422146 DEFAULT CHARSET=utf8mb4;

IDEA 通过Jpa 生成 实体类代码

package cn.fllday.pojo;

import java.util.Objects;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/2 15:37
 */
@javax.persistence.Entity
@javax.persistence.Table(name = "t_order")
public class TOrder0Entity {
    private long orderId;
    private long userId;
    private String status;

    @javax.persistence.Id
    public long getOrderId() {
        return orderId;
    }

    public void setOrderId(long orderId) {
        this.orderId = orderId;
    }

    public long getUserId() {
        return userId;
    }

    public void setUserId(long userId) {
        this.userId = userId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

这里注意一下这个注解 @Table 里面的值

创建ShardingJdbcConfig配置类

ShardingJdbcConfig

package cn.fllday.sharding;

import cn.fllday.algorithm.PreciseModuloShardingTableAlgorithm;
import cn.fllday.datasource.DataSourceUtils;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
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.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/210:11
 */
@Configuration
public class ShardingJdbcConfig {

    @Bean
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
        // 设置分表规则
        shardingRuleConfiguration.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        // 绑定
        shardingRuleConfiguration.getBindingTableGroups().add("t_order");
        // 设置数据源分片规则
        shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        // 设置数据表分片规则
        shardingRuleConfiguration.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id",new PreciseModuloShardingTableAlgorithm()));
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(),shardingRuleConfiguration,getProperties());
    }

    public static KeyGeneratorConfiguration getKeyGeneratorConfiguration(){
        // 算法，字段名
        // 主键生成列
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE","order_id",getProperties());
        return result;

    }

    public static TableRuleConfiguration getOrderTableRuleConfiguration(){
        // 设置逻辑表
        // param1 : 逻辑表名， param2 ： 真实存在的节点，由数据源 + 表明组成， ds${0..1} 代表 数据库选择 ds 后缀为 0 - 1 之间，t_order 代表数据表 t_order 后缀 0 - 1 之间
        TableRuleConfiguration result = new TableRuleConfiguration("t_order","ds${0..1}.t_order_${0..1}");
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        return result;

    }


    public static Properties getProperties(){
        Properties result = new Properties();
        result.setProperty("worker.id","123");
        return result;
    }

    Map<String,DataSource> createDataSourceMap(){
        DataSource ds0 = DataSourceUtils.createDataSource("ds0");
        DataSource ds1 = DataSourceUtils.createDataSource("ds1");
        Map<String,DataSource> map = new HashMap<>();
        map.put("ds0",ds0);
        map.put("ds1",ds1);
        return map;
    }
}

数据源工具类

package cn.fllday.datasource;

import org.apache.commons.dbcp2.BasicDataSource;

import javax.sql.DataSource;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/212:27
 * @Description: 数据源工具类
 */
public class DataSourceUtils {

    private static final String HOST = "localhost";

    private static final int PORT = 3306;

    private static final String USER_NAME = "root";

    private static final String PASSWORD = "root";

    public static DataSource createDataSource(final String dataSourceName) {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName(com.mysql.cj.jdbc.Driver.class.getName());
        basicDataSource.setUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", HOST, PORT, dataSourceName));
        basicDataSource.setUsername(USER_NAME);
        basicDataSource.setPassword(PASSWORD);
        return basicDataSource;
    }
}

PreciseModuloShardingTableAlgorithm

package cn.fllday.algorithm;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/213:48
 * @Description 根据 order id 来指定分表规则
 */
public class PreciseModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        for (String each:collection
             ) {
            if (each.endsWith(preciseShardingValue.getValue() % 2 + "")){
                return each;
            }
        }
        System.out.println(preciseShardingValue.getValue());
        throw new UnsupportedOperationException();
    }
}

getOrderTableRuleConfiguration()

PreciseModuloShardingTableAlgorithm.doSharding() 分表逻辑

---

InlineShardingStrategyConfiguration 数据库分库逻辑

Dao 层编码

TOrderDao

package cn.fllday.dao;

import cn.fllday.pojo.TOrder0Entity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/215:40
 */
@Repository
public interface TOrderDao extends JpaRepository<TOrder0Entity,Long> {
}

Service 层编码

package cn.fllday.service;

import cn.fllday.dao.TOrderDao;
import cn.fllday.pojo.TOrder0Entity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/216:03
 */
@Service
public class TOrderService {
    @Autowired
    private TOrderDao tOrderDao;

    public void add(TOrder0Entity entity){
        tOrderDao.save(entity);
    }

    public TOrder0Entity getOrderById(Long id){
        return tOrderDao.findById(id).get();
    }
}

Controller 层编码

package cn.fllday.controller;

import cn.fllday.pojo.TOrder0Entity;
import cn.fllday.service.TOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.Random;

/**
 *
 * @Author:gssznb
 * @Date:Created 2019/7/216:06
 */
@Controller
@ResponseBody
public class TOrderController
{
    @Autowired
    private TOrderService tOrderService;

    @RequestMapping(value = "order/add.do")
    public String addOrder(){
        Random random = new Random();
        for (int i = 0; i < 1000; i ++){
            int userId = random.nextInt(1000000000);
            int orderId = random.nextInt(1000000000);
            TOrder0Entity entity = new TOrder0Entity();
            entity.setOrderId(orderId);
            entity.setStatus("1");
            entity.setUserId(userId);
            tOrderService.add(entity);
        }
        return "ok";
    }

    @RequestMapping(value = "order/{id}",method = RequestMethod.GET)
    public TOrder0Entity get(@PathVariable(value = "id")Long id){
        return tOrderService.getOrderById(id);
    }

}

运行测试：

访问localhost:8080/order/add.do
数据库结果

    public static KeyGeneratorConfiguration getKeyGeneratorConfiguration(){
        // 算法，字段名
        // 主键生成列
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE","order_id",getProperties());
        return result;

    }

这个设置主键生成列，根据SNOWFLAKE 算法来计算主键id，根据我的猜想应该是自己主动帮忙生成order_id的。不知道为什么没有生效。还需要在研究一番，有大佬知道的可以留言一下 谢谢啦