0

I am struggling with JPQL dynamic where condition. I tried searching the syntax for the same but coluldn't find one.

in my case if user is passing the name parameter then the select query should be

select * from user where name = 'sanjay'

if user is not passing name parameter then select query should be 

select * from user

Below is my jpql query format which fails when name parameter is not passed.

entity_manager.createQuery("select u from user u where u.name = :name").setParameter("name",params[:name]).getResultList()

How can i update above JPQL query to support both the cases i.e when the name parameter is passed and when the name parameter is not passed ??

Sanjay Salunkhe
  • 2,665
  • 5
  • 28
  • 52
  • Use two different queries, or append the where clause if the parameter is there, or use the criteria API, or use QueryDSL. – JB Nizet Dec 21 '16 at 16:01
  • Here are your options to have dynamic queries: http://stackoverflow.com/questions/28874135/dynamic-spring-data-jpa-repository-query-with-arbitrary-and-clauses/ – Robert Niestroj Jan 01 '17 at 08:00

2 Answers2

1

This is not possible in JPQL. You even cannot do something like

createQuery("select u from user u where u.name = :name OR :name IS NULL")

It is not possible. That simple. Use two queries or use the Criteria API.

badera
  • 1,495
  • 2
  • 24
  • 49
0

This is the answer I get when I tries to do like you it is working with some modification.

In my case I had the problem that my optional parameter was a List<String> and the solution was the following:

@Query(value = "SELECT *
                FROM ...
                 WHERE (COLUMN_X IN :categories OR COALESCE(:categories, null) IS NULL)"
, nativeQuery = true)
List<Archive> findByCustomCriteria1(@Param("categories") List<String> categories);

This way:

  • If the parameter has one or more values it is selected by the left side of the OR operator
  • If the parameter categories is null, meaning that i have to select all values for COLUMN_X, will always return TRUE by the right side of the OR operator

Why COALESCE and why a null value inside of it?

Let's explore the WHERE clause in all conditions:

Case 1: categories = null

(COLUMN_X IN null OR COALESCE(null, null) IS NULL)

The left part of the OR will return false, while the right part of the OR will always return true, in fact COALESCE will return the first non-null value if present and returns null if all arguments are null.

Case 2: categories = ()

(COLUMN_X IN null OR COALESCE(null, null) IS NULL)

JPA will automatically identify an empty list as a null value, hence same result of Case 1.

Case 3: categories = ('ONE_VALUE')

(COLUMN_X IN ('ONE_VALUE') OR COALESCE('ONE_VALUE', null) IS NULL)

The left part of the OR will return true only for those values for which COLUMN_X = 'ONE_VALUE' while the right part of the OR will never return true, because it is equals to 'ONE_VALUE' IS NULL (that is false).

Why the null as second parameter? Well, that's because COALESCE needs at least two parameters.

Case 4: categories = ('ONE_VALUE', 'TWO_VALUE')

(COLUMN_X IN ('ONE_VALUE', 'TWO_VALUE') OR COALESCE('ONE_VALUE', 'TWO_VALUE', null) IS NULL)

As in Case 3, the left part of the OR operator will select only the rows for which COLUMN_X is equale to 'ONE_VALUE' or 'TWO_VALUE'.

Eyasu
  • 240
  • 3
  • 13