记录两张数据库表及Ibatis操作

建表语句

CREATE TABLE `TS_MopayInvoiceComposition` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RequestID` int(11) NOT NULL COMMENT '开票ID',
  `CustomerID` int(11) NOT NULL COMMENT '客户ID',
  `ShopID` int(11) NOT NULL COMMENT '门店ID',
  `InvoiceAmount` decimal(10,2) NOT NULL COMMENT '开票金额',
  `Status` tinyint(4) NOT NULL COMMENT '状态，1:初始,3:开票中,4:提交失败,5:开票成功，6:冲销成功，7:作废 8:撤销，9 驳回',
  `AddTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `Memo` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_RequestID_ShopID` (`RequestID`,`ShopID`),
  KEY `IX_CustomerID_ShopID` (`CustomerID`,`ShopID`),
  KEY `IX_ShopID` (`ShopID`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COMMENT='闪惠开票申请金额组成';

CREATE TABLE `TS_TGInvoiceComposition` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RequestID` int(11) NOT NULL COMMENT '开票ID',
  `CustomerID` int(11) NOT NULL COMMENT '客户ID',
  `DealGroupID` int(11) NOT NULL COMMENT '团购ID',
  `InvoiceAmount` decimal(10,2) NOT NULL COMMENT '开票金额',
  `Status` tinyint(4) NOT NULL COMMENT '状态，1:初始,3:开票中,4:提交失败,5:开票成功，6:冲销成功，7:作废 8:撤销，9 驳回',
  `AddTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `Memo` varchar(255) DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_RequestID_DealGroupID` (`RequestID`,`DealGroupID`),
  KEY `IX_CustomerID_DealGroupID` (`CustomerID`,`DealGroupID`),
  KEY `IX_DealGroupID` (`DealGroupID`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='团购开票申请金额组成';

ibatis的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="MopayActivityVoucher">
    <typeAlias alias="mopayActivityVoucherData" type="com.ts.mopay.settle.biz.data.MopayActivityVoucherData"/>
    <resultMap id="mopayActivityVoucherData" class="mopayActivityVoucherData">
        <result column="ID" property="id"/>
        <result column="OrderID" property="orderId"/>
        <result column="ShopID" property="shopId"/>
        <result column="SerialNumber" property="serialNumber"/>
        <result column="OrderAddTime" property="orderAddTime"/>
        <result column="OrderUpdateTime" property="orderUpdateTime"/>
        <result column="OrderStatus" property="orderStatus"/>
        <result column="TradeType" property="tradeType"/>
        <result column="SolutionID" property="solutionId"/>
        <result column="SchemeID" property="schemeId"/>
        <result column="UniCashierOrderID" property="uniCashierOrderId"/>
        <result column="OriginAmount" property="originAmount"/>
        <result column="DiscountAmount" property="discountAmount"/>
        <result column="ActivityAmount" property="activityAmount"/>
        <result column="USerPayAmount" property="userPayAmount"/>
        <result column="NoDiscountAmount" property="noDiscountAmount"/>
        <result column="CouponOfferMessagesJsonStr" property="couponOfferMessagesJsonStr"/>
        <result column="Memo" property="memo"/>
        <result column="SettleStatus" property="settleStatus"/>
        <result column="SnapshotID" property="snapshotId"/>
        <result column="IsOld" property="isOld"/>
        <result column="AddTime" property="addTime"/>
        <result column="UpdateTime" property="updateTime"/>
    </resultMap>

    <sql id="sql_select">
       SELECT
        ID,
        OrderID,
        ShopID,
        SerialNumber,
        SchemeID,
        OrderAddTime,
        OrderUpdateTime,
        OrderStatus,
        TradeType,
        SolutionID,
        SnapshotID,
        UniCashierOrderID,
        OriginAmount,
        DiscountAmount,
        ActivityAmount,
        USerPayAmount,
        NoDiscountAmount,
        CouponOfferMessagesJsonStr,
        SettleStatus,
        IsOld,
        AddTime,
        UpdateTime,
        Memo
        from TS_MopayActivityVoucher
    </sql>

    <insert id="insertMopayActivityVoucherData">
        INSERT INTO TS_MopayActivityVoucher
        (OrderID,ShopID,SerialNumber,SchemeID,OrderAddTime,OrderUpdateTime,OrderStatus,TradeType,SolutionID,SnapshotID,UniCashierOrderID,
        OriginAmount,DiscountAmount,ActivityAmount,USerPayAmount,NoDiscountAmount,
        CouponOfferMessagesJsonStr,SettleStatus,IsOld,AddTime,UpdateTime,Memo)
        VALUES (
         #mopayActivityVoucherData.orderId#,
         #mopayActivityVoucherData.shopId#,
         #mopayActivityVoucherData.serialNumber#,
         #mopayActivityVoucherData.schemeId#,
         #mopayActivityVoucherData.orderAddTime#,
         #mopayActivityVoucherData.orderUpdateTime#,
         #mopayActivityVoucherData.orderStatus#,
         #mopayActivityVoucherData.tradeType#,
         #mopayActivityVoucherData.solutionId#,
         #mopayActivityVoucherData.snapshotId#,
         #mopayActivityVoucherData.uniCashierOrderId#,
         #mopayActivityVoucherData.originAmount#,
         #mopayActivityVoucherData.discountAmount#,
         #mopayActivityVoucherData.activityAmount#,
         #mopayActivityVoucherData.userPayAmount#,
         #mopayActivityVoucherData.noDiscountAmount#,
         #mopayActivityVoucherData.couponOfferMessagesJsonStr#,
         #mopayActivityVoucherData.settleStatus#,
         #mopayActivityVoucherData.isOld#,
         now(),
         now(),
         #mopayActivityVoucherData.memo#
        )
        <selectKey resultClass="java.lang.Integer" keyProperty="Id">
            <![CDATA[
            SELECT @@IDENTITY AS Id
            ]]>
        </selectKey>
    </insert>

    <update id="updateVoucherSettleStatus" parameterClass="map">
        update TS_MopayActivityVoucher
        set SettleStatus = #settleStatus#
        where ID = #id#
    </update>

    <select id="queryVoucherByTimeAndStatus" parameterClass="map" resultClass="mopayActivityVoucherData">
        <include refid="sql_select"/>
        where AddTime &gt;= #beginTime# and AddTime &lt;= #endTime# and SettleStatus = #status#
    </select>

    <select id="queryVoucherById" parameterClass="map" resultClass="mopayActivityVoucherData">
        <include refid="sql_select"/>
        where ID in
        <iterate property="idList" open="(" close=")" conjunction=",">
            #idList[]#
        </iterate>
    </select>

    <select id="loadByOrderIdAndTradeType" parameterClass="map" resultClass="mopayActivityVoucherData">
        <include refid="sql_select"/>
        where OrderId = #orderId#
        and TradeType = #tradeType#
    </select>


    <select id="queryVoucherByTimeAndStatusList" parameterClass="map" resultClass="mopayActivityVoucherData">
        <include refid="sql_select"/>
        where AddTime &gt;= #beginTime#
        and AddTime &lt;= #endTime#
        and SettleStatus in
        <iterate property="statusList" open="(" close=")" conjunction=",">
            #statusList[]#
        </iterate>
    </select>
    <select id="queryVoucherByTimeAndStatusTradeType" parameterClass="map" resultClass="mopayActivityVoucherData">
        <include refid="sql_select"/>
        where
        <![CDATA[AddTime >= #beginTime# and AddTime <= #endTime#  ]]>
        and SettleStatus = #status#
        and TradeType = #tradeType#;
    </select>
</sqlMap>

 

动态update

<update id="updateInfoSort" parameterClass="org.limojfip.domain.FipInfoSort">
    update FIP_InfoSorts

    <dynamic prepend="set">
        <isNotNull prepend="," property="isKey">
            ISKey=#isKey#
        </isNotNull>
        <isNotNull prepend="," property="SName">
            SName=#SName#
        </isNotNull>
        <isNotNull prepend="," property="description">
            Description=#description#
        </isNotNull>
        <isNull prepend="," property="description">
            Description=null
        </isNull>
        <isNotNull prepend="," property="parentKey">
            ParentKey=#parentKey#
        </isNotNull>
        <isNull prepend="," property="parentKey">
            ParentKey=null
        </isNull>
    </dynamic>
    where SKey = #SKey#

</update>