springboot电脑商城demo1

视频教学30-加入购物车-持久层_哔哩哔哩_bilibili

1.数据库

CREATE TABLE t_cart (
	cid INT AUTO_INCREMENT COMMENT '���ﳵ����id',
	uid INT NOT NULL COMMENT '�û�id',
	pid INT NOT NULL COMMENT '��Ʒid',
	price BIGINT COMMENT '����ʱ��Ʒ����',
	num INT COMMENT '��Ʒ����',
	created_user VARCHAR(20) COMMENT '������',
	created_time DATETIME COMMENT '����ʱ��',
	modified_user VARCHAR(20) COMMENT '�޸���',
	modified_time DATETIME COMMENT '�޸�ʱ��',
	PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.创建实体类

package com.wjc.vo;

import java.io.Serializable;

/** 购物车数据的Value Object类 */
public class CartVO implements Serializable {
    private Integer cid;
    private Integer uid;
    private Integer pid;
    private Long price;
    private Integer num;
    private String title;
    private Long realPrice;
    private String image;

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public Long getPrice() {
        return price;
    }

    public void setPrice(Long price) {
        this.price = price;
    }

    public Integer getNum() {
        return num;
    }

    public void setNum(Integer num) {
        this.num = num;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Long getRealPrice() {
        return realPrice;
    }

    public void setRealPrice(Long realPrice) {
        this.realPrice = realPrice;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof CartVO)) return false;

        CartVO cartVO = (CartVO) o;

        if (getCid() != null ? !getCid().equals(cartVO.getCid()) : cartVO.getCid() != null) return false;
        if (getUid() != null ? !getUid().equals(cartVO.getUid()) : cartVO.getUid() != null) return false;
        if (getPid() != null ? !getPid().equals(cartVO.getPid()) : cartVO.getPid() != null) return false;
        if (getPrice() != null ? !getPrice().equals(cartVO.getPrice()) : cartVO.getPrice() != null) return false;
        if (getNum() != null ? !getNum().equals(cartVO.getNum()) : cartVO.getNum() != null) return false;
        if (getTitle() != null ? !getTitle().equals(cartVO.getTitle()) : cartVO.getTitle() != null) return false;
        if (getRealPrice() != null ? !getRealPrice().equals(cartVO.getRealPrice()) : cartVO.getRealPrice() != null)
            return false;
        return getImage() != null ? getImage().equals(cartVO.getImage()) : cartVO.getImage() == null;
    }

    @Override
    public int hashCode() {
        int result = getCid() != null ? getCid().hashCode() : 0;
        result = 31 * result + (getUid() != null ? getUid().hashCode() : 0);
        result = 31 * result + (getPid() != null ? getPid().hashCode() : 0);
        result = 31 * result + (getPrice() != null ? getPrice().hashCode() : 0);
        result = 31 * result + (getNum() != null ? getNum().hashCode() : 0);
        result = 31 * result + (getTitle() != null ? getTitle().hashCode() : 0);
        result = 31 * result + (getRealPrice() != null ? getRealPrice().hashCode() : 0);
        result = 31 * result + (getImage() != null ? getImage().hashCode() : 0);
        return result;
    }

    @Override
    public String toString() {
        return "CartVO{" +
                "cid=" + cid +
                ", uid=" + uid +
                ", pid=" + pid +
                ", price=" + price +
                ", num=" + num +
                ", title='" + title + '\'' +
                ", realPrice=" + realPrice +
                ", image='" + image + '\'' +
                '}';
    }
}

3.持久层

3.1规划需要执行的sql语句

1.向购物车表t_cart中加入数据

<insert id="insert" useGeneratedKeys="true" keyProperty="cid">
        INSERT INTO t_cart (uid, pid, price, num, created_user, created_time, modified_user, modified_time)
        VALUES (#{uid}, #{pid}, #{price}, #{num}, #{createdUser}, #{createdTime}, #{modifiedUser}, #{modifiedTime})
    </insert>

2.若当前商品已经在购物车中存在，则直接更新num数量

 <update id="updateNumByCid">
        UPDATE
            t_cart
        SET
            num=#{num},
            modified_user=#{modifiedUser},
            modified_time=#{modifiedTime}
        WHERE
            cid=#{cid}
    </update>

3.在插入或更新选择时，取决于数据库t_cart中某个用户是否已经有这个商品数据，进行查询再确定

<select id="findByUidAndPid" resultMap="CartEntityMap">
        SELECT
            *
        FROM
            t_cart
        WHERE
            uid=#{uid} AND pid=#{pid}
    </select>

3.2设计接口和抽象方法

package com.wjc.mapper;


import com.wjc.pojo.Cart;
import com.wjc.vo.CartVO;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

/** 处理购物车数据的持久层接口 */
public interface CartMapper {
    /**
     * 插入购物车数据
     * @param cart 购物车数据
     * @return 受影响的行数
     */
    Integer insert(Cart cart);

    /**
     * 修改购物车数据中商品的数量
     * @param cid 购物车数据的id
     * @param num 新的数量
     * @param modifiedUser 修改执行人
     * @param modifiedTime 修改时间
     * @return 受影响的行数
     */
    Integer updateNumByCid(
            @Param("cid") Integer cid,
            @Param("num") Integer num,
            @Param("modifiedUser") String modifiedUser,
            @Param("modifiedTime") Date modifiedTime);

    /**
     * 根据用户id和商品id查询购物车中的数据
     * @param uid 用户id
     * @param pid 商品id
     * @return 匹配的购物车数据，如果该用户的购物车中并没有该商品，则返回null
     */
    Cart findByUidAndPid(
            @Param("uid") Integer uid,
            @Param("pid") Integer pid);

    /**
     * 查询某用户的购物车数据
     * @param uid 用户id
     * @return 该用户的购物车数据的列表
     */
    List<CartVO> findVOByUid(Integer uid);

    /**
     * 根据购物车数据id查询购物车数据详情
     * @param cid 购物车数据id
     * @return 匹配的购物车数据详情，如果没有匹配的数据则返回null
     */
    Cart findByCid(Integer cid);

    /**
     * 根据若干个购物车数据id查询详情的列表
     * @param cids 若干个购物车数据id
     * @return 匹配的购物车数据详情的列表
     */
    List<CartVO> findVOByCids(Integer[] cids);
}

3.3SQL映射 

创建一个CartMapper.xml映射文件，添加以上三个方法的sql语句映射

<?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">
<mapper namespace="com.wjc.mapper.CartMapper">
    <resultMap id="CartEntityMap" type="Cart">
        <id column="cid" property="cid"/>
        <result column="created_user" property="createdUser"/>
        <result column="created_time" property="createdTime"/>
        <result column="modified_user" property="modifiedUser"/>
        <result column="modified_time" property="modifiedTime"/>
    </resultMap>

    <!-- 插入购物车数据：Integer insert(Cart cart) -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="cid">
        INSERT INTO t_cart (uid, pid, price, num, created_user, created_time, modified_user, modified_time)
        VALUES (#{uid}, #{pid}, #{price}, #{num}, #{createdUser}, #{createdTime}, #{modifiedUser}, #{modifiedTime})
    </insert>

    <!-- 修改购物车数据中商品的数量：
         Integer updateNumByCid(
            @Param("cid") Integer cid,
            @Param("num") Integer num,
            @Param("modifiedUser") String modifiedUser,
            @Param("modifiedTime") Date modifiedTime) -->
    <update id="updateNumByCid">
        UPDATE
            t_cart
        SET
            num=#{num},
            modified_user=#{modifiedUser},
            modified_time=#{modifiedTime}
        WHERE
            cid=#{cid}
    </update>

    <!-- 根据用户id和商品id查询购物车中的数据：
         Cart findByUidAndPid(
            @Param("uid") Integer uid,
            @Param("pid") Integer pid) -->
    <select id="findByUidAndPid" resultMap="CartEntityMap">
        SELECT
            *
        FROM
            t_cart
        WHERE
            uid=#{uid} AND pid=#{pid}
    </select>

    <!-- 查询某用户的购物车数据：List<CartVO> findVOByUid(Integer uid) -->
    <select id="findVOByUid" resultType="com.wjc.vo.CartVO">
        SELECT
            cid,
            uid,
            pid,
            t_cart.price,
            t_cart.num,
            t_product.title,
            t_product.price AS realPrice,
            t_product.image
        FROM
            t_cart
                LEFT JOIN t_product ON t_cart.pid = t_product.id
        WHERE
            uid = #{uid}
        ORDER BY
            t_cart.created_time DESC
    </select>

    <!-- 根据购物车数据id查询购物车数据详情：Cart findByCid(Integer cid) -->
    <select id="findByCid" resultMap="CartEntityMap">
        SELECT
            *
        FROM
            t_cart
        WHERE
            cid = #{cid}
    </select>

    <!-- 根据若干个购物车数据id查询详情的列表：List<CartVO> findVOByCids(Integer[] cids) -->
    <select id="findVOByCids" resultType="com.wjc.vo.CartVO">
        SELECT
            cid,
            uid,
            pid,
            t_cart.price,
            t_cart.num,
            t_product.title,
            t_product.price AS realPrice,
            t_product.image
        FROM
            t_cart
                LEFT JOIN t_product ON t_cart.pid = t_product.id
        WHERE
            cid IN (
                <foreach collection="array" item="cid" separator=",">
                    #{cid}
                </foreach>
            )
        ORDER BY
            t_cart.created_time DESC
    </select>
</mapper>