JPA自定义查询

蔚落 2023-10-04 17:49 63阅读 0赞

在使用JPA过程中，写nativeQuery无法满足所有需求，只能寻求其他的途径，下面是一种方法

/**
    * 增加过滤条件
    *
    * @return
    */
    private Specification<User> listUserSpec(BizUserQueryCriteria criteria) {
        
       return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        
           List<Predicate> list = new ArrayList<>();
           //常规查询条件
           list = QueryHelp.getList(list, root, criteria, cb);
           // order by id desc
           query.orderBy(cb.desc(root.get("id")));
           //子查询开始
    
           Subquery<User> subquery = query.subquery(User.class);
           Root<User> root1 = subquery.from(User.class);
           //select id from
           subquery = subquery.select(root1.get("id"));
    
           List<Predicate> sub = new ArrayList();
           if (criteria.getGradeFlag()) {
          // 年级不在指定值范围内
               String[] grades = new String[]{
        "六年级","初三"};
               sub.add(cb.not(root1.get("grade").in(Arrays.asList(grades))));
               //or grade not in ('六年级','初三')
           }
           if (criteria.getSchoolTypeFlag()) {
         // 学校类型不在指定值范围内
               String[] schoolTypes = new String[]{
        "小学","初中"};
               sub.add(cb.not(root1.get("schoolType").in(Arrays.asList(schoolTypes))));
               //or school_type not in ('小学','初中')
           }
           if (criteria.getCardNumFlag()) {
         // 身份证号码不符合规范
               sub.add(cb.and(cb.equal(root1.get("cardType"), "身份证"), cb.and(cb.notEqual(cb.length(cb.trim(root1.get("cardNum"))), 18), cb.notEqual(cb.length(cb.trim(root1.get("cardNum"))), 15))));
               //or (card_type` = '身份证' and length(`card_num`) <> 18 and length(`card_num`) <> 15)
           }
           if (criteria.getGenderFlag()) {
         // 性别为空
               sub.add(cb.or(cb.isNull(root1.get("gender")), cb.equal(cb.trim(root1.get("gender")), "")));
               //or gender is null
           }
           if (criteria.getSchoolYearFlag()) {
         // 学年不符合规范
               sub.add(cb.notEqual(cb.length(cb.trim(root1.get("schoolYear"))),15));
               //or (length(`school_year`) <> 15)
           }
           if (criteria.getSchoolYearNullFlag()) {
         //学年为空
               sub.add(cb.isNull(root1.get("schoolYear")));
               //or school_year is null
           }
           if (criteria.getHouseholdTypeFlag()) {
         // 户籍类型为空
               sub.add(cb.isNull(root1.get("householdType")));
               //or household_type is null
           }
           if (criteria.getRegionFlag()) {
         //区域或城市为空
               sub.add(cb.and(cb.isNull(root1.get("region")), cb.isNull(root1.get("outsideCity"))));
               //or (region is null and outside_city is null)
           }
           if (criteria.getCurrentAddressFlag()) {
         // 住址为空
               sub.add(cb.isNull(root1.get("currentAddress")));
               //or current_address is null
           }
           if (criteria.getAvatarPathFlag()) {
         // 头像为空
               sub.add(cb.isNull(root1.get("avatarPath")));
               //or avatar_path is null
           }
           int subSize = sub.size();
           if (subSize > 0) {
        
               Predicate predicate = cb.or(sub.toArray(new Predicate[subSize]));
               subquery = subquery.where(predicate);
               list.add(cb.and(root.get("id").in(subquery)));
           }
           int size = list.size();
           return cb.and(list.toArray(new Predicate[size]));
       };
    }
    
    // 调用
    public List<User> queryAll(BizUserQueryCriteria criteria) {
        
    	List<User> users = userRepository.findAll(listUserSpec(criteria));
    }

可以研究一下 `CriteriaBuilder` ，有很多可能用得到的方法

package javax.persistence.criteria;
    
    import java.math.BigDecimal;
    import java.math.BigInteger;
    import java.sql.Date;
    import java.sql.Time;
    import java.sql.Timestamp;
    import java.util.Collection;
    import java.util.Map;
    import java.util.Set;
    import javax.persistence.Tuple;
    
    public interface CriteriaBuilder {
        
        CriteriaQuery<Object> createQuery();
    
        <T> CriteriaQuery<T> createQuery(Class<T> var1);
    
        CriteriaQuery<Tuple> createTupleQuery();
    
        <T> CriteriaUpdate<T> createCriteriaUpdate(Class<T> var1);
    
        <T> CriteriaDelete<T> createCriteriaDelete(Class<T> var1);
    
        <Y> CompoundSelection<Y> construct(Class<Y> var1, Selection<?>... var2);
    
        CompoundSelection<Tuple> tuple(Selection<?>... var1);
    
        CompoundSelection<Object[]> array(Selection<?>... var1);
    
        Order asc(Expression<?> var1);
    
        Order desc(Expression<?> var1);
    
        <N extends Number> Expression<Double> avg(Expression<N> var1);
    
        <N extends Number> Expression<N> sum(Expression<N> var1);
    
        Expression<Long> sumAsLong(Expression<Integer> var1);
    
        Expression<Double> sumAsDouble(Expression<Float> var1);
    
        <N extends Number> Expression<N> max(Expression<N> var1);
    
        <N extends Number> Expression<N> min(Expression<N> var1);
    
        <X extends Comparable<? super X>> Expression<X> greatest(Expression<X> var1);
    
        <X extends Comparable<? super X>> Expression<X> least(Expression<X> var1);
    
        Expression<Long> count(Expression<?> var1);
    
        Expression<Long> countDistinct(Expression<?> var1);
    
        Predicate exists(Subquery<?> var1);
    
        <Y> Expression<Y> all(Subquery<Y> var1);
    
        <Y> Expression<Y> some(Subquery<Y> var1);
    
        <Y> Expression<Y> any(Subquery<Y> var1);
    
        Predicate and(Expression<Boolean> var1, Expression<Boolean> var2);
    
        Predicate and(Predicate... var1);
    
        Predicate or(Expression<Boolean> var1, Expression<Boolean> var2);
    
        Predicate or(Predicate... var1);
    
        Predicate not(Expression<Boolean> var1);
    
        Predicate conjunction();
    
        Predicate disjunction();
    
        Predicate isTrue(Expression<Boolean> var1);
    
        Predicate isFalse(Expression<Boolean> var1);
    
        Predicate isNull(Expression<?> var1);
    
        Predicate isNotNull(Expression<?> var1);
    
        Predicate equal(Expression<?> var1, Expression<?> var2);
    
        Predicate equal(Expression<?> var1, Object var2);
    
        Predicate notEqual(Expression<?> var1, Expression<?> var2);
    
        Predicate notEqual(Expression<?> var1, Object var2);
    
        <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> var1, Expression<? extends Y> var2);
    
        <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> var1, Y var2);
    
        <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> var1, Expression<? extends Y> var2);
    
        <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> var1, Y var2);
    
        <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> var1, Expression<? extends Y> var2);
    
        <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> var1, Y var2);
    
        <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> var1, Expression<? extends Y> var2);
    
        <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> var1, Y var2);
    
        <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> var1, Expression<? extends Y> var2, Expression<? extends Y> var3);
    
        <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> var1, Y var2, Y var3);
    
        Predicate gt(Expression<? extends Number> var1, Expression<? extends Number> var2);
    
        Predicate gt(Expression<? extends Number> var1, Number var2);
    
        Predicate ge(Expression<? extends Number> var1, Expression<? extends Number> var2);
    
        Predicate ge(Expression<? extends Number> var1, Number var2);
    
        Predicate lt(Expression<? extends Number> var1, Expression<? extends Number> var2);
    
        Predicate lt(Expression<? extends Number> var1, Number var2);
    
        Predicate le(Expression<? extends Number> var1, Expression<? extends Number> var2);
    
        Predicate le(Expression<? extends Number> var1, Number var2);
    
        <N extends Number> Expression<N> neg(Expression<N> var1);
    
        <N extends Number> Expression<N> abs(Expression<N> var1);
    
        <N extends Number> Expression<N> sum(Expression<? extends N> var1, Expression<? extends N> var2);
    
        <N extends Number> Expression<N> sum(Expression<? extends N> var1, N var2);
    
        <N extends Number> Expression<N> sum(N var1, Expression<? extends N> var2);
    
        <N extends Number> Expression<N> prod(Expression<? extends N> var1, Expression<? extends N> var2);
    
        <N extends Number> Expression<N> prod(Expression<? extends N> var1, N var2);
    
        <N extends Number> Expression<N> prod(N var1, Expression<? extends N> var2);
    
        <N extends Number> Expression<N> diff(Expression<? extends N> var1, Expression<? extends N> var2);
    
        <N extends Number> Expression<N> diff(Expression<? extends N> var1, N var2);
    
        <N extends Number> Expression<N> diff(N var1, Expression<? extends N> var2);
    
        Expression<Number> quot(Expression<? extends Number> var1, Expression<? extends Number> var2);
    
        Expression<Number> quot(Expression<? extends Number> var1, Number var2);
    
        Expression<Number> quot(Number var1, Expression<? extends Number> var2);
    
        Expression<Integer> mod(Expression<Integer> var1, Expression<Integer> var2);
    
        Expression<Integer> mod(Expression<Integer> var1, Integer var2);
    
        Expression<Integer> mod(Integer var1, Expression<Integer> var2);
    
        Expression<Double> sqrt(Expression<? extends Number> var1);
    
        Expression<Long> toLong(Expression<? extends Number> var1);
    
        Expression<Integer> toInteger(Expression<? extends Number> var1);
    
        Expression<Float> toFloat(Expression<? extends Number> var1);
    
        Expression<Double> toDouble(Expression<? extends Number> var1);
    
        Expression<BigDecimal> toBigDecimal(Expression<? extends Number> var1);
    
        Expression<BigInteger> toBigInteger(Expression<? extends Number> var1);
    
        Expression<String> toString(Expression<Character> var1);
    
        <T> Expression<T> literal(T var1);
    
        <T> Expression<T> nullLiteral(Class<T> var1);
    
        <T> ParameterExpression<T> parameter(Class<T> var1);
    
        <T> ParameterExpression<T> parameter(Class<T> var1, String var2);
    
        <C extends Collection<?>> Predicate isEmpty(Expression<C> var1);
    
        <C extends Collection<?>> Predicate isNotEmpty(Expression<C> var1);
    
        <C extends Collection<?>> Expression<Integer> size(Expression<C> var1);
    
        <C extends Collection<?>> Expression<Integer> size(C var1);
    
        <E, C extends Collection<E>> Predicate isMember(Expression<E> var1, Expression<C> var2);
    
        <E, C extends Collection<E>> Predicate isMember(E var1, Expression<C> var2);
    
        <E, C extends Collection<E>> Predicate isNotMember(Expression<E> var1, Expression<C> var2);
    
        <E, C extends Collection<E>> Predicate isNotMember(E var1, Expression<C> var2);
    
        <V, M extends Map<?, V>> Expression<Collection<V>> values(M var1);
    
        <K, M extends Map<K, ?>> Expression<Set<K>> keys(M var1);
    
        Predicate like(Expression<String> var1, Expression<String> var2);
    
        Predicate like(Expression<String> var1, String var2);
    
        Predicate like(Expression<String> var1, Expression<String> var2, Expression<Character> var3);
    
        Predicate like(Expression<String> var1, Expression<String> var2, char var3);
    
        Predicate like(Expression<String> var1, String var2, Expression<Character> var3);
    
        Predicate like(Expression<String> var1, String var2, char var3);
    
        Predicate notLike(Expression<String> var1, Expression<String> var2);
    
        Predicate notLike(Expression<String> var1, String var2);
    
        Predicate notLike(Expression<String> var1, Expression<String> var2, Expression<Character> var3);
    
        Predicate notLike(Expression<String> var1, Expression<String> var2, char var3);
    
        Predicate notLike(Expression<String> var1, String var2, Expression<Character> var3);
    
        Predicate notLike(Expression<String> var1, String var2, char var3);
    
        Expression<String> concat(Expression<String> var1, Expression<String> var2);
    
        Expression<String> concat(Expression<String> var1, String var2);
    
        Expression<String> concat(String var1, Expression<String> var2);
    
        Expression<String> substring(Expression<String> var1, Expression<Integer> var2);
    
        Expression<String> substring(Expression<String> var1, int var2);
    
        Expression<String> substring(Expression<String> var1, Expression<Integer> var2, Expression<Integer> var3);
    
        Expression<String> substring(Expression<String> var1, int var2, int var3);
    
        Expression<String> trim(Expression<String> var1);
    
        Expression<String> trim(CriteriaBuilder.Trimspec var1, Expression<String> var2);
    
        Expression<String> trim(Expression<Character> var1, Expression<String> var2);
    
        Expression<String> trim(CriteriaBuilder.Trimspec var1, Expression<Character> var2, Expression<String> var3);
    
        Expression<String> trim(char var1, Expression<String> var2);
    
        Expression<String> trim(CriteriaBuilder.Trimspec var1, char var2, Expression<String> var3);
    
        Expression<String> lower(Expression<String> var1);
    
        Expression<String> upper(Expression<String> var1);
    
        Expression<Integer> length(Expression<String> var1);
    
        Expression<Integer> locate(Expression<String> var1, Expression<String> var2);
    
        Expression<Integer> locate(Expression<String> var1, String var2);
    
        Expression<Integer> locate(Expression<String> var1, Expression<String> var2, Expression<Integer> var3);
    
        Expression<Integer> locate(Expression<String> var1, String var2, int var3);
    
        Expression<Date> currentDate();
    
        Expression<Timestamp> currentTimestamp();
    
        Expression<Time> currentTime();
    
        <T> CriteriaBuilder.In<T> in(Expression<? extends T> var1);
    
        <Y> Expression<Y> coalesce(Expression<? extends Y> var1, Expression<? extends Y> var2);
    
        <Y> Expression<Y> coalesce(Expression<? extends Y> var1, Y var2);
    
        <Y> Expression<Y> nullif(Expression<Y> var1, Expression<?> var2);
    
        <Y> Expression<Y> nullif(Expression<Y> var1, Y var2);
    
        <T> CriteriaBuilder.Coalesce<T> coalesce();
    
        <C, R> CriteriaBuilder.SimpleCase<C, R> selectCase(Expression<? extends C> var1);
    
        <R> CriteriaBuilder.Case<R> selectCase();
    
        <T> Expression<T> function(String var1, Class<T> var2, Expression<?>... var3);
    
        <X, T, V extends T> Join<X, V> treat(Join<X, T> var1, Class<V> var2);
    
        <X, T, E extends T> CollectionJoin<X, E> treat(CollectionJoin<X, T> var1, Class<E> var2);
    
        <X, T, E extends T> SetJoin<X, E> treat(SetJoin<X, T> var1, Class<E> var2);
    
        <X, T, E extends T> ListJoin<X, E> treat(ListJoin<X, T> var1, Class<E> var2);
    
        <X, K, T, V extends T> MapJoin<X, K, V> treat(MapJoin<X, K, T> var1, Class<V> var2);
    
        <X, T extends X> Path<T> treat(Path<X> var1, Class<T> var2);
    
        <X, T extends X> Root<T> treat(Root<X> var1, Class<T> var2);
    
        public interface Case<R> extends Expression<R> {
        
            CriteriaBuilder.Case<R> when(Expression<Boolean> var1, R var2);
    
            CriteriaBuilder.Case<R> when(Expression<Boolean> var1, Expression<? extends R> var2);
    
            Expression<R> otherwise(R var1);
    
            Expression<R> otherwise(Expression<? extends R> var1);
        }
    
        public interface SimpleCase<C, R> extends Expression<R> {
        
            Expression<C> getExpression();
    
            CriteriaBuilder.SimpleCase<C, R> when(C var1, R var2);
    
            CriteriaBuilder.SimpleCase<C, R> when(C var1, Expression<? extends R> var2);
    
            Expression<R> otherwise(R var1);
    
            Expression<R> otherwise(Expression<? extends R> var1);
        }
    
        public interface Coalesce<T> extends Expression<T> {
        
            CriteriaBuilder.Coalesce<T> value(T var1);
    
            CriteriaBuilder.Coalesce<T> value(Expression<? extends T> var1);
        }
    
        public interface In<T> extends Predicate {
        
            Expression<T> getExpression();
    
            CriteriaBuilder.In<T> value(T var1);
    
            CriteriaBuilder.In<T> value(Expression<? extends T> var1);
        }
    
        public static enum Trimspec {
        
            LEADING,
            TRAILING,
            BOTH;
    
            private Trimspec() {
        
            }
        }
    }