出于什么考虑还单独searchListCount了一下？

Question

<select id="searchListByPage" parameterType="Map" resultType="HashMap">
    SELECT id,
           `code`,
           title,
           description,
           image,
           initial_price AS "initialPrice",
           current_price AS "currentPrice",
           sales_volume AS "salesVolume"
    FROM tb_goods
    WHERE `status` = 1
    <if test="keyword!=null">
        AND ( title LIKE CONCAT("%",#{keyword},"%") OR 
              code LIKE CONCAT("%",#{keyword},"%") 
            )
    </if>
    <if test="type!=null">
        AND type = #{type}
    </if>
    <if test="sex!=null">
        AND JSON_CONTAINS (tag, CONCAT('"',#{sex},'"'))
    </if>
    <choose>
        <when test="priceType==1">
            AND current_price &gt;= 0 AND current_price &lt; 100
        </when>
        <when test="priceType==2">
            AND current_price &gt;= 100 AND current_price &lt; 500
        </when>
        <when test="priceType==3">
            AND current_price &gt;= 500 AND current_price &lt; 1000
        </when>
        <when test="priceType==4">
            AND current_price &gt;= 1000
        </when>
    </choose>
    <choose>
        <when test="orderType==1">
            ORDER BY id DESC
        </when>
        <when test="orderType==2">
            ORDER BY sales_volume DESC
        </when>
        <when test="orderType==3">
            ORDER BY currentPrice ASC
        </when>
        <when test="orderType==4">
            ORDER BY currentPrice DESC
        </when>
        <otherwise>
            ORDER BY sales_volume DESC, id DESC
        </otherwise>
    </choose>
    LIMIT #{start}, #{length}
</select>
<select id="searchListCount" parameterType="Map" resultType="long">
    SELECT COUNT(*)
    FROM tb_goods
    WHERE `status` = 1
    <if test="keyword!=null">
        AND ( title LIKE CONCAT("%",#{keyword},"%") OR 
              code LIKE CONCAT("%",#{keyword},"%") 
            )
    </if>
    <if test="type!=null">
        AND type = #{type}
    </if>
    <if test="sex!=null">
        AND JSON_CONTAINS (tag, CONCAT('"',#{sex},'"'))
    </if>
    <choose>
        <when test="priceType==1">
            AND current_price &gt;= 0 AND current_price &lt; 100
        </when>
        <when test="priceType==2">
            AND current_price &gt;= 100 AND current_price &lt; 500
        </when>
        <when test="priceType==3">
            AND current_price &gt;= 500 AND current_price &lt; 1000
        </when>
        <when test="priceType==4">
            AND current_price &gt;= 1000
        </when>
    </choose>
</select>

@Service("FrontGoodsServiceImpl")
@Slf4j
public class GoodsServiceImpl implements GoodsService {
    ……
    @Override
    public PageUtils searchListByPage(Map param) {
        ArrayList<HashMap> list = new ArrayList<>();
        long count = goodsDao.searchListCount(param);
        if (count > 0) {
            list = goodsDao.searchListByPage(param);
        }
        int page = MapUtil.getInt(param, "page");
        int length = MapUtil.getInt(param, "length");
        PageUtils pageUtils = new PageUtils(list, count, page, length);
        return pageUtils;
    }
}

直接从 searchListByPage 里取size不就可以了吗

而且这俩sql都蛮不简单的，单独查下count感觉也消耗不小

---

本人犯二。。。 是要单独写

Answer 1

神思者 2023-08-22 21:39:33

你这么说都把我惊到了，你想想看limit子句是干什么的

• 提问者 demonCry #1

  没懂。。。 是因为相对searchListByPage下面 searchListCount 的 count(*) 有什么优化吗？

• 神思者 回复 提问者 demonCry #2

  sql语句要查询记录总数是多少，不是要查询一页有多少条记录

• 提问者 demonCry 回复 神思者 #3

  嗷~ 我sb了。。。我咋想的。。。

点开查看后面2条评论