mybatis动态sql

001 where 和 if

<select id="findAllByCondition" resultType="com.po.pf.domain.User" parameterType="com.po.pf.domain.User">
select * from user
<where>
<if test="username != null">
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</where>
</select>

或者

<select id="findAllByCondition" resultType="com.po.pf.domain.User" parameterType="com.po.pf.domain.User">
select * from user where 1=1
<if test="username != null">
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</select>

002-foreach

<select id="findUserInIds" resultType="com.po.pf.domain.User" parameterType="list">
select * from user
<where>
<if test="list != null and list.size()>0">
<foreach collection="list" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
 
List<User> findUserInIds(List<Integer> list);

003-sql标签

<sql id="defaultUser">
select * from user
</sql>
 
<select id="findById1" parameterType="int" resultType="com.po.pf.domain.User">
<include refid="defaultUser"></include>
where id = #{uid}
</select>

004- trim
Trim 可以在条件判断完的SQL语句前后添加或者去掉指定的字符
prefix: 添加前缀
prefixOverrides: 去掉前缀
suffix: 添加后缀
suffixOverrides: 去掉后缀

<select id="findAllByCondition" resultType="com.po.pf.domain.User" parameterType="com.po.pf.domain.User">
select * from user
<trim prefix="where" suffixOverrides="and">
<if test="username != null">
username = #{username} and
</if>
<if test="sex != null">
sex = #{sex} and
</if>
</trim>
</select>