shop--8.店铺列表展示--Dao层

实现商铺管理

实现商店列表

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

分页查询中使用LIMIT

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

 1 /**
 2      * 分页查询店铺,可输入的条件有：店铺名(模糊)，店铺状态，店铺类别，区域id，owner
 3      * @param shopCondition
 4      * @param rowIndex 从第几行开始取数据
 5      * @param pageSize 表示返回多少行数据
 6      * @param 标签的作用 取参数的时候需要唯一的标识
 7      * @return
 8      */
 9     List<Shop> queryShopList(@Param("shopCondition")Shop shopCondition,
10             @Param("rowIndex") int rowIndex,@Param("pageSize") int pageSize);
11     /**
12      * 返回queryShopList总数
13      * @param shopCondition
14      * @return
15      */
16     int queryShopCount(@Param("shopCondition")Shop shopCondition);

 

SQL

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

使用动态SQL来查

    <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.enable_status,
        s.advice,
        a.area_id,
        a.area_name,
        sc.shop_category_id,
        sc.shop_category_name
        FROM
        tb_shop s,
        tb_area a,
        tb_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.shopCategory.areaId!=null">
                and shop_area_id = #{shopCondition.area.areaId}
            </if>
            <if test="shopCondition.shopName!=null">
              and s.shop_name like '%${shopCondition.shopName}%'
            </if>
            <if test="shopCondition.enableStatus!=null">
              and s.enable_status = #{shopCondition.status}
            </if>
            <if test="shopCondition.owner!=null and shopCondition.owner.userId!=null">
              and s.owner_id = #{shopCondition.owner.userId}
            </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>
    <select id="queryShopCount" resultType="int">
      SELECT
      count(1)
      FROM
        tb_shop s,
        tb_area a,
        tb_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.shopCategory.areaId!=null">
                and shop_area_id = #{shopCondition.area.areaId}
            </if>
            <if test="shopCondition.shopName!=null">
              and s.shop_name like '%${shopCondition.shopName}%'
            </if>
            <if test="shopCondition.enableStatus!=null">
              and s.enable_status = #{shopCondition.status}
            </if>
            <if test="shopCondition.owner!=null and shopCondition.owner.userId!=null">
              and s.owner_id = #{shopCondition.owner.userId}
            </if>
            AND
            s.area_id = a.area_id
            AND
            s.shop_category_id = sc.shop_category_id
        </where>
    </select>

 

posted @ 2018-07-29 19:10  windbag7  阅读(342)  评论(0)  编辑  收藏  举报