本文演示的是ShardingSphere-JDBC5.2整合mybatisPlus实现单库的水平分表功能,项目需要了解mybatisPlus, 基本上是在mybatisPlus的基础上加上 sharding-jdbc的水平分片的配置实现了一个数据库多个表的水平分表需求。
1.父pom依赖,由于本人项目情况就不做简化了,实际上这个依赖可以不用管,可以根据自己项目情况做调整,主要是springboot, springCloud Alibaba的依赖:
<?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>com.xmc</groupId>
<artifactId>springcloud-alibaba</artifactId>
<packaging>pom</packaging>
<version>1.0-SNAPSHOT</version>
<modules>
<!--这里的依赖实际上可以不管,是我在测试时建的模块-->
<module>shop-common</module>
<module>core</module>
<module>sharding</module>
</modules>
<!--父工程-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
</parent>
<!-- 依赖版本-->
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-cloud.version>Greenwich.RELEASE</spring-cloud.version>
<spring-cloud-alibaba.version>2.1.0.RELEASE</spring-cloud-alibaba.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>com.alibaba.cloud</groupId>
<artifactId>spring-cloud-alibaba-dependencies</artifactId>
<version>${spring-cloud-alibaba.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
</project>
子模块的pom依赖,核心依赖文件,必须引入sharding-jdbc和mybatisPlus的依赖。
<?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">
<parent>
<artifactId>springcloud-alibaba</artifactId>
<groupId>com.xmc</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>sharding</artifactId>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!--核心依赖shardingjdbc5.2-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--mybatisPlus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
</project>
PO层代码如下:
package com.xmc.po;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("shop_user")
public class User {
@TableId
private Integer id;
private String username;
private String password;
private String telephone;
}
Mapper接口如下:
package com.xmc.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmc.po.User;
public interface UserMapper extends BaseMapper<User> {
}
Service接口如下:
package com.xmc.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.xmc.po.User;
public interface UserService2 extends IService<User> {
User selectById(String userId);
int insert(User vo);
int update(User vo);
int delete(int userId);
}
Service实现类如下:
package com.xmc.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xmc.mapper.UserMapper;
import com.xmc.po.User;
import com.xmc.service.UserService2;
import org.springframework.stereotype.Service;
@Service
public class UserService2Impl extends ServiceImpl<UserMapper, User> implements UserService2 {
@Override
public User selectById(String userId) {
Integer integer = Integer.valueOf(userId);
return baseMapper.selectById(integer);
}
@Override
public int insert(User vo) {
return baseMapper.insert(vo);
}
@Override
public int update(User user) {
return baseMapper.updateById(user);
}
@Override
public int delete(int userId) {
return baseMapper.deleteById(userId);
}
}
controller如下:
package com.xmc.controller;
import com.xmc.po.User;
import com.xmc.service.UserService2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/test")
public class UserController2 {
@Autowired
private UserService2 userService2Impl;
@GetMapping("/get")
public User get(@RequestParam(name = "userId", required = false)String userId){
return userService2Impl.selectById(userId);
}
@PostMapping("/insert")
public int insert(@RequestBody User vo) {
return userService2Impl.insert(vo);
}
@PostMapping("/update")
public int update(@RequestBody User vo) {
return userService2Impl.update(vo);
}
@PostMapping("/delete")
public int delete(@RequestBody User vo) {
return userService2Impl.delete(vo.getId());
}
}
启动类如下:
package com.xmc;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages="com.xmc.mapper")
public class ShardingApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingApplication.class, args);
}
}
核心配置文件application如下,只演示单库的水平分表示例:
server.port= 8991
spring.application.name = service-sharding
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
## shardingjdbc 分片策略
## 配置数据源,给数据源起名称
spring.shardingsphere.datasource.names=d1
#spring.shardingsphere.mode.type=Standalone
## 一个实体类对应多张表,覆盖
spring.main.allow-bean-definition-overriding=true
#
##配置数据源具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.d1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.d1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.d1.jdbc-url=jdbc:mysql://127.0.0.1:3306/shop?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.d1.username=root
spring.shardingsphere.datasource.d1.password=abcd1234
#指定 逻辑表 shop_user 表分布情况,配置表在哪个数据库里面,表名称都是什么
#d1.shop_user_$->{0..2} 表示 d1.shop_user_0,d1.shop_user_1,d1.shop_user_2
spring.shardingsphere.rules.sharding.tables.shop_user.actual-data-nodes=d1.shop_user_$->{0..2}
#
##指定表分片策略的分片列名称为id
spring.shardingsphere.rules.sharding.tables.shop_user.table-strategy.standard.sharding-column=id
## 分片算法配置
#分片算法名称
spring.shardingsphere.rules.sharding.tables.shop_user.table-strategy.standard.sharding-algorithm-name=table-user-inline
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.table-user-inline.type=INLINE
# 分片算法属性配置
# shop_user_$->{id % 3} 这个写法一定要注意 sql穿id参数时必须是数字类型而不能是字符串类型,否则sql在经过shardingSphere解析的时候会报MismatchedInlineShardingAlgorit 异常的
spring.shardingsphere.rules.sharding.sharding-algorithms.table-user-inline.props.algorithm-expression=shop_user_$->{id % 3}
# 打开 sql 输出日志
spring.shardingsphere.props.sql-show=true
测试结果如下:
如果id=1,则id%3 = 1,则最终解析为查询shop_user_1表。
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@77b7511d] will not be managed by Spring
==> Preparing: SELECT id,username,password,telephone FROM shop_user WHERE id=?
==> Parameters: 1(Integer)
2022-10-23 13:33:33.821 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,username,password,telephone FROM shop_user WHERE id=?
2022-10-23 13:33:33.821 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=36, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=26, identifier=IdentifierValue(value=password, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=28, stopIndex=36, identifier=IdentifierValue(value=telephone, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=43, stopIndex=51, identifier=IdentifierValue(value=shop_user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=53, stopIndex=62, expr=BinaryOperationExpression(startIndex=59, stopIndex=62, left=ColumnSegment(startIndex=59, stopIndex=60, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-10-23 13:33:33.821 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : Actual SQL: d1 ::: SELECT id,username,password,telephone FROM shop_user_1 WHERE id=? ::: [1]
<== Columns: id, username, password, telephone
<== Row: 1, 曹操, 123,
<== Total: 1
如果id=2,则id%3=2,则最终解析为查询shop_user_2表。
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@732245af] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@45a3fe37] will not be managed by Spring
==> Preparing: SELECT id,username,password,telephone FROM shop_user WHERE id=?
==> Parameters: 2(Integer)
2022-10-23 13:35:56.237 INFO 71852 --- [nio-8991-exec-7] ShardingSphere-SQL : Logic SQL: SELECT id,username,password,telephone FROM shop_user WHERE id=?
2022-10-23 13:35:56.237 INFO 71852 --- [nio-8991-exec-7] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=36, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=26, identifier=IdentifierValue(value=password, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=28, stopIndex=36, identifier=IdentifierValue(value=telephone, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=43, stopIndex=51, identifier=IdentifierValue(value=shop_user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=53, stopIndex=62, expr=BinaryOperationExpression(startIndex=59, stopIndex=62, left=ColumnSegment(startIndex=59, stopIndex=60, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-10-23 13:35:56.237 INFO 71852 --- [nio-8991-exec-7] ShardingSphere-SQL : Actual SQL: d1 ::: SELECT id,username,password,telephone FROM shop_user_2 WHERE id=? ::: [2]
<== Columns: id, username, password, telephone
<== Row: 2, 22222, 12323, 157665068669
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@732245af]
如果id=3,则id%3=0,则最终解析为查询shop_user_0表。
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a04071c] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@7f688f8] will not be managed by Spring
==> Preparing: SELECT id,username,password,telephone FROM shop_user WHERE id=?
==> Parameters: 3(Integer)
2022-10-23 13:37:23.155 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,username,password,telephone FROM shop_user WHERE id=?
2022-10-23 13:37:23.155 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=36, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=26, identifier=IdentifierValue(value=password, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=28, stopIndex=36, identifier=IdentifierValue(value=telephone, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=43, stopIndex=51, identifier=IdentifierValue(value=shop_user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=53, stopIndex=62, expr=BinaryOperationExpression(startIndex=59, stopIndex=62, left=ColumnSegment(startIndex=59, stopIndex=60, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-10-23 13:37:23.156 INFO 71852 --- [nio-8991-exec-9] ShardingSphere-SQL : Actual SQL: d1 ::: SELECT id,username,password,telephone FROM shop_user_0 WHERE id=? ::: [3]
<== Columns: id, username, password, telephone
<== Row: 3, 张飞, 1, 123
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a04071c]
其他的增删改操作的测试结果就不演示了。