Jpa is a database framework that I have always recommended to use in Springboot and microservice projects. Because the official api is not very friendly and easy to use, it is inconvenient for many people to use. Here is the code after I encapsulated the api. It greatly reduces the difficulty of use.
Effect display
First, let's look directly at the final result:
For example, there is an entity called PtActivity, which has a Repository.
public interface PtActivityRepository extends JpaRepository<PtActivity, Long>,
JpaSpecificationExecutor<PtActivity> {
}After inheriting the JpaSpecificationExecutor, it has such a method:
Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
That is, pass in a Specification object to complete the condition query. Let's take a simple example. MySpecification is an encapsulated tool class, which can greatly simplify jpa's operation of building conditional queries.
private Page<PtActivity> find(String states, String name, String begin, String end, Pageable pageable) {
MySpecification<PtActivity> mySpecification = new MySpecification<>();
String[] stateArray = states.split(",");
if (begin != null) {
mySpecification.add(Restrictions.gte("createTime", CommonUtil.beginOfDay(begin), true));
}
if (end != null) {
mySpecification.add(Restrictions.lte("createTime", CommonUtil.endOfDay(end), true));
}
mySpecification.add(Restrictions.in("state", Arrays.asList(stateArray), true));
mySpecification.add(Restrictions.like("name", name, true));
mySpecification.add(Restrictions.eq("deleteFlag", false, true));
return ptActivityManager.findAll(mySpecification, pageable);
}The demo constructs a query condition that createTime is greater than begin and less than end, the value of state field is within an array range, the name field like s a passed value, and the deleteFlag field is equal to false. If a field does not pass a value, the filter condition is ignored.
In this way, the code looks easy to understand. Here is a slightly more complex example:
public void find() {
MySpecification<PtActivity> criteriaQueryBuilder = new MySpecification<>();
criteriaQueryBuilder.addAll(Restrictions.pickSome("id","state"));
//criteriaQueryBuilder.add(Restrictions.sum("id"));
//criteriaQueryBuilder.add(Restrictions.max("state"));
criteriaQueryBuilder.add(Restrictions.gte("createTime", CommonUtil.beginOfDay("2019-05-01"), true));
criteriaQueryBuilder.add(Restrictions.lte("createTime", CommonUtil.endOfDay("2019-05-31"), true));
//criteriaQueryBuilder.add(Restrictions.groupBy("state"));
List<Tuple> tuples = criteriaQueryBuilder.findResult(em, PtActivity.class);
for (Tuple tuple : tuples) {
Object count = tuple.get(0);
System.out.println(count);
}
}This method only queries the id and state fields, and the createTime is within a certain time range. If you release the comment, you will query the sum(id),max(state) and group by state fields.
Detailed analysis
What is Specification
Returning to the interface of Jpa, we can see that to complete a query, the main work is to build a Specification, and the Specification interface is mainly a method, that is, toPredicate method. This method is to construct the where condition of the select * from table where xxxxx statement. Other not and are the intersection and union of Specification, that is, and and or in the where statement.
public interface JpaSpecificationExecutor<T> {
Optional<T> findOne(@Nullable Specification<T> var1);
List<T> findAll(@Nullable Specification<T> var1);
Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
List<T> findAll(@Nullable Specification<T> var1, Sort var2);
long count(@Nullable Specification<T> var1);
}public interface Specification<T> extends Serializable {
long serialVersionUID = 1L;
static <T> Specification<T> not(Specification<T> spec) {
return Specifications.negated(spec);
}
static <T> Specification<T> where(Specification<T> spec) {
return Specifications.where(spec);
}
default Specification<T> and(Specification<T> other) {
return Specifications.composed(this, other, CompositionType.AND);
}
default Specification<T> or(Specification<T> other) {
return Specifications.composed(this, other, CompositionType.OR);
}
@Nullable
Predicate toPredicate(Root<T> var1, CriteriaQuery<?> var2, CriteriaBuilder var3);
}We can understand that everything we need to do is to build a Predicate object, which combines N query sub statements.
So what we need to do is to build multiple Predicate objects according to the fields passed from the front end, and then assemble these multiple predicates into a Predicate object to complete the construction of conditional query.
If the official api is used to complete a complex condition query, the code may be as follows:
public void findTemp() {
ptActivityManager.findAll(new Specification<PtActivity>() {
@Override
public Predicate toPredicate(Root<PtActivity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder
criteriaBuilder) {
Path idPath = root.get("id");
Predicate predicate1 = criteriaBuilder.equal(idPath, "12345");
Path statePath = root.get("state");
Predicate predicate2 = criteriaBuilder.equal(statePath, "1");
return criteriaBuilder.and(predicate1, predicate2);
}
});
}At a glance, it's actually quite messy. What ghosts are root, criteriaQuery and criteriaBuilder, and how to build predict? Novices are at a loss. Let's solve the puzzle. What are these ghosts.
Parsing native underlying queries
In fact, to complete a condition query, its process is as follows:
public List<Tuple> findResult(EntityManager entityManager, Class<T> t) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<T> root = criteriaQuery.from(t);
if (!selectorList.isEmpty()) {
criteriaQuery.multiselect(buildSelections(criteriaBuilder, root));
}
if (!criterionList.isEmpty()) {
criteriaQuery.groupBy(buildGroupBy(root));
}
criteriaQuery.where(toPredicate(root, criteriaQuery, criteriaBuilder));
return entityManager.createQuery(criteriaQuery).getResultList();
}First get the EntityManager, then get the CriteriaBuilder from the EntityManager, then create a CriteriaQuery from the CriteriaBuilder, then combine all conditions into the CriteriaQuery, and finally pass the EntityManager createQuery(CriteriaQuery). Getresultlist() to get the query results.
For example, a query is as follows: select a, B, sum (c) from table where a > 0 and C < 1 group by A
Then a, b and sum(c) belong to the select parameter in CriteriaQuery, the conditions after where belong to the parameters after where in CriteriaQuery, and groupBy and having belong to the corresponding parameters of CriteriaQuery. Finally, it is combined into a full CriteriaQuery, and the EntityManager creates the query and obtains the result set.
You can see that there is a very complete construction method. What we need to do is to combine the objects after select into Selection objects, and the objects after where into predict objects. We can combine having and groupBy according to the attribute type.
How to build these Selection and Predicate objects depends on the CriteriaBuilder.
The arrow methods in CriteriaBuilder are used to build Selection.
These are built to predict.
As for having and groupBy, it is simpler to use root Just get ("field name").
Knowing these, the problem is simpler. What we need to do is to encapsulate the methods of building these three combinations. Otherwise, it's inconvenient to use these provided by the above official.
How does jpaspecification executor understand
We know that when we use this findAll(Specification var1) at ordinary times, we only need to build the Predicate.
The root, CriteriaQuery and builder have been assigned by Jpa. We only need to focus on the construction of Predicate, that is, this findAll method can only complete the construction of where conditions, but can not realize the selection of attributes after select and the construction of groupBy.
How does JPA assign values to root? In fact, JPA is a specification, which is implemented by hibernate and OpenJPA. For example, Springboot uses hibernate to implement JPA by default, that is, the set of EntityManager mentioned in the previous section. Hibernate creates CriteriaQuery, Builder and root, and assigns values to the parameters in the figure above for users to use, To build the Predicate object required by the where condition.
Coding encapsulation API
If you understand all the above, you can code. We can build the encapsulation of Selection, Predicate and Expression, and complete all single table complex queries.
Define an ultimate interface:
/**
* It is applicable to sum, avg, count and other operations on a single table. The query conditions are not fixed, and the predicate needs to be generated dynamically</p>
* For example, select sum (a), count (b), count distinct (c) from table where a =& b = ?
*
* @author wuweifeng wrote on 2018/1/3.
*/
public interface CriteriaQueryBuilder<T> extends Specification<T> {
/**
* Build select field
*/
List<Selection<?>> buildSelections(CriteriaBuilder builder, Root<T> root);
/**
* Build groupBy field
*/
List<Expression<?>> buildGroupBy(Root<T> root);
/**
* Gets the returned result set
*/
List<Tuple> findResult(EntityManager entityManager, Class<T> t);
}As long as the four methods (including toPredicate in the Specification) are completed, you can get the desired result set from findResult.
Provide an implementation class:
package com.maimeng.jd.global.specify;
import com.maimeng.jd.global.specify.simple.IExpression;
import com.maimeng.jd.global.specify.simple.IPredicate;
import com.maimeng.jd.global.specify.simple.ISelector;
import javax.persistence.EntityManager;
import javax.persistence.Tuple;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;
/**
* Define a query criteria container to build where criteria, select fields, and groupBy fields
*
* @author wuwf on 17/6/6.
*/
public class NbQueryBuilder<T> implements CriteriaQueryBuilder<T> {
private List<IPredicate> criterionList = new ArrayList<>();
private List<ISelector> selectorList = new ArrayList<>();
private List<IExpression> expressionList = new ArrayList<>();
@Override
public List<Selection<?>> buildSelections(CriteriaBuilder builder, Root<T> root) {
List<Selection<?>> selections = new ArrayList<>();
for (ISelector iSelector : selectorList) {
selections.add(iSelector.getSelection(root, builder));
}
return selections;
}
@Override
public List<Expression<?>> buildGroupBy(Root<T> root) {
List<Expression<?>> expressions = new ArrayList<>();
for (IExpression expression : expressionList) {
expressions.add(expression.getGroupBy(root));
}
return expressions;
}
@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
if (!criterionList.isEmpty()) {
List<Predicate> predicates = new ArrayList<>();
for (IPredicate c : criterionList) {
predicates.add(c.toPredicate(root, criteriaBuilder));
}
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
}
return criteriaBuilder.conjunction();
}
@Override
public List<Tuple> findResult(EntityManager entityManager, Class<T> t) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<T> root = criteriaQuery.from(t);
if (!selectorList.isEmpty()) {
criteriaQuery.multiselect(buildSelections(criteriaBuilder, root));
}
if (!criterionList.isEmpty()) {
criteriaQuery.groupBy(buildGroupBy(root));
}
criteriaQuery.where(toPredicate(root, criteriaQuery, criteriaBuilder));
return entityManager.createQuery(criteriaQuery).getResultList();
}
/**
* Add simple conditional expression
*/
public void add(ISelector iSelector) {
if (iSelector != null) {
selectorList.add(iSelector);
}
}
/**
* Add where sub statement
*/
public void add(IPredicate iPredicate) {
if (iPredicate != null) {
criterionList.add(iPredicate);
}
}
/**
* Add tail statement
*/
public void add(IExpression iExpression) {
if (iExpression != null) {
expressionList.add(iExpression);
}
}
public <R extends ISelector> void addAll(List<R> selectors) {
selectorList.addAll(selectors);
}
}Finally, it is used in the service as follows:
public void find() {
NbQueryBuilder<PtActivity> criteriaQueryBuilder = new NbQueryBuilder<>();
criteriaQueryBuilder.addAll(Restrictions.pickSome("id", "state"));
//criteriaQueryBuilder.add(Restrictions.sum("id"));
//criteriaQueryBuilder.add(Restrictions.max("state"));
criteriaQueryBuilder.add(Restrictions.gte("createTime", CommonUtil.beginOfDay("2019-05-01"), true));
criteriaQueryBuilder.add(Restrictions.lte("createTime", CommonUtil.endOfDay("2019-05-31"), true));
//criteriaQueryBuilder.add(Restrictions.groupBy("state"));
List<Tuple> tuples = criteriaQueryBuilder.findResult(em, PtActivity.class);
for (Tuple tuple : tuples) {
Object count = tuple.get(0);
System.out.println(count);
}
}Of course, if you don't need to build Selection and groupBy, you can just build Predicate and use jpa's findAll() method.
The code structure is as follows, which are some encapsulation of construction conditions and a factory class of Restrictions.
Because there are many codes, I won't post them one by one. If you can understand the full text, you should be able to write it yourself.
If you can't write it, you can go to my open source blockchain platform project https://gitee.com/tianyalei/md_blockchain Find the contact information and ask for the code.
It should be noted that this encapsulation is for a single table and does not encapsulate multi table joint queries, because I have never done any foreign key or multi table cascade queries.