ProductDao.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">
<mapper namespace="com.imooc.o2o.dao.ProductDao">
        <resultMap id="productMap" type="com.imooc.o2o.entity.Product">
            <id column="product_id" property="productId" />
            <result column="product_name" property="productName" />
            <result column="product_desc" property="productDesc" />
            <result column="img_addr" property="imgAddr" />
            <result column="normal_price" property="normalPrice" />
            <result column="promotion_price" property="promotionPrice" />
            <result column="priority" property="priority" />
            <result column="create_time" property="createTime" />
            <result column="last_edit_time" property="lastEditTime" />
            <result column="enable_status" property="enableStatus" />
            <association property="productCategory" column="product_category_id"
                javaType="com.imooc.o2o.entity.ProductCategory">
                <id column="product_category_id" property="productCategoryId" />
                <result column="product_category_name" property="productCategoryName" />
            </association>
            <association property="shop" column="shop_id"
                javaType="com.imooc.o2o.entity.Shop">
                <id column="shop_id" property="shopId" />
                <result column="owner_id" property="ownerId" />
                <result column="shop_name" property="shopName" />
            </association>
            <collection property="productImgList" column="product_id"
                ofType="com.imooc.o2o.entity.ProductImg">
                <id column="product_img_id" property="productImgId" />
                <result column="detail_img" property="imgAddr" />
                <result column="img_desc" property="imgDesc" />
                <result column="priority" property="priority" />
                <result column="create_time" property="createTime" />
                <result column="product_id" property="productId" />
            </collection>
        </resultMap>
    <select id="queryProductList" resultType="com.imooc.o2o.entity.Product">
        SELECT
        product_id,
        product_name,
        product_desc,
        img_addr,
        normal_price,
        promotion_price,
        priority,
        create_time,
        last_edit_time,
        enable_status,
        product_category_id,
        shop_id
        FROM
        tb_product
        <where>
            <if
                test="productCondition.shop!=null
                 and productCondition.shop.shopId!=null">
                and shop_id = #{productCondition.shop.shopId}
            </if>
            <if
                test="productCondition.productCategory!=null
                 and productCondition.productCategory.productCategoryId!=null">
                and product_category_id =
                #{productCondition.productCategory.productCategoryId}
            </if>
            <!-- 写like语句的时候 一般都会写成 like '% %' 在mybatis里面写就是应该是 like '%${name} %' 而不是 
                '%#{name} %' ${name} 是不带单引号的，而#{name} 是带单引号的 -->
            <if test="productCondition.productName!=null">
                and product_name like '%${productCondition.productName}%'
            </if>
            <if test="productCondition.enableStatus!=null">
                and enable_status = #{productCondition.enableStatus}
            </if>
        </where>
        ORDER BY
        priority DESC
        LIMIT #{rowIndex},#{pageSize};
    </select>

    <select id="queryProductCount" resultType="int">
        SELECT count(1) FROM tb_product
        <where>
            <if
                test="productCondition.shop!=null
                 and productCondition.shop.shopId!=null">
                and shop_id = #{productCondition.shop.shopId}
            </if>
            <if
                test="productCondition.productCategory!=null
                 and productCondition.productCategory.productCategoryId!=null">
                and product_category_id =
                #{productCondition.productCategory.productCategoryId}
            </if>
            <if test="productCondition.productName!=null">
                and product_name like '%${productCondition.productName}%'
            </if>
            <if test="productCondition.enableStatus!=null">
                and enable_status = #{productCondition.enableStatus}
            </if>
        </where>
    </select>
    <select id="queryProductById" resultMap="productMap"
        parameterType="Long">
        <!-- 具体的sql -->
        SELECT
        p.product_id,
        p.product_name,
        p.product_desc,
        p.img_addr,
        p.normal_price,
        p.promotion_price,
        p.priority,
        p.create_time,
        p.last_edit_time,
        p.enable_status,
        p.product_category_id,
        p.shop_id,
        pm.product_img_id,
        pm.img_addr AS detail_img,
        pm.img_desc,
        pm.priority,
        pm.create_time
        FROM
        tb_product p
        LEFT JOIN
        tb_product_img pm
        ON
        p.product_id =
        pm.product_id
        WHERE
        p.product_id =
        #{productId}
        ORDER BY
        pm.priority DESC
    </select>
    <insert id="insertProduct" parameterType="com.imooc.o2o.entity.Product"
        useGeneratedKeys="true" keyProperty="productId" keyColumn="product_id">
        INSERT INTO
        tb_product(product_name,product_desc,img_addr,
        normal_price,promotion_price,priority,create_time,
        last_edit_time,enable_status,product_category_id,
        shop_id)
        VALUES
        (#{productName},#{productDesc},#{imgAddr},
        #{normalPrice},#{promotionPrice},#{priority},#{createTime},
        #{lastEditTime},#{enableStatus},#{productCategory.productCategoryId},
        #{shop.shopId})
    </insert>
    <update id="updateProduct" parameterType="com.imooc.o2o.entity.Product"
        keyProperty="product_id" useGeneratedKeys="true">
        UPDATE tb_product
        <set>
            <if test="productName != null">product_name=#{productName},</if>
            <if test="productDesc != null">product_desc=#{productDesc},</if>
            <if test="imgAddr != null">img_addr=#{imgAddr},</if>
            <if test="normalPrice != null">normal_price=#{normalPrice},</if>
            <if test="promotionPrice != null">promotion_price=#{promotionPrice},</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="productCategory != null
                 and productCategory.productCategoryId != null">
                product_category_id=#{productCategory.productCategoryId}
            </if>
        </set>
        WHERE product_id = #{productId}
        AND shop_id=#{shop.shopId}
    </update>
    <update id="updateProductCategoryToNull" parameterType="Long">
        UPDATE
        tb_product
        SET
        product_category_id = null
        WHERE product_category_id =
        #{productCategoryId}
    </update>
    <delete id="deleteProduct">
        DELETE FROM
        tb_product
        WHERE
        product_id = #{productId}
        AND shop_id=#{shopId}
    </delete>
</mapper>