newbee商城购物车模块mapper.xml

1.浏览代码

1）表 自定义

DROP TABLE IF EXISTS `tb_newbee_mall_shopping_cart_item`;
CREATE TABLE `tb_newbee_mall_shopping_cart_item`  (`cart_item_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '购物项主键id',`user_id` bigint(20) NOT NULL COMMENT '用户主键id',`goods_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '关联商品id',`goods_count` int(11) NOT NULL DEFAULT 1 COMMENT '数量(最大为5)',`is_deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT '删除标识字段(0-未删除 1-已删除)',`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最新修改时间',PRIMARY KEY (`cart_item_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 69 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2）实体类和Mapper接口 可由mybatis代码生成器生成，再修改

@Data
public class NewBeeMallShoppingCartItem {private Long cartItemId;private Long userId;private Long goodsId;private Integer goodsCount;private Byte isDeleted;private Date createTime;private Date updateTime;
}

public interface NewBeeMallShoppingCartItemMapper {int deleteByPrimaryKey(Long cartItemId);int insert(NewBeeMallShoppingCartItem record);int insertSelective(NewBeeMallShoppingCartItem record);NewBeeMallShoppingCartItem selectByPrimaryKey(Long cartItemId);NewBeeMallShoppingCartItem selectByUserIdAndGoodsId(@Param("newBeeMallUserId") Long newBeeMallUserId, @Param("goodsId") Long goodsId);List<NewBeeMallShoppingCartItem> selectByUserId(@Param("newBeeMallUserId") Long newBeeMallUserId, @Param("number") int number);int selectCountByUserId(Long newBeeMallUserId);int updateByPrimaryKeySelective(NewBeeMallShoppingCartItem record);List<NewBeeMallShoppingCartItem> findMyNewBeeMallCartItems (PageQueryUtil pageutil);int getTotalMyNewBeeMallCartItems(PageQueryUtil pageutil);
}

3）Mapper.xml 文件 可由mybatis代码生成器生成,再修改

<?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="ltd.newbee.mall.dao.NewBeeMallShoppingCartItemMapper"><resultMap id="BaseResultMap" type="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem"><id column="cart_item_id" jdbcType="BIGINT" property="cartItemId"/><result column="user_id" jdbcType="BIGINT" property="userId"/><result column="goods_id" jdbcType="BIGINT" property="goodsId"/><result column="goods_count" jdbcType="INTEGER" property="goodsCount"/><result column="is_deleted" jdbcType="TINYINT" property="isDeleted"/><result column="create_time" jdbcType="TIMESTAMP" property="createTime"/><result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/></resultMap><sql id="Base_Column_List">cart_item_id, user_id, goods_id, goods_count, is_deleted, create_time, update_time</sql><select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from tb_newbee_mall_shopping_cart_itemwhere cart_item_id = #{cartItemId,jdbcType=BIGINT} and is_deleted = 0</select><select id="selectByUserIdAndGoodsId" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from tb_newbee_mall_shopping_cart_itemwhere user_id = #{newBeeMallUserId,jdbcType=BIGINT} and goods_id=#{goodsId,jdbcType=BIGINT} and is_deleted = 0limit 1</select><select id="selectByUserId" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from tb_newbee_mall_shopping_cart_itemwhere user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0limit #{number}</select><select id="findMyNewBeeMallCartItms" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from tb_newbee_mall_shopping_cart_itemwhere user_id = #{userId,jdbcType=BIGINT} and is_deleted = 0<if test="start!=null and limit!=null">limit #{start},#{limit}</if></select><select id="getTotalMyNewBeeMallCartItems" resultType="int">selectcount(*)from tb_newbee_mall_shopping_cart_itemwhere user_id = #{userId,jdbcType=BIGINT} and is_deleted = 0</select><select id="selectByUserIdAndCartItemIds" resultType="BaseResultMap">select<include refid="Base_Column_List"/>from tb_newbee_mall_shopping_cart_itemwhere cart_item_id in<foreach item="id" collection="cartItemIds" open="(" separator="," close=")">#{id}</foreach>and user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0</select><select id="selectCountByUserId" resultType="int">selectcount(*)from tb_newbee_mall_shopping_cart_itemwhere user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0</select><update id="deleteByPrimaryKey" parameterType="java.lang.Long">update tb_newbee_mall_shopping_cart_item set is_deleted = 1where cart_item_id = #{cartItemId,jdbcType=BIGINT} and is_deleted = 0</update><insert id="insertSelective" parameterType="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem">insert into tb_newbee_mall_shopping_cart_item<trim prefix="(" suffix=")" suffixOverrides=","><if test="cartItemId != null">cart_item_id,</if><if test="userId != null">user_id,</if><if test="goodsId != null">goods_id,</if><if test="goodsCount != null">goods_count,</if><if test="isDeleted != null">is_deleted,</if><if test="createTime != null">create_time,</if><if test="updateTime != null">update_time,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="cartItemId != null">#{cartItemId,jdbcType=BIGINT},</if><if test="userId != null">#{userId,jdbcType=BIGINT},</if><if test="goodsId != null">#{goodsId,jdbcType=BIGINT},</if><if test="goodsCount != null">#{goodsCount,jdbcType=INTEGER},</if><if test="isDeleted != null">#{isDeleted,jdbcType=TINYINT},</if><if test="createTime != null">#{createTime,jdbcType=TIMESTAMP},</if><if test="updateTime != null">#{updateTime,jdbcType=TIMESTAMP},</if></trim></insert><update id="updateByPrimaryKeySelective" parameterType="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem">update tb_newbee_mall_shopping_cart_item<set><if test="userId != null">user_id = #{userId,jdbcType=BIGINT},</if><if test="goodsId != null">goods_id = #{goodsId,jdbcType=BIGINT},</if><if test="goodsCount != null">goods_count = #{goodsCount,jdbcType=INTEGER},</if><if test="isDeleted != null">is_deleted = #{isDeleted,jdbcType=TINYINT},</if><if test="createTime != null">create_time = #{createTime,jdbcType=TIMESTAMP},</if><if test="updateTime != null">update_time = #{updateTime,jdbcType=TIMESTAMP},</if></set>where cart_item_id = #{cartItemId,jdbcType=BIGINT}</update>
</mapper>

2.mapper.xml文件分段解释

1）字段

<?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="ltd.newbee.mall.dao.NewBeeMallShoppingCartItemMapper">
    <resultMap id="BaseResultMap" type="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem">
        <id column="cart_item_id" jdbcType="BIGINT" property="cartItemId"/>
        <result column="user_id" jdbcType="BIGINT" property="userId"/>
        <result column="goods_id" jdbcType="BIGINT" property="goodsId"/>
        <result column="goods_count" jdbcType="INTEGER" property="goodsCount"/>
        <result column="is_deleted" jdbcType="TINYINT" property="isDeleted"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>

2）sql查询语句

Base_Column_List 为公共字段，包含所有字段，通过  <include refid="Base_Column_List"/> 复用，避免重复书写字段。

<sql id="Base_Column_List">
    cart_item_id, user_id, goods_id, goods_count, is_deleted, create_time, update_time
  </sql>

select 明确查询范围 from 指出查询的表 where 过滤条件,只查询某字段，某状态内容 

limit 自定义要求 1表示只返回一条数据，#{number} 只返回数字（注：用#，不用$）

 
    <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_newbee_mall_shopping_cart_item
        where cart_item_id = #{cartItemId,jdbcType=BIGINT} and is_deleted = 0
    </select>
    <select id="selectByUserIdAndGoodsId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_newbee_mall_shopping_cart_item
        where user_id = #{newBeeMallUserId,jdbcType=BIGINT} and goods_id=#{goodsId,jdbcType=BIGINT} and is_deleted = 0
        limit 1
    </select>
    <select id="selectByUserId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_newbee_mall_shopping_cart_item
        where user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0
        limit #{number}
    </select>
    <select id="findMyNewBeeMallCartItms" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_newbee_mall_shopping_cart_item
        where user_id = #{userId,jdbcType=BIGINT} and is_deleted = 0
        <if test="start!=null and limit!=null">
            limit #{start},#{limit}
        </if>
    </select>
    <select id="getTotalMyNewBeeMallCartItems" resultType="int">
        select
        count(*)
        from tb_newbee_mall_shopping_cart_item
        where user_id = #{userId,jdbcType=BIGINT} and is_deleted = 0
    </select>
    <select id="selectByUserIdAndCartItemIds" resultType="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_newbee_mall_shopping_cart_item
        where
        cart_item_id in
        <foreach item="id" collection="cartItemIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        and user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0
    </select>
    <select id="selectCountByUserId" resultType="int">
        select
        count(*)
        from tb_newbee_mall_shopping_cart_item
        where user_id = #{newBeeMallUserId,jdbcType=BIGINT} and is_deleted = 0
    </select>

3）sql 插入和更新语句

指定对应实体类：ltd.newbee.mall.entity.NewBeeMallShoppingCartItem

指定插入的表：tb_newbee_mall_shopping_cart_item

<trim prefix="(" suffix=")" suffixOverrides=","> 格式设置，自动包裹括号，去掉最后的,

<if test="cartItemId != null"> 非空校验，字段值非空才会包含这列

#{cartItemId,jdbcType=BIGINT} 匹配后，把下划线转换为驼峰式，  cart_item_id -> cartItemId

等效于 INSERT INTO tb_newbee_mall_shopping_cart_item ( cart_item_id,...)VALUES(...);

更新和插入类似，增加了状态的判断条件，符合状态才更新。

<insert id="insertSelective" parameterType="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem">
        insert into tb_newbee_mall_shopping_cart_item
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="cartItemId != null">
                cart_item_id,
            </if>
            <if test="userId != null">
                user_id,
            </if>
            <if test="goodsId != null">
                goods_id,
            </if>
            <if test="goodsCount != null">
                goods_count,
            </if>
            <if test="isDeleted != null">
                is_deleted,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="cartItemId != null">
                #{cartItemId,jdbcType=BIGINT},
            </if>
            <if test="userId != null">
                #{userId,jdbcType=BIGINT},
            </if>
            <if test="goodsId != null">
                #{goodsId,jdbcType=BIGINT},
            </if>
            <if test="goodsCount != null">
                #{goodsCount,jdbcType=INTEGER},
            </if>
            <if test="isDeleted != null">
                #{isDeleted,jdbcType=TINYINT},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>

    <update id="deleteByPrimaryKey" parameterType="java.lang.Long">
    update tb_newbee_mall_shopping_cart_item set is_deleted = 1
    where cart_item_id = #{cartItemId,jdbcType=BIGINT} and is_deleted = 0
   </update>
    <update id="updateByPrimaryKeySelective" parameterType="ltd.newbee.mall.entity.NewBeeMallShoppingCartItem">
        update tb_newbee_mall_shopping_cart_item
        <set>
            <if test="userId != null">
                user_id = #{userId,jdbcType=BIGINT},
            </if>
            <if test="goodsId != null">
                goods_id = #{goodsId,jdbcType=BIGINT},
            </if>
            <if test="goodsCount != null">
                goods_count = #{goodsCount,jdbcType=INTEGER},
            </if>
            <if test="isDeleted != null">
                is_deleted = #{isDeleted,jdbcType=TINYINT},
            </if>
            <if test="createTime != null">
                create_time = #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime,jdbcType=TIMESTAMP},
            </if>
        </set>
        where cart_item_id = #{cartItemId,jdbcType=BIGINT}
    </update>