性能优化之mybatis实现接口的批量查询，减少数据库的查询消耗

快来打我* 2021-12-12 06:37 664阅读 0赞

<select id="selectByTime" resultType="com.neo.xnol.api.activity.dto.ActivityMqmsgDTO">
            SELECT
                id,
                eventType,
                eventId,
                userId,
                userName,
                content,
                msgStatus,
                retryCount,
                errorDetails,
                createTime,
                updateTime,
                msgVersion,
                sysVersion
            FROM
                t_activity_mqmsg
            WHERE
                userId= #{userId}
            AND
                eventType IN
                <foreach collection="eventTypeList" index="index" item="item" open="(" separator="," close=")"> #{item}  </foreach>
            AND
                createTime >= #{beginTime}
            AND
                createTime <= #{endTime}
            ORDER BY
                createTime
        </select>

<select id="findActivityInfoListByCategoryId" resultMap="BaseResultMap" >
            SELECT
                a.*
            FROM  t_activity_info a
            WHERE
            a.categoryId in
            <foreach collection="list" item="categoryId" open="(" close=")" separator=","> #{categoryId} </foreach>
            and a.isDelete = 0
            and a.`endTime` > now()
            <if test="organizationId != null and organizationId != 3">
                AND a.organizationId = #{organizationId,jdbcType=INTEGER}
            </if>
            ORDER BY a.createTime DESC
        </select>

<select id="selectOneByRewardType" resultMap="BaseResultMap">
        select w.* 
          from t_lucky_winning w 
         inner join t_lucky_activity a 
            on w.luckyId = a.luckyId and a.activityId = #{activityId,jdbcType=INTEGER}
         inner join t_lucky_reward r on w.rewardId = r.id
         where r.rewardId in(
         <foreach collection="rewardIds" item="rewardId" separator=","> #{rewardId,jdbcType=INTEGER} </foreach>)
         order by w.luckytime desc
         limit 1
      </select>

转载于:https://www.cnblogs.com/qmfsun/p/11002258.html