Search code examples
jpaspring-data-jpaspring-datawildcardjpql

Is there such a thing as a wildcard for boolean value?

I have a JPA Repository with a few custom queries. I've been tasked with implementing a functionality which requires numerous new queries. One thing that would drastically cut down on the number of them is if I could use a wildcard for a boolean value.

I have tried modifying the query to use 'LIKE %' but Spring Data doesn't like it, throwing a numberFormatException (Infinite of NaN).

The original query:

 @Query("SELECT SUM(paymentSummary.paidAmount) FROM InvoiceLineItem invoice INNER JOIN PaymentSummary paymentSummary"
    + " ON invoice.invoiceKey=paymentSummary.invoiceKey"
    + " WHERE invoice.balanced=:balanced")
BigDecimal getInvoicePaidTotalByBalanced(@Param("balanced") boolean balanced);

Ideally, I'd like to use the same query, but instead of passing it true or false, I'd pass it a wildcard so that it acted on all records regardless of the true/false status.

As of now, the only way I know how to do this is to create a second query, same as the first, minus the where clause.

Solution

  • How about using the IN operator instead? (TRUE, FALSE) will then be your wildcard.

    Alternatively, you should be able to use WHERE :balanced IS NULL OR invoice.balanced=:balanced. In that case, change boolean to Boolean and null becomes the wildcard.