动态SQL
if和where
If标签:作为判断入参来使用的,如果符合条件,则把if标签体内的SQL拼接上
注意:用if进行判断是否为空时。不仅要判断null,还要判断空字符串" "
Where标签:会去掉条件中的第一个and符号。
xml配置:
<!--if和where的使用-->
<select id="findUserList" parameterType="userQueryVO" resultType="user">
/*性别和名字 */
select * from user
<where>
<if test="user != null">
<if test="user.sex != null and user.sex != ''">
sex = #{user.sex}
</if>
<if test="user.username != null and user.username != ''">
and username like '%${user.username}%'
</if>
</if>
</where>
</select>
SQL片段:
<!--if和where的使用-->
<sql id="select_user_where">
<if test="user != null">
<if test="user.sex != null and user.sex != ''">
sex = #{user.sex}
</if>
<if test="user.username != null and user.username != ''">
and username like '%${user.username}%'
</if>
</if>
</sql>
<select id="findUserList" parameterType="userQueryVO" resultType="user">
/*性别和名字 */
select * from user
<where>
<include refid="select_user_where"/>
</where>
</select>
foreach遍历
案例:查询指定id的用户
SQL语句:[select * from user where id in (31,32,33);]
UserQueryVO.java
//定义查询包装类,以后.xml的参数从这个对象取
public class UserQueryVO{
private User user;
private List<Integer> ids;
}
UserMapper.xml
<select id="findUserByIds" parameterType="userQueryVO" resultType="user">
/*性别和名字 */
select * from user
<where>
<if test="ids != null and ids.size > 0">
<!--
collection:集合,写集合属性
item:遍历接收变量
open:遍历开始
close:遍历结束
separator:拼接格式
for(Integer id : ids){
}
-->
<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
${id}
</foreach>
</if>
</where>
</select>
参数是数组:
<!--foreach,参数是数组,遍历判断的时候使用list这个变量-->
<select id="findUserByIds2" parameterType="java.util.List" resultType="user">
/*性别和名字 */
select * from user
<where>
<if test="list != null and list.size > 0">
<foreach collection="list" item="id" open="id in(" close=")" separator=",">
${id}
</foreach>
</if>
</where>
</select>