Mapper

/**
 * 批量写入记录
 */
int batchInsert(@Param("models") List<CtMaterial> models);


List<MaterialSortDO> selectStatistics(@Param("Page") Page<?> page,
                                      @Param("key") String key,
                                      @Param("categoryIds") Collection<Long> categoryIds,
                                      @Param("userId") String userId,
                                      @Param("sortRule") Integer sortRule,
                                      @Param("businessType") Integer businessType,
                                      @Param("wsTenantId") Long wsTenantId,
                                      @Param("filterMaterialIds") Long[] filterMaterialIds,
                                      @Param("materialCreateStartTime") Date materialCreateStartTime,
                                      @Param("materialCreateEndTime") Date materialCreateEndTime,
                                      @Param("useStatusList") List<Integer> useStatusList,
                                      @Param("customStatusList") List<Integer> customStatusList);

Mapper.xml

<!-- 批量新增数据 -->
<insert id="batchInsert" parameterType="com.wshoto.customer.service.entity.CoImportTagTaskDetail">
    INSERT INTO "co_import_tag_task_detail"(
        "id", "tenant_id", "ws_tenant_id", "gmt_create", "gmt_modified",
        "deleted", "version", "task_id", "type", "keyword",
        "external_user_id", "user_id", "tag_list", "tag_type", "status",
        "msg", "mark_tag_time", "name", "request_id", "union_id", "openid"
    ) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id}, #{item.tenantId}, #{item.wsTenantId}, #{item.gmtCreate}, #{item.gmtModified},
        0, 0, #{item.taskId}, #{item.type}, #{item.keyword},
        #{item.externalUserId},#{item.userId},#{item.tagList},#{item.tagType},#{item.status},
        #{item.msg},#{item.markTagTime},#{item.name},#{item.requestId},#{item.unionId},#{item.openid})
    </foreach>
</insert>


<insert id="insertOrUpdateBatch" keyProperty="id" useGeneratedKeys="true">
    insert into ct_promotion_custom_field(field_name, field_source, default_value, field_desc, promotion_code_id, tenant_id, ws_tenant_id, gmt_create, gmt_modified, deleted, version)
    values
    <foreach collection="entities" item="entity" separator=",">
        (#{entity.fieldName}, #{entity.fieldSource}, #{entity.defaultValue}, #{entity.fieldDesc}, #{entity.promotionCodeId}, #{entity.tenantId}, #{entity.wsTenantId}, now(), now(), #{entity.deleted}, #{entity.version})
    </foreach>
    on duplicate key update
    field_name = values(field_name),
    field_source = values(field_source),
    default_value = values(default_value),
    field_desc = values(field_desc),
    promotion_code_id = values(promotion_code_id),
    tenant_id = values(tenant_id),
    ws_tenant_id = values(ws_tenant_id),
    gmt_create = values(gmt_create),
    gmt_modified = values(gmt_modified),
    deleted = values(deleted),
    version = values(version)
</insert>


<select id="customerLossSummary" resultType="java.lang.Integer">
    select count(distinct cscr.external_id)
    from co_saas_customer_related cscr
    where tenant_id = #{tenantId} and ws_tenant_id = #{wsTenantId}
    and is_hidden = false
    and break_type = 1 and break_time between #{startDate} and #{endDate}
    <if test="isFirst != null">
        and is_first = #{isFirst}
    </if>
    <if test="userIdList !=null and userIdList.length>0 ">
        and user_id IN (select unnest(#{userIdList,jdbcType=ARRAY}) as temp_list)
    </if>
</select>

<select id="selectFavoriteMaterialSortPageByStatistics"
        resultType="com.wshoto.base.content.dal.model.dto.MaterialSortDO">
    select
    COALESCE(qms.send_num,0) as send_num,
    COALESCE(qms.visit_num,0) as visit_num,
    res.id, res.business_type, res.media_type, res.category_id, res.root_parent_id, res.title, res.summary,
    res.cover_image, res.cover_image_url, res.publish_status, res.start_time, res.end_time, res.affect_scope,
    res.affect_group_id, res.ws_tenant_id, res.creator, res.gmt_create, res.gmt_modified,
    res.custom_status, res.custom_ext_json, res.default_share_title
    from (
    select qm.*, qmf.gmt_create as favorite_time
    from ct_material qm inner join ct_material_favorite qmf on qm.id =qmf.material_id
    <where>
        qm.ws_tenant_id = #{wsTenantId}
        and qm.business_type=#{businessType}
        and qm.deleted=0
        and qmf.ws_tenant_id=#{wsTenantId}
        and qmf.deleted=0
        <if test="key != null and key != ''">
            and LOWER(qm.title) like LOWER(concat('%',#{key}::text,'%'))
        </if>
        <if test="userId != null and userId != ''">
            and qmf.collector = #{userId}
        </if>
        <if test="categoryIds != null and categoryIds.size > 0">
            and qm.category_id in
            <foreach collection="categoryIds" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="filterMaterialIds != null and filterMaterialIds.length > 0">
            and qm.id IN (select unnest(#{filterMaterialIds,jdbcType=ARRAY}) as temp_list)
        </if>
        <if test="materialCreateStartTime != null">
            and qm.gmt_create > #{materialCreateStartTime}
        </if>
        <if test="materialCreateEndTime != null">
            and qm.gmt_create &lt;= #{materialCreateEndTime}
        </if>
        <include refid="useStatusConditionSql"/><!-- 拼接使用状态筛选condition -->
        <include refid="customStatusConditionSql"/><!-- 拼接自定义状态筛选condition -->
    </where>
    ) res left join ct_material_statistics qms on res.id = qms.material_id and res.ws_tenant_id = qms.ws_tenant_id
    <choose>
        <when test="sortRule == 1">
            order by send_num desc nulls last, id desc
        </when>
        <when test="sortRule == 2">
            order by visit_num desc nulls last, id desc
        </when>
        <when test="sortRule == 7">
            order by favorite_time desc nulls last, id desc
        </when>
        <otherwise>
            order by gmt_create desc nulls last, id desc
        </otherwise>
    </choose>
</select>


<!-- 自定义状态筛选拼接sql -->
<sql id="customStatusConditionSql">
    <if test="customStatusList != null and customStatusList.size > 0">
        and qm.custom_status in
        <foreach collection="customStatusList" item="customStatus" index="index" open="(" separator="," close=")">
            #{customStatus}
        </foreach>
    </if>
</sql>


        <!-- 使用状态筛选拼接sql -->
<sql id="useStatusConditionSql">
<if test="useStatusList != null and useStatusList.size > 0">
    <foreach collection="useStatusList" item="useStatus" index="index" open="AND (" separator="OR" close=")">
        <choose>
            <when test="useStatus == 0"><!-- 使用中 -->
                ( qm.publish_status = 0 or (qm.publish_status = 2 and qm.start_time &lt;= now() and qm.end_time
                > now()) )
            </when>
            <when test="useStatus == 1"><!-- 已停用 -->
                ( qm.publish_status = 3 )
            </when>
            <when test="useStatus == 2"><!-- 未开始 -->
                ( qm.publish_status = 2 and qm.start_time > now() )
            </when>
            <when test="useStatus == 3"><!-- 已结束 -->
                ( qm.publish_status = 2 and qm.end_time &lt;= now() )
            </when>
        </choose>
    </foreach>
</if>
</sql>