在開發(fā)中,用到動(dòng)態(tài)查詢的地方,所有的查詢條件包括分頁(yè)參數(shù),都會(huì)被封裝成一個(gè)查詢類XxxQuery
比如說上一篇中的Item
那么ItemQuery就像這樣
@Data
public class ItemQuery {
private Integer itemId;//id精確查詢 =
private String itemName;//name模糊查詢 like
//價(jià)格查詢
private Integer itemPrice;// 價(jià)格小于'條件' <
}
那現(xiàn)在問題來了,如何去標(biāo)識(shí)這些字段該用怎樣的查詢條件連接呢,還要考慮到每個(gè)查詢類都可以通用.
可以用字段注解,來標(biāo)識(shí)字段的查詢連接條件
//用枚舉類表示查詢連接條件
public enum MatchType {
equal, // filed = value
//下面四個(gè)用于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
// 下面四個(gè)用于可比較類型(Comparable)的比較
greaterThan, // field > value
greaterThanOrEqualTo, // field >= value
lessThan, // field < value
lessThanOrEqualTo, // field <= value
;
}
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface QueryWord {
// 數(shù)據(jù)庫(kù)中字段名,默認(rèn)為空字符串,則Query類中的字段要與數(shù)據(jù)庫(kù)中字段一致
String column() default "";
// equal, like, gt, lt...
MatchType func() default MatchType.equal;
// object是否可以為null
boolean nullable() default false;
// 字符串是否可為空
boolean emptiable() default false;
}
好了,現(xiàn)在我們可以改造一下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;
}
現(xiàn)在,我們還需要去構(gòu)造出查詢時(shí)的動(dòng)態(tài)條件,那就創(chuàng)建一個(gè)所有查詢類的基類BaseQuery,我們把分頁(yè)的條件字段放在基類里.
/**
* 所有查詢類的基類
*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public abstract class BaseQuery<T> {
// start from 0
protected int pageIndex = 0;
protected int pageSize = 10;
/**
* 將查詢轉(zhuǎn)換成Specification
* @return
*/
public abstract Specification<T> toSpec();
//JPA分頁(yè)查詢類
public Pageable toPageable() {
return new PageRequest(pageIndex, pageSize);
}
//JPA分頁(yè)查詢類,帶排序條件
public Pageable toPageable(Sort sort) {
return new PageRequest(pageIndex, pageSize, sort);
}
//動(dòng)態(tài)查詢and連接
protected Specification<T> toSpecWithAnd() {
return this.toSpecWithLogicType("and");
}
//動(dòng)態(tài)查詢or連接
protected Specification<T> toSpecWithOr() {
return this.toSpecWithLogicType("or");
}
//logicType or/and
private Specification<T> toSpecWithLogicType(String logicType) {
BaseQuery outerThis = this;
return (root, criteriaQuery, cb) -> {
Class clazz = outerThis.getClass();
//獲取查詢類Query的所有字段,包括父類字段
List<Field> fields = getAllFieldsWithRoot(clazz);
List<Predicate> predicates = new ArrayList<>(fields.size());
for (Field field : fields) {
//獲取字段上的@QueryWord注解
QueryWord qw = field.getAnnotation(QueryWord.class);
if (qw == null)
continue;
// 獲取字段名
String column = qw.column();
//如果主注解上colume為默認(rèn)值"",則以field為準(zhǔn)
if (column.equals(""))
column = field.getName();
field.setAccessible(true);
try {
// nullable
Object value = field.get(outerThis);
//如果值為null,注解未標(biāo)注nullable,跳過
if (value == null && !qw.nullable())
continue;
// can be empty
if (value != null && String.class.isAssignableFrom(value.getClass())) {
String s = (String) value;
//如果值為"",且注解未標(biāo)注emptyable,跳過
if (s.equals("") && !qw.emptiable())
continue;
}
//通過注解上func屬性,構(gòu)建路徑表達(dá)式
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連接
} else if (logicType.equals("or")) {
p = cb.or(predicates.toArray(new Predicate[predicates.size()]));//or連接
}
return p;
};
}
//獲取類clazz的所有Field，包括其父類的Field
private List<Field> getAllFieldsWithRoot(Class<?> clazz) {
List<Field> fieldList = new ArrayList<>();
Field[] dFields = clazz.getDeclaredFields();//獲取本類所有字段
if (null != dFields && dFields.length > 0)
fieldList.addAll(Arrays.asList(dFields));
// 若父類是Object，則直接返回當(dāng)前Field列表
Class<?> superClass = clazz.getSuperclass();
if (superClass == Object.class) return Arrays.asList(dFields);
// 遞歸查詢父類的field列表
List<Field> superFields = getAllFieldsWithRoot(superClass);
if (null != superFields && !superFields.isEmpty()) {
superFields.stream().
filter(field -> !fieldList.contains(field)).//不重復(fù)字段
forEach(field -> fieldList.add(field));
}
return fieldList;
}
}
在BaseQuery里,就通過toSpecWithAnd() toSpecWithOr()方法動(dòng)態(tài)構(gòu)建出了查詢條件.
那現(xiàn)在ItemQuery就要繼承BaseQuery,并實(shí)現(xiàn)toSpec()抽象方法
@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() {
return super.toSpecWithAnd();//所有條件用and連接
}
}
當(dāng)然肯定還有其他不能在BaseQuery中構(gòu)建的查詢條件,那就在子類的toSpec()實(shí)現(xiàn)中添加,
比如下面的例子,ItemQuery條件改成這樣
@QueryWord(column = "item_id", func = MatchType.equal)
private Integer itemId;
@QueryWord(func = MatchType.like)
private String itemName;
//價(jià)格范圍查詢
private Integer itemPriceMin;
private Integer itemPriceMax;
那其他條件就可以在toSpec()添加,這樣就可以很靈活的構(gòu)建查詢條件了
@Override
public Specification<Item> toSpec() {
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()]));
});
}
調(diào)用:
@Test
public void test1() throws Exception {
ItemQuery itemQuery = new ItemQuery();
itemQuery.setItemName("車");
itemQuery.setItemPriceMax(50);
itemQuery.setItemPriceMax(200);
Pageable pageable = itemQuery.toPageable(new Sort(Sort.Direction.ASC, "itemId"));
Page<Item> all = itemRepository.findAll(itemQuery.toSpec(), pageable);
}
現(xiàn)在這個(gè)BaseQuery和QuertWord就可以在各個(gè)動(dòng)態(tài)查詢處使用了,只需在查詢字段上標(biāo)注@QueryWord注解,
然后實(shí)現(xiàn)BaseQuery中的抽象方法toSpec(),通過JpaSpecificationExecutor接口中的這幾個(gè)方法,就可以實(shí)現(xiàn)動(dòng)態(tài)查詢了,是不是很方便.
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);
}
