Reputation: 20591
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 null
s, 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
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