AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

JPA自定义查询

2023-08-02 59
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： JPA自定义查询

在使用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() {
   
        }
    }
}
文章标签：
Java
左边的天堂
目录
相关文章
闻缺陷则喜何志丹
|
11月前
XQilla2.3.2解析查询
XQilla2.3.2解析查询
闻缺陷则喜何志丹
56 0
宁波阿成.
|
4月前
|
SQL 前端开发
基于jeecgboot复杂sql查询的列表自定义列实现
基于jeecgboot复杂sql查询的列表自定义列实现
宁波阿成.
38 0
真题OK撒
|
4月前
|
XML Java 数据库连接
【MyBtis】各种查询功能
【MyBtis】各种查询功能
真题OK撒
92 0
子午s
|
存储 缓存 数据库
提高Djang查询速度的9种方法
在Web应用程序中，数据库查询是一个关键的环节。优化数据库查询可以显著提高应用程序的性能和响应速度。Django作为一个高度可扩展的Web框架，提供了多种方式来优化数据库查询。本文将介绍一些常用的Django数据库查询优化技巧，从入门到精通，帮助您构建高效的应用程序。
子午s
137 1
Masters
es聚合查询并且返回对应组的数据
es聚合查询并且返回对应组的数据
Masters
421 0
游客5m6wdh6ebwgya
流程定义查询和删除
流程定义查询流程定义查询和删除
游客5m6wdh6ebwgya
221 0
huihuizi
list自定义参数分页
list自定义参数分页
huihuizi
76 0
我是咔咔
【TP5】关联预加载只查询指定字段（类的属性不存在）
【TP5】关联预加载只查询指定字段（类的属性不存在）
我是咔咔
487 0
【TP5】关联预加载只查询指定字段（类的属性不存在）
学堂小助手
|
SQL Java 数据库连接
分页之查询条件保存到 PageBean 的 url 中| 学习笔记
快速学习分页之查询条件保存到 PageBean 的 url 中
学堂小助手
164 0
一念无明
|
NoSQL MongoDB 索引
查询计划解析
查看查询计划、查询计划的执行统计的方法：db.collection.explain()、cursor.explain()、explainexplain输出默认只输出queryPlanner、serverInfo部分，如果需要输出executionStats，则可以指定explain(allPlan...
一念无明
4307 0