23090

Spring JPA custom query with combination of parameters in WHERE condition?

Question:

How to write JPA query in Spring Data that uses at least one of three parameters?

I have these three parameters:

<ol><li>Id (PK)</li> <li>Name</li> <li>Surname</li> </ol>

Client must supply at least one of these three parameters and I want to find user by these not-empty parameters.

Is it possible to create such custom query to my repository or do I have to create custom queries for all possible combination of WHERE conditions?

Answer1:

You can have your repository extend org.springframework.data.querydsl.QueryDslPredicateExecutor and use the inherited findAll(Predicate predicate) method to query using any combination of parameters.

You would not then have to write any query methods:

<a href="http://docs.spring.io/spring-data/jpa/docs/1.10.5.RELEASE/reference/html/#core.extensions.querydsl" rel="nofollow">http://docs.spring.io/spring-data/jpa/docs/1.10.5.RELEASE/reference/html/#core.extensions.querydsl</a>

You can also have the Predicate automatically bound in a Spring MVC Controller as detailed here:

<a href="https://spring.io/blog/2015/09/04/what-s-new-in-spring-data-release-gosling#querydsl-web-support" rel="nofollow">https://spring.io/blog/2015/09/04/what-s-new-in-spring-data-release-gosling#querydsl-web-support</a>

and here:

<a href="https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#core.web.type-safe" rel="nofollow">https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#core.web.type-safe</a>

So your controller can then automatically handle a search with 1,2 or all 3 parameters passed as request parameters without your having to write any code at all.

Answer2:

This is where you need the ability to create dynamic queries at runtime.

In your application code, you should have logic to build the predicate based on whether each of the above properties from the input DTO is empty or not.

One way to do it is to use QueryDSL. To use QueryDSL, you should include the relevant dependency in your pom/gradle file and then your repository should extend the QueryDslPredicateExecutor interface. This will give you two additional generic finder methods.

T findAll(Predicate) or Page<T> findAll(Predicate, Pageable)

One thing to keep in mind is that the above two methods are appropriate where this is no need to do a join

Your requirement here seems to be a single table query, so either one of the finder methods should suffice.

If you do need to do a join with other tables and need fine grained control over how the JOIN happens (INNER JOIN vs LEFT OUTER JOIN vs CROSS JOIN) etc, then you should consider creating a Custom repository that your repository can extend. And then provide your own customImpl that you can now access from the repository.

Answer3:

thanks a lot for the reply ... as i read Spring is not easy flexible as other framework... for i.e i tried some node js api framework and it is easier and more flexible .. what do you think about this ?

Recommend

  • goJS dropdown remove items
  • Failed to find version-tag string. File must be updated
  • C# Excel interop - how to test if interop object is still working and performing a task?
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Why does java tzupdater add leap seconds?
  • using System.Speech.Synthesis with Windows10 universal app (XAML-C#)
  • GAE: Way to get reference to an HttpSession from its ID?
  • Angular2 component view does not update on value change via method
  • Spring boot 2.0.0.M4 required a bean named 'entityManagerFactory' that could not be found
  • Eliminate partial duplicate rows from result set
  • CakePHP ACL tutorial initDB function warnings
  • What is the purpose of TaskExecutor in spring?
  • Is it possible to access block's scope in method?
  • why do I get the error when installing the gem 'pg'? [duplicate]
  • Syntax for setting draggablecursor property in google maps api
  • Seeking advice on Jetty HttpClient Hang
  • does jqgrid support a multiple checkbox list for editing
  • Why is an OPTIONS request sent to the server?
  • Pass value from viewmodel to script in zk
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • Optimizing database types to compact database (SQLite)
  • How do I fake an specific browser client when using Java's Net library?
  • swift auto completion not working in Xcode6-Beta
  • Excel - Autoshape get it's name from cell (value)
  • Trying to switch camera back to front but getting exception
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • Do I've to free mysql result after storing it?
  • QuartzCore.framework for Mono Develop
  • Python - Map / Reduce - How do I read JSON specific field in using DISCO count words example
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • RestKit - RKRequestDelegate does not exist
  • bootstrap to use multiple ng-app
  • SetUp method failed while running tests from teamcity
  • How to get icons for entities from eclipse?
  • Proper way to use connect-multiparty with express.js?
  • Load html files in TinyMce
  • Understanding cpu registers
  • -fvisibility=hidden not passed by compiler for Debug builds
  • Turn off referential integrity in Derby? is it possible?
  • JaxB to read class hierarchy