1.遍历list查询
List<InvestDetailPO> getByPayOrderIds(@Param("payOrderIds") List<String> payOrderIds);
<select id="getByPayOrderIds" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM t_invest_detail
where pay_order_id in
(
<foreach collection="payOrderIds" item="item" separator="," >
#{item}
</foreach>
)
ORDER BY pay_order_id DESC
</select>
2.批量插入数据
int insertByBatch(List<T> var1);
<insert id="insertByBatch" useGeneratedKeys="true" parameterType="java.util.List">
insert into t_invest_order_detail ( uid,
order_id,
status,
remark,
create_time
)
values
<foreach collection="list" item="item" separator="," >
(
#{item.uid,jdbcType=BIGINT},
#{item.orderId,jdbcType=VARCHAR},
#{item.status,jdbcType=TINYINT},
#{item.remark,jdbcType=VARCHAR},
now()
)
</foreach>
</insert>
3.批量更新数据
int updateStatusByBatch(@Param("list")List<InvestDetailPO> investDetailList, @Param("status")int status, @Param("remark")String remark);
<update id="updateStatusByBatch" >
<foreach collection="list" item="item" separator=";">
update t_trade_flow
set status = #{status}, remark = #{remark}, update_time = now()
where order_id = #{item.orderId}
</foreach>
</update>
4.方法中一个参数
Integer isExistWaitPayOrder(@Param("uid") Long uid);
<select id="isExistWaitPayOrder" resultType="java.lang.Integer">
SELECT 1 FROM t_trade_flow WHERE uid=#{uid} and status=3 and trade_type=1 limit 1
</select>
5.方法中有多个参数
Long getInvestSumByUidAndPno(@Param("pno") String pno, @Param("productType") int productType, @Param("uid") Long uid);
<select id="getInvestSumByUidAndPno" resultType="java.lang.Long">
select IFNULL(sum(amount),0) from t_trade_flow where uid = #{uid} and pno = #{pno} and product_type = #{productType} and status in (0,1);
</select>
6.方法中是一个对象
int updateSyncStatus(InvestDetailPO detailPO);
<update id="updateSyncStatus" parameterType="com.wang.po.InvestDetailPO">
UPDATE t_invest_detail
SET sync_cs = #{syncCs,jdbcType=TINYINT},
update_time = now()
where pay_order_id = #{payOrderId,jdbcType=VARCHAR}
</update>
7.方法中是一个Map
List<InvestDetailPO> listByParam(Map<String,Object> param);
<select id="listByParam" parameterType="java.util.Map" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM t_invest_detail
WHERE 1 = 1
<if test="uid != null">
AND uid = #{uid,jdbcType=BIGINT}
</if>
<if test="pno != null">
AND pno = #{pno,jdbcType=VARCHAR}
</if>
<if test="status != null">
AND status = #{status,jdbcType=TINYINT}
</if>
<if test="intervalTime != null">
AND create_time <= #{intervalTime,jdbcType=TIMESTAMP}
</if>
</select>