mybatis 批量更新数据

mybatis 批量更新数据

点击查看参考文章

---

JAVA定时任务代码

    /**
     * 每天凌晨1点执行定时更新用户vip时长、状态
     */
    @Scheduled(cron="0 0 1 * * ?")
    //@Scheduled(cron="0 1/1 * * * ? ")
    public void updateVipTask(){
        String baseurl = Global.getConfig("rw.kawu.baseurl");
        String secret = Global.getConfig("rw.kawu.secret");   //应用标识     
        String method = "/data/api/getAllUserPermission";

        Map<String,String> params=new TreeMap<String, String>();
        params.put("secret",secret); 

        JSONObject result= Client.sendGet(baseurl+method, params);
        if(result.getInteger("code").intValue()==200){
            JSONArray jsonArray = result.getJSONArray("data");
            List<Map<String, Object>> vipDaysLs = Lists.newArrayList(); 
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                Map<String, Object> map = Maps.newHashMap();
                map.put("userName", jsonObject.getString("ssoUserId"));
                map.put("totalDays", jsonObject.getInteger("effectiveTotleDay"));
                Integer remainingDays = jsonObject.getInteger("effectiveLeftDay");
                map.put("remainingDays", remainingDays);
                map.put("isVip", remainingDays.intValue()>0 ? 1 : 0);
                vipDaysLs.add(map);
            }
            // 根据 userName 更新用户的 isVip totalDays remainingDays
            fyWebuserService.updateVipDays(vipDaysLs);
        }
    }

mybatis xml 代码

    <update id="updateVipDays" parameterType="java.util.List">
        <!-- 写法一 效率低 -->
        <!-- <foreach collection="vipDaysLs" item="item" open="" close="" separator=";">
          update fy_webuser 
            <set>
                total_days = #{item.totalDays},
                remaining_days = #{item.remainingDays},
                is_vip = #{item.isVip}
            </set>
          where  user_name = #{item.userName}
        </foreach> -->

        <!-- 写法二 -->
        update fy_webuser  
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="total_days =case" suffix="end,">  
                 <foreach collection="list" item="item">  
                     <if test="item.totalDays !=null">  
                         when user_name = #{item.userName} then #{item.totalDays}  
                     </if>  
                 </foreach>  
            </trim>  
            <trim prefix="remaining_days =case" suffix="end,">  
                 <foreach collection="list" item="item">  
                     <if test="item.remainingDays !=null">  
                         when user_name = #{item.userName} then #{item.remainingDays}  
                     </if>  
                 </foreach>  
            </trim>  
            <trim prefix="is_vip =case" suffix="end">  
                 <foreach collection="list" item="item">  
                     <if test="item.isVip !=null">  
                         when user_name = #{item.userName} then #{item.isVip}  
                     </if>  
                 </foreach>  
            </trim>  
        </trim>  
         where user_name in
        <foreach collection="list" item="item" separator="," open="(" close=")">  
            #{item.userName}  
        </foreach>  
    </update>