校园商铺-6店铺编辑列表和列表功能-1店铺信息编辑之Dao层开发
学习目标：
- 实现单个店铺信息的获取
- 实现对店铺编辑从底层到前端的开发
单个店铺信息的获取
在实现店铺信息编辑的同时，为什么还要获取店铺信息呢？
因为在对店铺编辑之前，是需要先获取原来的信息的。先传入shopId，获取原来的信息，然后再根据原来的信息去改动，这样的设计才是合理的。
1.接口中定义Dao接口方法
package com.csj2018.o2o.dao;
import com.csj2018.o2o.entity.Shop;
public interface ShopDao {
/**
* 通过shop id查询店铺
* @param shopId
* @return shop
*/
Shop queryByShopId(long shopId);
/**
* 新增店铺
* @param shop
* @return 返回影响的行数；-1插入失败
*/
int insertShop(Shop shop);
/**
* 更新店铺信息
* @param shop
* @return 返回影响的行数
*/
int updateShop(Shop shop);
}
2.mapper实现Dao方法
之前已经实现了店铺的更改，传入了一个Shop的实体类，只要其某个属性不为空，就更新其对应的属性。
queryByShopId不仅返回tb_shop的信息，还需要额外返回一些信息。比如tb_shop只有shop_category_id，而我们需要获取shop_category_name，所以要将tb_shop和tb_shop_category去连接，从而获取到shop_category_name，同时还要与tb_area连接去获取area_name。
在mapper里面，我们需要通过什么样的类型，去接收这几张表联合的返回值呢？
通过resultMapper去接收，然后在select中指定这个这个返回类型即可。
<!-- 定义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为对应实体类的成员变量的名字 -->
...
<!-- property：实体类的成员变量，column：通过那个字段关联 javaType：返回类型 -->
<association property="area" column="area_id" javaType="com.csj2018.o2o.entity.Area">
<select id="queryByShopId" resultMap="shopMap" parameterType="Long">...</select>
具体代码：
ShopDao.xml
<?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="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>
3.单元测试
package com.csj2018.o2o.dao;
import static org.junit.Assert.assertEquals;
import java.util.Date;
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
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);
}
}
问题
1.mapper中，如果一个字段在多个表中存在，可以对列使用别名，以便区分。
如area和shop都有priority
select s.priority,a.priority as area_priority from shopcategory s,area a where s.area_id == a.id;
