where，自动去除SQL语句中多余的and
在SQL语句中含有多个if判断条件一旦某一个if判断不满足条件，那么这个SQL语句会多出来一个and，这样就会导致SQL执行报错。
将测试方法中给birthDate赋值的代码注销，再次执行测试
这种情况下可以使用where标签，自动去除多余的and，修改SQL语句为
<select id="getTeacherList" resultType="com.citi.entity.Teacher"> select * from t_teacher <where> <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空--> <if test="id!=null"> id > #{id} </if> <if test="className!=null"> and class_name like #{className} </if> <if test="birthDate!=null"> and birth_date < #{birthDate} </if> </where> </select> 复制代码
再次执行测试
注销id赋值代码，执行测试
trim，截取字符串
去掉后面的and，判断条件可能条件前或者条件后出现多余的and，条件前出现多余的and可以使用where标签去除，条件后出现的对于的and where标签无能为力，这时可以使用trim标签
修改SQL语句
<select id="getTeacherList" resultType="com.citi.entity.Teacher"> select * from t_teacher <where> <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空--> <if test="id!=null"> id > #{id} and </if> <if test="className!=null"> class_name like #{className} and </if> <if test="birthDate!=null"> birth_date < #{birthDate} and </if> </where> </select> 复制代码
注销测试代码中id和birthDate的赋值代码，执行测试
修改SQL语句
<select id="getTeacherList" resultType="com.citi.entity.Teacher"> select * from t_teacher <trim prefix="where" suffixOverrides="and"> <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空--> <if test="id!=null"> id > #{id} and </if> <if test="className!=null"> class_name like #{className} and </if> <if test="birthDate!=null"> birth_date < #{birthDate} and </if> </trim> </select> 复制代码
- prefix=""：前缀，为trim标签包裹的SQL增加一个前缀
- prefixOverrides=""：去除整体字符串前多余的字符
- suffix=""：为整体添加一个后缀
- suffixOverrides=""：去除整体字符串后面多余的字符 再次执行测试
建议查询条件就放在where标签中，and关键字放在条件前面，一旦条件判断为false，where会自动去除多余的and关键字
foreach，遍历集合
TeacherMapper接口中新增一个方法，根据传入的ids集合查询所有的Teacher
List<Teacher> getTeacherListByIds(@Param("ids")List<Integer> ids); 复制代码
在TeacmerMapper.xml中增加方法的SQL语句
<select id="getTeacherListByIds" resultType="com.citi.entity.Teacher"> select * from t_teacher where id IN <foreach collection="ids" item="id_item" separator="," open="(" close=")"> #{id_item} </foreach> </select> 复制代码
- collection：指要遍历的集合的key
- open：以什么开始
- close：以什么结束
- separator：遍历元素的分隔符
- item：遍历的元素的变量名
- index：索引
- 遍历List:
- index：保存了当前元素的索引
- item：保存了当前元素的值
- 遍历Map:
- index：保存当前元素的key
- item：保存当前元素的值
新增测试方法
@Test public void getTeacherListByIds() { TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class); List<Integer> ids = Arrays.asList(1,2,3); List<Teacher> teacherList = teacherMapper.getTeacherListByIds(ids); System.out.println(teacherList); } 复制代码
执行测试
choose，分支选择，相当于if-else
TeacherMapper接口中新增一个方法
List<Teacher> getTeacherListByChoose(Teacher teacher); 复制代码
在TeacherMapper.xml中增加SQL语句
<select id="getTeacherListByChoose" resultType="com.citi.entity.Teacher"> select * from t_teacher <where> <choose> <when test="id!=null"> id=#{id} </when> <when test="className!=null and !className.equals("")"> class_name = #{className} </when> <when test="birthDate!=null"> birth_date = #{birthDate} </when> <otherwise> 1=1 </otherwise> </choose> </where> </select> 复制代码
@Test public void getTeacherListByChoose() { TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class); Teacher teacher = new Teacher(); teacher.setId(1); teacher.setClassName("三年二班"); // teacher.setBirthDate(new Date()); List<Teacher> teacherList = teacherMapper.getTeacherListByChoose(teacher); System.out.println(teacherList); } 复制代码
执行测试
id、className、birthDate赋值代码全部注销
choose可以实现条件查询条件的分支选择，if可以实现查询条件的叠加
set，实现动态更新
实现动态更新，自动去除逗号“,”
TeacherMapper中新增方法
int updateTeacher(Teacher teacher); 复制代码
在TeacherMapper.xml中新增SQL语句
<update id="updateTeacher"> update t_teacher <set> <if test="teacherName!=null and !teacherName.equals("")"> teacher_name = #{teacherName}, </if> <if test="className!=null and !className.equals("")"> class_name = #{className}, </if> <if test="address!=null and !address.equals("")"> address = #{address}, </if> <if test="birthDate!=null"> birth_date = #{birthDate} </if> </set> <where> id = #{id} </where> </update> 复制代码
增加测试方法
@Test public void updateTeacher() { TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class); Teacher teacher = new Teacher(); teacher.setId(1); teacher.setClassName("三年三班"); teacher.setTeacherName("Tony Stark"); teacher.setBirthDate(new Date()); teacherMapper.updateTeacher(teacher); Teacher updateTeacher = teacherMapper.getTeacherById(1); System.out.println(updateTeacher); } 复制代码
执行测试
include与sql，抽取可重用的语句
以getTeacherListByIds方法的SQL映射语句为例
<!--抽取的可重复使用的SQL--> <sql id="select*">select * from t_teacher</sql> <select id="getTeacherListByIds" resultType="com.citi.entity.Teacher"> <!--引用抽取的SQL--> <include refid="select*"></include> where id IN <foreach collection="ids" item="id_item" separator="," open="(" close=")"> #{id_item} </foreach> </select>
