Changes

升级依赖spring-data-jpa: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>

Maven排除项目已存在的依赖

<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.2.1</version>
    <exclusions>
        <exclusion>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
        </exclusion>
    </exclusions>
</dependency>

条件查询例子

每个条件查询支持三个参数:

condition: 如果为true(默认)，应用此条件查询。
property: 字段名称。
values: 具体查询的值，eq/ne/like 支持多个值。

例子

每个 Repository 类需要继承两个类 JpaRepository 和 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例子

查询任何昵称等于 “dog”，名字等于 “Jack”/”Eric”或为null并且公司也为null的人。

Test: EqualTest.java 和 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例子

查询任何名字等于 “Jack” 或 “Eric” 并且公司不等于 “ThoughtWorks” 或 “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);
}

比较例子

支持任何实现Comparable接口的类的比较，查询任何年纪大于等于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例子

查询任何年龄在18到25，生日在某个时间段的人。

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例子

查询任何名字包含 %ac% 或 %og%，公司不包含 %ec% 的人。

Test: LikeTest.java 和 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例子

支持或条件查询。

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

关联查询

每个条件查询都支持左连接查询。

Test: JoinTest.java

多对一查询，查询任何名字等于 “Jack” 并且此人的电话品牌是 “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);
}

多对多查询，查询任何年龄在10到35之间并且其地址在 “Chengdu” 的人。

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

自定义条件查询

你也可以自定义条件查询来实现多对一和多对多查询。

多对一查询，查询任何名字等于 “Jack” 并且此人的电话品牌是 “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);
}

多对多查询，查询任何年龄在10到35之间并且其地址在 “Chengdu” 的人。

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

排序

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

分页

分页并按照名字倒序生日升序查询。

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

虚拟视图

如果你不想使用数据库视图(数据库依赖)，可以 @org.hibernate.annotations.Subselect 虚拟视图代替(灵活修改/提升可读性)。

对于 Hibernate 映射来说虚拟视图和数据库视图没任何区别。

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

投射、分组和聚合

Spring Data JPA对投射、分组和聚合支持不是很好，

此外，投射、分组和聚合支大多数用在比较复杂的统计报表或性能要求比较高的查询，如果使用 Hibernate/JPA 来对象关系映射来解决可能有点过于复杂了。

或者，使用虚拟视图并给一个易读的、有意义的类名来解决特定的问题也许是一个不错的选择。

Copyright and license

Licensed under [Apache License]

jpa-spec

A JPA Query By Specification framework.