SpringBoot + MyBatis注解式实现Dept表完整CRUD（含分页/模糊/批量操作）
在SpringBoot项目开发中，MyBatis注解式开发无需编写XML映射文件，能极大简化持久层代码，提升开发效率。本文将以部门表（dept）为案例，完整实现基本增删改查、分页查询、模糊查询、批量新增、批量删除及局部修改功能，全程仅编写实体类、Mapper接口和测试类。
一、环境准备
核心技术：SpringBoot 2.7.x、MyBatis 3.x、MySQL 8.x、PageHelper（分页插件）
pom.xml文件配置
<dependencies>
<!--springMVC的依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis的依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter-test</artifactId>
<version>3.0.5</version>
<scope>test</scope>
</dependency>
<!--myBatis分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.6</version>
</dependency>
<!--测试依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--springboot 热部署-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<!--mysql 连接对象-->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
1.2 数据库表设计
1.3 核心配置（application.properties）
spring.application.name=boot-demo-dept
spring.datasource.url=jdbc:mysql://localhost:3306/employee?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
二、核心代码实现
项目结构说明（按以下目录创建，无需额外配置）：
2.1 实体类与查询对象
（1）部门实体类（Dept）
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
private Long deptno;
private String dname;
private String loc;
}
（2）查询对象（DeptQO）
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DeptQO {
private Long deptno;
private String dname;
private String loc;
}
2.2 Mapper接口（DeptMapper）
核心接口，用MyBatis注解编写SQL，无需XML。通过@Mapper注解将接口注入IOC容器，SpringBoot自动创建实例。
接口中包含所有CRUD、分页、模糊、批量操作，整合Provider类处理复杂SQL（模糊查询、批量操作等）：
package cn.wolfcode.mapper;
import cn.wolfcode.domain.Dept;
import cn.wolfcode.qo.DeptQO;
import org.apache.ibatis.annotations.*;
import org.springframework.util.StringUtils;
import java.util.List;
@Mapper
public interface DeptMapper {
@Select("select * from dept")
@Results({
@Result(property = "deptno",column = "deptno" ,id = true),
@Result(property = "dname",column = "dname"),
@Result(property = "loc",column = "loc")
})
List<Dept> selectAll();
@SelectProvider(type = DeptProvider.class,method = "selectByCondition")
@Results({
@Result(property = "deptno",column = "deptno" ,id = true),
@Result(property = "dname",column = "dname"),
@Result(property = "loc",column = "loc")
})
List<Dept> selectByCondition(DeptQO dept);
@Select("select * from dept where deptno=#{id}")
@Results({
@Result(property = "deptno",column = "deptno",id = true),
@Result(property = "dname",column = "dname"),
@Result(property = "loc",column = "loc")
})
Dept selectById(Long i);
@Insert("insert into dept(dname,loc) values (#{dname},#{loc})")
@Options(useGeneratedKeys = true,keyProperty = "deptno")
int insert(Dept dept);
@InsertProvider(type = DeptProvider.class,method = "inserts")
@Options(useGeneratedKeys = true,keyProperty = "deptno")
int inserts(List<Dept> dept);
@Delete("delete from dept where deptno=#{deptno}")
int delete(Long i);
@DeleteProvider(type = DeptProvider.class,method="deleteByIds")
int deleteByIds(Long[] i);
@Update("update dept set dname=#{dname},loc=#{loc} where deptno=#{deptno}")
int update(Dept dept);
@UpdateProvider(type = DeptProvider.class,method = "updatePatch")
int updatePatch(Dept dept);
class DeptProvider{
public String selectByCondition(DeptQO dept){
StringBuffer sql=new StringBuffer("select * from dept where 1=1 ");
if(!StringUtils.isEmpty(dept.getDname())) {
sql.append(" and dname like concat('%',#{dname},'%')");
}
if(!StringUtils.isEmpty(dept.getLoc()))
sql.append(" and loc like concat('%',#{loc},'%')");
//sql.substring(0,sql.length()-1);
return sql.toString();
}
public String inserts(List<Dept> dept){
StringBuffer sql = new StringBuffer("insert into dept(dname,loc) values ");
for(int i=0;i<dept.size();i++){
sql.append("(#{dept["+i+"].dname},#{dept["+i+"].loc}),");
}
String substring = sql.substring(0, sql.length() - 1);
return substring;
}
public String deleteByIds(Long[] i){
StringBuffer sql = new StringBuffer("delete from dept where deptno in(");
for (Long id:i){
sql.append(id).append(",");
}
String substring = sql.substring(0, sql.length() - 1);
substring=substring+")";
return substring;
}
public String updatePatch(Dept dept){
StringBuffer sql = new StringBuffer("update dept set");
if(!StringUtils.isEmpty(dept.getDname())){
sql.append(" dname=#{dname},");
}
if(!StringUtils.isEmpty(dept.getLoc())){
sql.append(" loc=#{loc},");
}
String substring = sql.substring(0, sql.length() - 1);
substring+=" where deptno=#{deptno}";
return substring;
}
}
}
2.3 测试类编写
创建测试类，通过@Autowired注入DeptMapper实例，逐个测试所有功能。测试类放在src/test/java目录下，与主程序包结构一致。
@SpringBootTest
class BootDemoDeptApplicationTests {
@Autowired
private DeptMapper deptMapper;
//查询全部
@Test
void selectAll() {
List<Dept> deptList=deptMapper.selectAll();
deptList.forEach(System.out::println);
}
//根据id查
@Test
void selectById() {
Long i=140L;
Dept dept=deptMapper.selectById(i);
System.out.println(dept);
}
//模糊查询
@Test
void selectByCondition() {
DeptQO dept=new DeptQO();
dept.setDname("A");
dept.setLoc("A");
List<Dept> deptList=deptMapper.selectByCondition(dept);
System.out.println(deptList);
deptList.forEach(System.out::println);
}
//分页查询
@Test
void selectByConditionPage() {
DeptQO dept=new DeptQO();
PageHelper.startPage(1,3);
List<Dept> deptList=deptMapper.selectByCondition(dept);
PageInfo<Dept> deptPageInfo = new PageInfo<>(deptList);
deptPageInfo.getList().stream().forEach(System.out::println);
}
//分页查询
@Test
void selectByConditionPage2() {
PageHelper.startPage(2,3);
List<Dept> deptList = deptMapper.selectAll();
PageInfo<Dept> deptPageInfo = new PageInfo<>(deptList);
deptPageInfo.getList().stream().forEach(System.out::println);
}
//增加
@Test
void insert(){
Dept dept = new Dept(null, "OTO", "ULC");
int i=deptMapper.insert(dept);
System.out.println(i>0?"添加成功":"添加失败");
}
//批量增加
@Test
void inserts(){
List<Dept> dept = List.of(
new Dept(null, "O.O", "ULC"),
new Dept(null,"O.o","URL")
);
int i=deptMapper.inserts(dept);
System.out.println(i>0?"添加成功":"添加失败");
}
//删除
@Test
void delete(){
Long i=181L;
int m=deptMapper.delete(i);
System.out.println(m>0?"删除成功":"删除失败");
}
//批量删除
@Test
void deleteByIds(){
Long[] i={182L,183L,184L};
int m=deptMapper.deleteByIds(i);
System.out.println(m>0?"删除成功":"删除失败");
}
//全量修改
@Test
void update(){
Dept dept = new Dept(120L, "产品部", "UK");
int m=deptMapper.update(dept);
System.out.println(m>0?"修改成功":"修改失败");
}
//局部修改1
@Test
void updatePatch1(){
Dept dept = new Dept(120L, null, "University");
int m=deptMapper.updatePatch(dept);
System.out.println(m>0?"修改成功":"修改失败");
}
//局部修改2
@Test
void updatePatch2(){
Dept deptData = new Dept(120L, null, "UK");
Dept dept = deptMapper.selectById(120L);
if(!StringUtils.isEmpty(deptData.getDname())){
dept.setDname(deptData.getDname());
}
if(!StringUtils.isEmpty(deptData.getLoc())){
dept.setLoc(deptData.getLoc());
}
int m=deptMapper.update(dept);
System.out.println(m>0?"修改成功":"修改失败");
}
}
三、关键知识点解析
3.1 MyBatis核心注解说明
-
@Mapper：将接口注入IOC容器，SpringBoot自动创建代理实现类，无需手动配置。
-
@Select/@Insert/@Delete/@Update：简化单条SQL操作，直接编写SQL语句。
-
@Results/@Result：用于字段映射（当实体类字段与数据库字段不一致时使用，本文字段一致可省略，但建议保留规范）。
-
@SelectProvider/@InsertProvider等：用于复杂SQL生成（如动态条件、批量操作），指定Provider类和方法，灵活拼接SQL。
-
@Options：配置主键自增，将数据库生成的自增主键赋值给实体类对应的字段。
3.2 PageHelper分页原理
PageHelper是MyBatis的分页插件，核心原理是动态代理+SQL拼接：
PageHelper.startPage()必须在查询语句之前调用，且仅对紧接着的第一个查询生效。
四、总结与扩展
4.1 总结
本文通过SpringBoot + MyBatis注解式开发，完整实现了部门表的所有核心持久层操作，核心优势：
-
无需编写XML映射文件，代码简洁，开发效率高。
-
通过Provider类灵活处理复杂SQL，兼顾灵活性和规范性。
-
整合PageHelper实现分页，简化分页逻辑。
浙公网安备 33010602011771号