J'ai un nativeQuery dans ma couche DAO dans mon application Spring Boot.

Fondamentalement, je passe trois paramètres, comme ceci :

@Query(name = "findInvoicesTour", nativeQuery = true)
    List<InvoiceDto> findInvoices(@Param("invoiceNumber") String invoiceNumber, @Param("companyName") String companyName, @Param("paid") boolean paid);

J'aimerais que différentes instructions WHERE s'exécutent en fonction de la valeur de companyName, quelque chose comme ceci :

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

Ou

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
0
MikeSomething 4 nov. 2020 à 17:07

1 réponse

Meilleure réponse

Utilisez la logique booléenne:

SELECT col1, col2, col3
FROM invoice_tour it
WHERE 
    it.paid =: paid 
    AND it.invoice_number LIKE :invoiceNumber
    AND (:companyName = '' OR it.companyName := companyName)
1
GMB 4 nov. 2020 à 14:08