MyBatis常用SQL语句整理笔记

<select id="productId" parameterType="Product" resultMap="getProduct">
    SELECT * FROM PRODUCT t WHERE (1=1)
    <if test="Product.productId!=null">
        AND t.PRODUCT_ID = #{Product.productId}
    </if>
    <if test="Product.productName!=null">
        AND t.PRODUCT_NAME like ‘%#{Product.productName}%‘
    </if>
    <if test="Product.inStock!=null">
        AND t.PRODUCT.INSTOCK = 0
    </if>
    ORDER BY SEQUENCE_NO
</select>

<insert id="addCategory" parameterType="Category">
    INSERT INTO CATEGORY(
    <if test="categoryName!=null and categoryName!=‘‘ ">
        CATEGORY_NAME
    </if>
    <if test="categoryId!=null and categoryId!=‘‘ ">
        CATEGORY_ID
    </if>
    ADD_TIME)
    VALUES(
        <if test="categoryName!=null and categoryName!=‘‘ ">
            #{categoryName, jdbcType=VARCHAR}
        </if>
        <if test="categoryId!=null and categoryId!=‘‘ ">
            #{categoryId, jdbcType=NUMERIC}
        </if>
        currentTimeStamp
    )
</insert>

<update id="updateCategory" parameterType="Category">
    update CATEGORY t SET
    <if test="categoryName!=null">
        t.CATEGORY_NAME = #{categoryName, jdbcType=VARCHAR},
    </if>
    <if test="updateUser!=null">
        t.UPDATE_USER = #{updateUser, jdbcType=VARCHAR},
    </if>
    t.UPDATE_TIME = currentTimeStamp
    WHERE t.CATEGORY_ID = #{categoryId, jdbcType=NUMERIC}
</update>

<select id="findInStockLike" resultType="Product">
    SELECT * FROM PRODUCT WHERE STATE = ‘INSTOCK‘
    <choose>
        <when>
            AND TITLE LIKE #{title}
        </when>
        <when>
            AND BRAND_NAME LIKE #{brand.name}
        </when>
        <otherwise>
            AND INMARKETING = 0
        </otherwise>
    </choose>
</select>

<select id="findInStockProductLike" resultType="Product">
    SELECT * FROM PRODUCT
    <when>
        <if    test="state!=null">
            STATE = #{state}
        </if>
        <if test="title!=null">
            AND TITLE LIKE #{title}
        </if>
        <if test="brand!=null and brand.name!=null">
            AND BRAND_NAME LIKE #{brand.name}
        </if>
    </when>
</select>

<update id="updateUserInfo">
    UPDATE USER
    <set>
        <if test="userName!=null">USER_NAME = #{userName}</if>
        <if test="userPsw!=null">USER_PSW = #{userPsw}</if>
        <if test="userEmail!=null">USER_EMAIL = #{userEmail}</if>
    </set>
    WHERE USER_ID = #{userId}
</update>

<select id="selectProdcutNoInOrder" resultType="String">
    SELECT COUNT(0) FROM PRODUCT a LEFT JOIN ORDER_INFO b
    ON a.PRODUCT_ID = b.PRODUCT_ID
    WHERE a.STATUS in(‘1‘, ‘2‘, ‘3‘, ‘6‘)
    <if test="list.size()>0">
        AND b.PHONE_NUM IN
        <foreach item="phoneNumList" collection="list" open="(" separator="," close=")">
            #{phoneNumList.num}
        </foreach>
    </if>
</select>

<select id="selectProductIn" resultType="com.dowik.dowikmall.Product">
    SELECT * FROM PRODUCT p WHERE PRODUCT_ID IN
    <foreach item="productIdList" index="index" collection="list" open="(" separetor="," close=")">
        #{productIdList}
    </foreach>
</select>

MyBatis常用SQL语句整理笔记

原文：http://my.oschina.net/lock0818/blog/506206