通过 Native Query 通过多个参数的组合进行搜索

Question

我目前正在开发一个功能，它允许系统通过 6 个参数的组合来搜索公共服务收据，这些参数可以为空，这意味着收据不应被此参数过滤：accountNumber、amountRangeMin、amountRangeMax、dateRangeMin、dateRangeMax，公共服务标识。然而，为每个参数组合制定一个方法不是一种选择，我认为必须有更好的方法，起初我的方法如下：

在我的服务中，我有这个方法：

public Map<String,Object> findPublicServiceReceiptsByParams(Integer accountNumber, BigDecimal amountRangeMin,

        BigDecimal amountRangeMax, LocalDate dateRangeMin, LocalDate dateRangeMax, Integer publicServiceId) {

    Map<String,Object> publicServiceReceipts = new HashMap<String,Object>();

    String accountNumberFilter = !(accountNumber==null) ? accountNumber.toString() : "AccountNumberTableName";

    String amountRangeMinFilter = !(amountRangeMin==null) ? amountRangeMin.toString() : "table.AmountColumnName";

    String amountRangeMaxFilter = !(amountRangeMax==null) ? amountRangeMax.toString() : "table.AmountColumnName";

    String dateRangeMinFilter = !(dateRangeMin==null) ? dateRangeMin.toString() : "Table.ReceiptCreationDateColumn";

    String dateRangeMaxFilter = !(dateRangeMax==null) ? dateRangeMax.toString() : "Table.ReceiptCreationDateColumn";

    String publicServiceIdFilter = !(publicServiceId==null) ? publicServiceId.toString() : "table.publicServiceIdColumn";

    publicServiceReceipts = publicServiceReceiptRepository.findPublicServiceReceiptsByParams(accountNumberFilter,

            amountRangeMinFilter, amountRangeMaxFilter, dateRangeMinFilter, dateRangeMaxFilter,

            publicServiceIdFilter);

    return publicServiceReceipts;

}

我的推理是，如果参数为空意味着使用 Web 服务的人对该参数不感兴趣，因此如果发生这种情况，我将该变量设置为列名，这样它就不会影响 WHERE 子句，并且理论上使它成为更简单，但我发现它会将名称作为字符串发送，因此它不会被识别为 sql 语句，这是我思想中的缺陷，正如我所说，除了为每个组合编写每个方法之外，必须有另一种方式，我感谢任何帮助:)。

Answer 1

我找到了解决此问题的方法，我是这样做的（我将仅显示本机查询，因为它是我唯一更改的内容）：DECLARE&nbsp; @actNum varchar(50),@crdNum varchar(50),@pseId varchar(50),@dateMin varchar(50),@dateMax varchar(50),@amountMin varchar(50),@amountMax varchar(50)SET @actNum = :actNumSET @crdNum = :crdNum&nbsp;SET @pseId =&nbsp; :pseId&nbsp;SET @dateMin = :dateMin&nbsp;SET @dateMax = :dateMax&nbsp;SET @amountMin = :amountMin&nbsp;SET @amountMax = :amountMax&nbsp;--Whatever Select with joins statement&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE ACT.ACT_AccountNumber = CASE WHEN @actNum = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;THEN ACT.ACT_AccountNumber&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE @actNum END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND CRD_CardNumber = CASE WHEN @crdNum = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN CRD_CardNumber&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE @crdNum END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND PSE.PSE_Id= CASE WHEN @pseId = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;THEN PSE.PSE_Id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE @pseId END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND PSR.PSR_CreateDate >= CASE WHEN @dateMin = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN PSR.PSR_CreateDate&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE @dateMin END&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND PSR.PSR_CreateDate <= CASE WHEN @dateMax = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN PSR.PSR_CreateDate&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE @dateMax END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND PSR.PSR_Amount BETWEEN CASE WHEN @amountMin = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN PSR.PSR_Amount&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE @amountMin END&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND CASE WHEN @amountMax = 'N/A'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN PSR.PSR_Amount&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE @amountMax END&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY PSR.PSR_CreateDate DESC后端会将参数作为“N/A”（如果不应该用于过滤数据）或实际值发送，这对我来说很好用！