一个困扰了很久的问题原来是因为思想固化了,一直跟着别人的思路走。
先上代码:
<isNotEmpty property="payNo">
and tbd.order_id = #payNo#
</isNotEmpty>
<!-- <isNotNull property="payNo">
and tbd.order_id = #payNo#
</isNotNull> -->
isNotNull:当参数不为null时。
isNotEmpty:当参数既不为空也不为Null。做如下操作。
场景:
<tr>
<td><div align="right">入账时间:</div></td>
<td colspan="1">
<input class="Wdate" type="text" id="startTime" name="startTime" onClick="WdatePicker({lang:'zh_CN',dateFmt:'yyyy-MM-dd HH:mm:ss',minDate:'#F{$dp.$D(\'endTime\',{M:-3})}',maxDate:'#F{$dp.$D(\'endTime\')}'})" value="${stValue!''}">
至 <input class="Wdate" type="text" id="endTime" name="endTime" onClick="WdatePicker({lang:'zh_CN',dateFmt:'yyyy-MM-dd HH:mm:ss',minDate:'#F{$dp.$D(\'startTime\')}',maxDate:'%y-%M-%d %H:%m:%s'})" value="${eValue!''}">
</td>
<td><div align="right">交易流水号:</div></td>
<td><input type="text" id="payNo" name="payNo" class="base-input"></td>
<td colspan="1"></td>
</tr>
同样的参数,一个可以根据OrderId查出来相对应的数据一个则查出来所有数据。
从SQL中我们可以看出当OrderId有值时,编译器可以取得到 Debug模式可以查出数据,但是当它没值的时候就出问题了。?参数一一对应。
SQL1:select fundTrace,balanceDate,pay,revenue,payerAccount,payeeAccount,payeeFee,payerFee,payNo,dealCode from (
SELECT ROWNUM rn,fundTrace,balanceDate,pay,revenue,payerAccount,payeeAccount,payeeFee,payerFee,payNo,dealCode FROM (
SELECT
tbd.deal_type fundTrace,
tbd.create_date balanceDate,
decode(tbd.payer_full_mem_acctcode,'2243010220110000003671201',tbd.amount) pay, decode(tbd.payee_full_mem_acctcode,'2243010220110000003671201',tbd.amount) revenue,
tbd.payer_full_mem_acctcode payerAccount,
tbd.payee_full_mem_acctcode payeeAccount,
tbd.payee_fee payeeFee,
tbd.payer_fee payerFee,
tbd.order_id payNo,
tbd.deal_code dealCode FROM
t_balance_deal tbd WHERE tbd.amount > 0
AND (tbd.payer_full_mem_acctcode = '2243010220110000003671201' OR tbd.payee_full_mem_acctcode = '2243010220110000003671201')
and tbd.order_id ='1061509251514022491' order by bdeal_id desc ) t)
SQL2:select fundTrace,balanceDate,pay,revenue,payerAccount,payeeAccount,payeeFee,payerFee,payNo,dealCode from (
SELECT ROWNUM rn,fundTrace,balanceDate,pay,revenue,payerAccount,payeeAccount,payeeFee,payerFee,payNo,dealCode FROM (
SELECT tbd.deal_type fundTrace, tbd.create_date balanceDate, decode(tbd.payer_full_mem_acctcode,'2243010220110000003671201',tbd.amount) pay,
decode(tbd.payee_full_mem_acctcode,'2243010220110000003671201',tbd.amount) revenue, tbd.payer_full_mem_acctcode payerAccount,
tbd.payee_full_mem_acctcode payeeAccount, tbd.payee_fee payeeFee, tbd.payer_fee payerFee, tbd.order_id payNo,
tbd.deal_code dealCode FROM t_balance_deal tbd WHERE tbd.amount > 0
AND (tbd.payer_full_mem_acctcode = '2243010220110000003671201' OR tbd.payee_full_mem_acctcode = '2243010220110000003671201')
and tbd.create_date between ? and ?
and tbd.order_id = ? order by bdeal_id desc ) t) where rn between ? and ?
参数一一补上之后发现依然会报错。
N遍断点Debug之后发现原来是isNotEmpty的问题。