mapper.xml中常用sql _常用sql

与天地兮比寿，与日月兮齐光。这篇文章主要讲述mapper.xml中常用sql相关的知识，希望能为你提供帮助。
1.模糊匹配，时间范围，集合用IN查找登的写法

SELECT * from tableName
< where> 1 = 1
< if test="vo.waybillPrintStatus != null and vo.waybillPrintStatus !=‘‘">
and waybillPrintStatus = #{vo.waybillPrintStatus}
< /if>
< if test="vo.startTime != null and vo.startTime !=‘‘">
< ![CDATA[ and time > = #{vo.startTime} ]]>
< /if>
< if test="vo.endTime != null and vo.endTime !=‘‘">
< ![CDATA[ and time < = #{vo.endTime} ]]>
< /if>
< if test="postProvinceIds != null and postProvinceIds.size > 0">
and province IN
< foreach collection="postProvinceIds" open="(" close=")" separator="," item="item">
#{item}
< /foreach>
< /if>
< /where>
order by time DESC

【mapper.xml中常用sql】2.批量更新，如果为null的值给0，IFNULL函数

< update id="updatePrintStatus">
UPDATE t_custom_xiaohs_orders
SET
WaybillPrintStatus= #{waybillPrintStatus},
WaybillPrintCount = IFNULL(WaybillPrintCount, 0) + 1,
WaybillPrintTime = NOW()
WHERE Id in
< foreach collection="orderIds" index="index" item="id" open="(" separator="," close=")">
#{id}
< /foreach>
< /update>

3.批量插入

< insert id="insertBatch" parameterType="java.util.List">
insert into t_cs_stock_goods_img
(good_code, good_img_id, good_address, good_sort, status, create_time, create_user, modify_time, modify_user)
values
< foreach collection="list" item="item" index="index" separator=",">
(
#{item.goodCode},#{item.goodImgId},#{item.goodAddress},#{item.goodSort},#{item.status},#{item.createTime},
#{item.createUser},#{item.modifyTime},#{item.modifyUser}
)
< /foreach>
< /insert>