sql使用总结

    public void addGood(Good good);
    public void updateGood(Good good);
    public void deleteGood(@Param("id") int id);

xml文件

    <insert id="addGood" parameterType="com.lequshe.goods.model.Good">
        insert into goods
        (<include refid="good_column_list"/>)
        values
        (
        #{id}, #{title},#{price},#{stock},#{saleNum},#{createTime},#{categoryName},#{brandName},#{spec}
        )
    </insert>

    <update id="updateGood" parameterType="com.lequshe.goods.model.Good">
        update goods
        <set>
            <if test="title!=null and title!=''">title=#{title},</if>
            <if test="price!=null">price=#{price},</if>
            <if test="stock!=null">stock=#{stock},</if>
            <if test="saleNum!=null">saleNum=#{saleNum},</if>
            <if test="createTime!=null">createTime=#{createTime},</if>
            <if test="categoryName!=null and categoryName!=''">categoryName=#{categoryName},</if>
            <if test="brandName!=null and brandName!=''">brandName=#{brandName}</if>
        </set>
        where id=#{id}
    </update>

    <delete id="deleteGood">
        delete from goods
        where id=#{id}
    </delete>

    public void batchAddGood(@Param("goods") List<Good> goods);
    public void batchUpdateGood(@Param("goods") List<Good> goods);
    public void batchDeleteGood(@Param("ids") List<Integer> ids);

xml文件

    <insert id="batchAddGood" parameterType="java.util.List">
        insert into goods (<include refid="good_column_list" />)
        VALUES
        <foreach collection="goods" index="index" separator="," item="item">
            (
            #{item.id}, #{item.title},#{item.price},#{item.stock},#{item.saleNum},#{item.createTime},
            #{item.categoryName},#{item.brandName},#{item.spec}
            )
        </foreach>
    </insert>

    <update id="batchUpdateGood" parameterType="java.util.List">
        update goods
        <set>
            <foreach collection="goods" open="title=case" close="end," item="item">
                <if test="item.title!=null and item.title!=''">
                when id=#{item.id} then #{item.title}
                </if>
            </foreach>
            <foreach collection="goods" item="item" open="price=case" close="end,">
                <if test="item.price!=null">
                when id=#{item.id} then #{item.price}
                </if>
            </foreach>
        </set>
            where id in (
            <foreach collection="goods" item="item" separator=",">
                #{item.id}
            </foreach>
        )
    </update>

    <delete id="batchDeleteGood" parameterType="java.util.List">
        delete from  goods
        where id in (
        <foreach collection="ids" item="item" separator=",">
            #{item}
        </foreach>
        )
    </delete>

List<Good> queryGoodByCondition(@Param("id") int id, @Param("title") String title);

xml文件

    <select id="queryGoodByCondition" resultMap="GoodInfo">
        select <include refid="good_column_list"/>
        from goods
        <where>
            <if test="id!=null">id=#{id}</if>
            <if test="title!=null and title!=''">
                and title like "%"#{title}"%"
            </if>
        </where>
    </select>

Good queryGoodAndLocal(@Param("id") int id);

xml文件

    <resultMap id="GoodLocal" type="com.lequshe.goods.model.Good" extends="GoodInfo">
        <collection property="local" javaType="java.util.List" ofType="com.lequshe.goods.model.GoodLang">
            <result column="lang" property="lang"/>
            <result column="local_name" property="localName"/>
        </collection>
    </resultMap>

    <select id="queryGoodAndLocal" resultMap="GoodLocal">
      select g1.id,g1.title,g1. price,g1.stock,g1.saleNum ,g1.createTime,g1.categoryName,g1.brandName,g1.spec,
      g2.lang,g2.local_name
      from goods g1
      left join goods_lang g2 on g1.id=g2.good_id
      where g1.id=#{id}
    </select>