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:[email protected];

Sample 2:Rearranged

address1:123 Xyz St;zip:12345;email:[email protected];name:John Smith;country:USA;id:ABC1234;city:New York;

Sample 3:Missing strings/text

zip:12345;email:[email protected];name:John Smith;id:ABC1234;city:New York;

Solution 1

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/

Solution 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
    }
}

Solution 3

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); }

Solution 4

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/

View more solutions