32

Is there any way to specify optional parameters (such as when search parameters are provided from a form and not all parameters are required) in a named query when using Hibernate? I'm using a native SQL query, but the question is probably applicable to named HQL queries as well.

I'm pretty sure the answer to this is 'no', but I haven't found the definitive answer in the documentation yet.

naXa
  • 26,677
  • 15
  • 154
  • 213
Ickster
  • 2,111
  • 4
  • 22
  • 42

5 Answers5

42

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
Mohammad Faisal
  • 5,241
  • 14
  • 63
  • 114
Ian Jones
  • 1,908
  • 1
  • 16
  • 16
  • When using ':value is null', I get error 'Illegal use of keyword NULL'. When using 'isnull(:value, 1) = 1', I get error 'The left expression is not a valid expression'. – Atul Kumbhar Nov 11 '18 at 22:16
  • 1
    you can also use `.. WHERE isnull(:value, o.category) = o.category ..` – Siamand Jan 02 '19 at 10:45
  • We used to use this solution for a while, until we did a load test and realized that this for some reason decreases the performance of the query considerably for cases where the optional parameters are not used. (Big table, few million entries, DB2, about four/five optional parameters). – Bernhard Mar 06 '20 at 07:56
16

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.

Community
  • 1
  • 1
Pascal Thivent
  • 535,937
  • 127
  • 1,027
  • 1,106
  • Thanks. That's pretty much what I figured. I'm familiar with both of the approaches you suggested in your other answer, but I'm stuck with a nightmarish legacy db schema that just isn't well suited to the Hibernate approach for my particular case. I've made it work so far, but have some new requirements I just can't seem to meet using straight Hibernate mapping. I'm probably going to give up and use iBATIS for this one case. Not real thrilled adding another technology to the stack as a band-aid, but that's life I guess. Thanks. – Ickster Mar 15 '10 at 02:14
  • 1
    Thanks for the update. I've considered that approach as well and given the number of parameters I'm dealing with, I decided it's probably cleaner to just use iBATIS. Thanks for the advice. – Ickster Mar 15 '10 at 15:30
9

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.

Kosima
  • 91
  • 1
  • 1
3

Another solution for handling optional list parameters is by checking for null using the COALESCE function. COALESCE is supported by Hibernate returns the first non-null parameter from a list, allowing you to check for null on a list without breaking the syntax when there are multiple items in the list.

HQL example with optional parameter and list parameter:

select obj from MyEntity obj
where ( COALESCE( null, :listParameter ) is null or obj.field1 in (:listParameter) )
  and ( :parameter is null or obj.field2 = :parameter )

This worked for me with a SQL Server dialect.

Jon Williams
  • 31
  • 3
  • This didn't work for me, hibernate complained about `COALESCE(null, )` when `:listParameter` was not defined. My solution was to also pass an additional parameter and have the where condition written this way: `(:listParamterSize = 0 or obj.field1 in (:listParameter))` – jeremija May 13 '15 at 09:59
  • It works, but `:listParameter` must be defined, at least as null. The advantage of this solution is that you can pass the parameter as null and there is no need for the additional parameter. – Daniel Olszewski Nov 27 '15 at 09:53
0

For those having issues with NULL values, another option is to use an alternate value. In my case, I used only positive values for my category field, that allows me to use as my alternate value = -1.

Therefore, before to execute the query, you can make a small validation:

if(value==null) {
   value = -1;
}
....
....
select p from Product p WHERE :value = -1 or p.category = :value
georgeos
  • 1,554
  • 2
  • 18
  • 22