In development, where dynamic queries are used, all query criteria, including paging parameters, are encapsulated into a query class called XxxQuery
For example, Item from the previous article
So an ItemQuery would look something like this
@Data
public class ItemQuery {
private Integer itemId;//id Exact query =
private String itemName;//name Fuzzy query like
// Price query
private Integer itemPrice;// The price is less than 'condition' <
}Copy the codeThe question now is how to identify which query criteria should be used to join these fields, considering that each query class is generic.
Field annotations can be used to identify query join conditions for fields
// Use enumeration classes to represent query connection conditions
public enum MatchType {
equal, // filed = value
// The following four are used for comparison of type Number
gt, // filed > value
ge, // field >= value
lt, // field < value
le, // field <= value
notEqual, // field ! = value
like, // field like value
notLike, // field not like value
The following four are used for comparisons of Comparable types
greaterThan, // field > value
greaterThanOrEqualTo, // field >= value
lessThan, // field < value
lessThanOrEqualTo, // field <= value
;
}Copy the code@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface QueryWord {
// The name of the field in the database. Default is an empty string, so the fields in the Query class must be the same as the fields in the database
String column(a) default "";
// equal, like, gt, lt...
MatchType func(a) default MatchType.equal;
// Whether object can be null
boolean nullable(a) default false;
// Whether the string can be null
boolean emptiable(a) default false;
}Copy the codeOk, now we can modify the ItemQuery
@Data
public class ItemQuery {
@QueryWord(column = "item_id", func = MatchType.equal)
private Integer itemId;
@QueryWord(func = MatchType.like)
private String itemName;
@QueryWord(func = MatchType.le)
private Integer itemPrice;
}Copy the codeNow, we also need to construct dynamic conditions for the query. We will create a base class for all query classes, BaseQuery, and we will put the paging condition fields in the base class.
/** * The base class for all query classes */
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public abstract class BaseQuery<T> {
// start from 0
protected int pageIndex = 0;
protected int pageSize = 10;
/** * convert the query to Specification *@return* /
public abstract Specification<T> toSpec(a);
//JPA paging query class
public Pageable toPageable(a) {
return new PageRequest(pageIndex, pageSize);
}
//JPA paging query class with sorting criteria
public Pageable toPageable(Sort sort) {
return new PageRequest(pageIndex, pageSize, sort);
}
// Dynamic query and join
protected Specification<T> toSpecWithAnd(a) {
return this.toSpecWithLogicType("and");
}
// Dynamic query or join
protected Specification<T> toSpecWithOr(a) {
return this.toSpecWithLogicType("or");
}
//logicType or/and
private Specification<T> toSpecWithLogicType(String logicType) {
BaseQuery outerThis = this;
return (root, criteriaQuery, cb) -> {
Class clazz = outerThis.getClass();
// Get all the fields of the Query class Query, including the superclass fields
List<Field> fields = getAllFieldsWithRoot(clazz);
List<Predicate> predicates = new ArrayList<>(fields.size());
for (Field field : fields) {
// Get the @queryword annotation on the field
QueryWord qw = field.getAnnotation(QueryWord.class);
if (qw == null)
continue;
// Get the field name
String column = qw.column();
// If colume on the main annotation is the default value "", field prevails
if (column.equals(""))
column = field.getName();
field.setAccessible(true);
try {
// nullable
Object value = field.get(outerThis);
// If the value is null, the annotation is not marked with nullable, skip
if (value == null && !qw.nullable())
continue;
// can be empty
if(value ! =null && String.class.isAssignableFrom(value.getClass())) {
String s = (String) value;
// If the value is "" and the annotation is not emptyable, skip
if (s.equals("") && !qw.emptiable())
continue;
}
// Build the path expression with the func attribute on the annotation
Path path = root.get(column);
switch (qw.func()) {
case equal:
predicates.add(cb.equal(path, value));
break;
case like:
predicates.add(cb.like(path, "%" + value + "%"));
break;
case gt:
predicates.add(cb.gt(path, (Number) value));
break;
case lt:
predicates.add(cb.lt(path, (Number) value));
break;
case ge:
predicates.add(cb.ge(path, (Number) value));
break;
case le:
predicates.add(cb.le(path, (Number) value));
break;
case notEqual:
predicates.add(cb.notEqual(path, value));
break;
case notLike:
predicates.add(cb.notLike(path, "%" + value + "%"));
break;
case greaterThan:
predicates.add(cb.greaterThan(path, (Comparable) value));
break;
case greaterThanOrEqualTo:
predicates.add(cb.greaterThanOrEqualTo(path, (Comparable) value));
break;
case lessThan:
predicates.add(cb.lessThan(path, (Comparable) value));
break;
case lessThanOrEqualTo:
predicates.add(cb.lessThanOrEqualTo(path, (Comparable) value));
break; }}catch (Exception e) {
continue;
}
}
Predicate p = null;
if (logicType == null || logicType.equals("") || logicType.equals("and")) {
p = cb.and(predicates.toArray(new Predicate[predicates.size()]));/ / and connections
} else if (logicType.equals("or")) {
p = cb.or(predicates.toArray(new Predicate[predicates.size()]));/ / or connection
}
return p;
};
}
// Get all fields of class clazz, including fields of its parent class
private List<Field> getAllFieldsWithRoot(Class
clazz) {
List<Field> fieldList = new ArrayList<>();
Field[] dFields = clazz.getDeclaredFields();// Get all fields of this class
if (null! = dFields && dFields.length >0)
fieldList.addAll(Arrays.asList(dFields));
// If the parent class is Object, the current Field list is returnedClass<? > superClass = clazz.getSuperclass();if (superClass == Object.class) return Arrays.asList(dFields);
// Recursively query the field list of the parent class
List<Field> superFields = getAllFieldsWithRoot(superClass);
if (null! = superFields && ! superFields.isEmpty()) { superFields.stream(). filter(field -> ! fieldList.contains(field)).// Do not repeat fields
forEach(field -> fieldList.add(field));
}
returnfieldList; }}Copy the codeIn BaseQuery, the query criteria are dynamically constructed using the toSpecWithAnd() toSpecWithOr() method.
Now ItemQuery inherits BaseQuery and implements the toSpec() abstract method
@Data
public class ItemQuery extends BaseQuery<Item> {
@QueryWord(column = "item_id", func = MatchType.equal)
private Integer itemId;
@QueryWord(func = MatchType.like)
private String itemName;
@QueryWord(func = MatchType.le)
private Integer itemPrice;
@Override
public Specification<Item> toSpec(a) {
return super.toSpecWithAnd();// all conditions are connected with and}}Copy the codeThere are certainly other query criteria that cannot be built in BaseQuery, so add them to the toSpec() implementation of subclasses,
For example, in the following example, the ItemQuery condition is changed to this
@QueryWord(column = "item_id", func = MatchType.equal)
private Integer itemId;
@QueryWord(func = MatchType.like)
private String itemName;
// Price range query
private Integer itemPriceMin;
private Integer itemPriceMax;Copy the codeOther criteria can then be added in toSpec(), which gives you flexibility in building query criteria
@Override
public Specification<Item> toSpec(a) {
Specification<Item> spec = super.toSpecWithAnd();
return ((root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicatesList = new ArrayList<>();
predicatesList.add(spec.toPredicate(root, criteriaQuery, criteriaBuilder));
if(itemPriceMin ! =null) {
predicatesList.add(
criteriaBuilder.and(
criteriaBuilder.ge(
root.get(Item_.itemPrice), itemPriceMin)));
}
if(itemPriceMax ! =null) {
predicatesList.add(
criteriaBuilder.and(
criteriaBuilder.le(
root.get(Item_.itemPrice), itemPriceMax)));
}
return criteriaBuilder.and(predicatesList.toArray(new Predicate[predicatesList.size()]));
});
}Copy the codeCall:
@Test
public void test1(a) throws Exception {
ItemQuery itemQuery = new ItemQuery();
itemQuery.setItemName("Car");
itemQuery.setItemPriceMax(50);
itemQuery.setItemPriceMax(200);
Pageable pageable = itemQuery.toPageable(new Sort(Sort.Direction.ASC, "itemId"));
Page<Item> all = itemRepository.findAll(itemQuery.toSpec(), pageable);
}Copy the codeBaseQuery and QuertWord can now be used in various dynamic queries by annotating @QueryWord on the query field.
ToSpec (), the abstract method in BaseQuery, is implemented through the JpaSpecificationExecutor interface.
public interface JpaSpecificationExecutor<T> {
T findOne(Specification<T> var1);
List<T> findAll(Specification<T> var1);
Page<T> findAll(Specification<T> var1, Pageable var2);
List<T> findAll(Specification<T> var1, Sort var2);
long count(Specification<T> var1);
}Copy the codeThe original link: Spring – Data – JPA dynamic query yao black science and technology | fire