您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- java- SpringBoot 整合 Sharding jdbc 实现应用层分库分表

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

bubuko 2022/1/25 20:07:36 java 字数 105852 阅读 1402 来源 http://www.bubuko.com/infolist-5-1.html

1、官网链接参考： https://shardingsphere.apache.org/document/current/cn/quick-start/sharding-jdbc-quick-start/ 感谢 ShardingSphere 团队！ 2、首先建立测试的数据库表： （1）表结构如下： ...

1、官网链接参考：

https://shardingsphere.apache.org/document/current/cn/quick-start/sharding-jdbc-quick-start/

感谢 ShardingSphere 团队！

 

2、首先建立测试的数据库表：

（1）表结构如下：

技术分享图片

 

 

 （2）建表脚本如下：

CREATE 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;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class OrderServiceImpl implements IOrderService {

    @Autowired
    OrderMapper orderMapper;

    @Override
    public void addOrder(Order order) {
        orderMapper.addOrder(order);
    }

    @Override
    public void addBatchOrder(List<Order> list) {
        orderMapper.addBatchOrder(list);
    }

    @Override
    public List<Order> getOrderList() {
        return orderMapper.getOrderList();
    }

    @Override
    public List<Order> getOrderListByIn(List<Long> list) {
        return orderMapper.getOrderListByIn(list);
    }

    @Override
    public List<Order> getOrderListByJoin() {
        return orderMapper.getOrderListByJoin();
    }
}

（12）IProductService 接口：

package api.service;

import common.entity.Product;

import java.util.List;

public interface IProductService {

    void addProduct(Product product);

    void addBatchProduct(List<Product> list);

}

（13）ProductServiceImpl 类：

package api.service.impl;

import api.mapper.ProductMapper;
import api.service.IProductService;
import common.entity.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class ProductServiceImpl implements IProductService {

    @Autowired
    ProductMapper productMapper;

    @Override
    public void addProduct(Product product) {
        productMapper.addProduct(product);
    }

    @Override
    public void addBatchProduct(List<Product> list) {
        productMapper.addBatchProduct(list);
    }
}

（14）StartSharding 类：

package api;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("api.mapper")
@SpringBootApplication(scanBasePackages = {"common", "api"})
public class StartSharding
{
    public static void main( String[] args )
    {
        SpringApplication.run(StartSharding.class, args);
    }
}

（15）CustomMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="api.mapper.CustomMapper">

    <!-- 新增 Custom -->
    <insert id="addCustom" parameterType="common.entity.Custom">
        insert into custom (
            custom_id,
            create_time
        ) values (
            #{customId},
            #{createTime}
        )
    </insert>

</mapper>

（16）OrderMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="api.mapper.OrderMapper">

    <resultMap id="OrderMap" type="common.entity.Order">
        <result column="order_id" property="orderId"></result>
        <result column="order_amount" property="orderAmount"></result>
        <result column="create_time" property="createTime"></result>
        <result column="user_id" property="userId"></result>
    </resultMap>


    <!-- 新增 order -->
    <insert id="addOrder" parameterType="common.entity.Order">
        /* sharding jdbc 对于关键字有限制，例如 order ，所以必须加上反引号 */
        insert into `order` (
            order_id,
            order_amount,
            create_time,
            user_id
        ) values (
            #{orderId},
            #{orderAmount},
            #{createTime},
            #{userId}
        )
    </insert>

    <!-- 批量新增 order -->
    <insert id="addBatchOrder" parameterType="java.util.List">
        insert into `order` (
            order_id,
            order_amount,
            create_time,
            user_id
        ) values
        <foreach collection="list" item="item" index="index" separator=",">
        (
            #{item.orderId},
            #{item.orderAmount},
            #{item.createTime},
            #{item.userId}
        )
        </foreach>
    </insert>

    <!-- order 表基本查询 -->
    <select id="getOrderList" resultMap="OrderMap">
        select * from `order`
    </select>

    <!-- order 表 in 查询 -->
    <select id="getOrderListByIn" resultMap="OrderMap">
        select * from `order`
        where order_id in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <!-- user 表与 order 表的 join 查询 -->
    <select id="getOrderListByJoin" resultMap="OrderMap">
        select b.* from `user` a
        inner join `order` b on a.user_id = b.user_id
    </select>


</mapper>

（17）ProductMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="api.mapper.ProductMapper">

    <!-- 新增 product -->
    <insert id="addProduct" parameterType="common.entity.Product">
        insert into product (
            product_id,
            product_name,
            product_price,
            create_time
        ) values (
            #{productId},
            #{productName},
            #{productPrice},
            #{createTime}
        )
    </insert>

    <!-- 批量新增 product -->
    <insert id="addBatchProduct" parameterType="java.util.List">
        insert into product (
          product_id,
          product_name,
          product_price,
          create_time
        ) values
        <foreach collection="list" item="item" index="index" separator=",">
        (
            #{item.productId},
            #{item.productName},
            #{item.productPrice},
            #{item.createTime}
        )
        </foreach>
    </insert>

</mapper>

（18）application.yml：

# 服务启动端口
server:
  port: 8089

# 定义数据源
datasource:
  sharding0:
    url: jdbc:mysql://127.0.0.1:3306/sharding0?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    maxActive: 5
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select ‘x‘
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    filters: stat,slf4j
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

  sharding1:
      url: jdbc:mysql://127.0.0.1:3306/sharding1?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      maxActive: 5
      initialSize: 1
      maxWait: 60000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select ‘x‘
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20
      filters: stat,slf4j
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000


# 打印sql
logging:
  level:
      api.mapper: debug

（19）Custom 实体类：

package common.entity;

import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

@Data
@Accessors(chain = true)
public class Custom implements Serializable {

    private Long customId;
    private Date createTime;

}

（20）Order 实体类：

package common.entity;

import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Data
@Accessors(chain = true)
public class Order implements Serializable {

    private Long orderId;
    private BigDecimal orderAmount;
    private Date createTime;
    private Long userId;

}

（21）Product 实体类：

package common.entity;

import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Data
@Accessors(chain = true)
public class Product implements Serializable {

    private Long productId;
    private String productName;
    private BigDecimal productPrice;
    private Date createTime;

}

（22）DateUtils 类：

package common.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtils {

    public static Date stringToDate(String date, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        try {
            return sdf.parse(date);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

    public static String formatDate(Date date, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }


}

（23）ShardingTest 单元测试类：

package api;


import api.service.ICustomService;
import api.service.IOrderService;
import api.service.IProductService;
import common.entity.Custom;
import common.entity.Order;
import common.entity.Product;
import common.util.DateUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.internal.matchers.Or;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingTest {

    @Autowired
    IOrderService orderService;

    @Autowired
    IProductService productService;

    @Autowired
    ICustomService customService;


    /**
     * 针对 order 表进行只分库不分表的新增测试
     */
    @Test
    public void testAddOrder() {
        Order order = new Order().setOrderId(0L)
                .setOrderAmount(new BigDecimal("10"))
                .setCreateTime(new Date());
        orderService.addOrder(order);

    }

    /**
     * 针对 product 表进行不分库只分表的新增测试
     */
    @Test
    public void testAddProduct() {
        Product product = new Product()
                .setProductId(10L)
                .setProductName("Mobile")
                .setProductPrice(new BigDecimal("3000"))
                .setCreateTime(new Date());

        productService.addProduct(product);
    }


    /**
     * 针对 order 表进行只分库不分表的批量新增测试
     */
    @Test
    public void testAddBatchOrder() {
        List<Order> orderList = new ArrayList<>();
        Order order1 = new Order()
                .setOrderId(10L)
                .setOrderAmount(new BigDecimal("100"))
                .setCreateTime(new Date())
                .setUserId(1L);


        Order order2 = new Order()
                .setOrderId(11L)
                .setOrderAmount(new BigDecimal("200"))
                .setCreateTime(new Date())
                .setUserId(1L);
        orderList.add(order1);
        orderList.add(order2);

        orderService.addBatchOrder(orderList);


    }


    /**
     * 针对 product 表进行不分库只分表的批量新增测试
     */
    @Test
    public void testAddBatchProduct() {
        List<Product> productList = new ArrayList<>();
        Product product1 = new Product()
                .setProductId(10L)
                .setProductName("Computer1")
                .setProductPrice(new BigDecimal("10000"))
                .setCreateTime(new Date());

        Product product2 = new Product()
                .setProductId(11L)
                .setProductName("Computer2")
                .setProductPrice(new BigDecimal("10000"))
                .setCreateTime(new Date());

        productList.add(product1);
        productList.add(product2);
        productService.addBatchProduct(productList);
    }


    /**
     * 针对 order 表进行只分库不分表的查询测试
     */
    @Test
    public void testOrderQuery() {
        List<Order> orderList = orderService.getOrderList();
        System.out.println(orderList);
    }

    /**
     * 针对 order 表进行只分库不分表的In查询测试
     */
    @Test
    public void testOrderQueryByIn() {
        List<Long> list = new ArrayList<>();
        list.add(0L);
        list.add(1L);
        List<Order> orderList = orderService.getOrderListByIn(list);
        System.out.println(orderList);
    }

    /**
     * 测试 user 表与 order 表的 join 查询
     */
    @Test
    public void testOrderJoinQuery() {
        List<Order> orderListByJoin = orderService.getOrderListByJoin();
        for (Order order : orderListByJoin) {
            System.out.println(order);
        }
    }

    /**
     * 针对 custom 表测试自定义的分表算法
     */
    @Test
    public void testAddCustom() {
        Date date = DateUtils.stringToDate("2020-02-01 10:00:00", "yyyy-MM-dd HH:mm:ss");
        Custom custom = new Custom().setCustomId(0L).setCreateTime(date);
        customService.addCustom(custom);

    }


}

4、测试总结：

1、对于 join 查询，Sharding jdbc 仅支持查询同一库下关联的表记录，不支持跨库查询，如果需要跨库查询，则需要拆分sql进行。

2、Sharding jdbc 已经整合了分布式事务的功能，不需要用户进行额外的配置。

3、Sharding jdbc 提供了自定义实现分片算法的接口，我们根据自己的业务，可自行定制数据路由规则。

 

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

原文：https://www.cnblogs.com/d0usr/p/12431606.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶