if条件
StudentMapper.java
public List<Student> searchStudents(Map<String,Object> map);
StudentMapper.xml
<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>
StudentTest.java
@Test
public void testSearchStudents() {
logger.info("查询学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
map.put("gradeId", 2);
map.put("name", "%李%");
map.put("age", 11);
List<Student> studentList=studentMapper.searchStudents(map);
for(Student student:studentList){
System.out.println(student);
}
}
choose,when和otherwise条件
StudentMapper.java
public List<Student> searchStudents2(Map<String,Object> map);
StudentMapper.xml
<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>
StudentTest.java
@Test
public void testSearchStudents2() {
logger.info("查询学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
map.put("searchBy", "age");
map.put("gradeId", 2);
map.put("name", "%李%");
map.put("age", 11);
List<Student> studentList=studentMapper.searchStudents2(map);
for(Student student:studentList){
System.out.println(student);
}
}
where条件
1,自动加上 where;
2,如果 where 子句以 and 或者 or 开头,则自动删除第一个 and 或者 or
StudentMapper.java新增searchStudents3方法、
StudentTest.java同if条件的StudentTest.java、
StudentMapper.xml
<select id="searchStudents3" parameterType="Map" resultMap="StudentResult">
select * from t_student
<where>
<if test="gradeId!=null">
where gradeId=#{gradeId}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</where>
</select>
trim条件
功能和 where 元素类似,提供了前缀,后缀功能,更加灵活;
StudentMapper.xml
<select id="searchStudents4" parameterType="Map" resultMap="StudentResult">
select * from t_student
<trim prefix="where" prefixOverrides="and|or">
<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>
foreach循环
StudentMapper.xml
<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>
StudentTest.java
@Test
public void testSearchStudents5() {
logger.info("查询学生(带条件)");
Map<String,Object> map=new HashMap<String,Object>();
List<Integer> gradeIds=new ArrayList<Integer>();
// gradeIds.add(1);
gradeIds.add(2);
map.put("gradeIds", gradeIds);
List<Student> studentList=studentMapper.searchStudents5(map);
for(Student student:studentList){
System.out.println(student);
}
}
set条件
StudentMapper.java
public int updateStudent(Student student);
StudentMapper.xml
<update id="updateStudent" parameterType="Student">
update t_student
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
where id=#{id}
</update>
StudentTest.java
@Test
public void testUpdateStudent(){
logger.info("更新学生(带条件)");
Student student=new Student();
student.setId(1);
student.setName("张三3");
student.setAge(13);
studentMapper.updateStudent(student);
sqlSession.commit();
}