1.使用动态SQL完成多条件查询
1.1使用if+where实现多条件查询
(1).if语句
<select id="selectUsers" parameterType="Student" resultMap="StudentMap">
SELECT u.*,r.roleName FROM User u,role r where u.userRole=r.id
<if test="userRole!=null">
and u.userRole like CONCAT ('%',#{userRole },'%')
</if>
<if test="userName!=null">
and u.userName like CONCAT ('%',#{userName},'%')
</if>
</select>
(2).where (弥补if不足,剔除and)
<select id="selectUsers" parameterType="Student" resultMap="StudentMap">
SELECT u.*,r.roleName FROM User u,role r where u.userRole=r.id
<where>
<if test="userRole!=null">
and u.userRole like CONCAT ('%',#{userRole },'%')
</if>
<if test="userName!=null">
and u.userName like CONCAT ('%',#{userName},'%')
</if>
</where>
</select>
注:where会自动剔除AND
1.2使用if+set改造更新操作
解决更新操作如果传的值为空,数据库更新为null
<update id="update" parameterType="Student">
update student_info
<set>
<if test="classId!=0">class_id=#{classId},</if>
<if test="studentName!=null">student_name=#{studentName},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
</set>
where id=#{id}
</update>
可以剔除条件末尾的任何不相关得逗号;
1.3使用if+trim改造修改操作
<update id="update" parameterType="Student">
update student_info
<trim prefix="set" suffixOverrides=“,”, suffix="where id=#{id}">
<if test="classId!=0">class_id=#{classId},</if>
<if test="studentName!=null">student_name=#{studentName},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
</trim >
</update>
1.4使用foreach完成复杂查询
<select id="selectByIn" resultMap="StudentMap">
SELECT * FROM `student_info` WHERE `id` IN
<foreach collection="array" item="ids" open="(" close=")"
separator=",">
#{ids}
</foreach>
</select>
item:表示集合中每一个元素进行迭代时的别名
open:表示语句从什么开始(既然是in,所以必然是以"(“开始)
separator:分隔符,(既然是in,所以必然是以”,“开始)
close:该语句什么结束(既然是in,所以必然是以”)"开始)
collection:
若入参为单参数且数据类型是一个list的时候,collection属性值为list
若入参为单参数且数据类型是一个数组的时候,collection属性值为array
若传入参数为多参数,就需要封装一个map处理
1.5choose(when,otherwise)
只会判断一种,若为true,后面则不会执行
<select id="selectByIn" resultMap="StudentMap">
SELECT * FROM `student_info` WHERE 1=1
<choose>
<when test="userName!=null and userName!=''">
and u.userRole like CONCAT ('%',#{userRole },'%')
</when>
<otherwise>
and YEAR(createionDate) =YEAR(#{createionDate})
</otherwise>
</choose>
</select>
MyBatis实现分页功能
<select id="selectUsers" parameterType="Student" resultMap="StudentMap">
SELECT u.*,r.roleName FROM User u,role r where u.userRole=r.id
<if test="userRole!=null">
and u.userRole like CONCAT ('%',#{userRole },'%')
</if>
<if test="userName!=null">
and u.userName like CONCAT ('%',#{userName},'%')
</if>
order by creationDate DESC limit #{from},#{pageSize}
</select>