1、简介

创建3个数据库t1，t2，t3。每个库里面创建order表和order_item表。利用shardingSphere新增、查询、修改数据库表。

2、pom

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>


    <repositories>
        <!--阿里云仓库-->
        <repository>
            <id>aliyun</id>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
        </repository>
        <!--快照版本使用,正式版本无需添加此仓库-->
        <repository>
            <id>snapshots</id>
            <url>https://oss.sonatype.org/content/repositories/snapshots/</url>
        </repository>
    </repositories>
    <properties>
        <java.version>1.8</java.version>
        <skipTest>true</skipTest>
        <mybatis-plus-generator.version>3.1.2</mybatis-plus-generator.version>
        <mybatis-plus-boot-starter.version>3.1.2</mybatis-plus-boot-starter.version>
        <org.apache.velocity.version>2.1</org.apache.velocity.version>

    </properties>
    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--mybatis-plus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus-boot-starter.version}</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

3、yml配置文件

server:
  port: 8080



spring:
  shardingsphere:
    # 打印sql
    props:
      sql:
        show: true
    # 数据源配置
    datasource:
      # names必须对应数据库的名字，且与下方配置相对应
      names: t1,t2,t3
      t1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/t1?useUnicode=true&characterEncoding=utf8
        username: root
        password:
      t2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/t2?useUnicode=true&characterEncoding=utf8
        username: root
        password:
      t3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/t3?useUnicode=true&characterEncoding=utf8
        username: root
        password:
    sharding:
      # 分库默认数据库策略 如果不想分库，可不设此配置
      default-database-strategy:
        standard:
          # 分片键 type  表示根据 数据type这一列的值与自定义的算法进行分片
          shardingColumn: type
          # 精准分片算法
          preciseAlgorithmClassName: com.example.demo.config.DbShardingAlgorithm
          # 范围分片算法
#          rangeAlgorithmClassName: com.example.demo.config.DbShardingAlgorithm
      # 表策略配置
      tables:
        # order是逻辑名表
        order:
          actualDataNodes: t1.order,t2.order,t3.order
#          table-strategy:
#            standard:
#              #分片键 type即根据type这列的值与设定的算法进行分表
#              shardingColumn: type
#              preciseAlgorithmClassName: com.example.demo.config.TableShardingAlgorithm
        order_item:
          actualDataNodes: t1.order_item,t2.order_item,t3.order_item
#          table-strategy:
#            standard:
#              #分片键 type即根据type这列的值与设定的算法进行分表
#              shardingColumn: type
#              preciseAlgorithmClassName: com.example.demo.config.TableShardingAlgorithm

# mybatis-plus相关配置
mybatis-plus:
  type-aliases-package: com.example.demo.mapper
  mapper-locations: classpath:mapper/*.xml
  global-config:
    db-config:
      id-type: ID_WORKER_STR
  configuration:
    cache-enabled: false
    map-underscore-to-camel-case: true

4、com.example.demo.config.DbShardingAlgorithm

package com.example.demo.config;



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

import java.util.Collection;

/**
 * Created by xqh on 2021/3/27 17:24
 *
 * @Description
 */
public class DbShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    private static final String DB_NAME_PREFIX = "t";
    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> shardingValue) {
        String targetTable = DB_NAME_PREFIX + shardingValue.getValue();
        if (availableTargetNames.contains(targetTable)){
            return targetTable;
        }
        throw new UnsupportedOperationException("无法判定的值: " + shardingValue.getValue());
    }
}

5、利用mybatis-plus自动生成代码，service代码不粘了

@Data
@TableName(value = "`order`")
public class Order extends Model<Order> {

    @TableId(type = IdType.ID_WORKER_STR)
    private String id;
    private String name;
    private Date createTime;
    private String type;
    private BigDecimal amount;
}
@Data
@TableName(value = "`order_item`")
public class OrderItem extends Model<OrderItem> {

    @TableId(type = IdType.ID_WORKER_STR)
    private String id;
    private String name;
    private String orderId;
    private String type;
    private BigDecimal amount;
}

6、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="com.example.demo.mapper.OrderMapper">

    <select id="findOrderInfos" resultType="com.example.demo.controller.OrderInfo">

        SELECT
            o.id order_id,
            o.`name` order_name,
            o.type order_type,
            o.amount order_amount,
            i.id order_item_id,
            i.`name` order_item_name,
            i.type order_item_type,
            i.amount order_item_amount
        FROM
            order_item i
        LEFT JOIN `order` o ON i.order_id=o.id
        where 1=1
        <if test="type != null">
            and i.type=#{type}
        </if>

    </select>

    <select id="statistic1" resultType="com.example.demo.controller.OrderInfo1">

        SELECT
            o.id order_id,
            o.`name` order_name,
            o.type order_type,
            o.amount order_amount,
            sum(i.amount) order_item_amount,
            count(i.id) order_item_count,
            group_concat(i.`name`) order_item_names,
            group_concat(i.type) order_item_types
        FROM
            `order` o
        LEFT JOIN `order_item` i ON i.order_id=o.id
        where 1=1
            <if test="type != null">
                and o.type=#{type}
            </if>
        group by o.id
    </select>

    <select id="pageList" resultType="com.example.demo.model.OrderItem">
        select * from order_item
            where
                  1=1
        <if test="type != null">
            and type=#{type}
        </if>
            limit #{pageNum},#{pageSize}
    </select>

</mapper>

7、创建三个数据库t1，t2，t3，分别导入下面表

CREATE TABLE `order` (
  `id` varchar(64) NOT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_item` (
  `id` varchar(255) NOT NULL,
  `amount` decimal(15,5) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `order_id` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

8、Test测试

package com.example.demo;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.controller.OrderInfo;
import com.example.demo.controller.OrderInfo1;
import com.example.demo.mapper.OrderMapper;
import com.example.demo.model.Order;
import com.example.demo.model.OrderItem;
import com.example.demo.service.IOrderItemService;
import com.example.demo.service.IOrderService;
import net.bytebuddy.utility.RandomString;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sound.midi.Soundbank;
import java.math.BigDecimal;
import java.util.*;

@SpringBootTest
class DemoApplicationTests {

    @Autowired
    private IOrderService orderService;

    @Autowired
    private IOrderItemService orderItemService;

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void contextLoads() {
        List<Order> orders = new ArrayList<>(5);
        List<OrderItem> orderItems = new ArrayList<>(15);
        for (int i=0; i< 10; i++){
            String type = 1 + new Random().nextInt(3) + "";
            int amount = new Random().nextInt(1000) + 300;
            Order o = new Order();
            o.setName("type-"+ type);
            o.setCreateTime(new Date());
            o.setType(type);
            o.setAmount(new BigDecimal(amount));
            orders.add(o);
        }
        orderService.saveBatch(orders);
        orders.forEach(item -> {
            BigDecimal amount = item.getAmount();
            BigDecimal amount1 = amount.divide(new BigDecimal("4"));
            BigDecimal amount2 = amount1.add(new BigDecimal("10"));
            BigDecimal amount3 = amount.subtract(amount1.add(amount2));
            List<BigDecimal> bigDecimals = Arrays.asList(amount1, amount2, amount3);

            for (int k=0; k<3; k++){
                OrderItem oi = new OrderItem();
                oi.setType(item.getType());
                oi.setOrderId(item.getId());
                oi.setName(item.getName() +"-" + item.getType());
                oi.setAmount(bigDecimals.get(k));
                orderItems.add(oi);
            }
        });
        orderItemService.saveBatch(orderItems);
    }
    @Test
    void orderInfos() {
        List<OrderInfo> orderInfos1 = orderMapper.findOrderInfos("1");
        List<OrderInfo> orderInfos2 = orderMapper.findOrderInfos("2");
        List<OrderInfo> orderInfos3 = orderMapper.findOrderInfos("3");
        System.err.println("type1-----------------------------");
        orderInfos1.forEach(System.err::println);

        System.err.println("type2-----------------------------");
        orderInfos2.forEach(System.out::println);
        System.err.println("type3-----------------------------");
        orderInfos3.forEach(System.err::println);
    }

    // 统计 order_item数量和金额 和 order金额
    @Test
    void statistic1() {
        List<OrderInfo1> orderInfos1 = orderMapper.statistic1("1");
        List<OrderInfo1> orderInfos2 = orderMapper.statistic1("2");
        List<OrderInfo1> orderInfos3 = orderMapper.statistic1("3");
        System.err.println("type1-----------------------------");
        orderInfos1.forEach(System.err::println);
        System.err.println("type2-----------------------------");
        orderInfos2.forEach(System.out::println);
        System.err.println("type3-----------------------------");
        orderInfos3.forEach(System.err::println);
    }

    @Test
    void page() {
        List<OrderItem> orderItems1 = orderMapper.pageList("3", 1, 2);
        System.err.println("type3-----------------------------");
        System.out.println(orderItems1.size());
        orderItems1.forEach(System.err::println);
        System.err.println("type3--plus-----------------------------");

        IPage<OrderItem> page = orderItemService.lambdaQuery().eq(OrderItem::getType, "3").page(new Page<>(2, 2));
        page.getRecords().forEach(System.err::println);
    }

    @Test
    void orderItems() {
        List<OrderItem> orderItems1 = orderItemService.lambdaQuery().eq(OrderItem::getType, "1").list();
        List<OrderItem> orderItems2 = orderItemService.lambdaQuery().eq(OrderItem::getType, "2").list();
        List<OrderItem> orderItems3 = orderItemService.lambdaQuery().eq(OrderItem::getType, "3").list();
        System.err.println("type1-----------------------------");
        orderItems1.forEach(System.err::println);
        System.err.println("type2-----------------------------");
        orderItems2.forEach(System.out::println);
        System.err.println("type3-----------------------------");
        orderItems3.forEach(System.err::println);
    }

    @Test
    void contextLoads1() {
        List<Order> type1 = orderService.lambdaQuery().eq(Order::getType, "1").list();
        List<Order> type2 = orderService.lambdaQuery().eq(Order::getType, "2").list();
        List<Order> type3 = orderService.lambdaQuery().eq(Order::getType, "3").list();
        System.err.println("type1-----------------------------");
        type1.forEach(System.err::println);
        System.err.println("type2-----------------------------");
        type2.forEach(System.out::println);
        System.err.println("type3-----------------------------");
        type3.forEach(System.err::println);
    }

    @Test
    void update() {
        List<Order> type1 = orderService.lambdaQuery().eq(Order::getType, "1").list();
        type1.forEach(System.out::println);
        orderService.lambdaUpdate().set(Order::getName, "test-update").eq(Order::getType, "1").update();

        orderService.lambdaQuery()
                .list()
                .forEach(System.err::println);

    }

    @Test
    void removeAll() {
        orderService.remove(null);
        orderItemService.remove(null);
    }

}