mybatis-update更新某个字段，去掉逗号问题，更新时间条件判断，设置当前时间

方法一是用if test判断，方法二是更新哪些字段就写哪些字段

特别注意要对图片相关字段用if test判断

<update id="updateRiskPoint" parameterType="com.zte.claa.inficombo.apigwv1.app.framework.model.risk.RiskPoint" >
  update t_whppt_risk_point
  set pointname = #{pointname,jdbcType=VARCHAR},
    pointdescp = #{pointdescp,jdbcType=VARCHAR},
    addr = #{addr,jdbcType=VARCHAR},
    status = #{status,jdbcType=INTEGER},
    manager = #{manager,jdbcType=VARCHAR},
    orgid = #{orgid,jdbcType=VARCHAR},
    managelevel = #{managelevel,jdbcType=VARCHAR},
    resid = #{resid,jdbcType=VARCHAR},
    restype = #{restype,jdbcType=VARCHAR}, 
    <if test ="photourl != null and photourl != ''">  
      photourl = #{photourl,jdbcType=VARCHAR},
    </if>
    <if test ="phototype != null and phototype != ''"> 
      phototype = #{phototype,jdbcType=VARCHAR},
    </if>
    <if test ="photo != null "> 
      photo = #{photo,jdbcType=LONGVARBINARY},
    </if>
  risktypecode = #{risktypecode,jdbcType=VARCHAR} 
  where pointid = #{pointid,jdbcType=VARCHAR}
  and projectid = #{projectid,jdbcType=VARCHAR}
</update>

<update id="updateRiskPointMeasure" parameterType="com.zte.claa.inficombo.apigwv1.app.framework.model.risk.RiskPoint" >
  update t_whppt_risk_point
  set 
    riskreason = #{riskreason,jdbcType=VARCHAR},
    rishret = #{rishret,jdbcType=VARCHAR},
    accident = #{accident,jdbcType=VARCHAR},
    dealmethod = #{dealmethod,jdbcType=VARCHAR},    
    modtime = #{modtime,jdbcType=TIMESTAMP}    
  where pointid = #{pointid,jdbcType=VARCHAR}
  and projectid = #{projectid,jdbcType=VARCHAR}
</update>

-- 例如：只更新图片字段
<update id="updateRiskPointURL" parameterType="com.zte.claa.inficombo.app.model.appext.whppt.riskmanager.RiskPoint" >
  update t_whppt_risk_point
  set  
      photourl = #{photourl,jdbcType=VARCHAR}
  where pointid = #{pointid,jdbcType=VARCHAR}
  and projectid = #{projectid,jdbcType=VARCHAR}
</update>


--注意：photo photourl都为空，remark后面有个逗号，报错
--update t_agedcare_cookbook_define     set cname = ?,       ctype = ?,       descp = ?,       ctime = ?,       targetperson = ?,       projectid = ?,       remark = ?,                  where cid = ?
<update id="modifyCookbookDefineModel"  >
    update t_agedcare_cookbook_define
   set cname = #{c.cname,jdbcType=VARCHAR},
     ctype = #{c.ctype,jdbcType=VARCHAR},
     descp = #{c.descp,jdbcType=VARCHAR},
     ctime = #{c.ctime,jdbcType=VARCHAR},
     targetperson = #{c.targetperson,jdbcType=VARCHAR},
     projectid = #{c.projectid,jdbcType=VARCHAR},
     remark = #{c.remark,jdbcType=VARCHAR},
     <if test="c.photo != null ">
	    photo = #{c.photo, jdbcType=LONGVARBINARY},
     </if>
     <if test="c.photourl != null and c.photourl !=''">
	    photourl = #{c.photourl, jdbcType=VARCHAR}
     </if>
   where cid = #{c.cid,jdbcType=VARCHAR}
</update>

--改成如下即可：
<update id="modifyCookbookDefineModel"  >
    update t_agedcare_cookbook_define
   set cname = #{c.cname,jdbcType=VARCHAR},
      ctype = #{c.ctype,jdbcType=VARCHAR},
      descp = #{c.descp,jdbcType=VARCHAR},
      ctime = #{c.ctime,jdbcType=VARCHAR},
      targetperson = #{c.targetperson,jdbcType=VARCHAR},
      projectid = #{c.projectid,jdbcType=VARCHAR},
      <if test="c.photo != null ">
	    photo = #{c.photo, jdbcType=LONGVARBINARY},
     </if>
     <if test="c.photourl != null and c.photourl !=''">
	    photourl = #{c.photourl, jdbcType=VARCHAR},
     </if>
	   remark = #{c.remark,jdbcType=VARCHAR}
    where cid = #{c.cid,jdbcType=VARCHAR}
</update>

【mybatis】mybatis 中update 更新操作，null字段不更新，有值才更新 - Angel挤一挤 - 博客园

mybatis update并非所有字段需要更新的解决办法_Java小白的学习日常-优快云博客_mybatis更新部分字段

---

但是上面方法还有问题，如果只更新两个字段呢，这两个字段都可能为空，这个逗号还是会存在

解决办法：使用<trim prefix="set" suffixOverrides=",">

<update id="updateTeamBoard">
        update team_board 
        <trim prefix="set" suffixOverrides=",">
            <if test ="teamboard.discoverType != null">
                team_board = #{teamboard.discoverType,jdbcType=INTEGER},
            </if>
            <if test ="teamboard.symbol != null and teamboard.symbol != ''">
                symbol = #{teamboard.symbol,jdbcType=VARCHAR}
            </if>
        </trim>
        where team_id = #{teamboard.teamId,jdbcType=VARCHAR}
        and board_id = #{teamboard.boardId,jdbcType=VARCHAR}
</update>

---

更新的时候时间条件判断：

@PostMapping("/savesatisfaction")
public ResponseEntity<Object> insertOrUpdateSatisfaction(@RequestBody SatisfactionExtra satisfaction){
        //增加判断，如果过了时间不给修改
        Instant instant = satisfaction.getActualEnded().toInstant();
        ZoneId zone = ZoneId.systemDefault();
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zone);
        if(localDateTime.plusDays(3).isBefore(LocalDateTime.now())){
            return ResponseEntity.accepted().body(ErrorVo.build("1000","已经过了sprint截止时间，不可修改!"));
        }
        satisfactionService.UpdateSatisfaction(satisfaction);
        return ResponseEntity.ok("success");
}

设置每次更新都是当前时间：

<update id="UpdateSatisfaction" parameterType="com.bmw.dorms.modules.satisfaction.model.entity.Satisfaction">
    update satisfaction
    <trim prefix="set" suffixOverrides=",">
        <if test ="score != null">
            score = #{score,jdbcType=INTEGER},
        </if>
        <if test ="comment != null and comment != ''">
            comment = #{comment,jdbcType=VARCHAR},
        </if>
            subtime = now()
    </trim>
    where id = #{id,jdbcType=VARCHAR}
</update>