当前位置：Gxlcms > 数据库问题 > SpringBoot 整合 Sharding jdbc 实现应用层分库分表

SpringBoot 整合 Sharding jdbc 实现应用层分库分表

时间：2021-07-01 10:21:17 帮助过：25人阅读

DATABASE `sharding0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `sharding0`; CREATE TABLE `order` ( `order_id` bigint(20) NOT NULL COMMENT ‘订单ID‘, `order_amount` decimal(20,4) DEFAULT NULL COMMENT ‘订单金额‘, `create_time` datetime DEFAULT NULL COMMENT ‘创建时间‘, `user_id` bigint(20) NOT NULL COMMENT ‘用户ID‘, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `product0` ( `product_id` bigint(20) NOT NULL COMMENT ‘商品ID‘, `product_name` varchar(50) DEFAULT NULL COMMENT ‘商品名称‘, `product_price` decimal(20,4) DEFAULT NULL COMMENT ‘商品价格‘, `create_time` datetime DEFAULT NULL COMMENT ‘创建时间‘ ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `product1` ( `product_id` bigint(20) NOT NULL COMMENT ‘商品ID‘, `product_name` varchar(50) DEFAULT NULL COMMENT ‘商品名称‘, `product_price` decimal(20,4) DEFAULT NULL COMMENT ‘商品价格‘, `create_time` datetime DEFAULT NULL COMMENT ‘创建时间‘ ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `user` ( `user_id` bigint(20) NOT NULL COMMENT ‘用户ID‘, `user_name` varchar(50) NOT NULL COMMENT ‘用户名‘, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
CREATE DATABASE `sharding1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `sharding1`;

CREATE TABLE `custom2020_01` (
  `custom_id` bigint(20) NOT NULL COMMENT ‘主键ID‘,
  `create_time` datetime NOT NULL COMMENT ‘创建时间‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `custom2020_02` (
  `custom_id` bigint(20) NOT NULL COMMENT ‘主键ID‘,
  `create_time` datetime NOT NULL COMMENT ‘创建时间‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order` (
  `order_id` bigint(20) NOT NULL COMMENT ‘订单ID‘,
  `order_amount` decimal(20,4) DEFAULT NULL COMMENT ‘订单金额‘,
  `create_time` datetime DEFAULT NULL COMMENT ‘创建时间‘,
  `user_id` bigint(20) NOT NULL COMMENT ‘用户ID‘,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL COMMENT ‘用户ID‘,
  `user_name` varchar(50) NOT NULL COMMENT ‘用户名‘,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、编写测试代码：

（1）代码结构如下（采用Maven多模块）:

技术图片

（2）sp-sharind-mysql 模块的POM.XML配置如下：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>sp-sharding-mysql</groupId>
    <artifactId>sp-sharding-mysql</artifactId>
    <packaging>pom</packaging>
    <version>1.0</version>
    <name>sp-sharding-mysql</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <druid.version>1.1.10</druid.version>
        <mysql.version>5.1.45</mysql.version>
        <lombok.version>1.16.20</lombok.version>
        <mybatis.version>2.1.0</mybatis.version>
        <spring-boot.version>2.1.6.RELEASE</spring-boot.version>
        <sharding-jdbc-core.version>4.0.0</sharding-jdbc-core.version>
    </properties>

    <!-- 定义包信息，统一管理 -->
    <dependencyManagement>
        <dependencies>
            <!--使用 spring-boot-dependencies 管理包依赖-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>

            <!-- fastjson -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>${fastjson.version}</version>
            </dependency>

            <!-- mybatis -->
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>${mybatis.version}</version>
            </dependency>

            <!-- druid -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.version}</version>
            </dependency>

            <!-- mysql -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>

            <!-- sharding-jdbc-core -->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>${sharding-jdbc-core.version}</version>
            </dependency>

            <!-- lombok -->
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>${lombok.version}</version>
            </dependency>

        </dependencies>
    </dependencyManagement>

    <!-- 聚合子模块 -->
    <modules>
        <module>common</module>
        <module>api</module>
    </modules>

</project>

（2）common 模块的POM.XML配置如下：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>sp-sharding-mysql</artifactId>
        <groupId>sp-sharding-mysql</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <groupId>common</groupId>
    <artifactId>common</artifactId>
    <version>1.0</version>
    <name>common</name>

    <dependencies>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

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

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>

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



</project>

（3）api 模块的POM.XML配置如下：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>sp-sharding-mysql</artifactId>
        <groupId>sp-sharding-mysql</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <groupId>api</groupId>
    <artifactId>api</artifactId>
    <version>1.0</version>
    <name>api</name>

    <dependencies>

        <!-- 引入公共 common 模块 -->
        <dependency>
            <groupId>common</groupId>
            <artifactId>common</artifactId>
            <version>1.0</version>
        </dependency>


        <!-- 引入 sharding-jdbc-core -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</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>

    </dependencies>

</project>

（4）MyDataSourceConfig类：

package api.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
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.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

/**
 * 数据源配置
 */
@Configuration
public class MyDataSourceConfig {


    @Bean(name="sharding0")
    @ConfigurationProperties(prefix = "datasource.sharding0")
    public DataSource getFirstDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name="sharding1")
    @ConfigurationProperties(prefix = "datasource.sharding1")
    public DataSource getSecondDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name="shardingDataSource")
    public DataSource shardingDataSource(@Qualifier("sharding0") DataSource sharding0,
                                         @Qualifier("sharding1") DataSource sharding1) throws Exception {

        // 配置 user 表规则
        TableRuleConfiguration userTableRuleConfig = new
                TableRuleConfiguration("user", "sharding${0..1}.user");

        // 配置 order 表规则
        TableRuleConfiguration orderTableRuleConfig = new
                TableRuleConfiguration("order", "sharding${0..1}.order");

        // 配置 product 表规则
        TableRuleConfiguration productTableRuleConfig = new
                TableRuleConfiguration("product", "sharding0.product${0..1}");


        // 配置 custom 表规则
        TableRuleConfiguration customTableRuleConfig = new
                TableRuleConfiguration("custom", "sharding1.custom${[‘2020_01‘,‘2020_02‘]}");


        // 配置 order 表分库策略（根据 order_id 取模分片）
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new
                InlineShardingStrategyConfiguration("order_id", "sharding${order_id % 2}"));

        // 配置 product 表分表策略（根据 product_id 取模分片）
        productTableRuleConfig.setTableShardingStrategyConfig(new
                InlineShardingStrategyConfiguration("product_id", "product${product_id % 2}"));

        // 配置 custom 表分表策略（根据 create_time 进行分片）
        customTableRuleConfig.setTableShardingStrategyConfig(new
                StandardShardingStrategyConfiguration("create_time", new MyShardingAlgorithm()));

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

        // 获取数据源对象
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("sharding0", sharding0);
        dataSourceMap.put("sharding1", sharding1);
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        return dataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        // 设置 *.Mapper 映射文件所在路径
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/**/*Mapper.xml"));
        // 设置实体类所在路径
        sqlSessionFactory.setTypeAliasesPackage("common.entity");
        return sqlSessionFactory.getObject();
    }

    @Bean
    public DataSourceTransactionManager shardTransactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


}

（4）MyShardingAlgorithm类：

package api.config;

import common.util.DateUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;
import java.util.Date;

/**
 * 自定义数据分片实现
 */
public class MyShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {

        // 通过日期对数据进行分片
        Date keyDate = preciseShardingValue.getValue();
        String dateStr = DateUtils.formatDate(keyDate, "yyyy_MM");

        for (String tableName : collection) {
            if (tableName.indexOf(dateStr) > 0) {
                return tableName;
            }
        }
        return null;
    }
}

（5）CustomMapper 接口：

package api.mapper;

import common.entity.Custom;

public interface CustomMapper {

    void addCustom(Custom custom);

}

（6）OrderMapper 接口：

package api.mapper;

import common.entity.Order;

import java.util.List;

public interface OrderMapper {

    void addOrder(Order order);

    void addBatchOrder(List<Order> list);

    List<Order> getOrderList();

    List<Order> getOrderListByIn(List<Long> list);

    List<Order> getOrderListByJoin();


}

（7）ProductMapper 接口：

package api.mapper;

import common.entity.Product;

import java.util.List;

public interface ProductMapper {

    void addProduct(Product product);

    void addBatchProduct(List<Product> list);

}

（8）ICustomService 接口：

package api.service;

import common.entity.Custom;

public interface ICustomService {

    void addCustom(Custom custom);

}

（9）CustomServiceImpl 类：

package api.service.impl;

import api.mapper.CustomMapper;
import api.service.ICustomService;
import common.entity.Custom;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class CustomServiceImpl implements ICustomService {

    @Autowired
    CustomMapper customMapper;

    @Override
    public void addCustom(Custom custom) {
        customMapper.addCustom(custom);
    }
}

（10）IOrderService 接口：

package api.service;

import common.entity.Order;

import java.util.List;

public interface IOrderService {

    void addOrder(Order order);

    void addBatchOrder(List<Order> list);

    List<Order> getOrderList();

    List<Order> getOrderListByIn(List<Long> list);

    List<Order> getOrderListByJoin();


}

（11）OrderServiceImpl 类：

package api.service.impl;

import api.mapper.OrderMapper;
import api.service.IOrderService;
import common.entity.Order;

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！