ibatis中sql.xml文件配置示例

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Login">
<typeAlias alias="Goods" type="com.globalzt.b2c.domain.administration.Goods"/>
<!-- 修改商品is_delete属性信息 -->
<insert id="insertGoods" parameterClass="Goods" >

INSERT INTO b2c_tbl_goods (goods_id,cat_id,goods_sn,goods_name,goods_name_style,brand_id,market_price,
shop_price,promote_price,promote_start_date,promote_end_date,goods_img,goods_thumb,
add_time,is_new,is_promote,give_integral,goods_desc,last_update,keywords,integral
)
VALUES
(b2c_sp_next_id(),#catId#,#goodsSn#,#goodsName#,"",#brandId#,#marketPrice#,
#shopPrice#,#promotePrice#,#promoteStartDate#,#promoteEndDate#,#goodsImg#,#goodsThumb#,
#addTime#,#isNew#,#isPromote#,#giveIntegral#,"",#lastUpdate#,#keywords#,#integral# ) 
<selectKey resultClass="int" keyProperty="goodsId" >
   SELECT @@IDENTITY AS goodsId
  </selectKey>
</insert>
<!-- 根据商品ID删除商品信息 -->
<delete id="deleteGoodsById" parameterClass="int">
delete from b2c_tbl_goods where goods_id = #goodsId#
</delete>
<!-- 根据商品ID查询商品信息 -->
<select id="getGoodsById" parameterClass="int" resultClass="Goods">
         SELECT
goods_id as goodsId ,
cat_id as catId,
goods_sn as goodsSn,
goods_name as goodsName,
goods_name_style as goodsNameStyle,
click_count as  clickCount,
brand_id as brandId,
provider_name as providerName,
goods_number as goodsNumber,
goods_weight as goodsWeight,
market_price as marketPrice,
shop_price as shopPrice ,
promote_price as promotePrice,
promote_start_date as promoteStartDate,
promote_end_date as promoteEndDate,
warn_number as warnNumber,
keywords as keywords,
goods_brief as goodsBrief,
goods_desc as goodsDesc,
goods_thumb as goodsThumb,
goods_img as goodsImg,
original_img as originalImg,
is_real as isReal,
extension_code as extensionCode,
is_on_sale as isOnSale,
is_alone_sale as isAloneSale,
integral as integral,
add_time as addTime,
sort_order as sortOrder,
is_delete as isDelete,
is_best as isBest,
is_new as isNew,
is_hot as isHot,
is_promote as isPromote,
bonus_type_id as bonusTypeId,
last_update as lastUpdate,
goods_type as goodsType,
seller_note as sellerNote,
give_integral as giveIntegral
         FROM b2c_tbl_goods
         WHERE goods_id = #value#
   </select>
  
   <select id="getGoodsByParameter" parameterClass="java.util.HashMap" resultClass="Goods">
         SELECT
goods_id as goodsId ,
cat_id as catId,
goods_sn as goodsSn,
goods_name as goodsName,
goods_name_style as goodsNameStyle,
click_count as  clickCount,
brand_id as brandId,
provider_name as providerName,
goods_number as goodsNumber,
goods_weight as goodsWeight,
market_price as marketPrice,
shop_price as shopPrice ,
promote_price as promotePrice,
promote_start_date as promoteStartDate,
promote_end_date as promoteEndDate,
warn_number as warnNumber,
keywords as keywords,
goods_brief as goodsBrief,
goods_desc as goodsDesc,
goods_thumb as goodsThumb,
goods_img as goodsImg,
original_img as originalImg,
is_real as isReal,
extension_code as extensionCode,
is_on_sale as isOnSale,
is_alone_sale as isAloneSale,
integral as integral,
add_time as addTime,
sort_order as sortOrder,
is_delete as isDelete,
is_best as isBest,
is_new as isNew,
is_hot as isHot,
is_promote as isPromote,
bonus_type_id as bonusTypeId,
last_update as lastUpdate,
goods_type as goodsType,
seller_note as sellerNote,
give_integral as giveIntegral
         FROM b2c_tbl_goods
         WHERE (#category#=-1 or cat_id=#category#) AND (is_delete = 0)
         and  (#brand#=-1 or brand_id=#brand#)
         and  (#state#!='is_best' or is_best=1)
         and  (#state#!='is_new' or is_new=1)
         and  (#state#!='is_hot' or is_hot=1)
         and  (#state#!='is_promote' or is_promote=1)
         and  (#keywords#='' or (keywords like CONCAT('%', #keywords#, '%')))
      
   </select>

<!-- 修改商品信息 -->
   <update id="updateGoodsById" parameterClass="Goods">
   update b2c_tbl_goods
   set cat_id=#catId#,goods_sn=#goodsSn#,goods_name=#goodsName#,goods_name_style="",brand_id=#brandId#,market_price=#marketPrice#,
shop_price=#shopPrice#,promote_price=#promotePrice#,promote_start_date=#promoteStartDate#,promote_end_date=#promoteEndDate#,
goods_img=#goodsImg#,goods_thumb=#goodsThumb#,
is_promote=#isPromote#,give_integral=#giveIntegral#,last_update=#lastUpdate#,integral=#integral#
where goods_id=#goodsId#

   </update>
   <!-- 修改单个商品is_delete属性信息 -->
   <update id="updateGoodsParamDelete" parameterClass="Goods">
   update b2c_tbl_goods
set is_delete=#isDelete#
where goods_id=#goodsId#
</update>
<!-- 修改多个商品is_delete属性信息为1 -->
<update id="updateMultiGoodsParamDelete" parameterClass="string">
update b2c_tbl_goods set is_delete=1 where goods_id in ($goodsIds$)
</update>

<!-- 商品上架 -->
<update id="updateGoodSsaleTrue" parameterClass="string">
update b2c_tbl_goods set is_on_sale=1 where goods_id in ($goodsIds$)
</update>
<!-- 商品下架 -->
<update id="updateGoodSsaleFalse" parameterClass="string">
update b2c_tbl_goods set is_on_sale=0 where goods_id in ($goodsIds$)
</update>
<!-- 商品精品 -->
<update id="updateGoodBestTrue" parameterClass="string">
update b2c_tbl_goods set is_best=1 where goods_id in ($goodsIds$)
</update>
<!-- 商品取消精品 -->
<update id="updateGoodBestFalse" parameterClass="string">
update b2c_tbl_goods set is_best=0 where goods_id in ($goodsIds$)
</update>
<!-- 商品新品 -->
<update id="updateGoodNewTrue" parameterClass="string">
update b2c_tbl_goods set is_new=1 where goods_id in ($goodsIds$)
</update>
<!-- 商品取消新品 -->
<update id="updateGoodNewFalse" parameterClass="string">
update b2c_tbl_goods set is_new=0 where goods_id in ($goodsIds$)
</update>
<!-- 商品热销 -->
<update id="updateGoodHotTrue" parameterClass="string">
update b2c_tbl_goods set is_hot=1 where goods_id in ($goodsIds$)
</update>
<!-- 商品取消热销 -->
<update id="updateGoodHotFalse" parameterClass="string">
update b2c_tbl_goods set is_hot=0 where goods_id in ($goodsIds$)
</update>
<!-- 商品分类 -->
<update id="updateGoodsCatID" parameterClass="java.util.HashMap">
update b2c_tbl_goods set cat_id=#cat_id# where goods_id in ($goodsIds$)
</update>


</sqlMap>