mybatis sql案例

 <select id="getBrandByCategoryId" parameterType="java.util.Map" resultType="com.if2c.model.Brand">
  select b.* from brand bjoin goods g on(b.id = g.brand_id) where g.category_id in
  <foreach item="categoryId" index="index" collection="categoryIds" open="(" separator="," close=")">
   ${categoryId}
  </foreach>
 </select>

 <select id="getBrandByCategoryIdCountryId" parameterType="java.util.Map" resultType="com.if2c.model.Brand">
  select b.* from brand b join goods g on(b.id = g.brand_id) where
  <if test="countryId!=0 and countryId!=null">b.country_id=#{countryId} and </if>
  g.category_id in
  <foreach item="categoryId" index="index" collection="categoryIds" open="(" separator="," close=")">
   ${categoryId}
  </foreach>
 </select>

<!-- 获取系列数量 -->
 <select id="getSeriesCount" parameterType="java.util.Map" resultType="int">
  select count(1) from goods_series
  <where>
   1=1
   <if test="categoryIds!=null">
    and category_id in
    <foreach item="categoryId" index="index" collection="categoryIds" open="(" separator="," close=")">
       ${categoryId}
    </foreach>
   </if>
   <if test="countryId!=0 and countryId!=null">and country_id=#{countryId}</if>
   <if test="brandId!=0 and brandId!=null">and brand_id=#{brandId}</if>
   <if test="searchKey!=null and searchKey!='%%'">and name like #{searchKey}</if>
  </where>
 </select>

 <select id="getCategoryList" parameterType="com.if2c.model.Category" resultType="com.if2c.model.Category">
  select * from category
  <where>
   <choose>
    <when test="parent_id != 0">
     parent_id = #{parent_id}
    </when>
    <otherwise>
     parent_id IS NULL;
    </otherwise>
   </choose>
  </where>
 </select>

 <select id="getSeriesCategoryList" parameterType="java.util.Map" resultType="java.util.Map">
  select category_id,brand_id from goods_series
  <where>
   1=1
   <if test="categoryIds!=null">
    and category_id in
    <foreach item="categoryId" index="index" collection="categoryIds" open="(" separator="," close=")">
       ${categoryId}
    </foreach>
   </if>
   <if test="countryId!=0 and countryId!=null">and country_id=#{countryId}</if>
   <if test="brandId!=0 and brandId!=null">and brand_id=#{brandId}</if>
   <if test="searchKey!=null and searchKey!='%%'">and name like #{searchKey}</if>
  </where>
   group by category_id,brand_id 
 </select>

 <select id="getSeriesList" parameterType="java.util.Map" resultType="java.util.Map" useCache="true">
  select g.id, g.name, g.base_price as price, g.series_id, a.name as series_name,a.price as series_price,a.default_goods_id as default_goods_id, a.img_num as img_num,g.brand_id from (select id,name,base_price as price,default_goods_id,img_num,sale_num,on_sale_time,comment_num,base_price from goods_series
  <where>
   1=1
   <if test="categoryIds!=null">
    and category_id in
    <foreach item="categoryId" index="index" collection="categoryIds" open="(" separator="," close=")">
       ${categoryId}
    </foreach>
   </if>
   <if test="countryId!=0 and countryId!=null">and country_id=#{countryId}</if>
   <if test="brandId!=0 and brandId!=null">and brand_id=#{brandId}</if>
   <if test="searchKey!=null and searchKey!='%%'">and name like #{searchKey}</if>
  </where>
  <if test="order!='' and order!=null"> order by ${order}, on_sale_time desc</if>
   limit ${beginPos},${length}
  ) a join goods g on (g.series_id=a.id)
  <if test="order!='' and order!=null"> order by a.${order},  a.on_sale_time desc</if>
 </select>

 <select id="getGoodsIdByExtendsAttrValueId" parameterType="java.util.Map" resultType="int">
  select goods_id from (select goods_id, count(1) as countNum from goods_extends_attr_value_relation where extends_attr_value_id in
  <foreach item="valueId" index="index" collection="extendsValueIds" open="(" separator="," close=")">
   ${valueId}
  </foreach>
  ) a where a.countNum=#{paramNum}
 </select>

 <insert id="addGoodsComment" keyColumn="id" parameterType="java.util.Map">
  insert into goods_comment(`user_id`, `series_id`, `goods_id`, `comment`, `order_id`, `reply_to`) values
        (#{user_id}, #{series_id}, ${goods_id}, #{comment}, #{order_id},
        <choose>
         <when test="reply_to==0">null</when>
         <otherwise>#{reply_to}</otherwise>
        </choose>
        )
 </insert>

 <select id="getGoodsCommentByUserId" parameterType="java.util.Map" resultType="java.util.Map">
  select gca.*, gcb.comment as reply_comment, DATE_FORMAT(gcb.publish_time, '%Y-%m-%d %h:%i:%s') as reply_publish_time from goods_comment gca left join goods_comment gcb on(gca.id = gcb.reply_to) where gca.user_id=#{user_id} and gca.reply_to is null order by gca.is_quintessence desc,gca.publish_time desc limit #{bPos},#{length}
  <!-- select * from goods_comment where series_id=#{series_id} order by publish_time desc,is_quintessence desc limit #{bPos},#{length}  -->
 </select>

 <update id="setUserById" parameterType="java.util.Map">
  update user
  <set>
   <if test="nickname!=null and nickname!='' ">nickname=#{nickname},</if>
   <if test="mobile!=null and mobile!='' ">mobile=#{mobile},</if>
   <if test="sex !=null and sex!=-1">sex=#{sex},</if>
   <if test="birthday!=null and birthday!=''">birthday=#{birthday},</if>
   <if test="email!=null and email!='' ">email=#{email},</if>
   <if test="province_id!=null and province_id!=0">province_id=#{province_id},</if>
   <if test="city_id !=null and city_id!=0">city_id=#{city_id}</if>
   <if test="photo!=null and photo!=-1">photo=#{photo}</if>
  </set>
  where id=#{id}
 </update>

 <select id="getInvalidCoupon" resultType="java.util.Map"
  parameterType="long">
  SELECT
  cb.type,cb.name,c.sum,c.total,concat(c.date_from,''),concat(c.date_to,''),
  ca.name
  FROM `category` ca,coupon c, coupon_batch
  cb,`coupon_category_relation` r
  WHERE c.user_id=104 AND
  ca.id=r.category_id AND c.date_to &lt; NOW()
  LIMIT #{start},#{length}
 </select>

 <update id="setChangeInventoryBy">
  update orders o
  join order_goods_relation ogr on(o.id=ogr.order_id)
  join goods_inventory gi on(ogr.goods_id=gi.goods_id)
  <set>
   <if test="status==0"> gi.front_inventory = gi.front_inventory+ogr.goods_num
   </if>   <!-- 取消订单 加前台库存 -->
   <if test="status==3 "> gi.reality_inventory =
    gi.reality_inventory-ogr.goods_num,o.send_order_time=now() </if>  <!-- 商家发货 减实际库存 -->
   <if test="status==2"> gi.virtual_inventory =
    gi.virtual_inventory-ogr.goods_num,o.payment_time=now()</if> <!-- 支付订单 减虚拟库存 -->
  </set>
  where order_id=#{order_id}
 </update>

 
 <select id="getFnishOrder" parameterType="long" resultType="java.util.Map">
  SELECT o.order_num,concat(o.create_time,'')as
  create_time,concat(o.`payment_time`,'')as
  payment_time,concat(send_order_time,'')as send_order_time,
  a.name,b.name,c.name,ua.address,ua.`zipcode`,ua.phone FROM orders o
  JOIN user_address ua ON (o.user_address_id=ua.id)
  JOIN stat a ON
  (ua.province_id=a.id) JOIN stat b ON (ua.city_id=b.id) JOIN stat c ON
  (ua.`area_id`=c.`id`)
  WHERE o.user_id=#{user_id} AND o.status=5
 </select>

 

 <select id="getOrderAddressByTakeDeliveryAndUserId" parameterType="long" resultType="java.util.Map">
  SELECT o.order_num,o.create_time,
  o.`payment_time`,send_order_time,a.name,b.name,c.name,ua.address,
  ua.`zipcode`,ua.phone
  FROM orders o JOIN user_address ua ON
  (o.user_address_id=ua.id) JOIN stat a ON (ua.province_id=a.id) JOIN
  stat b ON (ua.city_id=b.id)
  JOIN stat c ON (ua.`area_id`=c.`id`) WHERE
  o.user_id=#{user_id} AND o.status=3
 </select>

 
  <select id="getValidCoupon" resultType="java.util.Map"
  parameterType="long">
  select cb.`type` , c.`sum` ,c.`total`,
  CONCAT(c.`date_from`,'') as date_from,CONCAT(c.`date_to`,'') as date_to, ca.name
  from coupon_batch cb,coupon c , category ca,`coupon_category_relation` cr
  where c.`batch_id`=cb.`id` AND coupon_batch_id=c.`id` and
  cr.category_id=ca.id AND c.`date_to`>now() and c.`user_id`=#{user_id}
  order by c.`date_from` desc
  limit #{start},#{length}
 </select>

 
 <select id="getCategorySalesByGoodsId"  parameterType="int" resultType="java.util.Map">
  select ogr.goods_id,g.name,g.base_price,sum(ogr.goods_num) as goods_saller_num
  from goods g 
  join goods ga on(g.category_id=ga.category_id)
  join order_goods_relation ogr on (ga.id=ogr.goods_id)
  join orders o on(ogr.order_id=o.id)
  where  o.create_time>=(now()-INTERVAL 30 day) and g.id=#{goods_id} and ga.id!=#{goods_id}  group by ogr.goods_id order by goods_saller_num desc limit 0,#{num}
 </select>

 
 <select id="getGoodsPromotion" parameterType="int" resultType="java.util.Map">
  SELECT pr.name, pr.type, pr.discount, pr.price_down,pr.price_reach, pr.slogan FROM goods_series gs
  left JOIN `promoting_goods` pg ON (gs.`id`=pg.`goods_series_id`)
  left JOIN `promoting_category` pc ON(gs.`category_id`=pc.`category_id`)
  JOIN `promotion_rule` pr ON(pg.`promotion_rule_id`=pr.`id` OR pc.`promotion_rule_id`=pr.`id`)
  WHERE gs.id=#{goodsSeriesId} AND NOW() BETWEEN pr.date_from AND pr.date_to GROUP BY gs.id,pr.id ORDER BY pr.created_time
 </select>

 <insert id="addGoodsComment" keyColumn="id" parameterType="java.util.Map">
  insert into goods_comment(`user_id`, `series_id`, `goods_id`, `comment`, `order_id`, `reply_to`) values
        (#{user_id}, #{series_id}, ${goods_id}, #{comment}, #{order_id},
        <choose>
         <when test="reply_to==0">null</when>
         <otherwise>#{reply_to}</otherwise>
        </choose>
        )
 </insert>

  <select id="getDisposeResultInfo" parameterType="int"  resultType="java.util.Map">
   SELECT id,order_id,goods_id, picture_num, quantity,after_sale_type,content,money_return_mode,goods_return_mode,
    auditing_status,DATE_FORMAT(create_time,'%Y-%m-%d %T') AS create_time,
 DATE_FORMAT(dispose_time,'%Y-%m-%d %T') AS dispose_time,DATE_FORMAT(send_time,'%Y-%m-%d %T') AS send_time
     FROM sale_after_info WHERE id=#{id}

  </select>