MyBatis动态sql详解(foreach语句详解【IN】)

时间:2022-10-03 21:57:28

**MyBatis动态sql详解(foreach语句详解)**

理论基础:
一、介绍:
foreach主要是用于一个循环,大部分用到的是循环的生成sql,下面说一下动态foreach的属性:
foreach元素的属性主要有item,index,collection,open,separator,close。
1、collection表示如何来得到这个集合,如果传入的直接为一个List,那么collection值就为list,如果直接传入的为一个array不可变数组,那么collection值就为array,如果传入的为一个dto,比如dto里面的array变量名为idLists,那么collection的值就为idLists。
2、item表示集合中每一个元素进行迭代时的别名,比如item为value,那么,每次获取的都使用#{value}即可
3、index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,一般很少使用这个
4、open表示该语句以什么开始
5、separator表示在每次进行迭代之间以什么符号作为分隔符
6、close表示以什么结束
二、原理:
动态SQL,最后都会根据条件拼成SQL,foreach也不例外,原理为:
首先根据collection来得到集合,然后写入open字符,然后开始遍历:
每次遍历,都会执行里面的条件得到生成的sql,然后加入separator,遍历完成之后,加入close字符。
三、实战:

1.1、直接传入一个List可变数组:
比如我想找users表中id在一个数组中的,可以这样:
————————————————
//使用注解开发,注解在接口上实现
@Select("select * from tp_admin")
List<TpAdmin> gettp_admins();

//foreach yufachaxun
List<TpAdmin> foreachlists(List<Integer> idlist);
<!--    foreach fangshi chaxun-->
<select resultType="com.example.demo.entity.TpAdmin">
select * from tp_admin where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
////foreach yufachaxun
@Test
void testforeachlist(){
List<Integer> idlist = new ArrayList<>();
idlist.add(1);
idlist.add(2);
List<TpAdmin> foreachlists = tpadminmapper.foreachlists(idlist);
System.out.println(foreachlists);
}

  

2.collection属性值的三种情况
如果传入的参数类型为list时: collection的默认属性值为list,同样可以使用@Param注解自定义name;

如果传入的参数类型为array时: collection的默认属性值为array,同样可以使用@Param注解自定义name;

如果传入的参数类型为Map时: collection的属性值可为三种情况:(1.遍历map.keys;2.遍历map.values;3.遍历map.entrySet()),稍后会在代码中示例;​

2、直接传入一个array不可变数组:

<select  resultType="com.happyheng.entity.User">
select * from users where id in
<foreach collection="array" index = "index" item = "idArray" open="(" separator="," close=")">
#{idArray}
</foreach>
</select>

  java代码中的使用:

public static void testDynamicForeach() {
SqlSession session = MyBatisUtil.getSqlSession(true);

int[] ids = new int[]{7,8,10,11};
List<User> users = session.selectList("dymamicForeachArrayGetUser", ids);

for (User user : users) {
System.out.println(user);
}
session.close();
}

  

3、传入一个dto,里面有一个参数为List可变数组

<select  parameterType="com.happyheng.ParkQuery" resultMap="resultMap">
SELECT
xxx
FROM
fairyland_account_info account ,fairyland_community_info comm
where account.id = comm.fairyland_account_info_id
<if test="userInfoId != null">
AND account.user_info_id = #{userInfoId}
</if>

<if test="userIdList!=null">
AND account.user_info_id IN
<foreach collection="userIdList" item="userId" open="(" separator="," close=")">
#{userId}
</foreach>
</if>
</select>

  

其中,传入的dto为ParkQuery,里面有一个List 的userIdList参数(注意要有setter和getter) 所以下面的collection为userIdList,其对应的接口为:
public List findCommunityInfo(ParkQuery parkQuery);

4、传入一个dto,里面有一个参数为List可变数组,其中list中每个也是一个dto:

<update  parameterType="com.happyheng.query.FriendUpdateQuery">
UPDATE friend_introduce_info SET be_used = #{beUsed,jdbcType=TINYINT} WHERE
<foreach collection="listQuery" item="query" separator="or">
(user_info_id = #{query.uid} AND introduced_user_info_id =#{query.introduceUid})
</foreach>
</update>

  其中item=query即为list中的每个dto即为query,那么 #{query.uid}即为获取dto中的uid。

/**
* 批量插入
*
* @param list
* @return
*/
@Insert("<script>INSERT INTO logistics_goods "
+ "(logistics_no,goods_id,goods_amount,order_id,is_rule,order_no,goods_name,goods_weight,should_warehouse,real_warehouse,goods_price,order_create_time,spu_id,goods_label,self_flag,goods_cover_img,receiver_name,receiver_address,receiver_phone,is_alone,goods_max_amount,buyer_id,supplier_id,is_import,deliver_place,is_group)"
+ "VALUES "
+ "<foreach collection='list' item='l' index='index' separator=',' >"
+ "(#{l.logisticsNo},#{l.goodsId},#{l.goodsAmount},#{l.orderId},#{l.isRule},#{l.orderNo},#{l.goodsName},#{l.goodsWeight},#{l.shouldWarehouse},#{l.realWarehouse},#{l.goodsPrice},#{l.orderCreateTime},#{l.spuId},#{l.goodsLabel},#{l.selfFlag},#{l.goodsCoverImg},#{l.receiverName},#{l.receiverAddress},#{l.receiverPhone},#{l.isAlone},#{l.goodsMaxAmount},#{l.buyerId},#{l.supplierId},#{l.isImport},#{l.deliverPlace},#{l.isGroup})" +
"</foreach></script>")
int batchInsertGoods(List<LogisticsGoods> list);

/**
* 批量更新包裹号
*
* @param list
* @return
*/
@Update("<script>UPDATE logistics_goods SET" +
" logistics_no = CASE id" +
"<foreach collection='list' item='l' index='index'>" +
" WHEN #{l.id} THEN #{l.logisticsNo}" +
"</foreach>" +
" END" +
" WHERE id IN (" +
"<foreach collection='list' item='l' index='index' separator=',' >" +
"#{l.id}" +
"</foreach>)</script>")
int batchUpdateLogisticsNo(List<LogisticsGoods> list);

/**
* 批量更新包裹号
*
* @param list
* @return
*/
@Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
"UPDATE logistics_goods SET logistics_no = #{item.logisticsNo},goods_amount = #{item.goodsAmount} " +
"where id = #{item.id} and del_flag = 0 and logistics_status = 0" +
"</foreach></script>")
int batchUpdateLogisticsNoById(List<LogisticsGoods> list);

  

3.代码示例

3.1 collection属性值为List(Array等同):

@select(
"<script> " +
"SELECT *
" FROM user_info " +
" WHERE " +
" <if test="userName!= null and userName.size() >0"> " +
" USERNAME IN " +
" <foreach collection='userNames' item='value' separator=',' open='(' close=')'>" +
" #{value} " +
" </foreach>" +
" </if>" +
"</script>"
)
list<userInfo> getUserInfo(@param("userNames") list<String> userNames)


#separator不仅仅使用,使用or或者and,open不仅仅只是使用(而是and (
@select(
"<script> " +
"SELECT CONFIGID AS configId count(CONFIGID) as count*
" FROM user_info " +
" WHERE " +
" devGroupId = #{devGroupId}" +
" <foreach collection='viewRange' item='item' separator='or' open=' and (' close=')'>" +
" ORGNODEPATH like CONCAR('%','<![CDATA[#{item}]]>','%') " +
" </foreach>" +
"</script>"
)
list<Map<String,Long>> queryConfigAndCount(@param("devGroupId") long devGroupId, @param("viewRange") set<Long> viewRange)

  3.2 collection属性值类型为Map:

@select(
"<script> " +
"SELECT *
" FROM user_info " +
" WHERE " +
" <if test="userName!= null and userName.size() >0"> " +
" (USERNAME,AGE) IN " +
" <foreach collection='user.entrySet()' index='key' item='value' separator=',' open='(' close=')'>" +
" #{key},#{value} " +
" </foreach>" +
" </if>" +
"</script>"
)
List<UserList> getUserInfo(@Param("user") Map<String,String> user);


@select(
"<script> " +
"SELECT *
" FROM user_info " +
" WHERE " +
" <if test="userName!= null and userName.size() >0"> " +
" USERNAME IN " +
" <foreach collection='user.keys' item='key' separator=',' open='(' close=')'>" +
" #{key} " +
" </foreach>" +
" </if>" +
"</script>"
)
List<UserList> getUserInfo(@Param("user") Map<String,String> user);


@select(
"<script> " +
"SELECT *
" FROM user_info " +
" WHERE " +
" <if test="userName!= null and userName.size() >0"> " +
" USERNAME IN " +
" <foreach collection='user.values' item='value' separator=',' open='(' close=')'>" +
" #{value} " +
" </foreach>" +
" </if>" +
"</script>"
)
List<UserList> getUserInfo(@Param("user") Map<String,String> user);