shop--7.店铺编辑和列表--店铺列表 分页查询，模糊查询--dao层实现

实现商铺管理

实现商店列表

实现分页查询店铺，通过条件组合，来筛选出条件范围内的店铺列表

分页查询中使用LIMIT

第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目

dao层

/**
     * 分页查询店铺信息：可输入的条件有：店铺名（模糊查询），店铺状态，店铺类别，区域Id,owner
     * @param shopCondition
     * @param rowIndex  表示第几行去取数据
     * @param pageSize  表示返回多少行数据
     * @return
     */
    //有个问题：用中文的店铺名字做模糊查询时，不能得到结果。英文的就可以
    public List<Shop> queryShopList(@Param("shopCondition") Shop shopCondition,
                                    @Param( "rowIndex" ) int rowIndex,
                                    @Param( "pageSize" ) int pageSize);

    /**
     * 返回由queryShopList方法中的的条件组合查询能查出多少条总记录，用来做分页
     * @param shopCondition
     * @return
     */
    public int queryShopCount(@Param("shopCondition")Shop shopCondition);

　　

 

dao.xml

分页查询店铺信息：可输入的条件有：店铺名（模糊查询），店铺状态，店铺类别，区域Id,owner

rowIndex 表示第几行去取数据

pageSize 表示返回多少行数据

 

<!--public List<Shop> queryShopList(@Param("ShopCondition") Shop shopCondition,
                                    @Param( "rowIndex" ) int rowIndex,
                                    @Param( "pageSize" ) int pageSize);-->
    <select id="queryShopList" resultMap="shopMap">
        SELECT
        s.shop_id,
        s.shop_name,
        s.shop_desc,
        s.shop_addr,
        s.phone,
        s.shop_img,
        s.priority,
        s.create_time,
        s.last_edit_time,
        s.status,
        s.advice,
        a.area_id,
        a.area_name,
        sc.shop_category_id,
        sc.shop_category_name
        FROM
        shop s,
        area a,
        shop_category sc
        <where>
            <if test="shopCondition.shopCategory != null and
            shopCondition.shopCategory.shopCategoryId != null">
                AND s.shop_category_id=#{shopCondition.shopCategory.shopCategoryId}
            </if>
            <if test="shopCondition.area != null and shopCondition.area.areaId != null">
                AND s.area_id=#{shopCondition.area.areaId}
            </if>
            <if test="shopCondition.status != null">
                AND s.status=#{shopCondition.status}
            </if>
            <if test="shopCondition.owner != null and shopCondition.owner.userId != null">
                AND s.owner_id=#{shopCondition.owner.userId}
            </if>
            <if test="shopCondition.shopName != null">
                AND s.shop_name LIKE '%${shopCondition.shopName}%'
            </if>
            AND
            s.area_id=a.area_id
            AND
            s.shop_category_id=sc.shop_category_id
        </where>
        ORDER BY
        s.priority DESC
        LIMIT #{rowIndex},#{pageSize};
    </select>

　　

返回由queryShopList方法中的的条件组合查询能查出多少条总记录，用来做分页

    <!--public int queryShopCount(@Param("shopCondition")Shop shopCondition);-->
    <select id="queryShopCount" resultType="int" parameterType="com.shop.bean.Shop">
        SELECT
        count(1),
        s.shop_id,
        s.shop_name,
        s.shop_desc,
        s.shop_addr,
        s.phone,
        s.shop_img,
        s.priority,
        s.create_time,
        s.last_edit_time,
        s.status,
        s.advice,
        a.area_id,
        a.area_name,
        sc.shop_category_id,
        sc.shop_category_name
        FROM
        shop s,
        area a,
        shop_category sc
        <where>
            <if test="shopCondition.shopCategory != null and
            shopCondition.shopCategory.shopCategoryId != null">
                AND s.shop_category_id=#{shopCondition.shopCategory.shopCategoryId}
            </if>
            <if test="shopCondition.area != null and shopCondition.area.areaId != null">
                AND s.area_id=#{shopCondition.area.areaId}
            </if>
            <if test="shopCondition.status != null">
                AND s.status=#{shopCondition.status}
            </if>
            <if test="shopCondition.owner != null and shopCondition.owner.userId != null">
                AND s.owner_id=#{shopCondition.owner.userId}
            </if>
            <if test="shopCondition.shopName != null">
                AND s.shop_name LIKE '%${shopCondition.shopName}%'
            </if>
            AND
            s.area_id=a.area_id
            AND
            s.shop_category_id=sc.shop_category_id
        </where>
    </select>