java jpa框架,jpa-spec一个JAP查询规范框架

jpa-spec

Inspired by Legacy Hibernate Criteria Queries, while this should be considered deprecated vs JPA APIs,

but it still productive and easily understandable. Build on Spring Data JPA and simplify the dynamic query process.

Features

Compatible with Spring Data JPA and JPA 2.1 interface.

Equal/NotEqual/Like/NotLike/In/NotIn support multiple values, Equal/NotEqual support Null value.

Each specification support join query(left joiner).

Support custom specification.

Builder style specification creator.

Support pagination and sort builder.

Docs

Gradle

repositories {

jcenter()

}

dependencies {

implementation 'com.github.wenhao:jpa-spec:3.2.5'

}

Maven

com.github.wenhao

jpa-spec

3.2.5

Build

./gradlew clean build

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, JpaSpecificationExecutor {

}

public Page findAll(SearchRequest request) {

Specification specification = Specifications.and()

.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())

.gt(Objects.nonNull(request.getAge()), "age", 18)

.between("birthday", new Date(), 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.

public List findAll(SearchRequest request) {

Specification specification = Specifications.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".

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.in("name", request.getNames())

.notIn("company", Arrays.asList("ThoughtWorks", "IBM"))

.build();

return personRepository.findAll(specification);

}

Comparison Example

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

public List findAll(SearchRequest request) {

Specification specification = Specifications.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.

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.between(Objects.nonNull(request.getAge(), "age", 18, 25)

.between("birthday", new Date(), new Date())

.build();

return personRepository.findAll(specification);

}

Like/NotLike Example

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

public Page findAll(SearchRequest request) {

Specification specification = Specifications.and()

.like("name", "ac", "%og%")

.notLike("company", "ec")

.build();

return personRepository.findAll(specification);

}

Or

support or specifications.

public List findAll(SearchRequest request) {

Specification specification = Specifications.or()

.like("name", "%ac%")

.gt("age", 19)

.build();

return phoneRepository.findAll(specification);

}

Mixed And and Or

support mixed and and or specifications.

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.like("name", "%ac%")

.predicate(Specifications.or()

.lt("age", 19)

.gt("age", 25)

.build())

.build();

return personRepository.findAll(specification);

}

Join

each specification support association query as left join.

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

public List findAll(SearchRequest request) {

Specification specification = Specifications.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 findAll(SearchRequest request) {

Specification specification = Specifications.and()

.between("age", 10, 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".

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.eq(StringUtils.isNotBlank(request.getBrand()), "brand", "HuaWei")

.predicate(StringUtils.isNotBlank(request.getPersonName()), (root, query, cb) -> {

Path 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.

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.between("age", 10, 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

public List findAll(SearchRequest request) {

Specification specification = Specifications.and()

.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())

.gt("age", 18)

.between("birthday", new Date(), 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 findAll(SearchRequest request) {

Specification specification = Specifications.and()

.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())

.gt("age", 18)

.between("birthday", new Date(), new Date())

.like("nickName", "%og%")

.build();

Sort sort = Sorts.builder()

.desc(StringUtils.isNotBlank(request.getName()), "name")

.asc("birthday")

.build();

return personRepository.findAll(specification, PageRequest.of(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.

@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 findAll(SearchRequest request) {

Specification specification = Specifications.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

Copyright © 2016-2019 Wen Hao

Licensed under MIT License