Mybatis 常用sql之批量添加和修改

int updateByList(List list)

Mappe层

批量修改

<update id="updateByList" parameterType="java.util.List">

<foreach collection="list" item="item" index="index" separator="">

update jt_fin_repayment_plan

<set >

erestTotal != null" >

ORG_INTEREST_TOTAL = #{item.orgInterestTotal},

</if>

<if test="item.delFlag != null" >

DEL_FLAG = #{item.delFlag},

</if>

<if test="item.state != null" >

STATE = #{item.state},

</if>

<if test="item.createBy != null" >

CREATE_BY = #{item.createBy},

</if>

<if test="item.createTime != null" >

CREATE_TIME = #{item.createTime},

</if>

<if test="item.updateBy != null" >

UPDATE_BY = #{item.updateBy},

</if>

UPDATE_TIME = #{NOW(),

</set>

where REPAYMENT_ID = #{item.repaymentId}

</foreach>

</update>

----------------------------------------------------------------------------------------------------------

dao层

//批量添加

int addList(List list)

Map层

批量添加

<insert id="addList" parameterType="java.util.List">

insert into jt_fin_adjust_rates_project_history (

ID,ADJUST_RATES_ID,PROJECT_ID,START_WHOLE,

END_WHOLE,START_WITHOUT,END_WITHOUT,PROJECT_DATE,

KEEP,AUDIT_FLAG,STATUS,REMARK,DEL_FLAG,

CREATE_BY,CREATE_TIME,UPDATE_TIME,UPDATE_BY

)

values

<foreach collection="list" separator="," item="item" index="index">

((select UUID()), #{item.adjustRatesId},#{item.projectId},#{item.startWhole},

#{item.endWhole},#{item.startWithout},#{item.endWithout},#{item.projectDate},

#{item.keep},#{item.auditFlag},#{item.status},#{item.remark},#{item.delFlag},

#{item.createBy},sysdate(), #{item.updateTime}, #{item.updateBy})

</foreach>

</insert>

注意：我的id VARCHAR类型 主键 不能递增 在这里我用的是UUID生成的

原本，想写一个批量添加，回显id但是没实现就不写了，有大佬可以补充一下

     mapper.java层定义：

     int batchInsert(List stockList)

mapper.xml层的sql语句：

insert into t_stock (status, asset_classify_id,asset_id,asset_item_id, name,num, batch_num, tag_id,rfid, epc, barcode,qr_code, erp, unit,pic_url, specification, model,material, color,length,width, height, weight,density, volume, price01,price02,warehouse_id, storage_zone_id,storage_location_id,storage_location_tag_id,remark, attr01, attr02,attr03, create_date,last_update,creater, client_id)values (#{item.status,jdbcType=VARCHAR},#{item.assetClassifyId,jdbcType=BIGINT},#{item.assetId,jdbcType=BIGINT}, #{item.assetItemId,jdbcType=BIGINT},#{item.name,jdbcType=VARCHAR},#{item.num,jdbcType=VARCHAR},#{item.batchNum,jdbcType=VARCHAR}, #{item.tagId,jdbcType=VARCHAR},#{item.rfid,jdbcType=VARCHAR}, #{item.epc,jdbcType=VARCHAR},#{item.barcode,jdbcType=VARCHAR},#{item.qrCode,jdbcType=VARCHAR},#{item.erp,jdbcType=VARCHAR}, #{item.unit,jdbcType=VARCHAR},#{item.picUrl,jdbcType=VARCHAR},#{item.specification,jdbcType=VARCHAR},#{item.model,jdbcType=VARCHAR},#{item.material,jdbcType=VARCHAR},#{item.color,jdbcType=VARCHAR}, #{item.length,jdbcType=DECIMAL},#{item.width,jdbcType=DECIMAL}, #{item.height,jdbcType=DECIMAL},#{item.weight,jdbcType=DECIMAL},#{item.density,jdbcType=DECIMAL},#{item.volume,jdbcType=DECIMAL}, #{item.price01,jdbcType=DECIMAL},#{item.price02,jdbcType=DECIMAL},#{item.warehouseId,jdbcType=BIGINT},#{item.storageZoneId,jdbcType=BIGINT},#{item.storageLocationId,jdbcType=BIGINT},#{item.storageLocationTagId,jdbcType=BIGINT},#{item.remark,jdbcType=VARCHAR}, #{item.attr01,jdbcType=VARCHAR},#{item.attr02,jdbcType=VARCHAR},#{item.attr03,jdbcType=VARCHAR},#{item.createDate,jdbcType=TIMESTAMP},#{item.lastUpdate,jdbcType=TIMESTAMP},#{item.creater,jdbcType=BIGINT}, #{item.clientId,jdbcType=BIGINT})