iBATIS配置文件的特殊使用方法
JAVA中的iBATIS可以很方便的对数据库进行增删改、查询
最近在用iBATIS，这里列出一些我认为比较好的地方：
1、增加记录时返回随机生成的主键值：
<insert id="insertIntoDB" parameterClass="BepsContract">
<selectKey resultClass="int" keyProperty="contractPk">
SELECT BEPS_CONTRACT_PK.NEXTVAL FROM DUAL
</selectKey>
insert into NCS_BEPS_CONTRACT (CONTRACT_NO,ACK_NO,PAYER_NAME,PAYER_ACCOUNT,PAYEE_NAME,PAYEE_ACCOUNT,ACK_FLAG,DIRECTION,BRANCH_ID,PROCESS_STATUS,CREATE_ON,CREATE_BY,MODIFY_ON,MODIFY_BY,TYPE_ID,TYPE_DESC,CONTRACT_PK,type,SDN_STATUS,DDA_STATUS,LOAD_FILE_NAME,TXN_REF_NO,DESTINATION,EXPIRY_DATE,PAYER_BANKCODE,PAYER_BRANCH_CODE,PAYEE_BANKCODE,PAYEE_BRANCH_CODE,BUYER_SUBSCRIPTION_REF,MERCHANT_ID,TXN_CYCLE,PAYER_BANKNAME,PAYEE_BANKNAME,TXN_AMOUNT_LIMIT,CUMULATIVE_AMOUNT_LIMIT,TXN_COUNT_LIMIT,IS_FROM_BACKEND)
values (
#contractNo:VARCHAR#,
#ackNo:VARCHAR#,
#payerName:VARCHAR#,
#payerAccount:VARCHAR#,
#payeeName:VARCHAR#,
#payeeAccount:VARCHAR#,
#ackFlag:VARCHAR#,
#direction:VARCHAR#,
#branchId:VARCHAR#,
#processStatus:VARCHAR#,
SYSDATE,
#createBy:VARCHAR#,
SYSDATE,
#modifyBy:VARCHAR#,
#typeId:VARCHAR#,
#typeDesc:VARCHAR#,
#contractPk:NUMERIC#,
#type:VARCHAR#,
#sdnStatus:VARCHAR#,
#ddaStatus:VARCHAR#,
#loadFileName:VARCHAR#,
#txnRefNo:VARCHAR#,
#destination:VARCHAR#,
#expiryDate:DATE#,
#payerBankcode:VARCHAR#,
#payerBranchCode:VARCHAR#,
#payeeBankcode:VARCHAR#,
#payeeBranchCode:VARCHAR#,
#buyerSubscriptionRef:VARCHAR#,
#merchantId:VARCHAR#,
#txnCycle:VARCHAR#,
#payerBankname:VARCHAR#,
#payeeBankname:VARCHAR#,
#txnAmountLimit:DOUBLE#,
#cumulativeAmountLimit:DOUBLE#,
#txnCountLimit:NUMERIC#,
#isFromBackend:VARCHAR#
)
</insert>
2、一些特殊的查询：
<select id="selectIncomingTimeDebitPkg" resultClass="java.util.HashMap" parameterClass="CondPkgheadTo">
select B.*, FLOOR((select WORKING_DAY from NCS_BEPS_MBFE_STATUS where BRANCH_ID = #branchId:VARCHAR#) - B.CONSIGNED_DATE) AS REMAIN_DAYS
from NCS_BEPS_PKGHEAD B
where MSG_CODE = #msgCode:VARCHAR# and
BRANCH_ID = #branchId:VARCHAR# and
package_head_pk in (select package_head_pk from ncs_beps_txn_in where
MSG_CODE = #msgCode:VARCHAR# and
BRANCH_ID = #branchId:VARCHAR# and
GROUP_ID = #groupId:VARCHAR#
<isNotNull property="processStatusList">
<iterate prepend="and" property="processStatusList" open="(" close=")" conjunction="or">
PROCESS_STATUS =( #processStatusList[]# )
</iterate>
</isNotNull>
)
<iterate prepend = "and" property = "msgProcessStatusList" open = " (" close = ") " conjunction = "or">
MSG_PROCESS_STATUS = ( #msgProcessStatusList[]# )
</iterate>
<isNotNull property="searchFailStatus" prepend="and">
<![CDATA[ BEPS_STATUS <> 'BACK00' ]]>
</isNotNull>
</select>
最近在用iBATIS，这里列出一些我认为比较好的地方：
1、增加记录时返回随机生成的主键值：
<insert id="insertIntoDB" parameterClass="BepsContract">
<selectKey resultClass="int" keyProperty="contractPk">
SELECT BEPS_CONTRACT_PK.NEXTVAL FROM DUAL
</selectKey>
insert into NCS_BEPS_CONTRACT (CONTRACT_NO,ACK_NO,PAYER_NAME,PAYER_ACCOUNT,PAYEE_NAME,PAYEE_ACCOUNT,ACK_FLAG,DIRECTION,BRANCH_ID,PROCESS_STATUS,CREATE_ON,CREATE_BY,MODIFY_ON,MODIFY_BY,TYPE_ID,TYPE_DESC,CONTRACT_PK,type,SDN_STATUS,DDA_STATUS,LOAD_FILE_NAME,TXN_REF_NO,DESTINATION,EXPIRY_DATE,PAYER_BANKCODE,PAYER_BRANCH_CODE,PAYEE_BANKCODE,PAYEE_BRANCH_CODE,BUYER_SUBSCRIPTION_REF,MERCHANT_ID,TXN_CYCLE,PAYER_BANKNAME,PAYEE_BANKNAME,TXN_AMOUNT_LIMIT,CUMULATIVE_AMOUNT_LIMIT,TXN_COUNT_LIMIT,IS_FROM_BACKEND)
values (
#contractNo:VARCHAR#,
#ackNo:VARCHAR#,
#payerName:VARCHAR#,
#payerAccount:VARCHAR#,
#payeeName:VARCHAR#,
#payeeAccount:VARCHAR#,
#ackFlag:VARCHAR#,
#direction:VARCHAR#,
#branchId:VARCHAR#,
#processStatus:VARCHAR#,
SYSDATE,
#createBy:VARCHAR#,
SYSDATE,
#modifyBy:VARCHAR#,
#typeId:VARCHAR#,
#typeDesc:VARCHAR#,
#contractPk:NUMERIC#,
#type:VARCHAR#,
#sdnStatus:VARCHAR#,
#ddaStatus:VARCHAR#,
#loadFileName:VARCHAR#,
#txnRefNo:VARCHAR#,
#destination:VARCHAR#,
#expiryDate:DATE#,
#payerBankcode:VARCHAR#,
#payerBranchCode:VARCHAR#,
#payeeBankcode:VARCHAR#,
#payeeBranchCode:VARCHAR#,
#buyerSubscriptionRef:VARCHAR#,
#merchantId:VARCHAR#,
#txnCycle:VARCHAR#,
#payerBankname:VARCHAR#,
#payeeBankname:VARCHAR#,
#txnAmountLimit:DOUBLE#,
#cumulativeAmountLimit:DOUBLE#,
#txnCountLimit:NUMERIC#,
#isFromBackend:VARCHAR#
)
</insert>
2、一些特殊的查询：
<select id="selectIncomingTimeDebitPkg" resultClass="java.util.HashMap" parameterClass="CondPkgheadTo">
select B.*, FLOOR((select WORKING_DAY from NCS_BEPS_MBFE_STATUS where BRANCH_ID = #branchId:VARCHAR#) - B.CONSIGNED_DATE) AS REMAIN_DAYS
from NCS_BEPS_PKGHEAD B
where MSG_CODE = #msgCode:VARCHAR# and
BRANCH_ID = #branchId:VARCHAR# and
package_head_pk in (select package_head_pk from ncs_beps_txn_in where
MSG_CODE = #msgCode:VARCHAR# and
BRANCH_ID = #branchId:VARCHAR# and
GROUP_ID = #groupId:VARCHAR#
<isNotNull property="processStatusList">
<iterate prepend="and" property="processStatusList" open="(" close=")" conjunction="or">
PROCESS_STATUS =( #processStatusList[]# )
</iterate>
</isNotNull>
)
<iterate prepend = "and" property = "msgProcessStatusList" open = " (" close = ") " conjunction = "or">
MSG_PROCESS_STATUS = ( #msgProcessStatusList[]# )
</iterate>
<isNotNull property="searchFailStatus" prepend="and">
<![CDATA[ BEPS_STATUS <> 'BACK00' ]]>
</isNotNull>
</select>
