学习目标：
- 分页读取无级滚动
- 多条件排列组合查询店铺信息
1.Dao层
1.1修改mapper实现类
增加依据parentId筛选店铺的查询：
shopCondition.shopCategory != null and shopCondition.shopCategory.parent != null and shopCondition.shopCategory.parent.shopCategoryId != null
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!-- useGeneratedKeys="true"，一旦数据插入成功，使用JDBC的getGeneratedKeys获取数据库自增主键值 -->
<mapper namespace="com.csj2018.o2o.dao.ShopDao">
<!-- 定义resultMapper，类型为Shop，id命名为shopMap -->
<resultMap type="com.csj2018.o2o.entity.Shop" id="shopMap">
<id column="shop_id" property="shopId" /><!-- 定义主键id，column为数据表的主键，property为对应实体类的成员变量的名字 -->
<result column="shop_name" property="shopName" /><!-- 返回字段，column为数据表的字段，property为对应实体类的成员变量的名字 -->
<result column="shop_desc" property="shopDesc" />
<result column="shop_addr" property="shopAddr" />
<result column="phone" property="phone" />
<result column="shop_img" property="shopImg" />
<result column="priority" property="priority" />
<result column="create_time" property="createTime" />
<result column="last_edit_time" property="lastEditTime" />
<result column="enable_status" property="enableStatus" />
<result column="advice" property="advice" />
<!-- property：实体类的成员变量，column：通过那个字段关联 javaType：返回类型 -->
<association property="area" column="area_id" javaType="com.csj2018.o2o.entity.Area">
<id column="area_id" property="areaId" />
<result column="area_name" property="areaName" />
</association>
<association property="shopCategory" column="shop_category_id" javaType="com.csj2018.o2o.entity.ShopCategory">
<id column="shop_category_id" property="shopCategoryId" />
<result column="shop_category_name" property="shopCategoryName" />
</association>
<association property="owner" column="user_id" javaType="com.csj2018.o2o.entity.PersonInfo">
<id column="user_id" property="userId" />
<result column="name" property="name" />
</association>
</resultMap>
<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.enable_status,
s.create_time,
s.last_edit_time,
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.shopCategory != null and shopCondition.shopCategory.parent != null and shopCondition.shopCategory.parent.shopCategoryId != null">
and s.shop_category_id in (select shop_category_id from tb_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.shopName != null">
and s.shop_name like '%${shopCondition.shopName}%'
</if>
<if test="shopCondition.enableStatus != null">
and s.enable_status = #{shopCondition.enableStatus}
</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.shopCategory != null and shopCondition.shopCategory.parent != null and shopCondition.shopCategory.parent.shopCategoryId != null">
and s.shop_category_id in (select shop_category_id from tb_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.shopName != null">
and s.shop_name like '%${shopCondition.shopName}%'
</if>
<if test="shopCondition.enableStatus != null">
and s.enable_status = #{shopCondition.enableStatus}
</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>
<select id="queryByShopId" resultMap="shopMap" parameterType="Long">
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.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
s.area_id = a.area_id
and
s.shop_category_id = sc.shop_category_id
and
s.shop_id = #{shopId}
</select>
<insert id="insertShop" useGeneratedKeys="true"
keyColumn="shop_id" keyProperty="shopId">
insert into
tb_shop(owner_id, area_id, shop_category_id, shop_name,
shop_desc, shop_addr, phone, shop_img, priority,
create_time, last_edit_time, enable_status, advice)
values
(#{owner.userId}, #{area.areaId}, #{shopCategory.shopCategoryId}, #{shopName},
#{shopDesc}, #{shopAddr}, #{phone}, #{shopImg}, #{priority},
#{createTime}, #{lastEditTime}, #{enableStatus}, #{advice});
</insert>
<update id="updateShop" parameterType="com.csj2018.o2o.entity.Shop">
update tb_shop
<set>
<if test="shopName != null">shop_name=#{shopName},</if>
<if test="shopDesc != null">shop_desc=#{shopDesc},</if>
<if test="shopAddr != null">shop_addr=#{shopAddr},</if>
<if test="phone != null">phone=#{phone},</if>
<if test="shopImg != null">shop_img=#{shopImg},</if>
<if test="priority != null">priority=#{priority},</if>
<if test="lastEditTime != null">last_edit_time=#{lastEditTime},</if>
<if test="enableStatus != null">enable_status=#{enableStatus},</if>
<if test="advice != null">advice=#{advice},</if>
<if test="area != null">area_id=#{area.areaId},</if>
<if test="shopCategory != null">shop_category_id=#{shopCategory.shopCategoryId}</if>
</set>
where shop_id=#{shopId}
</update>
</mapper>
1.2单元测试
package com.csj2018.o2o.dao;
import static org.junit.Assert.assertEquals;
import java.util.Date;
import java.util.List;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import com.csj2018.o2o.BaseTest;
import com.csj2018.o2o.entity.Area;
import com.csj2018.o2o.entity.PersonInfo;
import com.csj2018.o2o.entity.Shop;
import com.csj2018.o2o.entity.ShopCategory;
public class ShopDaoTest extends BaseTest{
@Autowired
private ShopDao shopDao;
@Test
@Ignore
public void testQueryShopList() {
Shop shopCondition = new Shop();
PersonInfo owner = new PersonInfo();
owner.setUserId(1L);
shopCondition.setOwner(owner);
List<Shop> shopList = shopDao.queryShopList(shopCondition, 0, 5);
System.out.println("用户为1的第一页的数量："+shopList.size());
int count = shopDao.queryShopCount(shopCondition);
System.out.println("用户为1的店铺总数："+count);
for(Shop shop:shopList) {
System.out.print(shop.getShopId());
System.out.println(shop.getEnableStatus());
}
//shopCategoryId设置为1
ShopCategory shopCategory = new ShopCategory();
shopCategory.setShopCategoryId(1L);
shopCondition.setShopCategory(shopCategory);
List<Shop> shopList2 = shopDao.queryShopList(shopCondition, 0, 5);
System.out.println("用户为1且店铺类别为1时，第一页店铺："+shopList2.size());
int count2 = shopDao.queryShopCount(shopCondition);
System.out.println("用户为1且店铺类别为1的店铺总数"+count2);
}
@Test
@Ignore
public void testQueryShopById() {
long shopId=1;
Shop shop = shopDao.queryByShopId(shopId);
System.out.println(shop.getArea().getAreaId());
System.out.println(shop.getArea().getAreaName());
}
@Test
@Ignore
public void testInsertShop() {
Shop shop = new Shop();
PersonInfo owner = new PersonInfo();
Area area = new Area();
ShopCategory shopCategory = new ShopCategory();
owner.setUserId(1L);
area.setAreaId(2);
shopCategory.setShopCategoryId(1L);
shop.setOwner(owner);
shop.setArea(area);
shop.setShopCategory(shopCategory);
shop.setShopName("测试de店铺");
shop.setShopDesc("店铺描述");
shop.setShopAddr("测试路1号");
shop.setPhone("123456789");
shop.setShopImg("shopimg");
shop.setCreateTime(new Date());
shop.setEnableStatus(1);
shop.setAdvice("审核中");
shop.setPriority(3);
try{
int effectedNum = shopDao.insertShop(shop);
assertEquals(1,effectedNum);
System.out.print(effectedNum);
}catch (Exception e) {
e.printStackTrace();
}
}
@Test
@Ignore
public void testUpdateShop() {
Shop shop = new Shop();
shop.setShopId(1L);
shop.setShopDesc("江南皮革厂");
shop.setShopAddr("皮革厂路1号");
shop.setPhone("0571-3770571");
shop.setLastEditTime(new Date());
int effectedNum = shopDao.updateShop(shop);
assertEquals(1,effectedNum);
System.out.print(effectedNum);
}
@Test
public void testCategory() {
Shop shopCondition = new Shop();
ShopCategory childCategory = new ShopCategory();
ShopCategory parentCategory = new ShopCategory();
parentCategory.setShopCategoryId(3L);
childCategory.setParent(parentCategory);
shopCondition.setShopCategory(childCategory);
List<Shop> shopList = shopDao.queryShopList(shopCondition, 0, 6);
int count = shopDao.queryShopCount(shopCondition);
System.out.println(shopList.size());
System.out.println(count);
System.out.println(shopCondition.getShopCategory().getParent().getShopCategoryId());
}
}
2.Controller层
package com.csj2018.o2o.web.frontend;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.csj2018.o2o.dto.ShopExecution;
import com.csj2018.o2o.entity.Area;
import com.csj2018.o2o.entity.Shop;
import com.csj2018.o2o.entity.ShopCategory;
import com.csj2018.o2o.service.AreaService;
import com.csj2018.o2o.service.ShopCategoryService;
import com.csj2018.o2o.service.ShopService;
import com.csj2018.o2o.util.HttpServletRequestUtil;
@Controller
@RequestMapping(value="frontend")
public class ShopListController {
@Autowired
private AreaService areaService;
@Autowired
private ShopCategoryService shopCategoryService;
@Autowired
private ShopService shopService;
/**
* 返回商铺分类和区域列表，用于用户筛选
* @param request
* @return
*/
@RequestMapping(value="/listshopspageinfo", method=RequestMethod.GET)
@ResponseBody
private Map<String,Object> listShopsPageInfo(HttpServletRequest request){
Map<String,Object> modelMap = new HashMap<String,Object>();
//尝试从请求中获取parentId
long parentId = HttpServletRequestUtil.getLong(request, "parentId");
List<ShopCategory> shopCategoryList = null;
if(parentId>-1) {
//如果parentId不存在，则取出该商铺分类下的次商铺分类
try {
ShopCategory shopCategoryCondition = new ShopCategory();
ShopCategory parent = new ShopCategory();
parent.setShopCategoryId(parentId);
shopCategoryCondition.setParent(parent);
shopCategoryList = shopCategoryService.getShopCategoryList(shopCategoryCondition);
}catch(Exception e) {
modelMap.put("success", false);
modelMap.put("errMsg", e.getMessage());
}
}else {
try {
//如果parentId不存在，则取出所有一级商铺分类下的全部商铺（用户在首页点击“全部商店”）
shopCategoryList = shopCategoryService.getShopCategoryList(null);
}catch (Exception e) {
modelMap.put("success",false);
modelMap.put("errMsg",e.getMessage());
}
}
modelMap.put("shopCategoryList", shopCategoryList);
List<Area> areaList = null;
try {
//获取区域列表信息
areaList = areaService.getAreaList();
modelMap.put("areaList", areaList);
modelMap.put("success", true);
return modelMap;
}catch(Exception e) {
modelMap.put("success", false);
modelMap.put("errMsg", e.getMessage());
}
return modelMap;
}
@RequestMapping(value="/listshops",method=RequestMethod.GET)
@ResponseBody
private Map<String,Object> listShops(HttpServletRequest request){
Map<String,Object> modelMap = new HashMap<String,Object>();
//获取页面
int pageIndex = HttpServletRequestUtil.getInt(request, "pageIndex");
//获取每页的条数
int pageSize = HttpServletRequestUtil.getInt(request, "pageSize");
//非空判断
if((pageIndex > -1)&&(pageSize>-1)) {
//试着获取一级类别Id
long parentId = HttpServletRequestUtil.getLong(request, "parentId");
//试着获取二级类别
long shopCategoryId = HttpServletRequestUtil.getLong(request, "shopCategoryId");
//获取区域
int areaId = HttpServletRequestUtil.getInt(request, "areaId");
//获取店铺名称
String shopName = HttpServletRequestUtil.getString(request, "shopName");
//获取组合之后的查询条件
Shop shopCondition = compactShopCondition4Search(parentId,shopCategoryId,areaId,shopName);
ShopExecution se = shopService.getShopList(shopCondition, pageIndex, pageSize);
modelMap.put("success", true);
modelMap.put("shopList", se.getShopList());
modelMap.put("count", se.getCount());
}else {
modelMap.put("success", false);
modelMap.put("errMsg", "empty pageSize or pageIndex");
}
return modelMap;
}
private Shop compactShopCondition4Search(long parentId,long shopCategoryId,int areaId,String shopName) {
Shop shopCondition = new Shop();
if(parentId != -1) {
ShopCategory childCategory = new ShopCategory();
ShopCategory parentCategory = new ShopCategory();
parentCategory.setShopCategoryId(parentId);
childCategory.setParent(parentCategory);
shopCondition.setShopCategory(childCategory);
}
if(shopCategoryId != -1L) {//存在二级类别，就取二级类别，一级类别会被覆盖
ShopCategory shopCategory = new ShopCategory();
shopCategory.setShopCategoryId(shopCategoryId);
shopCondition.setShopCategory(shopCategory);
}
if(areaId != -1L) {
Area area = new Area();
area.setAreaId(areaId);
shopCondition.setArea(area);
}
if(shopName != null) {
shopCondition.setShopName(shopName);
}
shopCondition.setEnableStatus(1);
return shopCondition;
}
}
