How to query with hibernate and optional parameters?

Question

Is it possible to write a select query with optional parameters?

Example: basically I want to fetch using the following query:

SELECT p from Person p where p.firstname = :firstname and p.lastname = :lastname;

But if this does not return a result, I want to query only by lastname.

Is that possible in a single query? Or should I execute an additional query within an open transaction?

potential duplicate of: http://stackoverflow.com/questions/2444603/optional-parameters-with-named-query-in-hibernate — MWiesner, Aug 20 '15 at 19:34

score 2 · Accepted Answer · answered Aug 20 '15 at 20:55

As stated in the question linked by @MWiesner, there is no way as far as I'm aware to do this in hibernate HQL. However, if you're prepared to use hibernate to do a native query, you could use a case statement to achieve your aim. For example, the following query will work in MySQL, and with a small amount of tweaking could be made to work in any SQL database.

SELECT *
FROM Person p
WHERE (
  CASE (
    SELECT COUNT(*)
    FROM Person p2
    WHERE p2.firstname = :firstname AND p2.lastname = :lastname
     ) 
  WHEN 0 THEN 
    p.lastname = :lastname 
  ELSE 
    p.firstname = :firstname AND p.lastname = :lastname 
  END
);

How to query with hibernate and optional parameters?

1 Answers1