Search code examples
javaspringspring-bootspring-data-jpaspring-data

Spring Data JPA: Generate dynamic query

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

Solution

  • 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