mybatis学习之动态sql
mybatis的动态sql语句很强大，在mapper映射文件中使用简单的标签即可实现该效果，下面一个个记录：
1、select查询
简单的select类似如下：
<select id="findById" resultMap="StudentResult" parameterType="Integer">
select * from t_student where id = #{id}
</select>1）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 != null">
and age = #{age}
</if>
</select>结合where标签使用如下：
<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 != null">
and age = #{age}
</if>
</where>
</select>2）choose（同if..else..类似）
<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>3）trim
<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>prefix前置，prefixOverrides前置覆盖，简单理解为：trim子句中最前面的and或者or用where替换。
4）foreach
<select id="searchStudents5" parameterType="Map" resultMap="StudentResult">
select * from t_student
<if test="gradeIds != null">
<where>
gradeId in
<foreach collection="gradeIds" item="gradeId" open="("
close=")" separator=",">
#{gradeId}
</foreach>
</where>
</if>
</select>collections即数组集合，可以是list类型，如arrayList等，open指定左侧拼接方式，close指定右侧，separator指定分隔符，这里拼接后为括号括起来逗号隔开的字符串，从而用于in查询。
2、update
<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><update id="update" parameterType="Student">
update t_student set name =
#{name},age = #{age} where id = #{id}
</update>3、delete
<delete id="delete" parameterType="Integer">
delete from t_student where id = #{id}
</delete>4、insert
<!-- 插入 -->
<insert id="add" parameterType="Student">
insert into t_student(id,name,age) values(null,#{name},#{age})
</insert>本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016-07-04 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读