1.choose when otherwise
相当于java里的if else
<select id="List" resultType="com.mybatis.entity.User">
select * from user WHERE sex=1
<!--
1.一旦有条件成立的when,后续的when则不会执行
2.当所有的when都不执行时,才会执行otherwise
-->
<choose>
<when test="name!=null and name.trim()!=''">
and name like '%${name}%'
</when>
<when test="age!=null">
and age = #{age}
</when>
<otherwise>
and name='test'
</otherwise>
</choose>
</select>
2.if
<select id="List" resultType="com.mybatis.entity.User">
select * from user WHERE sex=1
<if test="name!=null and name.trim()!=''">
and name like '%${name}%'
</if>
</select>
3.where 和set
1.查询所有用户,如果输入了姓名按照姓名进行模糊查询,如果输入年龄,按照年龄进行查询,如果两者都输入,两个条件都要成立。
<select id="queryUserListByNameAndAge" resultType="com.zpc.mybatis.pojo.User">
select * from tb_user
<!--如果多出一个and,会自动去除,如果缺少and或者多出多个and则会报错-->
<where>
<if test="name!=null and name.trim()!=''">
and name like '%${name}%'
</if>
<if test="age!=null">
and age = #{age}
</if>
</where>
</select>
2.修改用户信息,如果参数user中的某个属性为null,则不修改。
<update id="updateUser" parameterType="com.zpc.mybatis.pojo.User">
UPDATE tb_user
<trim prefix="set" suffixOverrides=",">
<if test="userName!=null">user_name = #{userName},</if>
<if test="password!=null">password = #{password},</if>
<if test="name!=null">name = #{name},</if>
<if test="age!=null">age = #{age},</if>
<if test="sex!=null">sex = #{sex},</if>
<if test="birthday!=null">birthday = #{birthday},</if>
updated = now(),
</trim>
WHERE
(id = #{id});
</update>
4.foreach
按照多个id查询用户信息
接口:
/**
* 按多个Id查询
* @param ids
* @return
*/
List<User> queryUserListByIds(@Param("ids") String[] ids);
配置:
<select id="queryUserListByIds" resultType="com.zpc.mybatis.pojo.User">
select * from tb_user where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>