动态SQL
第一节 if条件
第二节 choose,where和otherwise条件
第三节 where条件
1.自动加上where
2.如果where字句以and 或者 or开头,则自动删除第一个and或or
第四节 trim条件
功能和where元素类似,提供了前缀、后缀功能,更加灵活
第五节 foreach条件
第六节 set条件
1.自动加上set
2.自动删除最后一个逗号“,”
代码
Student.java
省略了getter和setter、toString方法
package com.fzhiy.entity;
public class Student {
private Integer id;//自动生成
private String name;
private Integer age;
}
StudentDao
package com.fzhiy.dao;
import java.util.List;
import java.util.Map;
import com.fzhiy.entity.Student;
public interface StudentDao {
public List<Student> searchStudents(Map<String,Object>map);
public List<Student> searchStudents2(Map<String,Object>map);
public List<Student> searchStudents3(Map<String,Object>map);
public List<Student> searchStudents4(Map<String,Object>map);
public List<Student> searchStudents5(Map<String,Object>map);
public int updateStudent(Student student);
}
StudentDao.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fzhiy.dao.StudentDao">
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
</resultMap>
<select id="searchStudents" parameterType="Map" resultMap="StudentResult">
select * from t_student
where gradeId=#{gradeId}
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</select>
<select id="searchStudents2" parameterType="Map" resultMap="StudentResult">
select * from t_student
<choose><!-- 标志通过某个字段来查询 , 注意是 == -->
<when test="searchBy=='gradeId'" >
where gradeId=#{gradeId}
</when>
<when test="searchBy=='name'">
where name like #{name}
</when>
<otherwise>
where age=#{age}
</otherwise>
</choose>
</select>
<select id="searchStudents3" parameterType="Map" resultMap="StudentResult">
select * from t_student
<where>
<if test="gradeId!=null">
gradeId=#{gradeId}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</where>
</select>
<select id="searchStudents4" parameterType="Map" resultMap="StudentResult">
select * from t_student
<trim prefix="where" prefixOverrides="and/or"> <!-- 和where元素功能相同 -->
<if test="gradeId!=null">
gradeId=#{gradeId}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</trim>
</select>
<select id="searchStudents5" parameterType="Map" resultMap="StudentResult">
select * from t_student
<if test="gradeIds!=null">
<where>
gradeId in
<foreach item="gradeId" collection="gradeIds" open="(" separator="," close=")">
#{gradeId}
</foreach>
</where>
</if>
</select>
<update id="updateStudent" parameterType="Student">
update t_student
<!-- set元素自动删除最后一个逗号 -->
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
where id=#{id}
</update>
</mapper>
小结
where和set元素较为常用,必须掌握。trim元素很强大,灵活,多用。