spring data jpa 动态查询（工具类封装）
利用JPA的Specification<T>接口和元模型就实现动态查询了。但是这样每一个需要动态查询的地方都需要写一个这样类似的findByConditions方法，小型项目还好，大型项目中其实会造成人力资源的浪费，进行了大量的重复工作，所以想着对动态查询进行封装，使其使用起来更加方便。
在开发中,用到动态查询的地方,所有的查询条件包括分页参数,都会被封装成一个查询类XxxQuery，我们封装的思路是创建一个BaseQuery类，在其中实现动态查询的封装，即提供几个模板方法，将查询类的所有属性按照连接规则，拼装成一个Specification型的对象返回，那么问题来了，如何去标识这些字段该用怎样的查询条件连接呢,还要考虑到每个查询类都可以通用，可以用字段注解,来标识字段的查询连接条件。
创建枚举类MatchType，列出所有的连接条件
package powerx.io; public enum MatchType { equal, // filed = value //下面四个用于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 // 下面四个用于可比较类型(Comparable)的比较 greaterThan, // field > value greaterThanOrEqualTo, // field >= value lessThan, // field < value lessThanOrEqualTo // field <= value }
自定义注解，用来标识字段
package powerx.io; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) @Documented public @interface QueryCondition { // 数据库中字段名,默认为空字符串,则Query类中的字段要与数据库中字段一致 String column() default ""; // equal, like, gt, lt... MatchType func() default MatchType.equal; // object是否可以为null boolean nullable() default false; // 字符串是否可为空 boolean emptyable() default false; }
改造查询实体
package powerx.io.bean; import org.springframework.data.jpa.domain.Specification; import powerx.io.MatchType; import powerx.io.QueryCondition; public class ProductQuery { @QueryCondition(func=MatchType.like) private String name; @QueryCondition(func=MatchType.le) private Double price; public String getName() { return name; } public void setName(String name) { this.name = name; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } }
下面是最核心的部分，公共查询实体类，其中在toSpecWithLogicType方法中利用反射机制，将所有的属性按照注解的规则加入到动态查询条件中
package powerx.io.bean; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.persistence.criteria.Path; import javax.persistence.criteria.Predicate; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.data.jpa.domain.Specification; import powerx.io.QueryCondition; public abstract class BaseQuery<T> { // start from 0 protected int pageIndex = 0; protected int pageSize = 10; /** * 将查询转换成Specification * @return */ public abstract Specification<T> toSpec(); //JPA分页查询类 public Pageable toPageable() { return new PageRequest(pageIndex, pageSize); } //JPA分页查询类,带排序条件 public Pageable toPageable(Sort sort) { return new PageRequest(pageIndex, pageSize, sort); } //动态查询and连接 protected Specification<T> toSpecWithAnd() { return this.toSpecWithLogicType("and"); } //动态查询or连接 protected Specification<T> toSpecWithOr() { return this.toSpecWithLogicType("or"); } //logicType or/and @SuppressWarnings({ "rawtypes", "unchecked" }) 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注解 QueryCondition qw = field.getAnnotation(QueryCondition.class); if (qw == null) continue; // 获取字段名 String column = qw.column(); //如果主注解上colume为默认值"",则以field为准 if (column.equals("")) column = field.getName(); field.setAccessible(true); try { // nullable Object value = field.get(outerThis); //如果值为null,注解未标注nullable,跳过 if (value == null && !qw.nullable()) continue; // can be empty if (value != null && String.class.isAssignableFrom(value.getClass())) { String s = (String) value; //如果值为"",且注解未标注emptyable,跳过 if (s.equals("") && !qw.emptyable()) continue; } //通过注解上func属性,构建路径表达式 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，则直接返回当前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)).//不重复字段 forEach(field -> fieldList.add(field)); } return fieldList; } public int getPageIndex() { return pageIndex; } public void setPageIndex(int pageIndex) { this.pageIndex = pageIndex; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } }
在BaseQuery里,就通过toSpecWithAnd() toSpecWithOr()方法动态构建出了查询条件。那现在ItemQuery就要继承BaseQuery,并实现toSpec()抽象方法
package powerx.io.bean; import org.springframework.data.jpa.domain.Specification; import powerx.io.MatchType; import powerx.io.QueryCondition; public class ProductQuery extends BaseQuery<Product>{ @QueryCondition(func=MatchType.like) private String name; @QueryCondition(func=MatchType.le) private Double price; public String getName() { return name; } public void setName(String name) { this.name = name; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } @Override public Specification<Product> toSpec() { return super.toSpecWithAnd(); } }
当然肯定还有其他不能在BaseQuery中构建的查询条件,比如说查询价格在某个区间的情况，那就在子类的toSpec()实现中添加
@Override public Specification<Product> toSpec() { Specification<Product> spec = super.toSpecWithAnd(); return ((root, criteriaQuery, criteriaBuilder) -> { List<Predicate> predicatesList = new ArrayList<>(); predicatesList.add(spec.toPredicate(root, criteriaQuery, criteriaBuilder)); if (priceMin != null) { predicatesList.add( criteriaBuilder.and( criteriaBuilder.ge( root.get(Product_.price), priceMin))); } if (priceMax != null) { predicatesList.add( criteriaBuilder.and( criteriaBuilder.le( root.get(Product_.price), priceMax))); } return criteriaBuilder.and(predicatesList.toArray(new Predicate[predicatesList.size()])); }); }
调用代码
public Page<Product> findByConditions3(String name, Double price) { ProductQuery pq = new ProductQuery(); pq.setName(name); pq.setPrice(price); return productDao.findAll(pq.toSpec(), pq.toPageable()); }
响应如下：
现在这个BaseQuery和QuertWord就可以在各个动态查询处使用了,只需在查询字段上标注@QueryWord注解。然后实现BaseQuery中的抽象方法toSpec(),通过JpaSpecificationExecutor接口中的这几个方法,就可以实现动态查询了。
