mybatis 记录

1、关于集合循环
2、关于 null 判断，left join 
3、关于同一个属性多种值的时候or的使用（注意加括号）
4、mysql 模糊查询
5、查询片段eg
6、教程：
http://limingnihao.iteye.com/blog/782190
http://www.oschina.net/question/234345_51124
7、sql中不等于使用



 1、关于集合循环
 <select id="getCateAttrByGoodsList" resultMap="catgoryAttributeMap">
		select *
		from rrs_category_attribute 
		where 1=1
		<if test="goodsList.size()!=0">
		and rrs_category_attribute.pid in    
		       <foreach collection="goodsList" item="goods" index="index" open="(" separator="," close=")">
		        	#{goods.categoryId}
     		   </foreach>
        </if> 
        limit 0,#{num}
	</select>


	<select id="getCateAttrByGoodsList" resultMap="catgoryAttributeMap">
		select *
		from rrs_category_attribute 
		where 1=1
		<if test="categoryIdList.size()!=0">
		and rrs_category_attribute.pid in    
		       <foreach collection="categoryIdList" item="item" index="index" open="(" separator="," close=")">
		        	#{item}
     		   </foreach>
        </if> 
        limit 0,#{num}
	</select>

2、关于 null 判断，left join
 <sql id="searchRecord_fragement">
        select rrs_order_item_.*,rrs_order_.pay_time as pay_time,rrs_user_.user_name as user_name
        from rrs_user rrs_user_,rrs_order rrs_order_ left join rrs_order_item rrs_order_item_
        on rrs_order_.order_id=rrs_order_item_.order_id
        where rrs_order_item_.goods_id=#{record.goodsId}
        and rrs_order_item_.user_id=rrs_user_.id
        and rrs_order_.order_status not in ('WAITPAYMENT')
        and pay_time is not NULL
        order by rrs_order_.pay_time desc
    </sql>
 // 判断字符类型 为空pay_time is not NULL 或者 pay_time = ""

3、关于同一个属性多种值的时候or的使用（注意加括号）
// 查询商品按照销量排序
 <select id="gethotGoodsByShopIdAndAreaId" resultMap="goodsMap">
        SELECT  * FROM rrs_goods 
        WHERE 1=1 and is_top=1  
        <if test="areaId != null  and areaId != '' ">
             <![CDATA[and (FIND_IN_SET(#{areaId},region) OR region =0)  ]]>
        </if> 
        <if test="shopId != null  and shopId != '' ">
            and shop_id = #{shopId}
        </if>
        ORDER BY sp_sales DESC LIMIT 0,5;
         
   </select> 


4、mysql 模糊查询
<if test="goods.spName != null  and goods.spName != '' ">
            and (sp_name LIKE CONCAT('%',#{goods.spName},'%' ) ) 
        </if>

5、查询片段eg
<sql id="searchGoods_fragement">
        <include refid="selectAllGoodsColumns" />
        where 1=1  and sp_status =0 and freeze_status = 1
        <if test="goods.region != null  and goods.region != '' ">
        <![CDATA[ and (FIND_IN_SET(#{goods.region},region) OR region =0) ]]> 
        </if>
        <if test="goods.shopId != null  and goods.shopId != '' ">
            and shop_id = #{goods.shopId}
        </if>
        <if test="goods.brandId != null  and goods.brandId != '' ">
            and brand_id = #{goods.brandId}
        </if>
        <if test="goods.categoryId != null  and goods.categoryId != ''  ">
            and category_id = #{goods.categoryId}
        </if>
       <if test="param1.size()!=0">
       and category_id in
       <foreach collection="param1" item="item" index="index" open="(" separator="," close=")">
        #{item}
         </foreach>
        </if> 
        <if test="goods.spName != null  and goods.spName != '' ">
            and (sp_name LIKE CONCAT('%',#{goods.spName},'%' ) ) 
        </if>
        <if test="goods.spPrice != null  and goods.spPrice != '' ">
            and sp_price = #{goods.spPrice}
        </if>
        <if test="goods.spCost != null  and goods.spCost != '' ">
            and sp_cost = #{goods.spCost}
        </if>
        <if test="goods.favourite != null  and goods.favourite != '' ">
            and favourite = #{goods.favourite}
        </if>
        <if test="goods.introduction != null  and goods.introduction != '' ">
            and introduction = #{goods.introduction}
        </if>
        <if test="goods.spStatus != null  and goods.spStatus != '' ">
            and sp_status = #{goods.spStatus}
        </if>
        <if test="goods.freezeStatus != null  and goods.freezeStatus != '' ">
            and freeze_status = #{goods.freezeStatus}
        </if>
        <if test="goods.isTop != null  and goods.isTop != '' ">
            and is_top = #{goods.isTop}
        </if>
        <if test="goods.spkeyword != null  and goods.spkeyword != '' ">
            and sp_keyword = #{goods.spkeyword}
        </if>
        <if test="goods.spMemo != null  and goods.spMemo != '' ">
            and sp_memo = #{goods.spMemo}
        </if>
        <if test="goods.spSales != null  and goods.spSales != '' ">
            and sp_sales = #{goods.spSales}
        </if>
        
        <if test="goods.createDate != null  and goods.createDate != '' ">
            and create_date = #{goods.createDate}
        </if>
         <if test="goods.modifyDate != null  and goods.modifyDate != '' ">
            and modify_date = #{goods.modifyDate}
        </if> 
        <if test="goods.minprice != null  and goods.minprice != '' ">
         <![CDATA[ and sp_price >= #{goods.minprice} ]]>  
        </if>
        <if test="goods.maxprice != null  and goods.maxprice != '' ">
        <![CDATA[ and sp_price <= #{goods.maxprice} ]]> 
        </if>
        <include refid="selectAllGoodsstatus" />
        <include refid="selectAllGoodsSpStatus" />
    </sql>

7、sql中不等于使用
x <> 0  不等于 

8、获取插入记录的自增长字段值
在Mybatis Mapper文件中添加属性“useGeneratedKeys”和“keyProperty”，其中keyProperty是Java对象的属性名！
<insert id="insert" parameterType="Spares" 
        useGeneratedKeys="true" keyProperty="id">
        insert into spares(spares_id,spares_name,
            spares_type_id,spares_spec)
        values(#{id},#{name},#{typeId},#{spec})
    </insert>