JPA 多条件、多表查询

最新推荐文章于 2023-12-06 17:20:41 发布

冷小鱼

最新推荐文章于 2023-12-06 17:20:41 发布

阅读量3.8k

点赞数 3

分类专栏： Java 文章标签： JPA

本文链接：https://blog.csdn.net/yanxilou/article/details/83376758

版权

Java 专栏收录该内容

63 篇文章 2 订阅

订阅专栏

JPA对于简单的查询操作确实很给力，但是涉及到多表、多条件、分页的场景就不是很好实现了。

可行的解决方案

1、多条件

public interface BaseRepository<T,ID extends Serializable> extends JpaRepository<T,ID>, JpaSpecificationExecutor<T>

使用Specification来实现条件的拼接

2、多表

数据量不大的情况，可以考虑视图，视图实体配置和表的没有任何区别。

3、分页

可以自行实现分页逻辑，也可以使用框架的 Pageable 类。

核心代码示例

//	核心方法 拼接条件
	public default Predicate getPredocate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb,
			DataQueryObject dqo) {
		List<Predicate> predicates = new ArrayList<>();
//		获取查询对象的所有属性
		Field[] fields = dqo.getClass().getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			
			String queryFiled = null;
			QueryType queryType = null;
			Object value = null;
			Predicate predicate = null;
//			获取属性的 自定义注解类型
			QueryField annotaion = field.getAnnotation(QueryField.class);
//			如果没有注解 则跳过
			if(annotaion == null) {
				continue;
			}
//			如果注解中 name为空 则用字段名称作为属性名
			if(!StringUtils.isEmpty(annotaion.name())) {
				queryFiled = annotaion.name();
			} else {
				queryFiled = field.getName();
			}
			queryType = annotaion.type();
			try {
				value = field.get(dqo);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
//			判断字段类型是否为空
			if(value == null && queryType.isNotCanBeNull()) {
				logger.debug("查询类型：" + queryType + "不允许为空。");
				continue;
			}
//			判断注解中 的条件类型
			switch (queryType) {
				case EQUAL:
					Path<Object> equal = getRootByQueryFiled(queryFiled, root);
					predicate = cb.equal(equal, value);
					predicates.add(predicate);
					break;
				case BEWTEEN:
					Path<Comparable> between = getRootByQueryFiledComparable(queryFiled, root);
					QueryBetween queryBetween = null;
					if(value instanceof QueryBetween) 
						queryBetween = (QueryBetween) value;
					else 
						continue;
					predicate = cb.between(between, queryBetween.after, queryBetween.before);
					predicates.add(predicate);
					break;
				case LESS_THAN:
					Path<Comparable> lessThan = getRootByQueryFiledComparable(queryFiled, root);
					if(value instanceof QueryBetween) 
						queryBetween = (QueryBetween) value;
					else 
						continue;
					predicate = cb.lessThan(lessThan, queryBetween.after);
					predicates.add(predicate);
					break;
				case LESS_THAN_EQUAL:
					Path<Comparable> lessThanOrEqualTo = getRootByQueryFiledComparable(queryFiled, root);
					if(value instanceof QueryBetween) 
						queryBetween = (QueryBetween) value;
					else 
						continue;
					predicate = cb.lessThanOrEqualTo(lessThanOrEqualTo, queryBetween.after);
					predicates.add(predicate);
					break;
				case GREATEROR_THAN:
					Path<Comparable> greaterThan = getRootByQueryFiledComparable(queryFiled, root);
					if(value instanceof QueryBetween) 
						queryBetween = (QueryBetween) value;
					else 
						continue;
					predicate = cb.greaterThan(greaterThan, queryBetween.after);
					predicates.add(predicate);
					break;
				case GREATEROR_THAN_EQUAL:
					Path<Comparable> greaterThanOrEqualTo = getRootByQueryFiledComparable(queryFiled, root);
					if(value instanceof QueryBetween) 
						queryBetween = (QueryBetween) value;
					else 
						continue;
					predicate = cb.lessThanOrEqualTo(greaterThanOrEqualTo, queryBetween.after);
					predicates.add(predicate);
					break;
				case NOT_EQUAL:
					Path<Object> notEqual = getRootByQueryFiled(queryFiled, root);
					predicate = cb.notEqual(notEqual, value);
					predicates.add(predicate);
					break;
				case IS_NULL:
					Path<Object> isNull = getRootByQueryFiled(queryFiled, root);
					predicate = cb.isNull(isNull);
					predicates.add(predicate);
					break;
				case IS_NOT_NULL:
					Path<Object> isNotNull = getRootByQueryFiled(queryFiled, root);
					predicate = cb.isNotNull(isNotNull);
					predicates.add(predicate);
					break;
				case LEFT_LIKE:
					Path<String> leftLike = getRootByQueryFiledString(queryFiled, root);
					predicate = cb.like(leftLike, "%" + value.toString());
					predicates.add(predicate);
					break;
				case RIGHT_LIKE:
					Path<String> rightLike = getRootByQueryFiledString(queryFiled, root);
					predicate = cb.like(rightLike, value.toString() + "%");
					predicates.add(predicate);
					break;
				case FULL_LIKE:
					Path<String> fullLike = getRootByQueryFiledString(queryFiled, root);
					predicate = cb.like(fullLike, "%" + value.toString() + "%");
					predicates.add(predicate);
					break;
				case DEFAULT_LIKE:
					Path<String> like = getRootByQueryFiledString(queryFiled, root);
					predicate = cb.like(like, value.toString());
					predicates.add(predicate);
					break;
				case NOT_LIKE:
					Path<String> notLike = getRootByQueryFiledString(queryFiled, root);
					predicate = cb.like(notLike, value.toString());
					predicates.add(predicate);
					break;
				case IN:
					Path<Object> in = getRootByQueryFiled(queryFiled, root);
					In ins = cb.in(in);
					List inList = null;
					if(value instanceof List) {
						inList = (List) value;
					}
					for (Object object : inList) {
						ins.value(object);
					}
					predicates.add(ins);
					break;
				default:
					break;
			}
		}
//		如果 为空 代表  没有任何有效的条件
		if(predicates.size() == 0) {
			return cb.and();
		}
		Object[] list = predicates.toArray();
		Predicate[] t = new Predicate[predicates.size()];
		Predicate[] result = predicates.toArray(t);
		return cb.and(result);
	}

	public default Path<Object> getRootByQueryFiled(String queryFiled, Root<T> root) {
		if(queryFiled.indexOf(".") < 0) {
			return root.get(queryFiled);
		} else {
			return getRootByQueryFiled(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), root.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}

	public default Path<Object> getRootByQueryFiled(String queryFiled, Path<Object> path) {
		if(queryFiled.indexOf(".") < 0) {
			return path.get(queryFiled);
		} else {
			return getRootByQueryFiled(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), path.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}
	
	public default Path<String> getRootByQueryFiledString(String queryFiled, Root<T> root) {
		if(queryFiled.indexOf(".") < 0) {
			return root.get(queryFiled);
		} else {
			return getRootByQueryFiledString(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), root.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}

	public default Path<String> getRootByQueryFiledString(String queryFiled, Path<Object> path) {
		if(queryFiled.indexOf(".") < 0) {
			return path.get(queryFiled);
		} else {
			return getRootByQueryFiledString(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), path.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}
	
	public default Path<Comparable> getRootByQueryFiledComparable(String queryFiled, Root<T> root) {
		if(queryFiled.indexOf(".") < 0) {
			return root.get(queryFiled);
		} else {
			return getRootByQueryFiledComparable(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), root.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}

	public default Path<Comparable> getRootByQueryFiledComparable(String queryFiled, Path<Object> path) {
		if(queryFiled.indexOf(".") < 0) {
			return path.get(queryFiled);
		} else {
			return getRootByQueryFiledComparable(queryFiled.substring(queryFiled.indexOf(".") + 1, queryFiled.length()), path.get(queryFiled.substring(0, queryFiled.indexOf("."))));
		}
	}

自定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface QueryField {
	QueryType type();
	String name() default "";
}

接收参数

	@QueryField(type = QueryType.EQUAL, name = "redddlCode")
	@ApiModelProperty(value="XXX",name="redddlCode",example="S2344")
	private String redddlCode;