mybatis中的动态SQL语句
有时候，静态的sql语句并不能满足应用程序的需求。我们可以根据一些条件，来动态地构建 sql语句。
例如，在web应用程序中，有可能有一些搜索界面，需要输入一个或多个选项，然后根据这些已选择的条件去执行检索操作。我们可能需要根据用户选择的条件来构建动态的sql语句。如果用户提供了任何一个条件，我们需要将那个条件添加到sql语句的where子句中。
！以下内容基于自己建的表和类！
1.<if>标签被用来通过条件嵌入sql片段，如果条件为true，则相应地sql片段将会被添加到sql语句中。
例如：
假定有一个课程搜索界面，设置了讲师（tutor）下拉列表框，课程名称（coursename）文本输入框，开始时间（startdate）输入框，结束时间（enddate）输入框，作为搜索条件。假定课讲师下拉列表是必须选的，其他的都是可选的。当用户点击搜索按钮时，需要显示符合条件的列表数据。
对应的sql映射文件，如下所示：
<!-- 独立的course封装映射 --> <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>
<!-- 查询course的select语句，里面加入了if条件判断 -->
<select id="searchcourses" parametertype="map" resultmap="courseresult">
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 <![cdata[ <= ]]> #{enddate}
</if>
</select>
映射接口：
public interface dynamicsqlmapper{
list<course> searchcourses(map<string, object> map);
}
测试方法：
@test
public void test_searchcourses1(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
map<string,object> map = new hashmap<string,object>();
map.put("tutorid", 1);
map.put("coursename", "%java%");
localdate date = localdate.of(2019, 1, 10);
map.put("startdate", date);
list<course> courses = mapper.searchcourses(map);
courses.foreach(system.out::println);
} catch (exception e) {
e.printstacktrace();
}
}
2.choose,when 和 otherwise 条件
有时候，查询功能是以查询类别为基础的。首先，用户需要先选择是通过讲师查询，还是课程名称查询，还是开始时间查询。然后根据选择的查询类别，输入相应的参数，再进行查询。
例如，页面中有一个下拉列表，可以选择查询的类别，可以选择根据讲师查询、根据课程名查询、根据时间查询等等，选择了列表之后，再输入关键字进行查询。
mybatis提供了<choose>标签可以支持此类型的查询处理。 假设如果用户都没有选择，那么默认可以根据当前时间进行查询。
注意:mysql中now()表示当前时间 oracle需要使用sysdate
对应的sql映射文件，如下所示：
<select id="searchcourses" parametertype="map" 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 start_date >= sysdate
</otherwise>
</choose>
</select>
测试方法：
@test
public void test_searchcourses2(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
map<string,object> map = new hashmap<string,object>();
// map.put("searchby", "tutor");
// map.put("tutorid", 1);
map.put("searchby", "coursename");
map.put("coursename", "%mybatis%");
list<course> courses = mapper.searchcourses(map);
courses.foreach(system.out::println);
} catch (exception e) {
e.printstacktrace();
}
}
mybatis计算<choose>中条件的值，并使用第一个值为true的子句。如果没有条件为 true，则使用<otherwise>内的子句。
3.where 条件
有时候，所有的查询条件应该是可选的。在需要使用至少一种查询条件的情况下，可以直接使用where子句。
如果有多个条件，我们需要在条件中添加and或or。mybatis提供了<where>元素支持这种类型的动态sql语句。
例如，在查询课程界面，假设所有的查询条件是可选的。
注意，<where>元素只有在其内部标签有返回内容时才会在动态语句上插入where条件语句。
并且，如果where子句以and或者or打头，则打头的and或or将会被移除。
映射文件：
<select id="searchcourses" parametertype="map" 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>
</where>
</select>
测试方法：
@test
public void test_searchcourses3(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
map<string,object> map = new hashmap<string,object>();
//map.put("tutorid", 1);
//map.put("coursename", "javase");
//map.put("startdate", localdate.of(2019, 1, 10));
list<course> courses = mapper.searchcourses(map);
courses.foreach(system.out::println);
} catch (exception e) {
e.printstacktrace();
}
}
4.<trim>条件
<trim>元素和<where>元素类似，但是<trim>提供了添加 前缀/后缀 或者 移除 前缀/后缀 的功能。
映射文件：
<select id="searchcourses" parametertype="map" resultmap="courseresult">
select * from courses
<trim prefix="where" suffixoverrides="and">
<if test=" tutorid != null ">
tutor_id = #{tutorid} and
</if>
<if test="coursename != null">
name like #{coursename} and
</if>
</trim>
</select>
prefix表示有一个if成立则插入where语句，没有if成立，就会去掉where直接查询
suffix表示后缀,和prefix相反
suffixoverrides="and"表示如果最后生成的sql语句多一个and,则自动去掉.
prefixoverrides的意思是处理前缀,和suffixoverrides相反
测试方法：
@test
public void test_searchcourses4(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
map<string,object> map = new hashmap<string,object>();
// map.put("tutorid", 1);
// map.put("coursename", "javase");
list<course> courses = mapper.searchcourses(map);
courses.foreach(system.out::println);
} catch (exception e) {
e.printstacktrace();
}
}
5.foreach 循环
另外一个强大的动态sql语句构造标签是<foreach>。它可以迭代遍历一个数组或者列表，构造and/or条件或一个in子句。
假设查询tutor_id为 1，3，6的讲师所教授的课程，我们可以传递一个tutor_id组成的列表给映射语句，然后通过<foreach>遍历此列表构造动态sql。
映射文件：
<select id="searchcoursesbytutors" parametertype="map" resultmap="courseresult">
select * from courses
<if test="tutorids != null">
<where>
<!-- 在这里的 tutorid指的是集合中存入准备查询的tutor_id-->
<foreach item="tutorid" collection="tutorids">
or tutor_id = #{tutorid}
</foreach>
</where>
</if>
</select>
映射接口:
public interface dynamicsqlmapper{
list<course> searchcoursesbytutors(map<string,object> map);
}
测试方法：
@test
public void test_searchcoursesbytutors(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
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);
list<course> courses = mapper.searchcoursesbytutors(map);
courses.foreach(system.out::println);
} catch (exception e) {
e.printstacktrace();
}
}
和上面同样的功能，使用<foreach>生成in子句：
<select id="searchcoursesbytutors" parametertype="map" resultmap="courseresult">
select * from courses
<if test="tutorids != null">
<where>
tutor_id in
<foreach item="tempvalue" collection="tutorids" open="(" separator="," close=")">
#{tempvalue}
</foreach>
</where>
</if>
</select>
测试方法保持不变。
6.set 条件，专用于update更新操作
<set>元素和<where>元素类似，但是set元素只是针对update更新语句使用的。
<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=#{studid}
</update>
这里，如果<if>条件返回了任何文本内容，<set>将会插入set关键字和其文本内容，并且会剔除将末尾的逗号","。
测试方法：
@test
public void test_updatestudent(){
sqlsession sqlsession = null;
try {
sqlsession = mybatissqlsessionfactory.opensession();
dynamicsqlmapper mapper = sqlsession.getmapper(dynamicsqlmapper.class);
student student = new student();
student.setstudid(45);
student.setemail("xx@briup.com");
mapper.updatestudent(student);
sqlsession.commit();
} catch (exception e) {
e.printstacktrace();
}
}