dorado5之SqlMatchRule

在拼接的sql中使用in关键字，而in括号里的内容来自于该dataset的parameters

var parameters = datasetCUSTOMER.parameters();
   parameters.setValue("DN_HME_TEL", DN_HME_TEL);
   parameters.setValue("DN_CO_TEL", DN_CO_TEL);
   parameters.setValue("DN_CRY_TEL", DN_CRY_TEL);
   parameters.setValue("DN_CRY_TEL2", DN_CRY_TEL2);

如果dataset的SqlMatchRule如下定义：

<MatchRules>
    <MatchRule level="1" type="Sql" enabled="true" escapeEnabled="false"   sql="DN_CUSTOMER.DN_HME_TEL in (:DN_HME_TEL,:DN_CO_TEL,:DN_CRY_TEL,:DN_CRY_TEL2)" />
    <MatchRule level="1" kind="or" type="AndOr" />
    <MatchRule level="1" type="Sql" enabled="true" escapeEnabled="false"   sql="DN_CUSTOMER.DN_CRY_TEL in (:DN_HME_TEL,:DN_CO_TEL,:DN_CRY_TEL,:DN_CRY_TEL2)" />
    <MatchRule level="1" kind="or" type="AndOr" />
    <MatchRule level="1" type="Sql" enabled="true" escapeEnabled="false"   sql="DN_CUSTOMER.DN_CO_TEL in (:DN_HME_TEL,:DN_CO_TEL,:DN_CRY_TEL,:DN_CRY_TEL2)" />
    <MatchRule level="1" kind="or" type="AndOr" />
    <MatchRule level="1" type="Sql" enabled="true" escapeEnabled="false"   sql="DN_CUSTOMER.DN_CRY_TEL2 in (:DN_HME_TEL,:DN_CO_TEL,:DN_CRY_TEL,:DN_CRY_TEL2)" />
   </MatchRules>

 

则生产的sql如下：

SELECT * FROM ( SELECT
 DN_CUSTOMER.DN_CST_ID,
 DN_CUSTOMER.DN_CST_NM,
 DN_CUSTOMER.DN_HME_TEL,
 DN_CUSTOMER.DN_CRY_TEL,
 DN_CUSTOMER.DN_CO_TEL,
 DN_CUSTOMER.DN_CRY_TEL2
 FROM
 DN_CUSTOMER
WHERE
 DN_CUSTOMER.DN_HME_TEL in (?,?,?,?) or DN_CUSTOMER.DN_CRY_TEL in (?,?,?,?) or DN_CUSTOMER.DN_CO_TEL in (?,?,?,?) or (DN_CUSTOMER.DN_CRY_TEL2 in (?,?,?,?) and (DN_CUSTOMER.DN_HME_TEL like ?) and (DN_CUSTOMER.DN_CO_TEL like ?))
 ORDER BY
 TO_NUMBER(DN_CST_ID)
  ) WHERE rownum <= 100

 

可以看出上面的sql中除了in还多出了like，原因是parameters中的key和value一致了，可以将key改为其他的名字，同时修改SqlMatchRule的内容即可，如下：

 

parameters.setValue("hmeTel", DN_HME_TEL);
   parameters.setValue("coTel", DN_CO_TEL);
   parameters.setValue("cryTel", DN_CRY_TEL);
   parameters.setValue("cryTel2", DN_CRY_TEL2);

 

SELECT * FROM ( SELECT
 DN_CUSTOMER.DN_CST_ID,
 DN_CUSTOMER.DN_CST_NM,
 DN_CUSTOMER.DN_HME_TEL,
 DN_CUSTOMER.DN_CRY_TEL,
 DN_CUSTOMER.DN_CO_TEL,
 DN_CUSTOMER.DN_CRY_TEL2
 FROM
 DN_CUSTOMER
WHERE
 DN_CUSTOMER.DN_HME_TEL in (?,?,?,?) or DN_CUSTOMER.DN_CRY_TEL in (?,?,?,?) or DN_CUSTOMER.DN_CO_TEL in (?,?,?,?) or DN_CUSTOMER.DN_CRY_TEL2 in (?,?,?,?)
 ORDER BY
 TO_NUMBER(DN_CST_ID)
  ) WHERE rownum <= 100

 

可以看到，没有了like。

 

OK