CODEWITHSUNDEEP
javahibernate
WelcomeTo
WelcomeTo

Reputation: 20591

Hibernate. Analog of Query#setParameterList

I have native SQL query in Hibernate: 

SELECT * FROM my_table where id IN (:ids)

I want to substitute :ids named parameter with List<Long>.

Query query = query.createSQLQuery(...)
query.setParameter( "ids", ids, ???); // WHAT Hibernate type to use here?

What type should I use as 3-rd parameter of setParameter method?

For example for Boolean I used this one and it worked (but didn't work without specifying type): 

query.setParameter( "active", active, new NumericBooleanType());

Please note, I can't use Query#setParameterList (because my list can be null)

EDIT:

Actually my query looks more like this:

SELECT * FROM my_table where :ids IS NULL OR id IN (:ids)

it's help me to support optional parameters without need to dynamically creating SQL (which is code smell I think). And if my List is empty, I'm replacing it with null because SQL does not allow an empty IN clause (if it null, then the OR id IN (:ids) section will not be executed at all in my case).

So I'm using setParameter only because it allows to use nulls, but setParameterList throws exception when collection is null.

How I can cause setParameter to work just like setParameterList but without throwing exception?

Upvotes: 0

Views: 394

Answers (1)

Scott Sosna
Scott Sosna

Reputation: 1413

Since you can't use setParameterList, you must dynamically create the SQL with n substitution params and call setParameter() on each.

SQL in general does not allow an empty IN clause, so you'll need to handle that case separately.

Upvotes: 1

Related Questions