JPA : Use of list in where clause and Case statement together - Is there a way to loop through?-CodePudding

I am using JPA-Hibernate in our application and I am working with a query like :

@Query(
value = "ReportDTO(report) FROM Report report "  
    "JOIN p.clientFile cf "  
    "JOIN cf.client cl "  
    "WHERE report.active = TRUE "  
    "AND :companyKey = CASE WHEN report.companyKey IS NOT NULL "  
        "THEN report.companyKey "  
        "ELSE cl.companyKey "  
        "END "  
    "ORDER BY report.publishedDate DESC",
countQuery = “..”
)
@NonNull
Page<TagReportDTO> findAll(@NonNull Pageable pageable, @Param("companyKey") String companyKey);

when the "companyKey" was a single value, it worked. Now, I need to change this findAll method to accept a list of company keys. So it should like like ( after the change) :

@NonNull
Page<TagReportDTO> findAll(@NonNull Pageable pageable, @Param("companyKeys") List<String> companyKeys);

Now I am getting hard time to accommodate the list of objects or companyKeys (instead of :companyKey) in the query where the the case statement will be applicable to each item in the list. Is there a way I can loop through ?

CodePudding user response：

Did you try using IN operator? Like this:

"AND (CASE WHEN report.companyKey IS NOT NULL "  
"THEN report.companyKey "  
"ELSE cl.companyKey "  
"END) IN :companyKeys "