Changes

Upgrade spring-data-jpa dependency to 2.0.5.RELEASE.

Gradle

repositories {
    jcenter()
}

dependencies {
    compile 'com.github.wenhao:jpa-spec:3.2.1'
}

Maven

<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.2.1</version>
</dependency>

Specification By Examples:

Each specification support three parameters:

condition: if true(default), apply this specification.
property: field name.
values: compare value with model, eq/ne/like support multiple values.

General Example

each Repository class should extends from two super class JpaRepository and JpaSpecificationExecutor.

public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {
}    

public Page<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
            .gt(Objects.nonNull(request.getAge()), "age", 18)
            .between("birthday", Range.of(inclusive(new Date()), inclusive(new Date())))
            .like("nickName", "%og%", "%me")
            .build();

    return personRepository.findAll(specification, new PageRequest(0, 15));
}

Equal/NotEqual Example

find any person nickName equals to “dog” and name equals to “Jack”/”Eric” or null value, and company is null.

Test: EqualTest.java and NotEqualTest.java

public List<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .eq("nickName", "dog")
            .eq(StringUtils.isNotBlank(request.getName()), "name", "Jack", "Eric", null)
            .eq("company", null) //or eq("company", (Object) null)
            .build();

    return personRepository.findAll(specification);
}

In/NotIn Example

find any person name in “Jack” or “Eric” and company not in “ThoughtWorks” or “IBM”.

Test: InTest.java

public List<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .in("name", request.getNames().toArray()) //or in("name", "Jack", "Eric")
            .notIn("company", "ThoughtWorks", "IBM")
            .build();

    return personRepository.findAll(specification);
}

Comparison Example

Support any comparison class which implements Comparable interface, find any people age bigger than 18.

Test: GtTest.java

public List<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .gt(Objects.nonNull(request.getAge()), "age", 18)
            .lt("birthday", new Date())
            .build();

    return personRepository.findAll(specification);
}

Between Example

find any person age between 18 and 25, birthday between someday and someday.

Test: BetweenTest.java

public List<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .between(Objects.nonNull(request.getAge(), "age", Range.of(inclusive(18), inclusive(25)))
            .between("birthday", Range.of(inclusive(new Date()), inclusive(new Date())))
            .build();

    return personRepository.findAll(specification);
}  

Like/NotLike Example

find any person name like %ac% or %og%, company not like %ec%.

Test: LikeTest.java and NotLikeTest.java

public Page<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .like("name", "ac", "%og%")
            .notLike("company", "ec")
            .build();

    return personRepository.findAll(specification);
}

Or

support or specifications.

Test: OrTest.java

public List<Phone> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>or()
                    .like("name", "%ac%")
                    .gt("age", 19)
                    .build();

    return phoneRepository.findAll(specification);
}

Join

each specification support association query as left join.

Test: JoinTest.java

@ManyToOne association query, find person name equals to “Jack” and phone brand equals to “HuaWei”.

public List<Phone> findAll(SearchRequest request) {
    Specification<Phone> specification = Specifications.<Phone>and()
        .eq(StringUtils.isNotBlank(request.getBrand()), "brand", "HuaWei")
        .eq(StringUtils.isNotBlank(request.getPersonName()), "person.name", "Jack")
        .build();

    return phoneRepository.findAll(specification);
}

@ManyToMany association query, find person age between 10 and 35, live in “Chengdu” street.

public List<Phone> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
        .between("age", Range.of(inclusive(10), inclusive(35)))
        .eq(StringUtils.isNotBlank(jack.getName()), "addresses.street", "Chengdu")
        .build();

    return phoneRepository.findAll(specification);
}

Custom Specification

You can custom specification to do the @ManyToOne and @ManyToMany as well.

@ManyToOne association query, find person name equals to “Jack” and phone brand equals to “HuaWei”.

Test: AndTest.java

public List<Phone> findAll(SearchRequest request) {
    Specification<Phone> specification = Specifications.<Phone>and()
        .eq(StringUtils.isNotBlank(request.getBrand()), "brand", "HuaWei")
        .predicate(StringUtils.isNotBlank(request.getPersonName()), (root, query, cb) -> {
            Path<Person> person = root.get("person");
            return cb.equal(person.get("name"), "Jack");
        })
        .build();

    return phoneRepository.findAll(specification);
}

@ManyToMany association query, find person age between 10 and 35, live in “Chengdu” street.

Test: AndTest.java

public List<Phone> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
        .between("age", Range.of(inclusive(10), inclusive(35)))
        .predicate(StringUtils.isNotBlank(jack.getName()), ((root, query, cb) -> {
            Join address = root.join("addresses", JoinType.LEFT);
            return cb.equal(address.get("street"), "Chengdu");
        }))
        .build();

    return phoneRepository.findAll(specification);
}

Sort

Test: SortTest.java

public List<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
            .gt("age", 18)
            .between("birthday", Range.of(inclusive(new Date()), inclusive(new Date())))
            .like("nickName", "%og%")
            .build();

    Sort sort = Sorts.builder()
        .desc(StringUtils.isNotBlank(request.getName()), "name")
        .asc("birthday")
        .build();

    return personRepository.findAll(specification, sort);
}

Pagination

find person by pagination and sort by name desc and birthday asc.

public Page<Person> findAll(SearchRequest request) {
    Specification<Person> specification = Specifications.<Person>and()
            .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
            .gt("age", 18)
            .between("birthday", Range.of(inclusive(new Date()), inclusive(new Date())))
            .like("nickName", "%og%")
            .build();

    Sort sort = Sorts.builder()
        .desc(StringUtils.isNotBlank(request.getName()), "name")
        .asc("birthday")
        .build();

    return personRepository.findAll(specification, new PageRequest(0, 15, sort));
}

Virtual View

Using @org.hibernate.annotations.Subselect to define a virtual view if you don’t want a database table view.

There is no difference between a view and a database table for a Hibernate mapping.

Test: VirtualViewTest.java

@Entity
@Immutable
@Subselect("SELECT p.id, p.name, p.age, ic.number " +
           "FROM person p " +
           "LEFT JOIN id_card ic " +
           "ON p.id_card_id=ic.id")
public class PersonIdCard {
    @Id
    private Long id;
    private String name;
    private Integer age;
    private String number;

    // Getters and setters are omitted for brevity
}    

public List<PersonIdCard> findAll(SearchRequest request) {
    Specification<PersonIdCard> specification = Specifications.<PersonIdCard>and()
            .gt(Objects.nonNull(request.getAge()), "age", 18)
            .build();

    return personIdCardRepository.findAll(specification);
}

Projection, GroupBy, Aggregation

Spring Data JPA doesn’t support Projection(a little but trick), GroupBy and Aggregation,

furthermore, Projection/GroupBy/Aggregation are often used for complex statistics report, it might seem like overkill to use Hibernate/JPA ORM to solve it.

Alternatively, using virtual view and give a readable/significant class name to against your problem domain may be a better option.

Copyright and license

Licensed under [Apache License]

jpa-spec

A JPA Query By Specification framework.