How to fetch Data using JPA for dynamic Where Clause

Question

I am new to JPA. I am currently using JPA2.0 in WAS 8.5.5

I have a search screen where we have lot of search criteria's. Now, I have to create Query in JPA such a way that any criteria user has selected, it automatically check for that particular column in DB.

I am not able to find any solution on that. It seems to me that for every search criteria, I have to write new named Query.

Any suggestion or pointers will be appreciated.

Predrag Maric · Accepted Answer · 2015-04-29 12:42:59Z

2

You could do it in named query, but you would have to check every single possible criteria if it is null in order to avoid null values affect the results. Beware of unnecessary inner joins, each nullable relation should be included as left join

select e from Employee e left join e.department d
where (:name is null or e.name = :name)
  and (:email is null or e.email = :email)
  and (:deptId is null or d.id = :deptId)
...

However, depending on complexity of possible combinations, better option could be to not use named queries, but dynamically construct a JPQL depending on selected criteria.

answered Apr 29, 2015 at 12:42

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fetch Data using JPA for dynamic Where Clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related