JPA query check if @Param set is empty or null

Question

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?

Christian Beikov · Accepted Answer · 2022-07-29 11:48:20Z

1

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.

answered Jul 29, 2022 at 11:48

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rafael Lima Over a year ago

It doesn't work by accident, it works by design. I've never used the specification api, if you can point out how to build this behavior in there I might more useful than just say that it "works by accident"

Davide D'Alto Over a year ago

His point is that you should create the query programmatically based on the parameters you want to pass. You can do it using the Criteria API, or by not concatenating the where clause when codes is empty.

Christian Beikov Over a year ago

I am one of the Hibernate developers and I can tell you that using a list parameter anywhere other than in an IN predicate is actually illegal and only works by accident. So if you want your code to still work with Hibernate 6, and also want your database to be able to provide you results fast, you should look into the JPA Criteria API approach with Spring Data Specification :)

Rafael Lima · Accepted Answer · 2022-07-27 22:14:27Z

0

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)

answered Jul 27, 2022 at 22:14

Rafael Lima

3,5859 gold badges63 silver badges140 bronze badges

Comments

Steph · Accepted Answer · 2023-09-22 20:15:50Z

-1

your arrays should be made of primitive types for hql to propperly compare each one

in your pseudocode example, instead of

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

it should be

select * from table t where t.code IN (int[])

answered Sep 22, 2023 at 20:15

Steph

1154 bronze badges

Collectives™ on Stack Overflow

JPA query check if @Param set is empty or null

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related