在拼接的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