分页读取与无限滚动
多条件排列组合查询店铺信息
dao层在查询店铺列表时,加上parentId不为空时,进行二级子列表的查询
<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.shopCategory != null
and shopCondition.shopCategory.parent != null
and shopCondition.shopCategory.parent.ShopCategoryId != null">
AND s.shop_category_id IN (
SELECT shop_category_id
FROM shop_category
WHERE parent_id=#{shopCondition.shopCategory.parent.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>
<!--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.shopCategory != null
and shopCondition.shopCategory.parent != null
and shopCondition.shopCategory.parent.ShopCategoryId != null">
AND s.shop_category_id IN (
SELECT shop_category_id
FROM shop_category
WHERE parent_id=#{shopCondition.shopCategory.parent.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>
service层不变
controller层
/**
* 返回商品列表里的ShopCategory列表(一级或二级),以及区域信息列表
* @param request
* @return
*/
@RequestMapping(value="/listshoppageinfo", method= RequestMethod.GET)
@ResponseBody
private Map<String, Object> listShopPageInfo(HttpServletRequest request){
Map<String, Object> modelMap = new HashMap<>( );
//从前端获取parentId
long parentId = HttpServletRequestUtil.getLong(request, "parentId");
List<ShopCategory> shopCategoryList = null;
//如果parentId不为null,则是点击某一个商品列表时进行的查询,则取出该parent级别下的二级shopCategory列表
if(parentId != -1){
try{
ShopCategory shopCategoryCondition = new ShopCategory();
ShopCategory parentShopCategory = new ShopCategory();
parentShopCategory.setShopCategoryId( parentId );
shopCategoryCondition.setParent( parentShopCategory );
shopCategoryList = shopCategoryService.getShopCategoryList( shopCategoryCondition );
} catch (Exception e){
modelMap.put( "success", false );
modelMap.put( "errMsg", e.toString() );
}
} else{
try{
//如果parentId不存在,则是点击全部商品时进行的查询,则取出所有商品类别的列表
shopCategoryList = shopCategoryService.getShopCategoryList( null );
} catch (Exception e){
modelMap.put( "success", false );
modelMap.put( "errMsg", e.toString() );
}
}
modelMap.put( "shopCategoryList", shopCategoryList );
//获取区域列表信息
List<Area> areaList = null;
try{
areaList = areaService.getAreaList();
modelMap.put( "areaList", areaList );
} catch(Exception e){
modelMap.put( "success", false );
modelMap.put( "errMsg", e.toString() );
}
modelMap.put( "success", true );
return modelMap;
}
/**
* 获取指定查询条件下的店铺信息
* @param request
* @return
*/
@RequestMapping(value="/listshop", method=RequestMethod.GET)
@ResponseBody
private Map<String, Object> listShop(HttpServletRequest request){
Map<String, Object> modelMap = new HashMap<>( );
//获取页码pageIndex
int pageIndex = HttpServletRequestUtil.getInt( request, "pageIndex" );
//获取一页需要的数据条数pageSize
int pageSize = HttpServletRequestUtil.getInt( request, "pageSize" );
//非空判断
if(pageIndex > -1 && pageSize > -1){
//试着获取一级类别Id
long parentId = HttpServletRequestUtil.getLong( request, "parentId");
//试着获取指定的二级类别Id
long shopCategoryId = HttpServletRequestUtil.getLong( request, "shopCategoryId" );
//试着获取区域Id
int areaId = HttpServletRequestUtil.getInt( request, "areaId" );
//试着获取模糊查询的名字
String shopName = HttpServletRequestUtil.getString( request, "shopName");
//获取组合后的查询条件
Shop shopCondition = compactShopConditionForSearch(parentId, shopCategoryId, areaId, shopName);
//根据查询条件和分页信息获取店铺列表,并返回总数
ShopExecution shopExecution = shopService.getShopList( shopCondition, pageIndex, pageSize );
modelMap.put( "shopList", shopExecution.getShopList() );
modelMap.put( "count", shopExecution.getCount() );
modelMap.put( "success", true );
} else{
modelMap.put( "success", false );
modelMap.put( "errMsg", "empty pageSize or pageIndex");
}
return modelMap;
}
/**
* 组合查询条件,封装进shopCondition中
* @param parentId
* @param shopCategoryId
* @param areaId
* @param shopName
* @return
*/
private Shop compactShopConditionForSearch(long parentId, long shopCategoryId, int areaId, String shopName) {
Shop shopCondition = new Shop();
if(parentId != -1){
//查询parentId下的二级店铺列表
ShopCategory childCategory = new ShopCategory();
ShopCategory parentCategory = new ShopCategory();
parentCategory.setShopCategoryId( parentId );
childCategory.setParent( parentCategory );
shopCondition.setShopCategory( childCategory );
}
if(shopCategoryId != -1){
//查询某个二级类别下的店铺列表
ShopCategory shopCategory = new ShopCategory();
shopCategory.setShopCategoryId( shopCategoryId );
shopCondition.setShopCategory( shopCategory );
}
if(areaId != -1){
//查询位于某个区域下的店铺列表
Area area = new Area();
area.setAreaId( areaId );
shopCondition.setArea( area );
}
if(shopName != null){
//模糊查询 查询店铺名中包含shopName的店铺列表
shopCondition.setShopName( shopName );
}
//同时设置只展示status = 1 的店铺
shopCondition.setStatus( 1 );
return shopCondition;
}