Spring Data JPA: Generate dynamic query

Question

I have an entity that hold some logic data :

@Entity
public class Person {
  private Long id.
  private String name;
  private int age;
  private String address;
  ...
}

I create my Spring data interface

@Repository
public interface CardInventoryRepository extends JpaRepository<Person , Long> {
}

My purpose is to create a dynamic query based on the exist values of my entity for example if the name is null the query is :

select * from Person p  Where p.age=12 AND p.address="adress.."

When the address is null the query should be :

select * from Person p  Where p.age=12 AND p.name="ALI"

I want to extract data using only the non empty fields ?

is there any solution suing spring data for building dynamic queries ? Thanks in advance

score 2 · Answer 1 · answered Sep 16 '18 at 13:13

Yes, please take a look at the QueryDSL support for Spring Data. Your use case can be implemented via a Predicate. In a nutshell, you have to create a predicate in which you would pass the non null fields, and then pass that predicate to a findAll method that takes a Predicate as argument. Your repository interface also has to extend QueryDslPredicateExecutor

score 2 · Accepted Answer · answered Sep 16 '18 at 13:27

Based on Spring doc https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require you to write queries that contain field names. In fact, Query by Example does not require you to write queries by using store-specific query languages at all.

DEFINITION: An Example takes a data object (usually the entity object or a sub-type of it) and a specification how to match properties. You can use Query by Example with JPA Repositories.

To do so, let your repository interface extend QueryByExampleExecutor<T>, for example:

public interface PersonRepository extends CrudRepository<Person, String>, QueryByExampleExecutor<Person> {
}

Here are the available methods in QueryByExampleExecutor :

public interface QueryByExampleExecutor<T> {

  <S extends T> S findOne(Example<S> example);

  <S extends T> Iterable<S> findAll(Example<S> example);

  // … more functionality omitted.
}

USAGES:

Example<Person> example = Example.of(new Person("Jon", "Snow"));
repo.findAll(example);


ExampleMatcher matcher = ExampleMatcher.matching().
    .withMatcher("firstname", endsWith())
    .withMatcher("lastname", startsWith().ignoreCase());

Example<Person> example = Example.of(new Person("Jon", "Snow"), matcher); 
repo.count(example);

MORE INFO

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

https://github.com/spring-projects/spring-data-examples/tree/master/jpa/query-by-example

Spring Data JPA: Query by Example?

score 0 · Answer 3 · edited Mar 31 '22 at 10:25

Need to extend repository from JpaSpecificationExecutor

@Repository
@Transactional
public interface EmployeeDAO extends CrudRepository<Employee,Long>,JpaSpecificationExecutor<Employee>{
}

Use specification and predicate like below

public List<Employee> findByCriteria(String employeeName,String employeeRole){
       return employeeDAO.findAll(new Specification<Employee>() {
           @Override
           public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
               List<Predicate> predicates = new ArrayList<>();
               if(employeeName!=null) {
                   predicates.add(criteriaBuilder.and(criteriaBuilder.like(root.get("employeeName"), "%"+employeeName+"%")));
               }
               if(employeeRole!=null){
                   predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeRole"), employeeRole)));
               }
               return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
           }
       });
   }

Spring Data JPA: Generate dynamic query

3 Answers3

Linked