jpa2 subquery

最新推荐文章于 2024-08-02 12:16:56 发布

lifaming15

最新推荐文章于 2024-08-02 12:16:56 发布

阅读量7.8k

点赞数 3

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/lifaming15/article/details/45115993

本文介绍如何使用Java持久化查询语言(JPQL)构建复杂的子查询，并结合多个条件进行数据过滤。通过实例展示了如何根据不同的业务需求，如产品类别、日期范围等，灵活地组合查询条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
		Root<Order> root = criteriaQuery.from(Order.class);
		Path<Object> path = root.get("id"); // field to map with sub-query
		//
		criteriaQuery.select(root);
		Subquery<Order> subquery = criteriaQuery.subquery(Order.class);
		Root<OrderItem> fromProject = subquery.from(OrderItem.class);
		subquery.select(fromProject.<Order>get("order")); // field to map with main-query
		Predicate restrictions = criteriaBuilder.conjunction();
		if(productCategory != null) {
			if(productCategory.getId() == 1) {//浴桶
				subquery.where(criteriaBuilder.equal(fromProject.get("product").get("productCategory").get("id"), 1));
				restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.in(path).value(subquery));
			} else {
				subquery.where(criteriaBuilder.equal(fromProject.get("product").get("productCategory").get("id"), 1));
				restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(criteriaBuilder.in(path).value(subquery)));
			}
		}
		if (beginDate != null) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
		}
		if (endDate != null) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date> get("createDate"), endDate));
		}
		if (orderStatus != null) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
		}
		if (paymentStatus != null) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), paymentStatus));
		}
		if (shippingStatus != null) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("shippingStatus"), shippingStatus));
		}
		if (shops != null && !shops.isEmpty()) {
			restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.in(root.get("shop")).value(shops));
		}
		if (hasExpired != null) {
			if (hasExpired) {
				restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThan(root.<Date> get("expire"), new Date()));
			} else {
				restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date> get("expire"), new Date())));
			}
		}
		criteriaQuery.where(restrictions);