springboot:2.1.9
cloud:Greenwich.SR2
sharding:4.0.0-RC1

本文的主要目的是通過shardingsphere對(duì)同一庫中的數(shù)據(jù)進(jìn)行分表處理。

1：首先在數(shù)據(jù)庫中新建3張表tab_user0戴而、tab_user1劳曹、tab_user2
2：再對(duì)配置文件進(jìn)行響應(yīng)修改
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.1.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sharding</groupId>
    <artifactId>stuTab</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>stuTab</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <druid>1.1.16</druid>
        <spring-cloud.version>Greenwich.SR2</spring-cloud.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--druid數(shù)據(jù)源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid}</version>
        </dependency>
        <!--shardingsphere最新版本-->
        <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>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.2</version>
        </dependency>
        <!--加入分頁依賴-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
            <version>2.1.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</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>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
        <finalName>stuTab</finalName>    #項(xiàng)目名稱
    </build>

</project>

application

server.port=8066
spring.shardingsphere.datasource.names=master #設(shè)置庫的別名

# 數(shù)據(jù)源 
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource  
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://xxx:3306/xxx?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=welcome123

#數(shù)據(jù)分表規(guī)則
#指定所需分的表
spring.shardingsphere.sharding.tables.tab_user.actual-data-nodes=master.tab_user$->{0..2}
#指定主鍵
spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.sharding-column=id
#分表規(guī)則為主鍵除以3取模
spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.algorithm-expression=tab_user$->{id % 3}

#設(shè)置mybatis相關(guān)信息
mybatis.typeAliasesPackage=com.sharding.stuTab.pojo
mybatis.mapperLocations=classpath:mapper/*.xml

#打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

#設(shè)置springcloud相關(guān)
spring.application.name=stuTab
spring.cloud.nacos.discovery.server-addr=10.241.95.109:8848,10.241.95.110:8848,10.241.95.111:8848
spring.cloud.nacos.discovery.cluster-name=stuTab
spring.cloud.nacos.discovery.service=stuTab
spring.cloud.nacos.discovery.register-enabled=true
spring.cloud.nacos.discovery.namespace=public
spring.cloud.nacos.discovery.heart-beat-timeout=5000
nacos.config.server-addr=10.241.95.109:8848,10.241.95.110:8848,10.241.95.111:8848

3：配置相關(guān)完成后進(jìn)入實(shí)際代碼部分
啟動(dòng)類

package com.sharding.stuTab;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
// cloud相關(guān)
@EnableDiscoveryClient
@Configuration
@SpringBootApplication
// 事務(wù)支持'
@EnableTransactionManagement
public class StuTabApplication {

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

}

接口類

package com.sharding.stuTab.controller;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.base.Strings;
import com.sharding.stuTab.mapper.TabUserMapper;
import com.sharding.stuTab.pojo.TabUser;
import com.sharding.stuTab.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@RestController
public class UserController {
    protected static final Logger logger = LoggerFactory
            .getLogger(UserController.class);

    @Autowired
    private UserService userService;

    @Autowired
    private TabUserMapper tabUserMapper;

    /**
     * 數(shù)據(jù)插入測(cè)試
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/dataInsert",method = RequestMethod.GET)
    @ResponseBody
    public JSONObject dataInsert(HttpServletRequest request, HttpServletResponse response ) throws Exception{
        try {
            ArrayList<TabUser> arr = new ArrayList<>();
            for (int i=0;i<10;i++){
                TabUser user = new TabUser();
                arr.add(user);
            }
            userService.dataInsert(arr);
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("return_code", "0");
            jsonObject.put("operation","2");
            return jsonObject;
        } catch (Exception e) {
            logger.error("UserController.dataInsert error", e);
            return getBusinessJson("參數(shù)異常，請(qǐng)聯(lián)系管理員");
        }
    }


    /**
     * 分頁查詢+數(shù)據(jù)刪除
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/dataDelete",method = RequestMethod.GET)
    @ResponseBody
    public JSONObject dataDelete(HttpServletRequest request, HttpServletResponse response ) throws Exception{
        try {
            TabUser queryBean = new TabUser();
            PageHelper.startPage(1,10);
            List<TabUser> tabUsers = tabUserMapper.selectAll(queryBean);
            PageInfo pageInfo = new PageInfo(tabUsers);
            tabUsers = pageInfo.getList();
            for (TabUser record : tabUsers){
                tabUserMapper.deleteByPrimaryKey(record.getId());
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("return_code", "0");
            jsonObject.put("operation","2");
            jsonObject.put("data",tabUsers);
            return jsonObject;
        } catch (Exception e) {
            logger.error("UserController.dataDelete error", e);
            return getBusinessJson("參數(shù)異常，請(qǐng)聯(lián)系管理員");
        }
    }

    /**
     * 數(shù)據(jù)全刪
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/dataDestory",method = RequestMethod.GET)
    @ResponseBody
    public JSONObject dataDestory(HttpServletRequest request, HttpServletResponse response ) throws Exception{
        try {
            tabUserMapper.deleteAll();

            JSONObject jsonObject = new JSONObject();
            jsonObject.put("return_code", "0");
            jsonObject.put("operation","2");

            return jsonObject;
        } catch (Exception e) {
            logger.error("UserController.dataDestory error", e);
            return getBusinessJson("參數(shù)異常固灵，請(qǐng)聯(lián)系管理員");
        }
    }

    public JSONObject getBusinessJson(String msg){
        if(Strings.isNullOrEmpty(msg)) {
            msg="數(shù)據(jù)不合法降狠，請(qǐng)重試！";
        }
        JSONObject jsonObject=new JSONObject();
        jsonObject.put("return_code", "-1002");
        jsonObject.put("return_msg", msg);
        return jsonObject;
    }
}

服務(wù)層

package com.sharding.stuTab.service;

import com.sharding.stuTab.pojo.TabUser;

import java.util.List;

public interface UserService {

    void dataInsert(List<TabUser> tabUserList);

}

package com.sharding.stuTab.service.impl;

import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.sharding.stuTab.mapper.TabUserMapper;
import com.sharding.stuTab.pojo.TabUser;
import com.sharding.stuTab.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private TabUserMapper tabUserMapper;


    @Override
    public void dataInsert(List<TabUser> tabUserList) {
       // 通過雪花算法來獲取主鍵
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        for (TabUser user : tabUserList) {
            user.setCreateTime(new Date());
            user.setUpdateTime(new Date());
            user.setStatus(0);
            user.setId(snowflake.nextId());
            user.setAge(28);
            user.setName("tudinle");
            user.setSex("男");
            tabUserMapper.insertSelective(user);
        }
    }

}

實(shí)體類

package com.sharding.stuTab.pojo;

import java.util.Date;

public class TabUser {
    private Long id;

    private String name;

    private String sex;

    private Integer age;

    private Date createTime;

    private Date updateTime;

    private Integer status;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }
}

dao層

package com.sharding.stuTab.mapper;

import com.sharding.stuTab.pojo.TabUser;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface TabUserMapper {
    int deleteByPrimaryKey(Long id);

    int insert(TabUser record);

    int insertSelective(TabUser record);

    TabUser selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(TabUser record);

    int updateByPrimaryKey(TabUser record);

    List<TabUser> selectAll(TabUser record);

    int deleteAll();
}

<?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.sharding.stuTab.mapper.TabUserMapper">
  <resultMap id="BaseResultMap" type="com.sharding.stuTab.pojo.TabUser">
    <!--@mbg.generated-->
    <!--@Table tab_user-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="sex" jdbcType="VARCHAR" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="status" jdbcType="INTEGER" property="status" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, `name`, sex, age, create_time, update_time, `status`
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from tab_user
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    <!--@mbg.generated-->
    delete from tab_user
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.sharding.stuTab.pojo.TabUser">
    <!--@mbg.generated-->
    insert into tab_user (id, `name`, sex, 
      age, create_time, update_time, 
      `status`)
    values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, 
      #{age,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}, 
      #{status,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.sharding.stuTab.pojo.TabUser">
    <!--@mbg.generated-->
    insert into tab_user
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name != null">
        `name`,
      </if>
      <if test="sex != null">
        sex,
      </if>
      <if test="age != null">
        age,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
      <if test="status != null">
        `status`,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="sex != null">
        #{sex,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        #{age,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null">
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="status != null">
        #{status,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.sharding.stuTab.pojo.TabUser">
    <!--@mbg.generated-->
    update tab_user
    <set>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      </if>
      <if test="sex != null">
        sex = #{sex,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="status != null">
        `status` = #{status,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.sharding.stuTab.pojo.TabUser">
    <!--@mbg.generated-->
    update tab_user
    set `name` = #{name,jdbcType=VARCHAR},
      sex = #{sex,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      `status` = #{status,jdbcType=INTEGER}
    where id = #{id,jdbcType=BIGINT}
  </update>

  <select id="selectAll" parameterType="com.sharding.stuTab.pojo.TabUser" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select
    <include refid="Base_Column_List" />
    from tab_user
  </select>

  <delete id="deleteAll" >
    <!--@mbg.generated-->
    delete from tab_user
  </delete>
</mapper>