JPA自定义查询

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

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

  1. /**
  2. * 增加过滤条件
  3. *
  4. * @return
  5. */
  6. private Specification<User> listUserSpec(BizUserQueryCriteria criteria) {
  8.    return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
  10.        List<Predicate> list = new ArrayList<>();
  11.        //常规查询条件
  12.        list = QueryHelp.getList(list, root, criteria, cb);
  13.        // order by id desc
  14.        query.orderBy(cb.desc(root.get("id")));
  15.        //子查询开始
  17.        Subquery<User> subquery = query.subquery(User.class);
  18.        Root<User> root1 = subquery.from(User.class);
  19.        //select id from
  20.        subquery = subquery.select(root1.get("id"));
  22.        List<Predicate> sub = new ArrayList();
  23.        if (criteria.getGradeFlag()) {
  24.       // 年级不在指定值范围内
  25.            String[] grades = new String[]{
  26.     "六年级","初三"};
  27.            sub.add(cb.not(root1.get("grade").in(Arrays.asList(grades))));
  28.            //or grade not in ('六年级','初三')
  29.        }
  30.        if (criteria.getSchoolTypeFlag()) {
  31.      // 学校类型不在指定值范围内
  32.            String[] schoolTypes = new String[]{
  33.     "小学","初中"};
  34.            sub.add(cb.not(root1.get("schoolType").in(Arrays.asList(schoolTypes))));
  35.            //or school_type not in ('小学','初中')
  36.        }
  37.        if (criteria.getCardNumFlag()) {
  38.      // 身份证号码不符合规范
  39.            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))));
  40.            //or (card_type` = '身份证' and length(`card_num`) <> 18 and length(`card_num`) <> 15)
  41.        }
  42.        if (criteria.getGenderFlag()) {
  43.      // 性别为空
  44.            sub.add(cb.or(cb.isNull(root1.get("gender")), cb.equal(cb.trim(root1.get("gender")), "")));
  45.            //or gender is null
  46.        }
  47.        if (criteria.getSchoolYearFlag()) {
  48.      // 学年不符合规范
  49.            sub.add(cb.notEqual(cb.length(cb.trim(root1.get("schoolYear"))),15));
  50.            //or (length(`school_year`) <> 15)
  51.        }
  52.        if (criteria.getSchoolYearNullFlag()) {
  53.      //学年为空
  54.            sub.add(cb.isNull(root1.get("schoolYear")));
  55.            //or school_year is null
  56.        }
  57.        if (criteria.getHouseholdTypeFlag()) {
  58.      // 户籍类型为空
  59.            sub.add(cb.isNull(root1.get("householdType")));
  60.            //or household_type is null
  61.        }
  62.        if (criteria.getRegionFlag()) {
  63.      //区域或城市为空
  64.            sub.add(cb.and(cb.isNull(root1.get("region")), cb.isNull(root1.get("outsideCity"))));
  65.            //or (region is null and outside_city is null)
  66.        }
  67.        if (criteria.getCurrentAddressFlag()) {
  68.      // 住址为空
  69.            sub.add(cb.isNull(root1.get("currentAddress")));
  70.            //or current_address is null
  71.        }
  72.        if (criteria.getAvatarPathFlag()) {
  73.      // 头像为空
  74.            sub.add(cb.isNull(root1.get("avatarPath")));
  75.            //or avatar_path is null
  76.        }
  77.        int subSize = sub.size();
  78.        if (subSize > 0) {
  80.            Predicate predicate = cb.or(sub.toArray(new Predicate[subSize]));
  81.            subquery = subquery.where(predicate);
  82.            list.add(cb.and(root.get("id").in(subquery)));
  83.        }
  84.        int size = list.size();
  85.        return cb.and(list.toArray(new Predicate[size]));
  86.    };
  87. }
  89. // 调用
  90. public List<User> queryAll(BizUserQueryCriteria criteria) {
  92.     List<User> users = userRepository.findAll(listUserSpec(criteria));
  93. }

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

  1. package javax.persistence.criteria;
  3. import java.math.BigDecimal;
  4. import java.math.BigInteger;
  5. import java.sql.Date;
  6. import java.sql.Time;
  7. import java.sql.Timestamp;
  8. import java.util.Collection;
  9. import java.util.Map;
  10. import java.util.Set;
  11. import javax.persistence.Tuple;
  13. public interface CriteriaBuilder {
  15.     CriteriaQuery<Object> createQuery();
  17.     <T> CriteriaQuery<T> createQuery(Class<T> var1);
  19.     CriteriaQuery<Tuple> createTupleQuery();
  21.     <T> CriteriaUpdate<T> createCriteriaUpdate(Class<T> var1);
  23.     <T> CriteriaDelete<T> createCriteriaDelete(Class<T> var1);
  25.     <Y> CompoundSelection<Y> construct(Class<Y> var1, Selection<?>... var2);
  27.     CompoundSelection<Tuple> tuple(Selection<?>... var1);
  29.     CompoundSelection<Object[]> array(Selection<?>... var1);
  31.     Order asc(Expression<?> var1);
  33.     Order desc(Expression<?> var1);
  35.     <N extends Number> Expression<Double> avg(Expression<N> var1);
  37.     <N extends Number> Expression<N> sum(Expression<N> var1);
  39.     Expression<Long> sumAsLong(Expression<Integer> var1);
  41.     Expression<Double> sumAsDouble(Expression<Float> var1);
  43.     <N extends Number> Expression<N> max(Expression<N> var1);
  45.     <N extends Number> Expression<N> min(Expression<N> var1);
  47.     <X extends Comparable<? super X>> Expression<X> greatest(Expression<X> var1);
  49.     <X extends Comparable<? super X>> Expression<X> least(Expression<X> var1);
  51.     Expression<Long> count(Expression<?> var1);
  53.     Expression<Long> countDistinct(Expression<?> var1);
  55.     Predicate exists(Subquery<?> var1);
  57.     <Y> Expression<Y> all(Subquery<Y> var1);
  59.     <Y> Expression<Y> some(Subquery<Y> var1);
  61.     <Y> Expression<Y> any(Subquery<Y> var1);
  63.     Predicate and(Expression<Boolean> var1, Expression<Boolean> var2);
  65.     Predicate and(Predicate... var1);
  67.     Predicate or(Expression<Boolean> var1, Expression<Boolean> var2);
  69.     Predicate or(Predicate... var1);
  71.     Predicate not(Expression<Boolean> var1);
  73.     Predicate conjunction();
  75.     Predicate disjunction();
  77.     Predicate isTrue(Expression<Boolean> var1);
  79.     Predicate isFalse(Expression<Boolean> var1);
  81.     Predicate isNull(Expression<?> var1);
  83.     Predicate isNotNull(Expression<?> var1);
  85.     Predicate equal(Expression<?> var1, Expression<?> var2);
  87.     Predicate equal(Expression<?> var1, Object var2);
  89.     Predicate notEqual(Expression<?> var1, Expression<?> var2);
  91.     Predicate notEqual(Expression<?> var1, Object var2);
  93.     <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> var1, Expression<? extends Y> var2);
  95.     <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> var1, Y var2);
  97.     <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> var1, Expression<? extends Y> var2);
  99.     <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> var1, Y var2);
  101.     <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> var1, Expression<? extends Y> var2);
  103.     <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> var1, Y var2);
  105.     <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> var1, Expression<? extends Y> var2);
  107.     <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> var1, Y var2);
  109.     <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> var1, Expression<? extends Y> var2, Expression<? extends Y> var3);
  111.     <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> var1, Y var2, Y var3);
  113.     Predicate gt(Expression<? extends Number> var1, Expression<? extends Number> var2);
  115.     Predicate gt(Expression<? extends Number> var1, Number var2);
  117.     Predicate ge(Expression<? extends Number> var1, Expression<? extends Number> var2);
  119.     Predicate ge(Expression<? extends Number> var1, Number var2);
  121.     Predicate lt(Expression<? extends Number> var1, Expression<? extends Number> var2);
  123.     Predicate lt(Expression<? extends Number> var1, Number var2);
  125.     Predicate le(Expression<? extends Number> var1, Expression<? extends Number> var2);
  127.     Predicate le(Expression<? extends Number> var1, Number var2);
  129.     <N extends Number> Expression<N> neg(Expression<N> var1);
  131.     <N extends Number> Expression<N> abs(Expression<N> var1);
  133.     <N extends Number> Expression<N> sum(Expression<? extends N> var1, Expression<? extends N> var2);
  135.     <N extends Number> Expression<N> sum(Expression<? extends N> var1, N var2);
  137.     <N extends Number> Expression<N> sum(N var1, Expression<? extends N> var2);
  139.     <N extends Number> Expression<N> prod(Expression<? extends N> var1, Expression<? extends N> var2);
  141.     <N extends Number> Expression<N> prod(Expression<? extends N> var1, N var2);
  143.     <N extends Number> Expression<N> prod(N var1, Expression<? extends N> var2);
  145.     <N extends Number> Expression<N> diff(Expression<? extends N> var1, Expression<? extends N> var2);
  147.     <N extends Number> Expression<N> diff(Expression<? extends N> var1, N var2);
  149.     <N extends Number> Expression<N> diff(N var1, Expression<? extends N> var2);
  151.     Expression<Number> quot(Expression<? extends Number> var1, Expression<? extends Number> var2);
  153.     Expression<Number> quot(Expression<? extends Number> var1, Number var2);
  155.     Expression<Number> quot(Number var1, Expression<? extends Number> var2);
  157.     Expression<Integer> mod(Expression<Integer> var1, Expression<Integer> var2);
  159.     Expression<Integer> mod(Expression<Integer> var1, Integer var2);
  161.     Expression<Integer> mod(Integer var1, Expression<Integer> var2);
  163.     Expression<Double> sqrt(Expression<? extends Number> var1);
  165.     Expression<Long> toLong(Expression<? extends Number> var1);
  167.     Expression<Integer> toInteger(Expression<? extends Number> var1);
  169.     Expression<Float> toFloat(Expression<? extends Number> var1);
  171.     Expression<Double> toDouble(Expression<? extends Number> var1);
  173.     Expression<BigDecimal> toBigDecimal(Expression<? extends Number> var1);
  175.     Expression<BigInteger> toBigInteger(Expression<? extends Number> var1);
  177.     Expression<String> toString(Expression<Character> var1);
  179.     <T> Expression<T> literal(T var1);
  181.     <T> Expression<T> nullLiteral(Class<T> var1);
  183.     <T> ParameterExpression<T> parameter(Class<T> var1);
  185.     <T> ParameterExpression<T> parameter(Class<T> var1, String var2);
  187.     <C extends Collection<?>> Predicate isEmpty(Expression<C> var1);
  189.     <C extends Collection<?>> Predicate isNotEmpty(Expression<C> var1);
  191.     <C extends Collection<?>> Expression<Integer> size(Expression<C> var1);
  193.     <C extends Collection<?>> Expression<Integer> size(C var1);
  195.     <E, C extends Collection<E>> Predicate isMember(Expression<E> var1, Expression<C> var2);
  197.     <E, C extends Collection<E>> Predicate isMember(E var1, Expression<C> var2);
  199.     <E, C extends Collection<E>> Predicate isNotMember(Expression<E> var1, Expression<C> var2);
  201.     <E, C extends Collection<E>> Predicate isNotMember(E var1, Expression<C> var2);
  203.     <V, M extends Map<?, V>> Expression<Collection<V>> values(M var1);
  205.     <K, M extends Map<K, ?>> Expression<Set<K>> keys(M var1);
  207.     Predicate like(Expression<String> var1, Expression<String> var2);
  209.     Predicate like(Expression<String> var1, String var2);
  211.     Predicate like(Expression<String> var1, Expression<String> var2, Expression<Character> var3);
  213.     Predicate like(Expression<String> var1, Expression<String> var2, char var3);
  215.     Predicate like(Expression<String> var1, String var2, Expression<Character> var3);
  217.     Predicate like(Expression<String> var1, String var2, char var3);
  219.     Predicate notLike(Expression<String> var1, Expression<String> var2);
  221.     Predicate notLike(Expression<String> var1, String var2);
  223.     Predicate notLike(Expression<String> var1, Expression<String> var2, Expression<Character> var3);
  225.     Predicate notLike(Expression<String> var1, Expression<String> var2, char var3);
  227.     Predicate notLike(Expression<String> var1, String var2, Expression<Character> var3);
  229.     Predicate notLike(Expression<String> var1, String var2, char var3);
  231.     Expression<String> concat(Expression<String> var1, Expression<String> var2);
  233.     Expression<String> concat(Expression<String> var1, String var2);
  235.     Expression<String> concat(String var1, Expression<String> var2);
  237.     Expression<String> substring(Expression<String> var1, Expression<Integer> var2);
  239.     Expression<String> substring(Expression<String> var1, int var2);
  241.     Expression<String> substring(Expression<String> var1, Expression<Integer> var2, Expression<Integer> var3);
  243.     Expression<String> substring(Expression<String> var1, int var2, int var3);
  245.     Expression<String> trim(Expression<String> var1);
  247.     Expression<String> trim(CriteriaBuilder.Trimspec var1, Expression<String> var2);
  249.     Expression<String> trim(Expression<Character> var1, Expression<String> var2);
  251.     Expression<String> trim(CriteriaBuilder.Trimspec var1, Expression<Character> var2, Expression<String> var3);
  253.     Expression<String> trim(char var1, Expression<String> var2);
  255.     Expression<String> trim(CriteriaBuilder.Trimspec var1, char var2, Expression<String> var3);
  257.     Expression<String> lower(Expression<String> var1);
  259.     Expression<String> upper(Expression<String> var1);
  261.     Expression<Integer> length(Expression<String> var1);
  263.     Expression<Integer> locate(Expression<String> var1, Expression<String> var2);
  265.     Expression<Integer> locate(Expression<String> var1, String var2);
  267.     Expression<Integer> locate(Expression<String> var1, Expression<String> var2, Expression<Integer> var3);
  269.     Expression<Integer> locate(Expression<String> var1, String var2, int var3);
  271.     Expression<Date> currentDate();
  273.     Expression<Timestamp> currentTimestamp();
  275.     Expression<Time> currentTime();
  277.     <T> CriteriaBuilder.In<T> in(Expression<? extends T> var1);
  279.     <Y> Expression<Y> coalesce(Expression<? extends Y> var1, Expression<? extends Y> var2);
  281.     <Y> Expression<Y> coalesce(Expression<? extends Y> var1, Y var2);
  283.     <Y> Expression<Y> nullif(Expression<Y> var1, Expression<?> var2);
  285.     <Y> Expression<Y> nullif(Expression<Y> var1, Y var2);
  287.     <T> CriteriaBuilder.Coalesce<T> coalesce();
  289.     <C, R> CriteriaBuilder.SimpleCase<C, R> selectCase(Expression<? extends C> var1);
  291.     <R> CriteriaBuilder.Case<R> selectCase();
  293.     <T> Expression<T> function(String var1, Class<T> var2, Expression<?>... var3);
  295.     <X, T, V extends T> Join<X, V> treat(Join<X, T> var1, Class<V> var2);
  297.     <X, T, E extends T> CollectionJoin<X, E> treat(CollectionJoin<X, T> var1, Class<E> var2);
  299.     <X, T, E extends T> SetJoin<X, E> treat(SetJoin<X, T> var1, Class<E> var2);
  301.     <X, T, E extends T> ListJoin<X, E> treat(ListJoin<X, T> var1, Class<E> var2);
  303.     <X, K, T, V extends T> MapJoin<X, K, V> treat(MapJoin<X, K, T> var1, Class<V> var2);
  305.     <X, T extends X> Path<T> treat(Path<X> var1, Class<T> var2);
  307.     <X, T extends X> Root<T> treat(Root<X> var1, Class<T> var2);
  309.     public interface Case<R> extends Expression<R> {
  311.         CriteriaBuilder.Case<R> when(Expression<Boolean> var1, R var2);
  313.         CriteriaBuilder.Case<R> when(Expression<Boolean> var1, Expression<? extends R> var2);
  315.         Expression<R> otherwise(R var1);
  317.         Expression<R> otherwise(Expression<? extends R> var1);
  318.     }
  320.     public interface SimpleCase<C, R> extends Expression<R> {
  322.         Expression<C> getExpression();
  324.         CriteriaBuilder.SimpleCase<C, R> when(C var1, R var2);
  326.         CriteriaBuilder.SimpleCase<C, R> when(C var1, Expression<? extends R> var2);
  328.         Expression<R> otherwise(R var1);
  330.         Expression<R> otherwise(Expression<? extends R> var1);
  331.     }
  333.     public interface Coalesce<T> extends Expression<T> {
  335.         CriteriaBuilder.Coalesce<T> value(T var1);
  337.         CriteriaBuilder.Coalesce<T> value(Expression<? extends T> var1);
  338.     }
  340.     public interface In<T> extends Predicate {
  342.         Expression<T> getExpression();
  344.         CriteriaBuilder.In<T> value(T var1);
  346.         CriteriaBuilder.In<T> value(Expression<? extends T> var1);
  347.     }
  349.     public static enum Trimspec {
  351.         LEADING,
  352.         TRAILING,
  353.         BOTH;
  355.         private Trimspec() {
  357.         }
  358.     }
  359. }

发表评论

表情：
评论列表 （有 0 条评论，171人围观）

还没有评论，来说两句吧...

相关阅读

    相关 JPA自定义查询

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

    蔚落蔚落/ 0 赞/ 172 阅读