向数据库中插入list集合
<insert id="batchInsertProductCategory" parameterType="java.util.List">
INSERT INTO
tb_product_category(product_category_name,priority,create_time,shop_id)
VALUES
<foreach collection="list" item="productCategory" index="index" separator=",">
(
#{productCategory.productCategoryName},
#{productCategory.priority},
#{productCategory.createTime},
#{productCategory.shopId}
)
</foreach>
</insert>
条件查询
注意分页查询的rowIndex 是从0开始算的
<!-- 根据条件分页查询店铺列表 -->
<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.create_time,
s.last_edit_time,
s.enable_status,
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.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>
条件更新
<update id="updateShop" parameterType="cn.zgd.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>
复合类的定义
<association>标签是在一对一对应关系时使用 包装类使用 javaType
<collection>标签是一对多对应关系时使用 ofType
<collection property="productImgList" column="product_id" ofType="cn.zgd.o2o.entity.ProductImg">
<id column="product_img_id" property="productImgId"/>
<result column="img_addr" 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>
<!-- 定义返回的shop格式 -->
<resultMap id="shopMap" type="cn.zgd.o2o.entity.Shop">
<id column="shop_id" property="shopId"/>
<result column="shop_name" property="shopName"/>
<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"/>
<association property="area" column="area_id" javaType="cn.zgd.o2o.entity.Area">
<id column="area_id" property="areaId"/>
<result column="area_name" property="areaName"/>
</association>
<association property="shopCategory" column="shop_category_id" javaType="cn.zgd.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="cn.zgd.o2o.entity.ShopCategory">
<id column="user_id" property="userId"/>
<result column="name" property="name"/>
</association>
</resultMap>
类获取插入数据库后自增长的id值
<!-- 获取插入数据库后自增长的ID的值 保存到店铺对象中 -->
<insert id="insertShop" useGeneratedKeys="true" keyColumn="shop_id" keyProperty="shopId"
parameterType="cn.zgd.o2o.entity.Shop">
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>
左外连接和右外连接
二者的共同点是都返回符合连接条件和查询条件(即:内连接)的数据行。不同点如下:
左外连接还返回左表中不符合连接条件但是符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
聚合函数
注意count与()之间是不能有空格的 否则就会报错
<select id="queryProductCount" resultType="java.lang.Integer">
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>