Spring JPA Repository Custom Query

Question

This custom query works(this is just a basic query to illustrate the problem):

public interface BookQueryRepositoryExample extends Repository<Book, Long> {
    @Query(value = "select * from Book b where b.name=?1", nativeQuery = true)
    List<Book> findByName(String name);
}

but I need another custom query where the where clause will be constructed dynamically before calling the method.

public interface BookQueryRepositoryExample extends Repository<Book, Long> {
    @Query(value = "select * from Book b where ?1", nativeQuery = true)
    List<Book> findByWhatever(String qry);
}

But I am not able to make it work. Is there any workaround?

Updated: 6/16/2017

Just want to mention this that the field I am searching is 'denormalized' form. The values can look like these(below). So my query has a series of like statements

Sample 1:

name:John Smith;address1:123 Xyz St;city:New York;zip:12345;country:USA;id:ABC1234;email:js@abc.com;

Sample 2:Rearranged

address1:123 Xyz St;zip:12345;email:js@abc.com;name:John Smith;country:USA;id:ABC1234;city:New York;

Sample 3:Missing strings/text

zip:12345;email:js@abc.com;name:John Smith;id:ABC1234;city:New York;

No, as that is not how parameter substitution works. Why not use JPQL or Criteria queries to do dynamic queries. (Your first custom query you don't even need only the method `findByName` is already enough to achieve what you want. — M. Deinum, Jun 13 '17 at 18:36
Ok. Will look into that Criteria queries. Thanks. I did not really use that findByName method. I kinda just put it out there for emphasis. — alltej, Jun 13 '17 at 21:17
You can visit this answer https://stackoverflow.com/a/50365522/3073945 — Md. Sajedul Karim, Jun 30 '18 at 06:11

score 2 · Answer 1 · answered Jun 14 '17 at 04:46

This won't work, at least not with this approach.

The placeholders in a query don't just get replaced with some arbitrary String, but are variables, that can only stand in for something you would provide as a literal otherwise.

But as @M. Deinum pointed out there are alternatives: You can write a custom method and use

JPA Criteria API
JPQL
Specifications
QueryDSL

See this article for some examples: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

score 2 · Answer 2 · edited Feb 03 '22 at 15:16

2

Work around for this would be like, you can have a class to execute dynamic queries by injecting the EntityManager as shown below:

//Pseudo code
@Repository
public class SomeDao {

    @PersistenceContext
    private EntityManager entityManager;
    
    public List<Book> findByWhatever(String qry){
        Query q = entityManager.createNativeQuery(qry);
        List<Object[]> books = q.getResultList();

        // Your logic goes here

        // return something
    }
}

edited Feb 03 '22 at 15:16

ProfiGrantler

3
3

answered Jun 14 '17 at 06:16

PRATHAP S

675
2
8
26

I need to implement it using the springframework Repository or JpaRepository. Any thoughts? – alltej Jun 16 '17 at 13:38
JPARepository, I had tried long ago but hard luck. Later implemented the same as shown above. As `Spring-Boot` can inject the `EnityManager` where ever is required. This need no extra configurations to do. You can also go with Criteria's – PRATHAP S Jun 19 '17 at 06:28
No where in the JPARepository documentation they have mentioned about passing the dynamic queries. I think need to do some trick – PRATHAP S Jun 19 '17 at 06:30

score 2 · Answer 3 · answered Jun 14 '17 at 13:53

For Example If you want to find the Book based on combination of the attribute like authorName,title and cost then You can use the following query

public interface BookQueryRepositoryExample extends Repository<Book, Long> { @Query(value = "select * from Book b where (?1 or null) and (?2 or null) and (?3 or null) ", nativeQuery = true ) List<Book> findByWhatever(String authorName,String title,Double cost); }

score 1 · Answer 4 · answered Jun 16 '17 at 12:36

1

You can create dynamic where clauses using Specification interface that spring-data provides.

Here is a link for you: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

answered Jun 16 '17 at 12:36

Ozan Kılıç

571
2
8

score 0 · Answer 5 · answered Jun 18 '17 at 13:31

@Query(value = "select * from Book b where ?1", nativeQuery = true) List findByWhatever(String qry);

First of all, your approach is not recommended and most likely will lead to SQL injection vulnerability (if you do not handle 'qry' parameter in a proper way).

Secondly, you are trying to reinvent the wheel. There are a lot of possible ways of implementing dynamic queries as @Jens Schauder has already mentioned in his answer. I will add one more way which seems to be the easiest one if you do not need complex stuff. It's called "Query by Example".

public interface BookRepository extends JpaRepository<Book, Long>{
}

Then you create an instance of an object that looks like those that you are trying to find, meaning that you have to set properties that you would use for a dynamic query generation:

Book book = new Book();
book.setYear(2015);
book.setPublisher("O'Realy")

Example<Book> bookExample = Example.of(book);

The last step is to pass your example object to the Spring Data JPA repository:

List<Book> books = bookRepository.findAll(bookExample);

As a result, you will get a list of books published in 2015 by O'Realy. The nice thing about it is that you can add more fields to search for in runtime just by setting it in book instance.

And if you need something more complex than match by exact values, you could use matchers. In the sample below Spring Data JPA will search for all books with a name starting with "O" ignoring case.

Book book = new Book();
book.setName("O")

ExampleMatcher matcher = ExampleMatcher.matching().
    .withMatcher("publisher", startsWith().ignoreCase());

Example<Book> bookExample = Example.of(book, matcher);

List<Book> books = bookRepository.findAll(bookExample);

Spring JPA Repository Custom Query

5 Answers5