Mybatis 一(6)之动态SQL:where if、sql片段(sql if、foreach)

【6】.动态SQL:

1. 动态 SQL:where if和 sql片段(sql if)

需求:自定义条件查询用户信息

where和 if:

where 标签相当于 where关键字,可以自动去除第一个 and;
if 相当于判断,test=“判断条件”;

Sql片段:

通用的 sql片段抽取出来,单独定义,方便其他的 statement引用;
通过 sql语句,where条件(即下面的 where if片段);
(定义 sql片段是写在< sql>标签中,< where>标签不写在片段中;使用时:用< include refid=" ">)

<sql id="query_user_where">
    <if test="userCustom!=null">
    	<if test="userCustom.username!=null and userCustom.username!='' ">
    		and username like'%${userCustom.username}%'
    	</if>
    	<if test="userCustom.age!=0">
    		and age>#{userCustom.age}
    	</if>
     </if>
</sql>
<include refid="query_user_where"/>

userCustom不为空,userCustom.username不为空(即有 username这个查询条件)且不为空字符,userCustom.age不为0;下图 Test中:
① 如果只是:userCustom.setUsername(“张”);
运行时 sql语句:select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’
② 如果是:userCustom.setUsername(“张”);
userCustom.setAge(10);
运行时 sql语句:select id u_id,username u_uaername,age u_age from Users where username like ’ %$张% ’ and age>?
查询名字、年龄时与查询用户个数时的 where if条件片段一样,可以将其提取出来,封装成 sql片段,进行多次利用:
where 条件抽取:< sql>标签是用来定义 sql语句的,id 为其标识,where 条件(即提取出来的 sql片段)中有基于用户的、下面< if>其他条件< /if>可能基于订单等其他的,并不是把下面< where>片段中的< if>都抽取出来,可以分开抽取,建议对单表抽取,提高公用性。

UserMapper.xml (提取 sql片段后)

<mapper namespace="com.mdd.mapper.UserMapper">

    <!-- where条件抽取(建议对单表抽取,提高公用性)-->
    <sql id="query_user_where">
       <if test="userCustom!=null">
	   <if test="userCustom.username!=null and userCustom.username!='' ">
	   	and username like'%${userCustom.username}%'
	   </if>
	   <if test="userCustom.age!=0">
	   	and age>#{userCustom.age}
	   </if>
        </if>
    </sql>

    <!-- 查询用户名字、年龄 -->
    <resultMap id="userListMap" type="user">
    	<id column="u_id" property="id"/> //主键
    	<result column="u_username" property="username"/> //普通列
    	<result column="u_age" property="age"/>
    </resultMap>
    <select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
        //select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
        select id u_id,username u_uaername,age u_age from Users 
        <where>
        	<include refid="query_user_where"/>
        	<include refid="其他条件"/>
        </where> 
    </select>
    <!-- 查询个数 -->
    <select id="findUserCount" parameterType="userQueryVo" resultType="int">
        //select count(*) from Users where username like '%${userCustom.username}%'
        select count(*) from Users 
        <where>
        	<include refid="query_user_where"/>
        </where> 
    </select>
</mapper>

UserMapper.xml (提取 sql片段前)

<mapper namespace="com.mdd.mapper.UserMapper">

    <!-- 查询用户名字、年龄 -->
    <resultMap id="userListMap" type="user">
    	<id column="u_id" property="id"/> //主键
    	<result column="u_username" property="username"/> //普通列
    	<result column="u_age" property="age"/>
    </resultMap>
    <select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
        //select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
        select id u_id,username u_uaername,age u_age from Users 
        <where>
        	<if test="userCustom!=null">
        		<if test="userCustom.username!=null and userCustom.username!='' ">
        			and username like'%${userCustom.username}%'
        		</if>
        		<if test="userCustom.age!=0">
        			and age>#{userCustom.age}
        		</if>
        		<if>
        			//其他if条件
        		</if>
        	</if>
        </where> 
    </select>
    <!-- 查询个数 -->
    <select id="findUserCount" parameterType="userQueryVo" resultType="int">
        //select count(*) from Users where username like '%${userCustom.username}%'
        select count(*) from Users 
        <where>
        	<if test="userCustom!=null">
        		<if test="userCustom.username!=null and userCustom.username!='' ">
        			and username like'%${userCustom.username}%'
        		</if>
        		<if test="userCustom.age!=0">
        			and age>#{userCustom.age}
        		</if>
        		<if>
        			//其他if条件
        		</if>
        	</if>
        </where> 
    </select>
</mapper>

AppTest.java

@Test
public  void testFindUserList() throws Exception(){
    SqlSession sqlSession=sqlSessionFactory.openSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    UserQueryVo userQueryVo=new UserQueryVo();
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("张");
    //userCustom.setAge(10);
    userQueryVo.setUserCustom(userCustom);
    List<User> users=userMapper.findUserList(userQueryVo);
    sqlSession.close();
    for (User user:users) {
    	System.out.println(user.getId()+","+user.getUsername());
    }   
}

2. sql片段(foreach的使用)

需求:根据多个用户 id查询用户信息
select * from Users where age>10 and id in(1,3,5,7) 或
select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7)
(即在 statement中 parameterType传集合)
解决:在包装类中定义一个集合(使用< foreach>标签进行遍历)。
① 包装类中定义 ids集合;② UserMapper.xml 添加< foreach>标签:collection:集合属性; open:开始循环时要拼接的字符串; close:结束循环时的字符串; item:每次循环的对象; seperator:分隔符。
③ AppTest.java中创建一个 ids集合,再set进去。运行的sql语句:
select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’ and id in (?,?,?)
select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’ and (id=? or id=? or id=?)

<!-- select * from Users where age>10 and id in(1,3,5,7) -->
<foreach collection="ids" open="and id in(" close=")" item="id" seperator=",">
   #{id}
</foreach>
<!-- select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7) -->
<foreach collection="ids" open="and(" close=")"  item="id" seperator="or">
   #{id}
</foreach>

UserQueryVo.java

public class UserQueryVo {
    private User user;  //用户信息
    private UserCustom userCustom;   //自定义User扩展对象
    private List<Integer> ids;  //定义集合
    set()、get()
}

UserMapper.xml (提取 sql片段后 + foreach遍历)

<mapper namespace="com.mdd.mapper.UserMapper">

    <!-- 【sql片段】 -->
    <!-- where条件抽取(建议对单表抽取,提高公用性)-->
    <sql id="query_user_where">
       <if test="userCustom!=null">
	   <if test="userCustom.username!=null and userCustom.username!='' ">
	   	and username like'%${userCustom.username}%'
	   </if>
	   <if test="userCustom.age!=0">
	   	and age>#{userCustom.age}
	   </if>
        </if>
        
        <!-- 【foreach遍历】(eg:遍历指定的id) -->
	<!-- select * from Users where age>10 and id in(1,3,5,7) -->
	<foreach collection="ids" open="and id in(" close=")" seperator=",">
	    #{id}
	</foreach>
	<!-- select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7) -->
	<foreach collection="ids" open="and(" item="id" seperator="or">
	    #{id}
	</foreach>
    </sql>

    <!-- 查询用户名字、年龄 -->
    <resultMap id="userListMap" type="user">
    	<id column="u_id" property="id"/> //主键
    	<result column="u_username" property="username"/> //普通列
    	<result column="u_age" property="age"/>
    </resultMap>
    <select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
        //select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
        select id u_id,username u_uaername,age u_age from Users 
        <where>
        	<include refid="query_user_where"/>
        	<include refid="其他条件"/>
        </where> 
    </select>
    <!-- 查询个数 -->
    <select id="findUserCount" parameterType="userQueryVo" resultType="int">
        //select count(*) from Users where username like '%${userCustom.username}%'
        select count(*) from Users 
        <where>
        	<include refid="query_user_where"/>
        </where> 
    </select>
</mapper>

AppTest.java

@Test
public  void testFindUserList() throws Exception(){
    SqlSession sqlSession=sqlSessionFactory.openSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    UserQueryVo userQueryVo=new UserQueryVo();
    List<Integer> ids=new ArrayList<>();
    ids.add(1);  ids.add(3);  ids.add(5);
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("张");
    userQueryVo.setUserCustom(userCustom);
    userQueryVo.setIds(ids);
    List<User> users=userMapper.findUserList(userQueryVo);
    sqlSession.close();
    for (User user:users) {
    	System.out.println(user.getId()+","+user.getUsername());
    }   
}

(首页网址: https://blog.csdn.net/qq_41029923/article/details/83472411

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值