CODEWITHSUNDEEP
spring-bootspring-dataspring-data-jpa
alltej
alltej

Reputation: 7285

Spring JPA Repository Custom Query

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;

Upvotes: 2

Views: 17513

Answers (5)

PRATHAP S
PRATHAP S

Reputation: 775

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

Upvotes: 2

Danylo Zatorsky
Danylo Zatorsky

Reputation: 6124

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

Upvotes: 0

Ozan Kılı&#231;
Ozan Kılı&#231;

Reputation: 591

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/

Upvotes: 1

Sathyendran a
Sathyendran a

Reputation: 1819

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

Upvotes: 2

Jens Schauder
Jens Schauder

Reputation: 81998

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/

Upvotes: 2

Related Questions