Optional parameters with named query in Hibernate?

unfortunately the solution under "Optional or Null Parameters" does not work for IN lists. I had to changed the query as followed ...

Named query definition:

select ls from KiCOHeader co
...
join lu.handlingType ht
where (:inHandlingTypesX = 1 OR ht.name in (:inHandlingTypes))

Code:

Set<KiHandlingTypeEnum> inHandlingTypes = ...

Query query = persistence.getEm().createNamedQuery("NAMED_QUERY");
query.setParameter("inHandlingTypesX", (inHandlingTypes == null) ? 1 : 0);
query.setParameter("inHandlingTypes", inHandlingTypes);

List<KiLogicalStock> stocks = query.getResultList();

Much fun working.

As mentioned in a different answer to the question referenced previously, the following HQL construct works for me:

select o from Product o WHERE :value is null or o.category = :value

if :value is passed in as null, all Products are returned.

See also Optional or Null Parameters

Note that this won't work in some versions of Sybase due to this bug, so the following is an alternative:

select o from Product o WHERE isnull(:value, 1) = 1 or o.category = :value

AFAIK, there is no such thing so you'll have to write a dynamic query for this. Maybe have a look at this previous answer showing how to do this in HQL (that you can transpose to SQL) and also showing how the Criteria API makes it simpler and is thus better suited for this job in my opinion.

Update: (answering a comment from the OP) Working with a legacy database can be indeed tricky with Hibernate. Maybe you can use a dynamic native query and return non-managed entities though. But on the long run, things might get worse (I can't tell that for you). Maybe Hibernate is not the best choice in your case and something like iBATIS would give you the flexibility you need.