I have a nativeQuery
in my DAO
layer inside my Spring Boot app.
Basically, I’ passing three parameters, like so:
x
@Query(name = "findInvoicesTour", nativeQuery = true)
List<InvoiceDto> findInvoices(@Param("invoiceNumber") String invoiceNumber, @Param("companyName") String companyName, @Param("paid") boolean paid);
I would like to have different WHERE statements executing depending on value of companyName
, something like this:
if(:companyName = '')
SELECT col1, col2, col3
FROM invoice_tour it
WHERE it.paid =: paid AND it.invoice_number LIKE :invoiceNumber
ELSE
SELECT col1, col2, col3
FROM invoice_tour it
WHERE it.paid =: paid AND it.invoice_number LIKE :invoiceNumber AND it.companyName := companyName
Or
SELECT col1, col2, col3
if(:companyName = '')
FROM invoice_tour it
WHERE it.paid =: paid AND it.invoice_number LIKE :invoiceNumber
ELSE
FROM invoice_tour it
WHERE it.paid =: paid AND it.invoice_number LIKE :invoiceNumber AND it.companyName := companyName
Advertisement
Answer
Use boolean logic:
SELECT col1, col2, col3
FROM invoice_tour it
WHERE
it.paid =: paid
AND it.invoice_number LIKE :invoiceNumber
AND (:companyName = '' OR it.companyName := companyName)