shardingsphare-jdbc 分库分表+读写分离+负载均衡

shardingsphare-jdbc 分库分表+读写分离+负载均衡

使用spring boot ,mybatis plus ,shardingsphare 搭建一个demo，水平分库分表。（demo中并未配置读写分离和安装负载均衡的工具），项目源码：https://gitee.com/lakelise/shardingsphare-jdbc.git

目录结构

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.3.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cnciems</groupId>
    <artifactId>shardingsphere</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingsphere</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <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>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>

        <!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

代码

web:OrderController

package com.cnciems.shardingsphere.order;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.common.collect.Ordering;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Random;

@RestController
@AllArgsConstructor
@RequestMapping("order")
public class OrderController {
    private OrderMapper orderMapper;

    @GetMapping("/insert")
    public int insert(){
        Order o ;
        for (int i = 1; i <=20 ; i++) {
            o = new Order();
//            o.setUserId(i);  使用系统给定的id,需要修改数据库中userId 字段的类型为varchar ,分库分表中，根据userId分库也需要修改
            o.setOrderId(new Random().nextInt(100));
            orderMapper.insert(o);
        }
        return 1;
    }

    @GetMapping("/get/{userId}")
    public Order getById(@PathVariable("userId") Integer userId){
        return orderMapper.selectById(userId);
    }

    @GetMapping("/get/{userId}/{orderId}")
    public Order getById(@PathVariable("userId") Integer userId,@PathVariable("orderId") Integer orderId){
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.eq("user_id",userId);
        q.eq("order_id",orderId);
        return orderMapper.selectOne(q);
    }
}

Entity : Order

package com.cnciems.shardingsphere.order;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.math.BigDecimal;

@Data
@TableName("t_order")
public class Order {
    @TableId
    @TableField("user_id")
    private String userId;

    @TableField("order_id")
    private Integer orderId;
}

数据层：OrderMapper

package com.cnciems.shardingsphere.order;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OrderMapper  extends BaseMapper<Order> {

}

启动类：ShardingsphereApplication

package com.cnciems.shardingsphere;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.BeanUtils;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;

@MapperScan("com.cnciems.shardingsphere.*")
@SpringBootApplication
public class ShardingsphereApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingsphereApplication.class, args);
    }
}

配置文件：application.properties

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.68.138:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.68.137:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{order_id % 2}

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2+1}

spring.shardingsphere.sharding.tables.t_order.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

数据脚本

-- 在两不同的数据库中执行如下sql 语句：
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */

CREATE TABLE `t_order_1` (
  `user_id` int(10) NOT NULL,
  `order_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `t_order_2` (
  `user_id` int(10) NOT NULL,
  `order_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据库架构