sql foreach记录

最新推荐文章于 2022-01-19 21:17:46 发布

转载最新推荐文章于 2022-01-19 21:17:46 发布 · 65 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3229047/blog/865218

文章标签：

#java #python

本文介绍了一个使用MyBatis进行复杂SQL查询及批量插入操作的例子。其中包括通过站码获取系统用户的查询过程，以及批量插入合租房源卧室信息的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

@RequestMapping("/test")
   public void springTest() {
       System.out.println("**************");
       List<String> list = new ArrayList();
       list.add("sys_manage");
       list.add("pledge");
       Map<String, Object> map = new HashMap<String, Object>();
       map.put("stationCode", list);
       List ss = TemplateUserDao.getSysUsersByStationCode(map);
       System.out.println(ss);

}

<select id="getSysUsersByStationCode" parameterType="java.util.Map"
       resultType="com.iqb.eatep.house.rabbit.mq.data.bean.SysUser">
       SELECT
       DISTINCT
       u.USER_CODE AS userCode,
       u.USER_NAME AS userName,
       u.REAL_NAME as realName,
       u.USER_EMAIL as userEmail,
       u.STATUS as status,
       u.USER_PHONE as userPhone,
       u.STATION_CODE as stationCode
       FROM
       iqb_sys_user u
       WHERE
       <if test="stationCode!=null and stationCode!=''">
       <foreach item="item" index="index" collection="stationCode" open="(" separator="or" close=")">
               u.STATION_CODE LIKE CONCAT('%', #{item,jdbcType=VARCHAR}, '%')
       </foreach>
       </if>
   </select>

<select id="getSysUsers" parameterType="java.util.Map"
       resultType="com.iqb.eatep.house.rabbit.mq.data.bean.SysUser">
       SELECT
       u.USER_CODE AS userCode,
       u.USER_NAME AS userName,
       u.REAL_NAME as realName,
       u.USER_EMAIL as userEmail,
       u.STATUS as status,
       u.USER_PHONE as userPhone,
       u.STATION_CODE as stationCode
       FROM
       iqb_sys_user u
       WHERE
       <if test="users!=null and users!=''">
           u.USER_CODE in
           <foreach item="item" index="index" collection="users" open="("
               separator="," close=")">
               #{item}
           </foreach>
       </if>
   </select>

<insert id="insertRentUserList" parameterType="java.util.List">
       insert into ete_rent_user_info (RENT_HOUSE_ID,RENT_TYPE,ROOM_LEVEL,ROOM_ACREAGE,RENT_TIME,RENT_STATUS)
       values
       <foreach collection="list" item="item" index="index" separator=",">
       (#{item.rentHouseId},#{item.rentType},#{item.roomLevel},#{item.roomAcreage},UNIX_TIMESTAMP(),#{item.rentStatus})
       </foreach>
   </insert>

转载于:https://my.oschina.net/u/3229047/blog/865218