sharding-jdbc-4.0.0-rc1 整合spring boot +mybatis plus 分库分表（含踩坑）

最新推荐文章于 2025-05-27 16:49:40 发布

置顶 p_moriarty

最新推荐文章于 2025-05-27 16:49:40 发布

阅读量5.2k

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/p_moriarty/article/details/102523935

spring boot 同时被 3 个专栏收录

5 篇文章

订阅专栏

mybatis plus

1 篇文章

订阅专栏

sharding-jdbc

1 篇文章

订阅专栏

本文详细介绍使用Sharding-JDBC 4.0.0.rc1版本进行分库分表的具体步骤，包括Maven配置、数据源设置、表策略定义、自增主键生成等关键环节，并分享了实战中的常见问题及解决办法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sharding-jdbc 官网入口

http://shardingsphere.apache.org/index_zh.html

下面是实战Demo

目录结构

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>sharding-jdbc-4.0.0.rc1</groupId>
    <artifactId>com.zzb</artifactId>
    <version>1.0-SNAPSHOT</version>
    <description>sharding-jdbc-4.0.0.rc1 分库分表</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/>
    </parent>
    
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
    </properties>

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

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

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置文件

#数据源名称，多数据源以逗号分隔(这个数据源名称下面会用到)
spring.shardingsphere.datasource.names=ds-master-0,ds-master-1
#上面配置的数据源名称ds-master-0，用于指向这个datasource
#数据库连接池类名称
spring.shardingsphere.datasource.ds-master-0.type=com.alibaba.druid.pool.DruidDataSource
#数据库驱动类名
spring.shardingsphere.datasource.ds-master-0.driver-class-name=com.mysql.jdbc.Driver
#数据库url连接
spring.shardingsphere.datasource.ds-master-0.url=jdbc:mysql://127.0.0.1:3306/ds-master-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
#数据库用户名
spring.shardingsphere.datasource.ds-master-0.username=root
#数据库密码
spring.shardingsphere.datasource.ds-master-0.password=root
#同理-同上面
spring.shardingsphere.datasource.ds-master-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-master-1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master-1.url=jdbc:mysql://127.0.0.1:3306/ds-master-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-master-1.username=root
spring.shardingsphere.datasource.ds-master-1.password=root


#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds-master-$->{0..1}.t_user_$->{0..4}
#分片列名称
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
#分片算法行表达式（好像只取最后两个数字进行分片）
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{id % 5}


#自增列名称，缺省表示不使用自增主键生成器
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
#自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID/LEAF_SEGMENT
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

#
#spring.shardingsphere.sharding.tables.user.database-strategy.hint.algorithm-class-name=

##绑定表规则列表 就是逻辑表名    t_user  代表了 user_0 1 2 3 4
#spring.shardingsphere.sharding.binding-tables=t_user
##未知配置
#spring.shardingsphere.sharding.broadcast-tables=t_config



#默认数据库分片策略，同分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#分片算法行表达式
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds-master-$->{id % 2}
#使用了mybatis-plus
mybatis-plus.mapper-locations= classpath*:mappers/*.xml
mybatis-plus.map-underscore-to-camel-case=true
#显示sql
spring.shardingsphere.props.sql.show=true
server.port=8001

entiy层

package com.zzb.entity;

import com.baomidou.mybatisplus.annotation.TableName;

import java.io.Serializable;
@TableName("`t_user`")
public class User implements Serializable ,Comparable{

    private static final long serialVersionUID = -1205226416664488559L;
    private Long id;
    private String username;
    private String password;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    @Override
    public int compareTo(Object o) {
        User u= (User) o;
        return this.id.compareTo(u.id);
    }
}

dao层

package com.zzb.repository;

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


@Mapper
public interface UserRepository extends BaseMapper<User> {


}

service层

package com.zzb.service;

import com.zzb.entity.User;

import java.util.List;

public interface UserService {

    Integer addUser(User user);

    List<User> getUsers();

    User getUser(Integer id);

    boolean deleteOne(Integer id);

}

package com.zzb.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zzb.entity.User;
import com.zzb.repository.UserRepository;
import com.zzb.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl  implements UserService {

    @Resource
    UserRepository userRepository;

    @Override
    public Integer addUser(User user) {
        return userRepository.insert(user);
    }

    @Override
    public List<User> getUsers() {
        return userRepository.selectList(new QueryWrapper<>());
    }

    @Override
    public User getUser(Integer id) {
        return null;
    }

    @Override
    public boolean deleteOne(Integer id) {
        int i = userRepository.deleteById(id);
        return i>0;
    }


}

controller层

package com.zzb.web;


import com.zzb.entity.User;
import com.zzb.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.Collections;
import java.util.List;

@RestController
public class UserController {

    Logger logger= LoggerFactory.getLogger(UserController.class);

    @Autowired
    private UserService userService;

    @GetMapping("/getUsers")
    public Object getUsers() {
        List<User> list= userService.getUsers();
        Collections.sort(list);
        return list;
    }
    @GetMapping("/getUser")
    public Object getUser(@RequestParam Integer id) {
        User user= userService.getUser(id);
        return user;
    }
    @GetMapping("/addUsers")
    public Object add() {
        for(int i=1;i<=50;i++) {
            User user = new User();
            user.setUsername("sharding-"+(i));
            user.setPassword("pw"+i);
           long resutl=   userService.addUser(user);
           logger.info("insert:"+user.toString()+" result:"+resutl);
        }
        return "添加成功";
    }

    @GetMapping("/deleteOne")
    public Object deleteOne(@RequestParam Integer id) {
        userService.deleteOne(id);
        return "删除成功！";
    }
}

启动类

package com.zzb;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
@EnableConfigurationProperties
@MapperScan("com.zzb.repository")
public class ShardingJdbcApplication {

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

}

执行结果

执行插入

http://127.0.0.1:8001/addUsers

执行查询

http://127.0.0.1:8001/getUsers

下面是踩坑记录

原有的表名叫做 user
1. 在执行增改查的时候是没问题的，可是执行删除的时候却报了查询表名不存在，这就很郁闷了，查了各种demo，发觉都差不多，唯一不同基本就是表名，难道表名有关系？于是改了一下表名，t_user 删除就成功了，现在也不清楚为啥~。由于实体类叫User，而表名叫t_user，所以实体类多加了一个注解映射表名
后续会尝试主从分离

github地址：https://github.com/moriartyZZB/sharding-jdbc-4.0.0.rc1

csdn地址https://download.youkuaiyun.com/download/p_moriarty/11865675