一 批量插入
/**
* 批量添加订单费用
* @param orderList
*/
int batchInsertOrderCost(@Param("list") List<OrderCost> orderList);
<insert id="batchInsertOrderCost">
insert into t_order_cost(id, order_id, settle_mode_id, settle_mode_name, oil_card, pay_mode_id, pay_mode_name,
tax_sts, tax_rate, price_unit_id, price_unit_name, unit_price, settle_amount, base_freight,
delivery_fee, distribution_fee, stevedor_fee, other_fee, other_fee_remarks, version,
createuser, createtime)
values
<foreach collection="list" item="item" separator=",">
(#{item.id}, #{item.orderId},#{item.settleModeId},#{item.settleModeName},#{item.oilCard},#{item.payModeId}, #{item.payModeName},
#{item.taxSts},#{item.taxRate},#{item.priceUnitId},#{item.unitPrice},#{item.priceUnitName},#{item.settleAmount},#{item.baseFreight},
#{item.deliveryFee},#{item.distributionFee},#{item.stevedorFee},#{item.otherFee},#{item.otherFeeRemarks},#{item.version},
#{item.createuser}, #{item.createtime})
</foreach>
</insert>
判断参数值是否为空sql
<insert id="SEBAreaDAO.saveArea" parameterType="haecMap">
<selectKey keyProperty="areaId" resultType="long" order="BEFORE">
SELECT seq_sec_area_tb.NEXTVAL as areaId FROM DUAL
</selectKey>
insert into sec_area_tb
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="areaId != null">area_id,</if>
<if test="areaNm != null">area_nm,</if>
<if test="areaHead != null">area_head,</if>
<if test="phoneNo != null">area_phe,</if>
<if test="areaEyeNum != null">area_eye_num,</if>
<if test="areaLel != null">area_lel,</if>
<if test="userName != null">area_ct_by,</if>
area_ct_tm,
<if test="areaUtBy != null">area_ut_by,</if>
area_ut_tm,
area_del
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="areaId != null">#{areaId},</if>
<if test="areaNm != null">#{areaNm},</if>
<if test="areaHead != null">#{areaHead},</if>
<if test="phoneNo != null">#{phoneNo},</if>
<if test="areaEyeNum != null">#{areaEyeNum},</if>
<if test="areaLel != null">#{areaLel},</if>
<if test="userName != null">#{userName},</if>
SYSDATE,
<if test="areaUtBy != null">#{areaUtBy},</if>
SYSDATE,
'0'
</trim>
</insert>
二 循环修改
<update id="deleteByOrderId">
UPDATE t_order_cost SET sts = 1,updateuser = #{userName},updatetime = NOW()
where order_id in
<foreach item="id" collection="ids" separator="," open="(" close=")" index="">
#{id}
</foreach>
</update>
<update id="SEBAreaDAO.updateArea" parameterType="haecMap">
update sec_area_tb
<trim prefix="SET" suffixOverrides=",">
<if test="areaNm != null">area_nm = #{areaNm},</if>
<if test="areaHead != null">area_head = #{areaHead},</if>
<if test="phoneNo != null">area_phe = #{phoneNo},</if>
<if test="areaEyeNum != null">area_eye_num = #{areaEyeNum},</if>
<if test="areaLel != null">area_lel = #{areaLel},</if>
<if test="userName != null">area_ut_by = #{userName},</if>
area_ut_tm=SYSDATE
</trim>
WHERE area_Id=#{areaId}
</update>
三 查询
<select id="queryCostByOrderId" resultType="com.communication.order.client.request.OrderCostReq">
select * from t_order_cost where sts = 0 and order_id = #{orderId}
</select>
<select id="PMCTeamBlackCompanyDAO.selectTeamBlackCompanyList" parameterType="haecMap" resultType="haecMap">
select
distinct
blpr_id,
blpr_cmy_nm,
blpr_soc_no,
blpr_lg_prn,
blpr_ads,
blpr_phe,
blpr_ct_by,
blpr_ct_tm,
blpr_ut_by,
blpr_ut_tm,
blpr_emp,
blpr_sts,
blpr_del
from
pmc_blpr_tb
where blpr_del=0
<if test="blprCmyNm != null "> and blpr_cmy_nm = #{blprCmyNm}</if>
<if test="blprSocNo != null "> and blpr_soc_no = #{blprSocNo}</if>
<if test="blprSts != null "> and blpr_sts = #{blprSts}</if>
//模糊查询
<if test="faqTte != null">and faq_tte like concat(concat('%',#{faqTte}),'%') </if>
order by blpr_ct_tm DESC
