I have a nativeQuery
in my DAO
layer inside my Spring Boot app.
Basically, I’ passing three parameters, like so:
@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)