mybatis各种Sql语句

向数据库中插入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>