MyBatis使用动态SQL语句

有时候，固定的SQL语句不能够满足我们的应用需求。我们需要在标准的基础上建立动态的查询语句。例如，在WEB的应用程序中，在标准的基础上，提供一个或多个输入选项或执行搜索。为了执行这些函数，我们需要在可选项的基础上建立动态查询语句。如果用户输入了多个标准的值，我们需要添加到WHERE的语句后。MyBatis提供子这些参数这实现动态语言：<if>、<choose>、<where>、<foreach>和<trim>。

1. if条件查询

<if>r的元素可以被嵌入到SQL的语句中。如果测试的情况满足其中一条if的语句，那么该if语句后的内容将会添加到SQL的语句中。

现在我们你想这样的一种场景，我在界面上有一个下拉框可供于选择教师的信息，然后，在课程的名字是个文本框，可以输入想要查找的课程，还有就是选择开始时期和结束日期。当用户点击查询的按钮时，我们需要显示课程的列表信息，这个时候我们调用过程如下：

1)        查询教师的信息

2)        查询输入的课程的名字，如果什么都没有查找到的话那么就返回所有的课程信息。

3)        查询课程的开始时间和结束时间，以用户输入的时间来查询。

查找课程的信息，我们可以使用下面的方法。

 <resultMap type="Course" id="CourseResult">
<id column="course_id" property="courseId"/>
<result column="name" property="name"/>
<result column="description" property="description"/>
<result column="start_date" property="startDate"/>
<result column="end_date" property="endDate"/>
</resultMap>
<select id="searchCourses" parameterType="hashmap"
resultMap="CourseResult">
<![CDATA[
SELECT * FROM COURSES
WHERE TUTOR_ID= #{tutorId}
<if test="courseName != null">
AND NAME LIKE #{courseName}
</if>
<if test="startDate != null">
AND START_DATE >= #{startDate}
</if>
<if test="endDate != null">
AND END_DATE <= #{endDate}
</if>
]]>
</select>

public interface CourseMapper
{
List<Course> searchCourses(Map<String, Object> map);
}
public void searchCourses()
{
Map<String, Object> map = new HashMap<String, Object>();
map.put("tutorId", 1);
map.put("courseName", "%java%");
map.put("startDate", new Date());
CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);
List<Course> courses = mapper.searchCourses(map);
for (Course course : courses) {
System.out.println(course);
}

这个将会生成的语句是：SELECT *FROM COURSES WHERE TUTOR_ID= ? AND
NAME like ? AND START_DATE >= ?。这就是使用动态的SQL语句，更容易去查询。

2.  choose、when和其它的情况

有时候，查询的功能需要根据查询的类型来查找。第一个，用户需要选择是否需要通过教师或课程或开始时间可结束时间来查询。MyBatis提供了<choose>的元素来实现动态SQL。现在我们通过写SQL语言来实现映射，查找课程的信息，如果我们没有输入任何的限制条件，那么就查找所有的课程信息。

<select id="searchCourses" parameterType="hashmap"
resultMap="CourseResult">
SELECT * FROM COURSES
<choose>
<when test="searchBy == 'Tutor'">
WHERE TUTOR_ID= #{tutorId}
</when>
<when test="searchBy == 'CourseName'">
WHERE name like #{courseName}
</when>
<otherwise>
WHERE TUTOR start_date >= now()
</otherwise>
</choose>
</select>

MyBatis在执行上面的语句时，如果第一种情况为True的话，那么就要添加SQL的语句中。如果没有找到适合的条件，那么就会执行<otherwise>的语句。

3.  Where情况

有时候查询的标准可能需要被选择。在例子中，至少有一个查询的条件是要被添加的，就如WHERE就是需要加入到查询语句中。同时，查询的语句还要添加AND或OR的限制条件。MyBatis就是提供了<where>的元素来实现动态的SQL查询。

在下面的例子中，我们想像这样的情况，那就是如果if的条件成立的话，那就会执行WHERE的语句。

<select id="searchCourses" parameterType="hashmap"
resultMap="CourseResult">
SELECT * FROM COURSES
<where>
<if test=" tutorId != null ">
TUTOR_ID= #{tutorId}
</if>
<if test="courseName != null">
AND name like #{courseName}
</if>
<if test="startDate != null">
AND start_date >= #{startDate}
</if>
<if test="endDate != null">
AND end_date <= #{endDate}
</if>
</where>
</select>

就如大家看到的，如果if语句返回的true，那么WHERE就会插入到查询语句中。当然，如果WHERE语句中已经有了AND或OR的字符，那么if中的AND或OR就自动去掉，或我们查找的是只有中间一个if的语句成立，那么一样的AND的前缀也是会去掉的。

4. trim情况

<trim>元素工作的性质与<where>的一样的，只是它提供了添加查询需要去掉前缀的定义，如AND或OR。不过在定义AND或OR时，你要使用分隔符。

<select id="searchCourses" parameterType="hashmap"
resultMap="CourseResult">
SELECT * FROM COURSES
<trim prefix="WHERE" prefixOverrides="AND | OR">
<if test=" tutorId != null ">
TUTOR_ID= #{tutorId}
</if>
<if test="courseName != null">
AND name like #{courseName}
</if>
</trim>
</select>

这里的<trim>将会插入到WHERE的语句中，如果<if>的情况成立的情况下。同时，在添加 WHERE时，会移除掉AND或OR的声明。

5. foreach循环

其它一个动态的的SQL，那就是<foreach>的元素了。它是非常有用的，在容器中， 我们使用过list和array都会使用foreach来遍历。不过这里使用的时候，我们需要建立AND/OR还有就是IN的情况。

现在我们想要教师课程的信息，只要查找教师的tutor_id的ID是1,2,3和6的。我们可以使用<foreach>的元素查询。

select id="searchCoursesByTutors" parameterType="map"
resultMap="CourseResult">
SELECT * FROM COURSES
<if test="tutorIds != null">
<where>
<foreach item="tutorId" collection="tutorIds">
OR tutor_id=#{tutorId}
</foreach>
</where>
</if>
</select>

public interface CourseMapper
{
List<Course> searchCoursesByTutors(Map<String, Object> map);
}
public void searchCoursesByTutors()
{
Map<String, Object> map = new HashMap<String, Object>();
List<Integer> tutorIds = new ArrayList<Integer>();
tutorIds.add(1);
tutorIds.add(3);
tutorIds.add(6);
map.put("tutorIds", tutorIds);
CourseMapper mapper =
sqlSession.getMapper(CourseMapper.class);
List<Course> courses = mapper.searchCoursesByTutors(map);
for (Course course : courses)
{
System.out.println(course);
}
}

当然我们也可以使用IN的字段。

<select id="searchCoursesByTutors" parameterType="map"
resultMap="CourseResult">
SELECT * FROM COURSES
<if test="tutorIds != null">
<where>
tutor_id IN
<foreach item="tutorId" collection="tutorIds"
open="(" separator="," close=")">
#{tutorId}
</foreach>
</where>
</if>
</select>

6. set情况

<set>的元素与<where>的元素是非常相似的，如果任何的if语句都成立的话，那么都会放入到SET中的。

<update id="updateStudent" parameterType="Student">
update students
<set>
<if test="name != null">name=#{name},</if>
<if test="email != null">email=#{email},</if>
<if test="phone != null">phone=#{phone},</if>
</set>
where stud_id=#{id}
</update>