Search by the combination of multiple parameters via Native Query

Question

I'm currently working on a fetaure that will allow the system to search public services receipts by the combination of 6 parameters which can be null meaning that receipts shouldn't be filtered by this parameter: accountNumber, amountRangeMin, amountRangeMax, dateRangeMin, dateRangeMax, publicServiceId. However making a method for each combination of the parameters is not an option, I'm thinking that there must be a better way, at first my approach was as following:

On my Service I have this method:

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;
}

And then in my repository I had:

final static String FIND_PUBLIC_SERVICES_BY_ARGS = "Select (Insert whatever logic should go in here to select columns from receipts the where clause is the one that matters)"
        + "    WHERE ACT.ACT_AccountNumber=:accountNumberFilter\n"
        + "      AND PSE.PSE_Id=:publicServiceIdFilter\n"
        + "      AND PSR.PSR_CreateDate BETWEEN :dateRangeMinFilter AND :dateRangeMaxFilter\n"
        + "      AND PSR.PSR_Amount BETWEEN :amountRangeMinFilter AND :amountRangeMaxFilter\n"
        + " order by PSR.PSR_CreateDate desc";

@Query(nativeQuery = true, value = FIND_PUBLIC_SERVICES_BY_ARGS)
Map<String, Object> findPublicServiceReceiptsByParams(@Param("accountNumberFilter") String accountNumberFilter,
        @Param("amountRangeMinFilter") String amountRangeMinFilter,
        @Param("amountRangeMaxFilter") String amountRangeMaxFilter,
        @Param("dateRangeMinFilter") String dateRangeMinFilter,
        @Param("dateRangeMaxFilter") String dateRangeMaxFilter,
        @Param("publicServiceIdFilter") String publicServiceIdFilter);

}

My reasoning was that if a parameter was null meant that whoever consumed the Web Service is not interested in that paramater so if that happens I set that variable as the Column Name so that it wouldn't affect in the WHERE clause and in theory make it simpler, but what I found was that It would send the names as Strings so it wouldn't be recognized as an sql statement which was the flaw in my thinking and as I said there must be another way other than writing each method for each combination, I appreciate any help :).

Answer 1

You should use the Criteria API, which was designed for creating dynamic queries. Named queries aren't really meant to be used in this case. With it you can do something like this:

@PersistenceContext
EntityManager em;

List<YourEntity> method(String argument) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<YourEntity> cq = cb.createQuery(YourEntity.class);
    Root<YourEntity> root = cq.from(YourEntity.class);

    List<Predicate> predicates = new ArrayList<>();
    if (argument == null) {
        predicates.add(cb.equal(root.get("yourAttribute"), argument);
    }
    // rest of your logic goes here

    cq.where(predicates.toArray(new Predicate[]{}));
    return em.createQuery(cq).getResultList();
}