在使用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() {
}
}
}
