浅谈mybatis的使用

MyBatis 基本配置与使用指南一、JDBC VS MyBatisJDBC 原始写法 手写 SQL → 手动设置参数 → 手动遍历 ResultSet → 手动关连接 100 行代码里 80 行是样板代码 MyBatis 只写 SQL 和映射关系剩下的它全包了 100 行变成 20 行JDBCMyBatisJPASQL 控制完全手写手写灵活自动生成学习成本低中高复杂查询代码多XML / 注解灵活Criteria API 复杂适用简单项目⭐ 国内主流简单 CRUD二、Spring Boot 集成 MyBatis2.1 依赖dependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion3.0.4/version/dependencydependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactIdscoperuntime/scope/dependency!-- 分页插件推荐 --dependencygroupIdcom.github.pagehelper/groupIdartifactIdpagehelper-spring-boot-starter/artifactIdversion2.1.0/version/dependency2.2 配置文件spring:datasource:url:jdbc:mysql://localhost:3306/mydb?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf-8username:rootpassword:123456driver-class-name:com.mysql.cj.jdbc.Driver# MyBatis 配置mybatis:mapper-locations:classpath:mapper/*.xml# XML 映射文件位置type-aliases-package:com.example.entity# 实体类别名XML 中不用写全路径configuration:map-underscore-to-camel-case:true# 下划线转驼峰user_name → userNamelog-impl:org.apache.ibatis.logging.stdout.StdOutImpl# 控制台打印 SQL三、两种使用方式注解 vs XML3.1 注解方式 — 简单 SQLMapperpublicinterfaceUserMapper{Select(SELECT * FROM users WHERE id #{id})UserfindById(Longid);Select(SELECT * FROM users WHERE age #{minAge})ListUserfindByMinAge(Param(minAge)intage);Insert(INSERT INTO users(name, age, email) VALUES(#{name}, #{age}, #{email}))Options(useGeneratedKeystrue,keyPropertyid)// 回填自增 IDintinsert(Useruser);Update(UPDATE users SET name #{name}, age #{age} WHERE id #{id})intupdate(Useruser);Delete(DELETE FROM users WHERE id #{id})intdelete(Longid);}3.2 XML 方式 — 复杂 SQL⭐推荐MapperpublicinterfaceUserMapper{UserfindById(Longid);ListUserfindByCondition(UserQueryquery);intinsert(Useruser);intupdate(Useruser);intdelete(Longid);intbatchInsert(Param(list)ListUserusers);}?xml version1.0 encodingUTF-8?!DOCTYPEmapperPUBLIC-//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmappernamespacecom.example.mapper.UserMapper!-- resultMap字段映射 --resultMapiduserMaptypecom.example.entity.Useridpropertyidcolumnid/resultpropertyuserNamecolumnuser_name/resultpropertyagecolumnage/resultpropertyemailcolumnemail//resultMap!-- 基础 CRUD --selectidfindByIdresultMapuserMapSELECT * FROM users WHERE id #{id}/selectselectidfindByConditionresultMapuserMapSELECT * FROM userswhereiftestname ! null and name ! AND name LIKE CONCAT(%, #{name}, %)/ififtestminAge ! nullAND age #{minAge}/ififtestmaxAge ! nullAND agelt; #{maxAge}/ififtestemail ! nullAND email #{email}/if/whereORDER BY id DESC/selectinsertidinsertuseGeneratedKeystruekeyPropertyidINSERT INTO users(name, age, email) VALUES(#{name}, #{age}, #{email})/insertupdateidupdateUPDATE userssetiftestname ! nullname #{name},/ififtestage ! nullage #{age},/ififtestemail ! nullemail #{email},/if/setWHERE id #{id}/updatedeleteiddeleteDELETE FROM users WHERE id #{id}/delete!-- 批量插入 --insertidbatchInsertuseGeneratedKeystruekeyPropertyidINSERT INTO users(name, age, email) VALUESforeachcollectionlistitemuserseparator,(#{user.name}, #{user.age}, #{user.email})/foreach/insert!-- 动态 IN 查询 --selectidfindByIdsresultMapuserMapSELECT * FROM users WHERE id INforeachcollectionidListitemidopen(separator,close)#{id}/foreach/select/mapper四、动态 SQL 标签速查标签作用示例if条件判断if testname ! nullAND name #{name}/ifwhere自动去掉开头 AND/OR无条件时去掉整个 WHEREwhere.../whereset自动去掉结尾逗号set.../setforeach遍历集合IN/ 批量 INSERTchoosewhenotherwiseif-else 分支多个条件互斥时用trim灵活控制前后缀比 where/set 更灵活sqlinclude复用 SQL 片段字段列表、公共条件!-- choose 示例分支选择 --selectidfindresultMapuserMapSELECT * FROM userswherechoosewhentestname ! nullAND name #{name}/whenwhentestemail ! nullAND email #{email}/whenotherwiseAND status ACTIVE/otherwise/choose/where/select!-- sql 片段复用 --sqliduserColumnsid, name, age, email, created_at/sqlselectidfindByIdresultMapuserMapSELECTincluderefiduserColumns/FROM users WHERE id #{id}/select!-- trim 示例 --trimprefixWHEREprefixOverridesAND |ORiftestname ! nullAND name #{name}/ififtestage ! nullAND age #{age}/if/trim五、ResultMap 进阶一对多 / 多对一5.1 表结构-- 用户表users(id,name,age)-- 订单表订单属于一个用户orders(id,user_id,product_name,amount)5.2 多对一订单 → 用户DatapublicclassOrder{privateLongid;privateStringproductName;privateBigDecimalamount;privateUseruser;// 订单关联的用户}resultMapidorderMaptypecom.example.entity.Orderidpropertyidcolumnid/resultpropertyproductNamecolumnproduct_name/resultpropertyamountcolumnamount/!-- association多对一 / 一对一 --associationpropertyuserjavaTypecom.example.entity.Useridpropertyidcolumnuser_id/resultpropertynamecolumnuser_name/resultpropertyagecolumnuser_age//association/resultMapselectidfindOrderByIdresultMaporderMapSELECT o.id, o.product_name, o.amount, u.id AS user_id, u.name AS user_name, u.age AS user_age FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.id #{id}/select5.3 一对多用户 → 订单列表DatapublicclassUser{privateLongid;privateStringname;privateIntegerage;privateListOrderorders;// 用户的订单列表}resultMapiduserWithOrdersMaptypecom.example.entity.Useridpropertyidcolumnid/resultpropertynamecolumnname/resultpropertyagecolumnage/!-- collection一对多 --collectionpropertyordersofTypecom.example.entity.Orderidpropertyidcolumnorder_id/resultpropertyproductNamecolumnproduct_name/resultpropertyamountcolumnamount//collection/resultMapselectidfindUserWithOrdersresultMapuserWithOrdersMapSELECT u.id, u.name, u.age, o.id AS order_id, o.product_name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.id #{id}/select六、#{} vs ${}#{} → 预编译占位符防 SQL 注入 ← 99% 用这个 ${} → 直接拼接不防注入 SELECT * FROM #{table} → SELECT * FROM ? ← 报错表名不能用占位符 SELECT * FROM ${table} → SELECT * FROM users ← 动态表名必须用这个 规则 字段值用 #{}userName、age、id 表名/列名/ORDER BY 字段用 ${}但必须白名单校验七、分页查询// PageHelper 分页最简单ServicepublicclassUserService{AutowiredprivateUserMapperuserMapper;publicPageInfoUserfindByPage(intpageNum,intpageSize,Stringname){// 这行代码后面紧跟的查询会被分页PageHelper.startPage(pageNum,pageSize);ListUserusersuserMapper.findByCondition(name);returnnewPageInfo(users);// PageInfo 包含总页数、总记录数、当前页、每页大小等}}!-- findByCondition 不用改和查全部一样 --selectidfindByConditionresultMapuserMapSELECT * FROM userswhereiftestname ! nullAND name LIKE CONCAT(%, #{name}, %)/if/where/select八、二级缓存慎用!-- MyBatis 一级缓存默认开启SqlSession 级别 --!-- 二级缓存需手动开启Mapper 级别 --mappernamespacecom.example.mapper.UserMappercache/!-- 开启二级缓存 --!-- useCachefalse 关闭某条 SQL 的缓存 --selectidfindByIdresultMapuserMapuseCachetrueSELECT * FROM users WHERE id #{id}/select/mapper⚠️ 二级缓存的坑 1. 多线程更新同一张表 → 缓存未失效 → 读到脏数据 2. 分布式环境下各节点缓存不互通 3. 联表查询时缓存命中率极低 建议分布式环境直接用 Redis 缓存别用 MyBatis 二级缓存九、常见问题问题原因解决Mapper.xml 找不到mapper-locations 配置路径不对确认 classpath 和目录结构字段映射为 NULL下划线命名和驼峰没转换map-underscore-to-camel-case: true#{} 报错There is no getter单个参数且没加 Paramfun(Param(id) Long id)或直接用_parameter返回 Map 而不是实体默认用 resultType联表查用 resultMap分页插件不生效PageHelper.startPage 后面找不到查询确保紧跟查询十、MyBatis-Plus进一步简化// MyBatis-Plus 的 Mapper继承 BaseMapper 自带 CRUDMapperpublicinterfaceUserMapperextendsBaseMapperUser{// 单表增删改查不需要写任何 SQL// userMapper.selectById(1L)// userMapper.selectList(new LambdaQueryWrapperUser().eq(User::getName, 张三))// userMapper.updateById(user)// userMapper.deleteById(1L)}MyBatis vs MyBatis-Plus MyBatis手写 SQL灵活适合复杂业务 MyBatis-Plus自动生成 CRUD省代码适合简单 CRUD 多的场景 → 大多数项目用 MyBatis-Plus 就够了遇到复杂 SQL 再手写十一、总结MyBatis 核心三层 ① Mapper 接口 → 定义方法签名 ② XML 映射文件 → 写 SQL 结果映射 ③ 实体类 → 承载查询结果 核心标签 查询select resultMap association/collection 条件where if foreach 更新set if只更新不为空的字段 一条铁律 能用 #{} 就别用 ${} 必须用 ${} 时ORDER BY / 表名一定加白名单校验