实现商铺管理
实现商店列表
实现分页查询店铺,通过条件组合,来筛选出条件范围内的店铺列表
分页查询中使用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>