MyBatis动态SQL：从条件拼接到底层原理实战

第一部分动态SQL概述1.1 为什么需要动态SQL⭐老师强调之前在Eclipse里查学生是在Java中用if-else硬拼接SQL——传名字、性别或班级就手动加条件和空格很麻烦。现在用动态SQL不用再自己复杂拼接了。对比硬拼接动态SQL实现方式代码里人工判断并拼字符串框架按条件自动处理易错点容易漏空格、多and/or框架自动处理维护性麻烦简洁⭐老师强调硬拼接是人工判断并拼字符串易漏空格出错动态SQL由框架按条件自动处理更简洁。两者内在联系是都为实现多条件查询但实现方式从手动变自动。1.2 参数设计优化⭐老师强调原本按姓名、性别、年龄分别传参但接口强制要求三个参数都必须传调用者不传就编不过。问题想按姓名查也得凑齐性别、年龄。解决改用传一个Student对象代替多个参数——对象里的字段可随意设没值的就不填。// ❌ 死板写法必须传三个参数 ListStudent search(String name, String sex, Integer age); // ✅ 灵活写法传对象按需设置字段 ListStudent searchStudent(Student student);⭐老师强调传参时若传入Student类型数据该对象本身肯定传了但像名字、性别这类字段有无值取决于是否显式设置——设置了才有没设置就是null。第二部分if 标签2.1 问题场景⭐老师强调用实体类查学生时若参数是null如age为null直接拼SQL会写成where age null找不到数据返回零拼接出错。问题SQLselect * from student where name ? and sex ? and age ? -- 若age为null变成 where age null查不到2.2 if 标签用法select idsearchStudent parameterTypecom.qcby.entity.Student resultTypecom.qcby.entity.Student select * from student where if testname!null and name! name #{name} /if if testsex!null and sex! and sex #{sex} /if if testage!null and age! and age #{age} /if /select⭐老师强调if标签类似Java的if判断但不能写if(条件)而是用test属性写条件。非空才把对应片段拼进语句。2.3 if 标签的问题⭐老师强调若不传name只传sex拼出where and sex?where后直接跟and不合理SQL语法出错。问题动态拼接要注意首条件不能带and。⭐老师强调动态拼接要注意首条件不能带and常需额外处理如where 11或trim前缀。拼接逻辑对但语义错——不传的字段不进条件没错但剩余条件若以and开头就破坏语句结构。第三部分where 标签3.1 where if 标签select idsearchStudent parameterTypecom.qcby.entity.Student resultTypecom.qcby.entity.Student select * from student where if testname!null and name! name #{name} /if if testsex!null and sex! and sex #{sex} /if if testage!null and age! and age #{age} /if /where /select⭐老师强调where标签的好处是能按语义自动剔除前方多余and/or——像只传年龄时它自己把sex前的and裁掉。这类似智能修剪枝叶只留有效条件避免语法错。效果对比传参生成SQL只传namewhere name ?传sex和agewhere sex? and age?传name、sex、agewhere name? and sex? and age?第四部分trim 标签4.1 trim 替代 whereselect idsearchStudent parameterTypecom.qcby.entity.Student resultTypecom.qcby.entity.Student select * from student trim prefixwhere prefixOverridesand | or if testname!null and name! name #{name} /if if testsex!null and sex! and sex #{sex} /if if testage!null and age! and age #{age} /if /trim /select4.2 trim 属性说明属性作用prefix前缀加在整体前面如whereprefixOverrides去掉第一个and或orsuffix后缀suffixOverrides去掉最后一个符号如逗号⭐老师强调trim标签是更通用的标记能代替set或where标签。prefixOverrides是去掉每句话前边的and/orsuffixOverrides是去掉句末符号。前后去什么都可配置。第五部分choose-when-otherwise 标签5.1 语法结构select idsearchStudent parameterTypecom.qcby.entity.Student resultTypecom.qcby.entity.Student select * from student where choose when testname!null and name! name #{name} /when when testsex!null and sex! and sex #{sex} /when when testage!null and age! and age #{age} /when otherwise and id 28 /otherwise /choose /where /select5.2 与Java if-else对比MyBatisJava说明choose整体分支结构包裹所有条件whenif/else if第一个when是if后续是else ifotherwiseelse都不满足时执行⭐老师强调程序会从上到下依次匹配条件——先判断第一个条件满足就只进该分支不满足再判断第二个三个都不满足才走otherwise分支。命中即止和if-else if一样。示例传参执行的SQL只传age38where age 38传name和age匹配到name分支where name ?都不传走otherwisewhere id 28第六部分set 标签动态修改6.1 问题场景⭐老师强调上午写法是set name?, age?, sex? where id?来做修改。三个参数都得传若少传一个如只改性别其余字段拼接到SQL里会是空值把原数据清空了。问题只想改年龄或性别却清空了名字等原有信息。6.2 set if 标签update idupdateStudent parameterTypecom.qcby.entity.Student update student set if testname!null and name! name #{name}, /if if testsex!null and sex! sex #{sex}, /if if testage!null and age! age #{age} /if /set where id #{id} /update⭐老师强调set会自动去掉末尾多余逗号按实际传参情况决定去留。这样能灵活按需更新不误清未传字段。6.3 trim 替代 setupdate idupdateStudent parameterTypecom.qcby.entity.Student update student trim prefixset suffixOverrides, if testname!null and name! name #{name}, /if if testsex!null and sex! sex #{sex}, /if if testage!null and age! age #{age} /if /trim where id #{id} /update⭐老师强调trim既能做前缀也能做后缀可当作一堆if条件的前缀来用。代替set时prefixset加前缀suffixOverrides,去掉末尾逗号。6.4 修改注意事项⭐老师强调必须写id条件修改时必须写where id?否则会误改全表数据参数莫漏漏传字段会导致置空提交事务改完要提交事务才生效第七部分foreach 标签批量操作7.1 批量删除接口int deleteStudent(Param(ids) Integer[] ids);映射文件delete iddeleteStudent delete from student where id in foreach collectionids itemid open( close) separator, #{id} /foreach /deleteforeach属性说明属性作用示例collection要循环的数组或集合idsitem数组中的每一个元素idopen循环开始(close循环结束)separator每个元素用什么隔开,生成的SQLdelete from student where id in (5, 6, 7)⭐老师强调open/close是整体循环的头尾包装只出现一次separator是每两个元素之间才插入不参与头尾多参数或复杂参数必须加Param注解才能注入7.2 批量添加接口int insertStudents(Param(students) ListStudent students);映射文件insert idinsertStudents insert into student(name,age,sex) values foreach collectionstudents itemstu separator, (#{stu.name},#{stu.age},#{stu.sex}) /foreach /insert生成的SQLinsert into student(name,age,sex) values (lili456,20,女),(lucy456,28,男),(tony456,32,女),(davi456,21,男)⭐老师强调批量添加本质就是单条insert语句里values后多组括号用逗号隔开foreach遍历集合每项拼出一组(#{stu.name},#{stu.age},#{stu.sex})拼的时候拿集合每一项用逗号隔开依次造结果如项1,项2,项3加括号、逗号等修饰都行第八部分完整代码汇总8.1 实体类Student.javapackage com.qcby.entity; public class Student { private Integer id; private String name; private Integer age; private String sex; public Student() {} public Student(String name, Integer age, String sex) { this.name name; this.age age; this.sex sex; } Override public String toString() { return Student{ id id , name name \ , age age , sex sex \ }; } public Integer getId() { return id; } public void setId(Integer id) { this.id id; } public String getName() { return name; } public void setName(String name) { this.name name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex sex; } }8.2 接口StudentDao.javapackage com.qcby.dao; import com.qcby.entity.Student; import org.apache.ibatis.annotations.Param; import java.util.List; public interface StudentDao { // 动态查找 ListStudent searchStudent(Student student); // 动态修改 int updateStudent(Student student); // 批量删除 int deleteStudent(Param(ids) Integer[] ids); // 批量添加 int insertStudents(Param(students) ListStudent students); }8.3 映射文件StudentDao.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.qcby.dao.StudentDao !-- where if 动态查找 -- select idsearchStudent parameterTypecom.qcby.entity.Student resultTypecom.qcby.entity.Student select * from student where if testname!null and name! name #{name} /if if testsex!null and sex! and sex #{sex} /if if testage!null and age! and age #{age} /if /where /select !-- trim 动态修改 -- update idupdateStudent parameterTypecom.qcby.entity.Student update student trim prefixset suffixOverrides, if testname!null and name! name #{name}, /if if testsex!null and sex! sex #{sex}, /if if testage!null and age! age #{age} /if /trim where id #{id} /update !-- foreach 批量删除 -- delete iddeleteStudent delete from student where id in foreach collectionids itemid open( close) separator, #{id} /foreach /delete !-- foreach 批量添加 -- insert idinsertStudents insert into student(name,age,sex) values foreach collectionstudents itemstu separator, (#{stu.name},#{stu.age},#{stu.sex}) /foreach /insert /mapper8.4 测试类StudentTest.javapackage com.qcby; import com.qcby.dao.StudentDao; import com.qcby.entity.Student; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class StudentTest { private InputStream inputStream null; private SqlSession session null; private StudentDao mapper null; Before public void init() throws IOException { inputStream Resources.getResourceAsStream(SqlMapConfig.xml); SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(inputStream); session sqlSessionFactory.openSession(); mapper session.getMapper(StudentDao.class); } After public void destroy() throws IOException { session.close(); inputStream.close(); } // 动态查找 Test public void run() { Student student new Student(); // student.setName(张三); // student.setAge(38); // student.setSex(女); ListStudent students mapper.searchStudent(student); for (Student stu : students) { System.out.println(stu); } } // 动态修改 Test public void update() { Student student new Student(); // student.setName(张三); // student.setAge(38); student.setSex(男); student.setId(28); int code mapper.updateStudent(student); session.commit(); System.out.println(code); } // 批量删除 Test public void delete() { int code mapper.deleteStudent(new Integer[]{5, 6, 7}); session.commit(); System.out.println(code); } // 批量添加 Test public void insert() { Student student1 new Student(lili456, 20, 女); Student student2 new Student(lucy456, 28, 男); Student student3 new Student(tony456, 32, 女); Student student4 new Student(davi456, 21, 男); ListStudent students new ArrayListStudent(); students.add(student1); students.add(student2); students.add(student3); students.add(student4); int code mapper.insertStudents(students); session.commit(); System.out.println(code); } }附录一动态SQL标签速查表标签作用典型场景if条件判断非空才拼动态查询条件where自动处理where和多余的and/or查询条件拼接trim通用格式化标签可替代where/set灵活定制前后缀choose/when/otherwise多选一分支if-else if-elseset动态更新自动去末尾逗号修改操作foreach遍历数组/集合批量删除、批量添加trim属性速查属性作用prefix整体前缀prefixOverrides去掉第一个and/orsuffix整体后缀suffixOverrides去掉末尾符号foreach属性速查属性作用collection要循环的数组或集合item每个元素的临时名open循环开始符号close循环结束符号separator元素间的分隔符