一、搭建环境
技术:SpringBoot2.5.6 + MybatisPlus3.4.1 + shardingsphere5.0.0 + Druid1.2.1连接池
1、在项目的pom.xml中引入maven依赖:
<?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> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.6</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.sharding.demo</groupId> <artifactId>sharding-demo</artifactId> <version>1.0-SNAPSHOT</version> <properties> <java.version>1.8</java.version> <commons.lang3.version>3.12.0</commons.lang3.version> <lombok.version>1.18.16</lombok.version> <guava.version>20.0</guava.version> <fastjson.version>1.2.75</fastjson.version> <hutool.version>5.5.1</hutool.version> <poi-ooxml.version>4.1.2</poi-ooxml.version> <!--升级log4j2版本,避免漏洞问题--> <log4j2.version>2.16.0</log4j2.version> <commons-pool2.version>2.11.0</commons-pool2.version> <shardingsphere.version>5.0.0</shardingsphere.version> <mysql-connector-java.version>8.0.28</mysql-connector-java.version> <druid.version>1.2.1</druid.version> <mybatis-plus.version>3.4.1</mybatis-plus.version> </properties> <dependencies> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatis-plus.version}</version> </dependency> <!--不能使用这个druid-spring-boot-starter,否则回合sharding-jdbc冲突--> <!--<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>${druid.version}</version> </dependency>--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>${druid.version}</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql-connector-java.version}</version> </dependency> <!--引入shardingsphere-jdbc--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>${shardingsphere.version}</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <!-- 去除springboot默认的logback配置--> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <!-- <optional>true</optional>--> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>${guava.version}</version> <scope>compile</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>${commons.lang3.version}</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>${fastjson.version}</version> </dependency> <!--工具包--> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>${hutool.version}</version> </dependency> <!--引入log4j2依赖--> <!-- 加入log4j2配置 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> </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> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <version>3.1.0</version> </plugin> </plugins> </build> </project>
2、按照水平分表的方式创建数据库、数据表
- 创建数据库 course_db。
- 在数据库中创建4张表 course_0 、 course_1和t_order_1、t_order_2
- 数据存放约定规则:添加的数据id为偶数放 第一张表中,id为奇数放 第二章 表中。
create database course_db; use course_db; create table course_0 ( `cid` bigint(20) primary key, `cname` varchar(50) not null, `user_id` bigint(20) not null, `cstatus` varchar(10) not null ); create table course_1 ( `cid` bigint(20) primary key, `cname` varchar(50) not null, `user_id` bigint(20) not null, `cstatus` varchar(10) not null ); create table t_order_1 ( `id` bigint(20) primary key, `name` varchar(50) not null, `status` bigint(20) not null ); create table t_order_2 ( `id` bigint(20) primary key, `name` varchar(50) not null, `status` bigint(20) not null );
3、在application.yml中定义公共的数据源,以及mybatis-plus,并且引入sharding-shphere文件
spring: application: name: kgf-java-learning2021 profiles: active: sharding datasource: url: jdbc:mysql://192.168.56.20:3306/course_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true username: root password: xxxxxx driver-class-name: com.mysql.cj.jdbc.Driver initial-size: 5 #初始连接数 min-idle: 10 #最小连接池数量 max-active: 20 #最大连接池数量 max-wait: 60000 #配置获取连接等待超时的时间 time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 min-evictable-idle-time-millis: 300000 #配置一个连接在池中最小生存的时间,单位是毫秒 max-evictable-idle-time-millis: 900000 #配置一个连接在池中最大生存的时间,单位是毫秒 validation-query: SELECT 1 FROM DUAL #配置检测连接是否有效 test-while-idle: true #建议配置为true,不影响性能,并且保证安全性申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 test-on-borrow: false #这里建议配置为TRUE,防止取到的连接不可用 test-on-return: false #归还连接时执行validationQuery检测连接是否有效 filters: stat,wall,slf4j #设置使用哪些插件 stat是统计,wall是SQL防火墙,防SQL注入的,log4j是用来输出统计数据的 pool-prepared-statements: false #是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。 max-pool-prepared-statement-per-connection-size: 0 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 #通过connectProperties属性来打开mergeSql功能;慢SQL记录 server: port: 8080 logging: config: classpath:log4j2.xml mybatis-plus: #注意:延迟加载不能在开启debug后,不能点开list信息,甚至不能把鼠标移到上面。否则在debug下会默认执行关联查询。 mapper-locations: classpath:/mappers/**/*.xml type-aliases-package: com.sharding.demo configuration: aggressive-lazy-loading: false lazy-loading-enabled: true
4、定义上面引入的application-sharding.yml文件
spring: shardingsphere: # 数据源配置 datasource: # 数据源名称,多数据源以逗号分隔,名称可以随意起名 names: db01 db01: driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource password: xxxxxx username: root url: jdbc:mysql://192.168.56.20:3306/course_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai # 规则配置 rules: sharding: # 分片算法配置 sharding-algorithms: course-table-inline: type: INLINE # 分片算法类型 props: # 分片算法的行表达式 algorithm-expression: course_$->{cid % 2} order-table-inline: type: INLINE # 分片算法类型 props: # 分片算法的行表达式 algorithm-expression: t_order_$->{id % 2 + 1} # 分布式序列算法配置 key-generators: course-snowflake: type: snowflake #分布式序列算法类型,雪花算法:SNOWFLAKE; UUID:UUID) column: cid #分布式主键列 order-snowflake: type: snowflake #分布式序列算法类型,雪花算法:SNOWFLAKE; UUID:UUID) column: id #分布式主键列 tables: # 逻辑表名称 course: # 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...} actual-data-nodes: db01.course_${0..1} # 分表策略 table-strategy: standard: # 分片列名称 sharding-column: cid # 分片算法名称 sharding-algorithm-name: course-table-inline key-generate-strategy: column: cid #分布式序列列名称 key-generator-name: course-snowflake #分布式序列算法名称 t_order: #表示t_order_分表 # 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...} actual-data-nodes: db01.t_order_${1..2} # 分表策略 table-strategy: standard: # 分片列名称 sharding-column: id # 分片算法名称 sharding-algorithm-name: order-table-inline key-generate-strategy: column: id #分布式序列列名称 key-generator-name: order-snowflake #分布式序列算法名称 # 属性配置 props: # 展示修改以后的sql语句 sql-show: true
二、编写代码实现对分表数据的操作
1、创建对应course的实体类
2、创建启动类扫描
3、创建controller类
package com.sharding.demo.web; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.sharding.demo.model.Course; import com.sharding.demo.service.CourseService; import org.apache.commons.lang.StringUtils; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; @RestController @RequestMapping(value = "course") public class CourseController { @Resource private CourseService courseService; @RequestMapping(value = "addCourse") public void addCourse(){ for (int i = 0; i < 10; i++) { Course course = new Course(); //cid由我们设置的策略,雪花算法进行生成(至少70年内生成的id不会重复) course.setCname("java"+i); course.setUserId(Long.valueOf(i)); course.setCstatus(i%2==0?String.valueOf(1):String.valueOf(0)); courseService.addCourse(course); } } @RequestMapping(value = "updateCourseByParam") public String updateCourseByParam(Course course){ return courseService.updateCourseByParam(course); } @RequestMapping(value = "delCourseByParam") public String delCourseByParam(Course course){ return courseService.delCourseByParam(course); } @RequestMapping(value = "queryCourseByParams") public List<Course> queryCourseByParams(Course course){ QueryWrapper<Course> wrapper = new QueryWrapper<>(); if (StringUtils.isNotBlank(course.getCname())){ wrapper.eq("cname", course.getCname()); } if (StringUtils.isNotBlank(course.getCstatus())){ wrapper.eq("cstatus", course.getCstatus()); } if (null!=course.getCid()){ wrapper.eq("cid", course.getCid()); } if (null!=course.getUserId()){ wrapper.eq("user_id", course.getUserId()); } return courseService.findCourse(wrapper); } }
package com.sharding.demo.web; import com.sharding.demo.model.Order; import com.sharding.demo.service.OrderService; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; @RestController @RequestMapping(value = "order") public class OrderController { @Resource private OrderService orderService; @RequestMapping(value = "addOrder") public void addOrder(){ for (int i = 0; i < 10; i++) { Order order = new Order(); //cid由我们设置的策略,雪花算法进行生成(至少70年内生成的id不会重复) order.setName("order_"+i); order.setStatus(i%2==0?1:0); orderService.addOrder(order); } } }
4、创建service接口类
package com.sharding.demo.service; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.sharding.demo.model.Course; import java.util.List; public interface CourseService { void addCourse(Course course); List<Course> findCourse(QueryWrapper<Course> wrapper); String updateCourseByParam(Course course); String delCourseByParam(Course course); }
package com.sharding.demo.service; import com.sharding.demo.model.Order; public interface OrderService { void addOrder(Order order); }
5、创建service的实现类
package com.sharding.demo.service.impl; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.sharding.demo.mapper.CourseMapper; import com.sharding.demo.model.Course; import com.sharding.demo.service.CourseService; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class CourseServiceImpl implements CourseService { @Resource private CourseMapper courseMapper; @Override public void addCourse(Course course) { courseMapper.insert(course); } @Override public List<Course> findCourse(QueryWrapper<Course> wrapper) { List<Course> courses = courseMapper.selectList(wrapper); return courses; } @Override public String updateCourseByParam(Course course) { courseMapper.updateCourseByParam(course); return "更新成功!"; } @Override public String delCourseByParam(Course course) { courseMapper.delCourseByParam(course); return "删除成功!"; } }
package com.sharding.demo.service.impl; import com.sharding.demo.mapper.OrderMapper; import com.sharding.demo.model.Order; import com.sharding.demo.service.OrderService; import org.springframework.stereotype.Service; import javax.annotation.Resource; @Service public class OrderServiceImpl implements OrderService { @Resource private OrderMapper orderMapper; @Override public void addOrder(Order order) { orderMapper.insertOrder(order); } }
6、创建Mapper接口类
package com.sharding.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.sharding.demo.model.Course; public interface CourseMapper extends BaseMapper<Course> { void updateCourseByParam(Course course); void delCourseByParam(Course course); }
package com.sharding.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.sharding.demo.model.Order; public interface OrderMapper extends BaseMapper<Order>{ void insertOrder(Order order); }
7、创建xml文件
<?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.demo.mapper.CourseMapper"> <update id="updateCourseByParam" parameterType="com.sharding.demo.model.Course"> update course <set> <if test="cname!=null and cname != ''"> cname = #{cname}, </if> <if test="cstatus!=null and cstatus != ''"> cstatus = #{cstatus}, </if> <if test="userId!=null and userId != ''"> user_id = #{userId}, </if> </set> <where> <if test="cid!=null and cid != ''"> cid = #{cid} </if> <if test="cstatus!=null and cstatus != ''"> and cstatus = #{cstatus} </if> </where> </update> <delete id="delCourseByParam"> delete from course <where> <if test="cid!=null and cid != ''"> cid = #{cid} </if> <if test="cstatus!=null and cstatus != ''"> and cstatus = #{cstatus} </if> </where> </delete> </mapper>
<?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.demo.mapper.OrderMapper"> <insert id="insertOrder" parameterType="com.sharding.demo.model.Order"> insert into t_order ( id, name, status )value( #{id}, #{name}, #{status} ) </insert> </mapper>
三、使用接口调用,进行测试
1、首先调用新增接口,向course_0和course_1中插入数据
路径:http://localhost:8080/course/addCourse
效果:
course_0表:
course_1表:
向t_order_1和t_order_2中插入数据
路径:http://localhost:8080/order/addOrder
效果:
t_order_1表:
t_order_2表:
![]()
2、根据条件查询数据
路径: http://localhost:8080/course/queryCourseByParams
加上条件:
3、删除数据接口调用
路径:http://localhost:8080/course/delCourseByParam?userId=3
4、调用修改接口
路径:http://localhost:8080/course/updateCourseByParam?userid=1&cname=test666