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

大家好，我是讯享网，很高兴认识大家。

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 = -L; 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.csdn.net/download/p_moriarty/