ShardingSphere-JDBC实现分库分表

建表2个库，4个表

-- ----------------------------
-- Table structure for admin0
-- ----------------------------
DROP TABLE IF EXISTS `admin0`;
CREATE TABLE `admin0`  (
  `id` bigint(20) NOT NULL,
  `role_id` bigint(20) NULL DEFAULT NULL,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

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 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.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.chen</groupId>
    <artifactId>sharding</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding</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</artifactId>
        </dependency>

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

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

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

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

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

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

<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>-->
<!--            <version>5.0.0</version>-->
<!--        </dependency>-->
    </dependencies>

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

</project>

实体

package com.chen.sharding.entity;

import lombok.Data;

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

@Data
public class Admin implements Serializable {
//    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    private Long roleId;

    private String username;

    private Date createTime;
}

Mapper

package com.chen.sharding.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.chen.sharding.entity.Admin;
import org.springframework.stereotype.Repository;

@Repository
public interface AdminMapper extends BaseMapper<Admin> {

}

启动类

package com.chen.sharding;

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

@MapperScan("com.chen.sharding.mapper")
@SpringBootApplication
public class ShardingApplication {

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

}

测试类

package com.chen.sharding;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.chen.sharding.entity.Admin;
import com.chen.sharding.entity.User;
import com.chen.sharding.mapper.AdminMapper;
import com.chen.sharding.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;
import java.util.List;


@SpringBootTest
class ShardingApplicationTests {

        水平分片    

    @Autowired
    AdminMapper adminMapper;

    @Test
    void insert() {
        for (long i = 1L; i <= 100L; i++) {
            Admin model = new Admin();
            model.setRoleId(i);
            model.setUsername("chenxb");
            model.setCreateTime(new Date());
            adminMapper.insert(model);
        }
    }

    @Test
    void getPage() {
        QueryWrapper<Admin> wrapper = new QueryWrapper();
        wrapper.orderByAsc("role_id");
        //wrapper.eq("role_id", 1L);
        int pageSize = 10;
        Integer page = 1;
        Page<Admin> pager = new Page<>(page, pageSize);
        IPage<Admin> iPage = adminMapper.selectPage(pager, wrapper);
        System.out.println(iPage.getRecords());
    }

    @Test
    void selectById() {
        Admin model = adminMapper.selectById(1514453238195740674L);
        System.out.println(model);
    }

        专库专表    

    @Autowired
    UserMapper userMapper;

    @Test
    void insertUser() {
        for (long i = 1L; i <= 10L; i++) {
            User model = new User();
            model.setRoleId(i);
            model.setUsername("chenxb");
            model.setCreateTime(new Date());
            userMapper.insert(model);
        }
    }

    @Test
    void getPageUser() {
        QueryWrapper<User> wrapper = new QueryWrapper();
        wrapper.orderByAsc("role_id");
        int pageSize = 5;
        Integer page = 1;
        Page<User> pager = new Page<>(page, pageSize);
        IPage<User> iPage = userMapper.selectPage(pager, wrapper);

        System.out.println(iPage.getRecords());
        System.out.println(iPage.getCurrent());
        System.out.println(iPage.getPages());
        System.out.println(iPage.getTotal());
    }

    @Test
    void getPageUser2() {
        QueryWrapper<User> wrapper = new QueryWrapper();
        wrapper.orderByAsc("role_id");
        wrapper.eq("role_id", 1L);

        List<User> list= userMapper.selectList(wrapper);

        System.out.println(list);
    }

    @Test
    void selectByIdUser() {
        User model = userMapper.selectById(1514434974388400130L);
        System.out.println(model);
    }
}

配置类application.properties

#打印sql日志
spring.shardingsphere.props.sql.show=true

#运行实体类重载，对应多个表
spring.main.allow-bean-definition-overriding=true

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2

################################ 水平分片 ################################

# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=Cxb#1987

# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=Cxb#1987

#数据分布情况
spring.shardingsphere.sharding.tables.admin.actual-data-nodes=ds$->{0..1}.admin$->{0..1}

#指定主键id生成规则，SNOWFLAKE是雪花算法
spring.shardingsphere.sharding.tables.admin.key-generator.column=id
spring.shardingsphere.sharding.tables.admin.key-generator.type=SNOWFLAKE

##分库(执行专库专表需注释以下两句)
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=role_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds${role_id % 2}

#分表
spring.shardingsphere.sharding.tables.admin.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.admin.table-strategy.inline.algorithm-expression=admin${id % 2}


################################ 专库专表 ################################

# 配置数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/ds2?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=Cxb#1987

#数据分布情况
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds2.user

#指定主键id生成规则，SNOWFLAKE是雪花算法
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE