ibatis 批量更新(一)

it2025-04-30  9

1.4.2.3 批量修改

  支持单个动态更新、批量动态更新

<update id="updateCONSULT_SCHEDULEDynamic" parameterClass="map"> UPDATE CONSULT_SCHEDULE <dynamic prepend="set"> <isNotEmpty prepend="," property="ORG_CODE"> ORG_CODE = #ORG_CODE# </isNotEmpty> <isNotEmpty prepend="," property="DEPENT_ID"> DEPENT_ID = #DEPENT_ID# </isNotEmpty> <isNotEmpty prepend="," property="DEPENT_NAME"> DEPENT_NAME = #DEPENT_NAME# </isNotEmpty> <isNotEmpty prepend="," property="DOCTOR_ID"> DOCTOR_ID = #DOCTOR_ID# </isNotEmpty> <isNotEmpty prepend="," property="DOCTOR_NAME"> DOCTOR_NAME = #DOCTOR_NAME# </isNotEmpty> <isNotEmpty prepend="," property="DOCTOR_PHONE"> DOCTOR_PHONE = #DOCTOR_PHONE# </isNotEmpty> <isNotEmpty prepend="," property="SCHEDULE_DATE"> SCHEDULE_DATE = TO_DATE(#SCHEDULE_DATE#,'YYYY-MM-DD') </isNotEmpty> <isNotEmpty prepend="," property="WEEK_TXT"> WEEK_TXT = #WEEK_TXT# </isNotEmpty> <isNotEmpty prepend="," property="WB_TYPE"> WB_TYPE = #WB_TYPE# </isNotEmpty> <isNotEmpty prepend="," property="CLOSE_TZ"> CLOSE_TZ = #CLOSE_TZ# </isNotEmpty> <isNotEmpty prepend="," property="REPLACE_TZ"> REPLACE_TZ = #REPLACE_TZ# </isNotEmpty> <isNotEmpty prepend="," property="DOCTOR_ID_TZ"> DOCTOR_ID_TZ = #DOCTOR_ID_TZ# </isNotEmpty> <isNotEmpty prepend="," property="DOCTOR_NAME_TZ"> DOCTOR_NAME_TZ = #DOCTOR_NAME_TZ# </isNotEmpty> <isNotNull prepend="," property="REMARK"> REMARK = #REMARK# </isNotNull> </dynamic> WHERE CONSULT_SCHEDULE_ID IN ($CONSULT_SCHEDULE_ID$) </update>

      注意:

        a.这种批量更新,只能统一更改某(几)个表字段的值(状态);

        b.批量操作时,iBATIS通过使用in()函数来实现,in()函数里面的内容只能通过"$变量$"来实现,不能通过"#变量#"实现;

        c.根据id进行修改,如果id是字符串,前台传参时,需手动拼接上字符串。

      举例:

        以id是字符串为例,讲解前台如何获取页面的值,并传至后台及iBATIS如何映射传递的值

        第一部分:js取值

          见文章:复选框-checkbox

        第二部分:Dao调取SQL

/** * 动态修改排班表表记录 * * @param paramMap 排班表表map对象集合 * @return 返回操作影响的行数 * @throws DaoException */ public int updateCONSULT_SCHEDULEDynamic(Map paramMap) throws DaoException { try { return sqlMapClient.update("telemedicine.service.bo.remoteCons.schedule.updateCONSULT_SCHEDULEDynamic", paramMap); } catch (SQLException e) { String err = "修改排班表表出错->出错原因:" + e.getMessage(); log.error(err, e); throw new DaoException(err, e); } }

      第三部分:SQL,见上面

    说明:

      a.使用 in(#CONSULT_SCHEDULE_ID#),则解析出来的结果是:in('id1,id2');(错误)

      b.使用 in($CONSULT_SCHEDULE_ID$),解析出来的结果是:in('id1','id2');(正确)

错误用法

  情景描述

  virtual_card表中有两个唯一的字段:virtual_id和index_id,想根据其中一个字段进行更新;

  但是,传参可能是0个,1个或2个,考虑到能匹配不到的情况,所以使用了1=1

  动态sql

update virtual_card set index_id=#index_id# where 1=1 <isNotEmpty prepend="and" property="virtual_id"> virtual_id=#virtual_id# </isNotEmpty> <isNotEmpty prepend="and" property="index_id"> index_id=#index_id# </isNotEmpty>

 

  当传递的参数匹配不到的时候,执行的结果是:

 

  说明:

  where 1=1条件相当于没用,与下面的sql起到的效果一致 

update virtual_card set index_id=?

  将该表中该字段的值全部更新成了?,所以一定不能使用这个动态sql。

 

 相关推荐:

ibatis 批量更新(二)Oracle 批量更新表字段

 

 

转载于:https://www.cnblogs.com/Marydon20170307/p/8986413.html

相关资源:数据结构—成绩单生成器
最新回复(0)