具体项目结构、完整代码、数据库脚本在另外一篇博客文档中
springboot+mybatis+sharding-jdbc整合示例代码-CSDN博客
执行效果如下 DemoController.java
package com.example.demo;
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.List;
import java.util.Map;
@RestController
public class DemoController {
@Autowired
public OrderMapper orderMapper;
/**
* 根据id查询一条数据
* 结论：和未分片前的原表表现相同
* 实际执行sql
* Actual SQL: m1 ::: select * from t_order_1 where order_id = ? ::: [1]
* 返回结果：
* [
* {
* "user_id": 1,
* "price": 1.00,
* "order_id": 1,
* "status": "2"
* }
* ]
*/
@GetMapping("/queryByIds")
public Object queryByIds() {
List<Map> maps = orderMapper.queryByIds(1L);
return maps;
}
/**
* 查询出所有数据
* 没有分片键的查询，将对所有分片的表进行sql查询，读取到jvm，在jvm中进行汇总
* 结论：和未分片前的原表表现相同
* 实际执行sql
* Actual SQL: m0 ::: select * from t_order_0
* Actual SQL: m0 ::: select * from t_order_2
* Actual SQL: m1 ::: select * from t_order_1
* Actual SQL: m1 ::: select * from t_order_3
* 返回结果：省略
*/
@GetMapping("/queryAll")
public Object queryAll() {
List<Map> map = orderMapper.queryAll();
return map;
}
/**
* 分页和排序查询
* 没有分片键的查询，将对所有分片的表进行sql查询，读取到jvm，在jvm中进行重新排序和重新分页
* 分页参数limit中第一个offset将为0，第二个参数size将为pageNum*pageSize
* 举例：翻到第二页，将对所有表执行sql: select * from t_order_* order by order_id asc limit 0,20，如果你由4张表就会执行4次
* 结论：和未分片前的原表表现相同
* 实际执行sql
* Actual SQL: m0 ::: select * from t_order_0 order by order_id asc limit ?,? ::: [0, 20]
* Actual SQL: m0 ::: select * from t_order_2 order by order_id asc limit ?,? ::: [0, 20]
* Actual SQL: m1 ::: select * from t_order_1 order by order_id asc limit ?,? ::: [0, 20]
* Actual SQL: m1 ::: select * from t_order_3 order by order_id asc limit ?,? ::: [0, 20]
*
* 返回结果
* [
* {
* "user_id": 10,
* "price": 10.00,
* "order_id": 10,
* "status": "1"
* },
* {
* "user_id": 11,
* "price": 11.00,
* "order_id": 11,
* "status": "2"
* },
* {
* "user_id": 12,
* "price": 12.00,
* "order_id": 12,
* "status": "3"
* },
* {
* "user_id": 13,
* "price": 13.00,
* "order_id": 13,
* "status": "4"
* },
* {
* "user_id": 14,
* "price": 14.00,
* "order_id": 14,
* "status": "5"
* },
* {
* "user_id": 15,
* "price": 15.00,
* "order_id": 15,
* "status": "1"
* },
* {
* "user_id": 16,
* "price": 16.00,
* "order_id": 16,
* "status": "2"
* },
* {
* "user_id": 17,
* "price": 17.00,
* "order_id": 17,
* "status": "3"
* },
* {
* "user_id": 18,
* "price": 18.00,
* "order_id": 18,
* "status": "4"
* },
* {
* "user_id": 19,
* "price": 19.00,
* "order_id": 19,
* "status": "5"
* }
* ]
*/
@GetMapping("/pageList")
public Object pageList(@RequestParam(defaultValue = "2") Integer pageNum,
@RequestParam(defaultValue = "10") Integer pageSize
) {
int offset = (pageNum - 1) * pageSize;
List<Map> map = orderMapper.pageList(offset, pageSize);
return map;
}
/**
* left join测试
* left join左表是分片表，右表不是分片表
* 结论：所有分片表的left join一遍读取到jvm，在jvm中进行汇总，可以发现明明限制只返回3条数据，结果却返回了12条，与未分片之前的原表结果不同
* Logic SQL: select * from t_order LEFT JOIN m0.`full_user` ON t_order.user_id = m0.`full_user`.userid limit 0,3
*
* Actual SQL: m0 ::: select * from t_order_0 LEFT JOIN m0.`full_user` ON t_order_0.user_id = m0.`full_user`.userid limit 0,3
* Actual SQL: m0 ::: select * from t_order_2 LEFT JOIN m0.`full_user` ON t_order_2.user_id = m0.`full_user`.userid limit 0,3
* Actual SQL: m1 ::: select * from t_order_1 LEFT JOIN m0.`full_user` ON t_order_1.user_id = m0.`full_user`.userid limit 0,3
* Actual SQL: m1 ::: select * from t_order_3 LEFT JOIN m0.`full_user` ON t_order_3.user_id = m0.`full_user`.userid limit 0,3
* 返回结果：
* [
* {
* "user_id": 0,
* "price": 0.00,
* "id": 0,
* "order_id": 0,
* "userid": 0,
* "status": "1",
* "username": "小明0"
* },
* {
* "user_id": 4,
* "price": 4.00,
* "id": 4,
* "order_id": 4,
* "userid": 4,
* "status": "5",
* "username": "小黄4"
* },
* {
* "user_id": 8,
* "price": 8.00,
* "id": 8,
* "order_id": 8,
* "userid": 8,
* "status": "4",
* "username": "小黄8"
* },
* {
* "user_id": 2,
* "price": 2.00,
* "id": 2,
* "order_id": 2,
* "userid": 2,
* "status": "3",
* "username": "小红2"
* },
* {
* "user_id": 6,
* "price": 6.00,
* "id": 6,
* "order_id": 6,
* "userid": 6,
* "status": "2",
* "username": "小黑6"
* },
* {
* "user_id": 10,
* "price": 10.00,
* "id": 10,
* "order_id": 10,
* "userid": 10,
* "status": "1",
* "username": "小明10"
* },
* {
* "user_id": 1,
* "price": 1.00,
* "id": 1,
* "order_id": 1,
* "userid": 1,
* "status": "2",
* "username": "小黑1"
* },
* {
* "user_id": 5,
* "price": 5.00,
* "id": 5,
* "order_id": 5,
* "userid": 5,
* "status": "1",
* "username": "小明5"
* },
* {
* "user_id": 9,
* "price": 9.00,
* "id": 9,
* "order_id": 9,
* "userid": 9,
* "status": "5",
* "username": "小黄9"
* },
* {
* "user_id": 3,
* "price": 3.00,
* "id": 3,
* "order_id": 3,
* "userid": 3,
* "status": "4",
* "username": "小黄3"
* },
* {
* "user_id": 7,
* "price": 7.00,
* "id": 7,
* "order_id": 7,
* "userid": 7,
* "status": "3",
* "username": "小红7"
* },
* {
* "user_id": 11,
* "price": 11.00,
* "id": 11,
* "order_id": 11,
* "userid": 11,
* "status": "2",
* "username": "小黑11"
* }
* ]
*/
@GetMapping("/leftjoin")
public Object leftjoin() {
List<Map> map = orderMapper.leftjoin();
return map;
}
/**
* leftjoinSharding
*
* 测试left join左右两边都是分片表
*
* 结论：筛选条件没有分片字段的情况，left join的两边都是分片表，会在同一个数据源内进行类似与笛卡尔积一样的join计算,返回jvm进行汇总
* Logic SQL: select * from t_order LEFT JOIN user ON t_order.user_id = user.userid order by t_order.order_id limit 2,10
*
* Actual SQL: m1 ::: select * from t_order_1 LEFT JOIN user_3 ON t_order_1.user_id = user_3.userid order by t_order_1.order_id limit 0,12
* Actual SQL: m1 ::: select * from t_order_1 LEFT JOIN user_1 ON t_order_1.user_id = user_1.userid order by t_order_1.order_id limit 0,12
* Actual SQL: m1 ::: select * from t_order_3 LEFT JOIN user_3 ON t_order_3.user_id = user_3.userid order by t_order_3.order_id limit 0,12
* Actual SQL: m1 ::: select * from t_order_3 LEFT JOIN user_1 ON t_order_3.user_id = user_1.userid order by t_order_3.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_0 LEFT JOIN user_4 ON t_order_0.user_id = user_4.userid order by t_order_0.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_0 LEFT JOIN user_2 ON t_order_0.user_id = user_2.userid order by t_order_0.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_0 LEFT JOIN user_0 ON t_order_0.user_id = user_0.userid order by t_order_0.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_2 LEFT JOIN user_4 ON t_order_2.user_id = user_4.userid order by t_order_2.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_2 LEFT JOIN user_2 ON t_order_2.user_id = user_2.userid order by t_order_2.order_id limit 0,12
* Actual SQL: m0 ::: select * from t_order_2 LEFT JOIN user_0 ON t_order_2.user_id = user_0.userid order by t_order_2.order_id limit 0,12
*
* 返回结果确实是10个，可以发现其中有重复数据
*
* 返回结果：[
* {
* "user_id": 0,
* "price": 0.00,
* "id": 0,
* "order_id": 0,
* "userid": 0,
* "status": "1",
* "username": "小明0"
* },
* {
* "user_id": 1,
* "price": 1.00,
* "order_id": 1,
* "status": "2"
* },
* {
* "user_id": 1,
* "price": 1.00,
* "id": 1,
* "order_id": 1,
* "userid": 1,
* "status": "2",
* "username": "小黑1"
* },
* {
* "user_id": 2,
* "price": 2.00,
* "order_id": 2,
* "status": "3"
* },
* {
* "user_id": 2,
* "price": 2.00,
* "id": 2,
* "order_id": 2,
* "userid": 2,
* "status": "3",
* "username": "小红2"
* },
* {
* "user_id": 2,
* "price": 2.00,
* "order_id": 2,
* "status": "3"
* },
* {
* "user_id": 3,
* "price": 3.00,
* "order_id": 3,
* "status": "4"
* },
* {
* "user_id": 3,
* "price": 3.00,
* "id": 3,
* "order_id": 3,
* "userid": 3,
* "status": "4",
* "username": "小黄3"
* },
* {
* "user_id": 4,
* "price": 4.00,
* "order_id": 4,
* "status": "5"
* },
* {
* "user_id": 4,
* "price": 4.00,
* "order_id": 4,
* "status": "5"
* }
* ]
*
*/
@GetMapping("/leftjoinSharding")
public Object leftjoinSharding() {
List<Map> map = orderMapper.leftjoinSharding();
return map;
}
/**
* 对比
* 结果：报错： Cannot find table rule and default data source with logic table: 'full_t_order'] with root cause
*/
@GetMapping("/fullleftjoinSharding")
public Object fullleftjoinSharding() {
List<Map> map = orderMapper.fullleftjoinSharding();
return map;
}
/**
*
* 进行group-count测试
* Logic SQL: select status,count(*) from t_order group by status
*
* Actual SQL: m0 ::: select status,count(*) from t_order_0 group by status ORDER BY status ASC
* Actual SQL: m0 ::: select status,count(*) from t_order_2 group by status ORDER BY status ASC
* Actual SQL: m1 ::: select status,count(*) from t_order_1 group by status ORDER BY status ASC
* Actual SQL: m1 ::: select status,count(*) from t_order_3 group by status ORDER BY status ASC
* 最终运行结果
* [
* {
* "count(*)": 24,
* "status": "1"
* },
* {
* "count(*)": 24,
* "status": "2"
* },
* {
* "count(*)": 24,
* "status": "3"
* },
* {
* "count(*)": 24,
* "status": "4"
* },
* {
* "count(*)": 24,
* "status": "5"
* }
* ]
* 结论：所有分片表的group count到jvm，在jvm中进行汇总
*/
@GetMapping("/groupCount")
public Object groupCount() {
List<Map> map = orderMapper.groupCount();
return map;
}
/**
* 对比
* Logic SQL: select status,count(*) from m0.full_t_order group by status
* Actual SQL: m1 ::: select status,count(*) from m0.full_t_order group by status
* 返回结果
* [
* {
* "count(*)": 24,
* "status": "1"
* },
* {
* "count(*)": 24,
* "status": "2"
* },
* {
* "count(*)": 24,
* "status": "3"
* },
* {
* "count(*)": 24,
* "status": "4"
* },
* {
* "count(*)": 24,
* "status": "5"
* }
* ]
* 结论：所有分片表的group count到jvm，在jvm中进行汇总，分片表和原表查询结果相同
*/
@GetMapping("/fullgroupCount")
public Object fullgroupCount() {
List<Map> map = orderMapper.fullgroupCount();
return map;
}
/**
* 测试：根据分片键字段进行更新
* Rule Type: sharding
* Logic SQL: UPDATE t_order SET `price` = 0.01 WHERE `order_id` = 30
* Actual SQL: m0 ::: UPDATE t_order_2 SET `price` = 0.01 WHERE `order_id` = 30
* 返回：1
*/
@GetMapping("/updateById")
public Object updateById() {
Integer num = orderMapper.updateById();
return num;
}
/**
* 测试：根据非分片字段键进行更新
* 返回：1
*
* Logic SQL: UPDATE t_order SET `price` = 0.02 WHERE `price` = 0.01
*
* Actual SQL: m0 ::: UPDATE t_order_0 SET `price` = 0.02 WHERE `price` = 0.01
* Actual SQL: m0 ::: UPDATE t_order_2 SET `price` = 0.02 WHERE `price` = 0.01
* Actual SQL: m1 ::: UPDATE t_order_1 SET `price` = 0.02 WHERE `price` = 0.01
* Actual SQL: m1 ::: UPDATE t_order_3 SET `price` = 0.02 WHERE `price` = 0.01
*/
@GetMapping("/updateByOther")
public Object updateByOther() {
Integer num = orderMapper.updateByOther();
return num;
}
/**
* 手动指定id，插入一条数据
* Logic SQL: INSERT INTO `m0`.`t_order`(`order_id`, `price`, `user_id`, `status`) VALUES (1000, 0.01, 30, '1');
* Actual SQL: m0 ::: INSERT INTO `t_order_0`(`order_id`, `price`, `user_id`, `status`) VALUES (1000, 0.01, 30, '1');
*/
@GetMapping("/insertOneWithId")
public Object insertOne() {
Integer num = orderMapper.insertOneWithId();
return num;
}
/**
* Logic SQL: INSERT INTO `m0`.`t_order`(`price`, `user_id`, `status`) VALUES ( 0.02, 30, '1');
* Actual SQL: m0 ::: INSERT INTO `t_order_0`(`price`, `user_id`, `status`, order_id) VALUES (0.02, 30, '1', 925818249805824000);
* 在不指定id的情况下插入一条数据
*/
@GetMapping("/insertOneWithOutId")
public Object insertOneWithOutId() {
Integer num = orderMapper.insertOneWithOutId();
return num;
}
}
OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.OrderMapper">
<!--根据分表键order_id进行查询-->
<select id="queryByIds" resultType="map">
select * from t_order where order_id = #{order_id}
</select>
<!--没有筛选条件，直接查询全部-->
<select id="queryAll" resultType="map">
select * from t_order
</select>
<!--分页查询-->
<select id="pageList" resultType="map">
select * from t_order order by order_id asc limit #{pageNum},#{pageSize}
</select>
<!--left join查询-->
<select id="leftjoin" resultType="map">
select * from t_order LEFT JOIN m0.`full_user` ON t_order.user_id = m0.`full_user`.userid limit 0,3
</select>
<!--left join查询,left join左右表均为分片表-->
<select id="leftjoinSharding" resultType="map">
select * from t_order LEFT JOIN user ON t_order.user_id = user.userid order by t_order.order_id limit 2,10
</select>
<!--对比-left join查询,left join左右表均为分片表-->
<select id="fullleftjoinSharding" resultType="map">
select * from m0.full_t_order LEFT JOIN m0.full_user ON m0.full_t_order.user_id = m0.full_user.userid order by m0.full_t_order.order_id limit 2,10
</select>
<!--groupCount-->
<select id="groupCount" resultType="map">
select status,count(*) from t_order group by status
</select>
<!--对比-groupCount-->
<select id="fullgroupCount" resultType="map">
select status,count(*) from m0.full_t_order group by status
</select>
<update id="updateById">
UPDATE t_order SET `price` = 0.01 WHERE `order_id` = 30
</update>
<update id="updateByOther">
UPDATE t_order SET `price` = 0.02 WHERE `price` = 0.01
</update>
<insert id="insertOneWithId">
INSERT INTO `m0`.`t_order`(`order_id`, `price`, `user_id`, `status`) VALUES (1000, 0.01, 30, '1');
</insert>
<insert id="insertOneWithOutId">
INSERT INTO `m0`.`t_order`(`price`, `user_id`, `status`) VALUES ( 0.02, 30, '1');
</insert>
</mapper>application.properties
server.port=8752
spring.application.name=sharding-sphere-demo
spring.shardingsphere.datasource.names=m0,m1
spring.main.allow-bean-definition-overriding = true
#定义m0数据源
#这里的数据库名称需要和实际名称相同
# jdbcUrl、username、password这些变量名称需要根据type来变化
spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbcUrl=jdbc:mysql://localhost:3306/m0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456
#定义m1数据源
spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbcUrl=jdbc:mysql://localhost:3306/m1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
#表t_order的选择策略
#表t_order的数据库选择策略
#表t_order的数据库根据哪个字段进行分配选择
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
#具体的分配计算公式
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=m$->{order_id%2}
#根据哪个字段进行分片选择表
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#具体的选择表算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 4}
#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{0..1}.t_order_$->{0..5}
#这里需要列举出所有数据库表，如果没有命中分片键，sql将会在所有表中运行一遍
#由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m0.t_order_0,m0.t_order_2,m1.t_order_1,m1.t_order_3
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#定义第二个分片表user
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=m$->{id%5}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 5}
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m0.user_0,m1.user_1,m0.user_2,m1.user_3,m0.user_4
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
