JPA query check if @Param set is empty or null-CodePudding

I've a complex HQL query which I want to run. One of my params is a Set<Integer> with valid values for the result

something like: select * from table t where t.code IN (Set<Integer>)

This is very simple right? the problem is that I want this query to return ALL ITEMS, case the Set<Integer> is empty or null (don't need to be empty or null, working either case is enough)

considering the JPA repository method:

public Page<Table> findAll(@Param("codes") Set<Integer> codes);

i've tried several approaches:

SELECT t FROM table t where :codes IS NULL OR t.code IN (:codes)

runtime error: SQL Error: 1241, SQLState: 21000 (conn=3123) Operand should contain 1 column(s)

SELECT t FROM table t where (:codes) IS EMPTY OR t.code IN (:codes)

spring wont even boot up ??? is not mapped

SELECT t FROM table t where size((:codes)) = 0 OR t.code IN (:codes)

and many others

Is there a generic way to make this check and make this query to work without build the query dinamically?

CodePudding user response：

Not the best answer but I found the solution on stackoverflow https://stackoverflow.com/a/54035166/5679560

The solution was simply to use COALESCE in addition to IS NULL so it can work with multiple values.

SELECT t FROM table t where COALESCE(:codes) IS NULL OR t.code IN (:codes)

CodePudding user response：

You should use a Specification for this instead and conditionally add predicates to your where clause with the JPA Criteria API. That way, your query plan will be better than with this generic solution and in addition it also doesn't use something that works by accident. Note that this query SELECT t FROM table t where COALESCE(:codes) IS NULL OR t.code IN (:codes) won't work anymore with Hibernate 6.