SpringBoot 整合 MyBatis 完整实战

SpringBoot MyBatis 可以说是国内后端开发最经典、最常用的组合了。本篇文章就来介绍一下SpringBoot如何整合MyBatis实现数据表的增删改查。一、引言SpringBoot 整合 MyBatis 是国内 Java 后端最主流的持久层方案• 灵活可控SQL 可优化、可维护• 适合复杂查询、多表关联、统计报表• 配合 XML 或注解开发效率极高• 分页、事务、缓存生态成熟这篇文章会带你完整实现1. 依赖与配置2. 数据库建表3. 实体类、Mapper接口、XML映射4. 单表增删改查5. 多条件动态查询6. 分页查询PageHelper7. Service 事务控制8. Controller 接口统一返回9. 真实开发规范 高频坑点解决二、引入完整依赖pom.xml包含MyBatis启动器、MySQL驱动、分页插件、Lombok、连接池!-- SpringBoot Web -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency !-- MyBatis SpringBoot Starter -- dependency groupIdorg.mybatis.spring.boot/groupId artifactIdmybatis-spring-boot-starter/artifactId version2.3.2/version /dependency !-- MySQL 驱动 -- dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId scoperuntime/scope /dependency !-- 分页插件 -- dependency groupIdcom.github.pagehelper/groupId artifactIdpagehelper-spring-boot-starter/artifactId version1.4.7/version /dependency !-- Lombok 简化实体 -- dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency三、application.yml 完整配置server: port:8080 spring: # 数据源配置 datasource: url:jdbc:mysql://localhost:3306/testdb?useUnicodetruecharacterEncodingutf-8serverTimezoneAsia/ShanghaiallowMultiQueriestrue username:root password:123456 driver-class-name:com.mysql.cj.jdbc.Driver # MyBatis 配置 mybatis: # XML 文件路径 mapper-locations:classpath:mapper/*.xml # 实体类别名 type-aliases-package:com.demo.entity configuration: # 下划线自动转驼峰必须开 map-underscore-to-camel-case:true # 打印SQL开发环境打开 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl四、建表 SQLuser 表CREATE TABLE user ( id bigintNOT NULL AUTO_INCREMENT COMMENT 主键ID, username varchar(50) NOT NULL COMMENT 用户名, password varchar(100) DEFAULTNULL COMMENT 密码, age intDEFAULTNULL COMMENT 年龄, email varchar(50) DEFAULTNULL COMMENT 邮箱, create_time datetime DEFAULTCURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT 更新时间, deleted tinyint DEFAULT0 COMMENT 逻辑删除 0未删 1已删, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;五、实体类Entitypackage com.demo.entity; import lombok.Data; import java.time.LocalDateTime; Data publicclassUser { private Long id; private String username; private String password; private Integer age; private String email; private LocalDateTime createTime; private LocalDateTime updateTime; private Integer deleted; }六、Mapper 接口数据访问层包含CRUD 多条件查询 分页package com.demo.mapper; import com.demo.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; publicinterfaceUserMapper { // 新增 intinsert(User user); // 根据ID删除 intdeleteById(Long id); // 根据ID修改 intupdateById(User user); // 根据ID查询 User selectById(Long id); // 查询全部 ListUser selectAll(); // 多条件动态查询 ListUser selectByCondition(Param(username) String username, Param(age) Integer age); }启动类添加 Mapper 扫描SpringBootApplication MapperScan(com.demo.mapper) public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }七、MyBatis XML 映射文件resources/mapper/UserMapper.xml?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.demo.mapper.UserMapper !-- 新增 -- insert idinsert insert into user(username,password,age,email) values(#{username},#{password},#{age},#{email}) /insert !-- 删除 -- delete iddeleteById delete from user where id#{id} /delete !-- 修改 -- update idupdateById update user set username#{username},age#{age},email#{email} where id#{id} /update !-- 根据ID查询 -- select idselectById resultTypeUser select * from user where id#{id} and deleted0 /select !-- 查询全部 -- select idselectAll resultTypeUser select * from user where deleted0 /select !-- 多条件动态查询 -- select idselectByCondition resultTypeUser select * from user where deleted0 if testusername ! null and username ! and username like concat(%,#{username},%) /if if testage ! null and age #{age} /if /where /select /mapper八、Service 层业务逻辑 事务8.1 UserService 接口package com.demo.service; import com.demo.entity.User; import com.github.pagehelper.PageInfo; import java.util.List; publicinterfaceUserService { // 新增 booleanadd(User user); // 删除 booleandelete(Long id); // 修改 booleanupdate(User user); // 根据ID查询 User getById(Long id); // 查询全部 ListUser list(); // 多条件查询 ListUser search(String username, Integer age); // 分页查询 PageInfoUser page(Integer pageNum, Integer pageSize); }8.2 UserServiceImpl 实现类package com.demo.service.impl; import com.demo.entity.User; import com.demo.mapper.UserMapper; import com.demo.service.UserService; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Resource; import java.util.List; Service publicclassUserServiceImplimplementsUserService { Resource private UserMapper userMapper; Override Transactional(rollbackFor Exception.class) publicbooleanadd(User user) { return userMapper.insert(user) 0; } Override Transactional(rollbackFor Exception.class) publicbooleandelete(Long id) { return userMapper.deleteById(id) 0; } Override Transactional(rollbackFor Exception.class) publicbooleanupdate(User user) { return userMapper.updateById(user) 0; } Override public User getById(Long id) { return userMapper.selectById(id); } Override public ListUser list() { return userMapper.selectAll(); } Override public ListUser search(String username, Integer age) { return userMapper.selectByCondition(username, age); } Override public PageInfoUser page(Integer pageNum, Integer pageSize) { PageHelper.startPage(pageNum, pageSize); ListUser list userMapper.selectAll(); returnnewPageInfo(list); } }九、Controller 层接口package com.demo.controller; import com.demo.common.Result; import com.demo.entity.User; import com.demo.service.UserService; import com.github.pagehelper.PageInfo; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import java.util.List; RestController RequestMapping(/user) publicclassUserController { Resource private UserService userService; // 新增 PostMapping(/add) public Result add(RequestBody User user) { userService.add(user); return Result.success(新增成功); } // 删除 DeleteMapping(/delete/{id}) public Result delete(PathVariable Long id) { userService.delete(id); return Result.success(删除成功); } // 修改 PutMapping(/update) public Result update(RequestBody User user) { userService.update(user); return Result.success(修改成功); } // 单个查询 GetMapping(/detail/{id}) public Result detail(PathVariable Long id) { return Result.success(userService.getById(id)); } // 列表 GetMapping(/list) public Result list() { return Result.success(userService.list()); } // 多条件搜索 GetMapping(/search) public Result search( RequestParam(required false) String username, RequestParam(required false) Integer age) { return Result.success(userService.search(username, age)); } // 分页 GetMapping(/page) public Result page( RequestParam(defaultValue 1) Integer pageNum, RequestParam(defaultValue 10) Integer pageSize) { PageInfoUser page userService.page(pageNum, pageSize); return Result.success(page); } }十、统一返回结果类package com.demo.common; import lombok.Data; Data publicclassResultT { privateint code; private String msg; private T data; publicstatic T ResultT success(T data) { ResultT r newResult(); r.setCode(200); r.setMsg(操作成功); r.setData(data); return r; } publicstatic T ResultT success(String msg) { ResultT r newResult(); r.setCode(200); r.setMsg(msg); return r; } }十一、重点功能1. 分页查询PageHelper• 必须在查询前一行使用• 自动拼接 LIMIT无需改 SQL• 返回 PageInfo 包含总条数、总页数、当前页、数据列表2. 动态 SQLif / where / foreach适合多条件搜索、后台管理系统查询。3. 事务控制Transactional任何异常自动回滚。4. 下划线自动转驼峰map-underscore-to-camel-case: truecreate_time→createTime十二、注意事项1.Mapper 注入失败启动类必须加MapperScan2.XML 绑定异常namespace 必须和 Mapper 全类名一致3.分页不生效PageHelper.startPage 必须紧跟查询4.SQL 语句报错XML 中不能直接写、需要转义5.字段映射不上开启驼峰转换6.参数多个时必须用 Param否则 MyBatis 识别不到