1.批处理的操作,操作的时候需要设置连接信息带上参数:allowMultiQueries=true
<!--批量处理的操作:更新用户的属性内容-->
<insert id="batchUpdateUserInfoData">
<foreach collection="userIdList" item="userid" index="index" open="" close="" separator=";">
UPDATE `${tableName}`
SET
<foreach item="value" index="key" collection="fieldObject.entrySet()" separator =",">
`${key}` =
<if test="value != null" >
#{value}
</if>
<if test="value == null" >
''
</if>
</foreach>
WHERE
userid = #{userid}
</foreach>
</insert>
2.返回的JSONArray的结果集:
JSONArray selectPrizeGroupStatusByPrizeIdList(@Param("tableName") String tableName,@Param("prizeIdList") List prizeIdList);
xml的书写:
<select id="selectPrizeGroupStatusByPrizeIdList" resultType="com.alibaba.fastjson.JSONObject">
SELECT
COUNT(*) as count,status,prize_id as prizeId
FROM `${tableName}`
WHERE
1 = 1
<if test="prizeIdList != null" >
AND prize_id IN
(
<foreach collection="prizeIdList" item="onePrizeId" index="index" separator=",">
${onePrizeId}
</foreach>
)
</if>
GROUP BY status,prize_id
</select>
3.List的查询操作:
<select id="xxDateToCount" resultType="com.alibaba.fastjson.JSONObject">
SELECT
`log_date` days,COUNT(id) numCount,type
FROM `${tableName}`
WHERE 1 =1
<if test="typeList != null" >
AND TYPE IN
(
<foreach collection="typeList" item="oneType" index="index" separator=",">
${oneType}
</foreach>
)
</if>
<if test="activityId != null" >
AND activity_id = #{activityId,jdbcType=INTEGER}
</if>
<if test="channel != null and channel !=''" >
AND channel = #{channel,jdbcType=VARCHAR}
</if>
<if test="startDate != null" >
AND `log_date` <![CDATA[ >= ]]> #{startDate,jdbcType=VARCHAR}
</if>
<if test="endDate != null" >
AND `log_date` <![CDATA[ <= ]]> #{endDate,jdbcType=VARCHAR}
</if>
GROUP BY days,TYPE
</select>
相应的目标方法名称:
JSONArray xxDateToCount(@Param("tableName") String tableName,
@Param("channel") String channel,
@Param("startDate") Date startDate,
@Param("endDate") Date endDate,
@Param("activityId") Integer activityId,
@Param("typeList") List<Integer> typeList);
4.batch批处理的操作:
<insert id ="insertMoreBatchDo">
insert into `${tableName}`
(
`node_id`,`userid`,`num`,`status`,`custom_data`,`message`,
<if test='type == "A"' >
`os`,
</if>
<if test='type == "B"' >
`fee`,
</if>
`created_at`,`updated_at`
)
values
<foreach collection ="list" item="record" index= "index" separator =",">
(
#{record.nodeId,jdbcType=VARCHAR},
#{record.userid,jdbcType=VARCHAR},
#{record.num,jdbcType=VARCHAR},
#{record.status,jdbcType=INTEGER},
#{record.customData,jdbcType=LONGVARCHAR},
#{record.message,jdbcType=VARCHAR},
<if test='type == "A"' >
<choose>
<when test='record.os != null'>
#{record.os,jdbcType=VARCHAR},
</when>
<otherwise>
"",
</otherwise>
</choose>
</if>
<if test='type == "B"' >
<choose>
<when test='record.os != null'>
#{record.fee,jdbcType=INTEGER},
</when>
<otherwise>
'0',
</otherwise>
</choose>
</if>
#{record.createdAt,jdbcType=TIMESTAMP},
#{record.updatedAt,jdbcType=TIMESTAMP}
)
</foreach >
</insert>
Mapper方法:
int insertMoreBatchDo(@Param("tableName") String tableName, @Param("type") String type, @Param("list") List<Bean> list);
5.left join + 分页查询的操作( like 用bind标签来进行组拼):
<resultMap id="prizeDTO" type="com.xxxx.PrizeDTO" extends="BaseResultMap" >
<result column="stock" property="stock" jdbcType="INTEGER" />
<result column="used" property="used" jdbcType="INTEGER" />
<result column="owned" property="owned" jdbcType="BIT" />
</resultMap>
<select id="selectByList" resultMap="prizeDTO">
SELECT
<include refid="prize_column" />
,
d.app_id,d.merchant_id,d.used,d.stock,d.owned
FROM prize_distributes d LEFT JOIN prizes p
ON p.id=d.prize_id
WHERE d.app_id=#{appId,jdbcType=INTEGER}
AND d.merchant_id=#{merchantId,jdbcType=INTEGER}
AND p.deleted_at is NULL
<if test="status != null and status == 'valid'" >
AND
(p.from_date <![CDATA[ <= ]]> now()
AND p.to_date <![CDATA[ >= ]]> now()
OR (p.from_date IS NULL AND p.to_date IS NULL))
</if>
<if test="status != null and status == 'expired'" >
AND p.to_date <![CDATA[ < ]]> now()
</if>
<if test="owned != null" >
AND d.owned = #{owned}
</if>
<if test="type != null and type !=''" >
AND p.type = #{type}
</if>
<if test="name != null and name !=''" >
<bind name="pattern" value="'%'+name+'%'"/>
AND p.name LIKE #{pattern}
</if>
ORDER BY p.created_at DESC
</select>
使用pageHelper插件来进行操作:
PageHelper.startPage(page, pageSize);
List<PrizeDTO> list = prizeMapper.selectByList(appId, merchantId, owValue, type, status, name);