MyBatis动态编写Sql语句

<?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.java1234.mappers.StudentMapper">
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>

</resultMap>

        <!--if条件-->

<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!=nulll">
and age=#{age}
</if>

</select>

<!-- choose，when 和otherwise 条件-->
<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>

<!--1，自动加上where；

              2，如果where 子句以and 或者or 开头，则自动删除第一个and 或者or；-->

<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!=nulll">
and age=#{age}
</if>
</where>

</select>

<!--trim功能和where 元素类似，提供了前缀，后缀功能，更加灵活；-->
<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!=nulll">
and age=#{age}
</if>
</trim>

</select>

<!--foreach 循环-->
<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>

        <!-- 1，自动加上set；
               2，自动剔除最后一个逗号“，”；-->

<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>

</mapper> 

测试类

@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);
}
}

@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);
}
}


@Test
public void testSearchStudents3() {
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.searchStudents3(map);
for(Student student:studentList){
System.out.println(student);
}
}

@Test
public void testSearchStudents4() {
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.searchStudents4(map);
for(Student student:studentList){
System.out.println(student);
}
}

@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);
}
}

@Test
public void testUpdateStudent(){
logger.info("更新学生(带条件)");
Student student=new Student();
student.setId(1);
student.setName("张三3");
student.setAge(13);
studentMapper.updateStudent(student);
sqlSession.commit();
}