Postgres & JPA named queries - any (*) value in WHERE

Question

I want to write filter queries for some specific GET cases.

What I am doing right now is writing separate queries for each case, which is pretty time-consuming. Instead, I would like to write one big named query, where I can either put a value into the WHERE clause if a filter is set, otherwise ANY. Is this possible with JPA? This is an example controller which returns payments by a specific client-ID:

public List<PaymentEntity> findAllByClientId(final int page, final int pageSize, final String fromDate,
        final String toDate, final Long clientId) {
    Map<String, Object> parameters = new HashMap<>();
    parameters.put("clientId", clientId);
    return super.findWithNamedQueryPagination("PaymentEntity.findAllWithPaginationByClientId", parameters,
            PaymentEntity.class, page, pageSize, fromDate, toDate);
}

And this is the belonging named query:

SELECT i FROM PaymentEntity i WHERE i.clientContractData.client.id = :clientId AND i.createdAt BETWEEN :fromDate AND :toDate ORDER BY i.createdAt DESC

What I would like to now is to be able to pass a parameter like this:

    if(clientId == null) {
        parameters.put("clientId", "*");
    } else {
        parameters.put("clientId", clientId);
    }

But now of course I get an type error: Parameter value [*] did not match expected type [java.lang.Long (n/a)]

What would be the best way to accomplish this? Do I have to write native queries or is there a way with named queries?

Edit to clarify: The example above shows a query with type Long, but I need to do this with String as well.

Answer 1

You could try to alter your query like:

WHERE (i.clientContractData.client.id = :clientId or :clientId = -1)

Note: I think that references in JPA goes as far as 2 levels deep and here you have 3 levels:

i.clientContractData.client.id

you may need to use an extra explicit join here