mybatis的sql的xml的配置文件中<![CDATA[ ]]>的用法。

具体见如下代码：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzcominfo.voucher.Voucher">
<cache-ref namespace="com.hzcominfo.dataggr.cloud" />


<insert id="insertVoucher" parameterType="com.hzcominfo.voucher.mapper.Voucher"
keyProperty="id">
INSERT INTO VOUCHER (
<include refid="fields" />
) VALUES (
<include refid="values" />
)
</insert>


<update id="updateVoucher" parameterType="com.hzcominfo.voucher.mapper.Voucher">
UPDATE VOUCHER
<include refid="set" />
WHERE id = #{id}
</update>


<update id="updateVoucherByCriteria" parameterType="net.butfly.albacore.dbo.criteria.Criteria">
UPDATE VOUCHER
<include refid="set" />
<include refid="where" />
</update>
<update id="updateList" parameterType="com.hzcominfo.voucher.mapper.Voucher">
UPDATE VOUCHER
<include refid="set" />
where promotion_id=#{promotionId}
</update>
<update id="deleteVoucher" parameterType="String">
DELETE FROM VOUCHER
WHERE id = #{id}
</update>


<update id="deleteVoucherByContentCriteria" parameterType="net.butfly.albacore.dbo.criteria.Criteria">
DELETE FROM VOUCHER
<include refid="where" />
</update>


<select id="selectVoucher" parameterType="String"
resultType="com.hzcominfo.voucher.mapper.Voucher">
SELECT * FROM VOUCHER where id = #{id}
</select>


<select id="selectVoucherByCriteria" parameterType="net.butfly.albacore.dbo.criteria.Criteria"
resultType="String">
SELECT id FROM VOUCHER
<include refid="where" />
</select>
<select id="countVoucherByCriteria" parameterType="net.butfly.albacore.dbo.criteria.Criteria"
resultType="long">
SELECT count(id) FROM VOUCHER
<include refid="where" />
</select>


<select id="selectVoucherIssue" parameterType="com.hzcominfo.voucher.mapper.VoucherIssue"
resultType="com.hzcominfo.voucher.mapper.VoucherIssue">
SELECT  P.ID PROMOTIONID,P.NAME,P.DESCRIPTION, 
        F.ID PROMOTIONFACTORID,F.CATEGORY_ID CATEGORYID,F.COMMODITY_ID COMMODITYID,F.MONEY,F.AMOUNT,
        F.STORE_ZOME_LEVEL STOREZOMELEVEL,F.ISSUE_TOTAIL_NUM ISSUETOTAILNUM,F.ISSUE_NUM ISSUENUM ,
        U.ID USEFACTORID,U.COMMODITY_ID USECOMMODITYID,U.TYPE,U.MONEY USEMONEY,U.AMOUNT USEAMOUNT,U.DISCOUNT_MONEY DISCOUNTMONEY,
        U.DISCOUNT_RATE DISCOUNTRATE,U.DISCOUNT_NUM DISCOUNTNUM,U.TARGET_ID TARGETID, U.STORE_ZOME_LEVEL USESTOREZOMELEVEL, 
        U.STORE_ZOME_CODE USESTOREZOMECODE,U.EFFECTIVE_DAYS EFFECTIVEDAYS
FROM PROMOTION P, PROMOTION_FACTOR F, USE_FACTOR U
WHERE P.ID=F.PROMOTION_ID AND U.ID=F.USE_FACTOR_ID AND F.ISSUING_FLAG='1'
AND F.ISSUE_TOTAIL_NUM>F.ISSUE_NUM AND P.DELETED = '0' AND F.DELETED='0' AND U.DELETED='0'
<if test="money!=null and money!=0">
<![CDATA[AND ${money}>=F.MONEY]]>
</if>
<if test="storeZomeLevel!=null and storeZomeLevel!=''">
<![CDATA[AND F.STORE_ZOME_LEVEL like '%${storeZomeLevel}%']]>
</if>
<if test="time!=null and time!=''">
<![CDATA[AND to_char(p.START_TIME,'yyyy-MM-dd hh24:mi:ss') <= '${time}']]>
<![CDATA[AND to_char(p.END_TIME,'yyyy-MM-dd hh24:mi:ss') >= '${time}']]>
</if>
<if test="isCash!=null and isCash!=''">
<![CDATA[AND F.COMMODITY_ID IS  NULL]]>
</if>
<if test="commodityId!=null and commodityId!=''">
<![CDATA[AND F.COMMODITY_ID = '${commodityId}']]>
</if>
  <if test="promotionFactorIds!=null and promotionFactorIds.length > 0">
AND F.ID in  
  <foreach item="temp" index="index" collection="promotionFactorIds" open="(" separator="," close=")">#{temp}</foreach> 
</if>
</select>
<select id="groupByUseID" parameterType="com.hzcominfo.voucher.facade.dto.CountVoucherResponse"
resultType="com.hzcominfo.voucher.facade.dto.CountVoucherResponse">
SELECT COUNT(V.ID) NUM,V.USE_ID USEID ,V.FACTOR_ID PROMOTIONFACTORID,MIN(V.START_TIME),MAX(V.END_TIME) FROM VOUCHER V 
WHERE V.DELETED='0' AND V.STATUS='1'
<if test="customerId!=null and customerId!=''">
<![CDATA[AND v.customer_id = '${customerId}']]>
</if>   
<if test="time!=null and time!=''">
<![CDATA[AND to_char(v.START_TIME,'yyyy-MM-dd hh24:mi:ss') <= '${time}']]>
<![CDATA[AND to_char(v.END_TIME,'yyyy-MM-dd hh24:mi:ss') >= '${time}']]>
</if>
GROUP BY V.USE_ID,V.FACTOR_ID
</select>
<select id="queryInfoByVoucherId" parameterType="com.hzcominfo.voucher.facade.dto.CountVoucherResponse"
resultType="com.hzcominfo.voucher.facade.dto.CountVoucherResponse">
SELECT V.ID VOUCHERID, V.CUSTOMER_ID CUSTOMERID, V.BAR_CODE BARCODE, V.START_TIME STARTTIME, V.END_TIME ENDTIME,
      U.COMMODITY_ID COMMODITYID, U.MONEY MONEY, U.AMOUNT AMOUNT, U.DISCOUNT_MONEY DISCOUNTMONEY, 
      U.DISCOUNT_NUM DISCOUNTNUM,U.TARGET_ID TARGETID,U.STORE_ZOME_LEVEL STOREZOMELEVEL,
        P.NAME PROMOTIONNAME, P.ID PROMTIONID, F.CATEGORY_ID CATEGORYID, SP.NAME TYPENAME
  FROM VOUCHER V, USE_FACTOR U, PROMOTION P, PROMOTION_FACTOR F, SUBDEV.PRMTOPIC SP
  WHERE V.USE_ID = U.ID AND V.FACTOR_ID = F.ID AND F.PROMOTION_ID = P.ID AND F.CATEGORY_ID = SP.CODE AND V.STATUS = '1'
    AND V.DELETED = '0' AND U.DELETED = '0' AND P.DELETED = '0' AND F.DELETED = '0'
    <if test="voucherId!=null and voucherId!=''">
<![CDATA[AND v.id = '${voucherId}']]>
</if>
    <if test="customerId!=null and customerId!=''">
<![CDATA[AND v.customer_id = '${customerId}']]>
</if>  
<if test="useId!=null and useId!=''">
<![CDATA[AND v.use_id = '${useId}']]>
</if> 
<if test="promotionFactorId!=null and promotionFactorId!=''">
<![CDATA[AND v.factor_id = '${promotionFactorId}']]>
</if>
<if test="time!=null and time!=''">
<![CDATA[AND to_char(v.START_TIME,'yyyy-MM-dd hh24:mi:ss') <= '${time}']]>
<![CDATA[AND to_char(v.END_TIME,'yyyy-MM-dd hh24:mi:ss') >= '${time}']]>
</if>
</select>
<select id="queryWeixinNotice" parameterType="com.hzcominfo.voucher.mapper.Voucher"
resultType="com.hzcominfo.voucher.mapper.Voucher">
SELECT V.*, C.NAME AS categoryName, F.PROMOTION_ID
FROM VOUCHER V,
    PROMOTION_FACTOR F,
    PROMOTION_CATEGORY C
WHERE V.FACTOR_ID = F.ID
AND F.CATEGORY_ID = C.ID
AND V.DELETED = '0'
AND F.DELETED = '0'
AND C.DELETED = '0'
    <if test="promotionId!=null and promotionId!=''">
<![CDATA[AND V.PROMOTION_ID = '${promotionId}']]>
</if>
    <if test="customerId!=null and customerId!=''">
<![CDATA[AND V.CUSTOMER_ID = '${customerId}']]>
</if>  
<if test="useId!=null and useId!=''">
<![CDATA[AND V.USE_ID = '${useId}']]>
</if> 
<if test="factorId!=null and factorId!=''">
<![CDATA[AND V.FACTOR_ID = '${factorId}']]>
</if>
<if test="infoType!=null and infoType==1">
<![CDATA[AND V.STATUS = '0']]>
</if>
<if test="infoType!=null and infoType==2">
<![CDATA[AND V.STATUS = '1'
AND TO_CHAR(V.END_TIME-3, 'YYYY-MM-DD') <= '${time}'
AND '${time}'<= TO_CHAR(V.END_TIME, 'YYYY-MM-DD')]]>
</if>
<if test="infoType!=null and infoType==3">
<![CDATA[AND V.STATUS = '1'
AND TO_CHAR(V.END_TIME, 'YYYY-MM-DD') < '${time}']]>
</if>
<if test="infoType!=null and infoType==4">
<![CDATA[AND V.STATUS = '2']]>
</if>
</select>
<select id="checkVoucher" parameterType="String"
resultType="com.hzcominfo.voucher.mapper.Voucher">
SELECT * FROM VOUCHER WHERE BAR_CODE = #{barCode}
</select>
<select id="useVoucher" parameterType="String"
resultType="com.hzcominfo.voucher.facade.dto.UseVoucher">
SELECT V.ID VOUCHERID, V.BAR_CODE BARCODE,V.FACTOR_ID FACTORID,U.DISCOUNT_RATE DISCOUNTRATE,U.DISCOUNT_MONEY DISCOUNTMONEY,
U.DISCOUNT_NUM DISCOUNTNUM,U.TARGET_ID,F.CATEGORY_ID FAVTYPE 
  FROM VOUCHER V, USE_FACTOR U, PROMOTION_FACTOR F
  WHERE V.USE_ID = V.ID AND V.FACTOR_ID = F.ID AND V.DELETED = '0' AND V.DELETED = '0' AND V.ID = #{id}
</select>
<sql id="fields">
id
<if test="barCode!=null">,BAR_CODE</if>
<if test="voucherName!=null">,VOUCHER_NAME</if>
<if test="promotionId!=null">,promotion_id</if>
<if test="status!=null">,status</if>
<if test="customerId!=null">,customer_id</if>
<if test="deleted!=null">,deleted</if>
<if test="addTime!=null">,add_time</if>
<if test="updateTime!=null">,update_time</if>
<if test="startTime!=null">,start_time</if>
<if test="endTime!=null">,end_time</if>
<if test="factorId!=null">,factor_id</if>
<if test="useId!=null">,use_id</if>
</sql>


<sql id="values">
#{id}
<if test="barCode!=null">,#{barCode}</if>
<if test="voucherName!=null">,#{voucherName}</if>
<if test="promotionId!=null">,#{promotionId}</if>
<if test="status!=null">,#{status}</if>
<if test="customerId!=null">,#{customerId}</if>
<if test="deleted!=null">,#{deleted}</if>
<if test="addTime!=null">,#{addTime}</if>
<if test="updateTime!=null">,#{updateTime}</if>
<if test="startTime!=null">,#{startTime}</if>
<if test="endTime!=null">,#{endTime}</if>
<if test="factorId!=null">,#{factorId}</if>
<if test="useId!=null">,#{useId}</if>

</sql>


<sql id="set">
<set>
<trim prefix="" prefixOverrides=",">
<if test="barCode!=null">,BAR_CODE=#{barCode}</if>
<if test="voucherName!=null">,VOUCHER_NAME=#{voucherName}</if>
<if test="promotionId!=null">,promotion_id=#{promotionId}</if>
<if test="status!=null">,status=#{status}</if>
<if test="customerId!=null">,customer_id=#{customerId}</if>
<if test="deleted!=null">,deleted=#{deleted}</if>
<if test="addTime!=null">,add_time=#{addTime}</if>
<if test="updateTime!=null">,update_time=#{updateTime}</if>
<if test="startTime!=null">,start_time=#{startTime}</if>
<if test="endTime!=null">,end_time=#{endTime}</if>
<if test="factorId!=null">,factor_id=#{factorId}</if>
<if test="useId!=null">,use_id=#{useId}</if>
</trim>
</set>
</sql>
<sql id="where">
<where>
DELETED = '0'
<if test="barCode!=null">AND BAR_CODE=#{barCode}</if>
<if test="voucherName!=null">AND VOUCHER_NAME=#{voucherName}</if>
<if test="id!=null">AND id=#{id}</if>
<if test="promotionId!=null">AND promotion_id=#{promotionId}</if>
<if test="status!=null">AND status=#{status}</if>
<if test="customerId!=null">AND customer_id=#{customerId}</if>
<if test="addTime!=null">AND add_time=#{addTime}</if>
<if test="updateTime!=null">AND update_time=#{updateTime}</if>
<if test="startTime!=null"><![CDATA[AND start_time <= #{startTime}]]></if>
<if test="endTime!=null"><![CDATA[AND end_time >= #{endTime}]]></if>
<if test="factorId!=null">AND factor_id=#{factorId}</if>
<if test="useId!=null">AND use_id=#{useId}</if>
</where>
</sql>


</mapper>

注意：在<![CDATA[  ]]>的空白处添加需要处理的内容；

to_char(p.START_TIME,'yyyy-MM-dd hh24:mi:ss')

此处为oracle的to_char()函数，TO_CHAR 是把日期或数字转换为字符串。