java criteria and_java-jpa-criteriaBuilder使用

CriteriaBuilder criteriaBuilder =entityManager.getCriteriaBuilder();

//查询结果所需要的类型(Entity相对应)

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Entity.class);

//查询所需要的主体类(Entity0相对应)

Root root = criteriaQuery.from(Entity0.class);

//查询结果-select(此处查询所有符合条件的主体类)

criteriaQuery.select(root);

//过滤条件用Predicate方法拼接

Predicate restrictions=criteriaBuilder.conjunction();

//过滤条件——equal(当Entity0关联member类时，Entity0：member=m：1)restrictions= criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));//过滤条件——likerestrictions= criteriaBuilder.and(restrictions, criteriaBuilder.like(root.get("str"), "%"+str+"%"));//用户名查询(member里面的username匹配) ———— 多层查询 ———— 子查询的一种：适用于m:1或1:1(即多对一或一对一)restrictions= criteriaBuilder.and(restrictions, criteriaBuilder.like(root.get("member").get("username"), "%"+username+"%"));//子查询(规范写法，先判断查询内容是否存在)(适用于1：m)(即一对多)

if (searchType != null || searchValue != null || hasExpired != null || status != null || type != null || isPendingReceive != null || isPendingRefunds != null || isAllocatedStock != null || businessType != null) {

//建立子查询

Subquery orderSubquery = criteriaQuery.subquery(Order.class);

Root orderSubqueryRoot = orderSubquery.from(Order.class);

orderSubquery.select(orderSubqueryRoot);

//子查询和父查询相关联

Predicate orderRestrictions= criteriaBuilder.equal(orderSubqueryRoot.get("mergeOrder"), root);//子查询过滤条件拼接

if (searchType != null && searchValue != null) {if ("phone".equals(searchType)) {

orderRestrictions= criteriaBuilder.and(orderRestrictions, criteriaBuilder.like(orderSubqueryRoot.get("phone"), "%"+searchValue+"%"));

}

}if (type != null) {

CriteriaBuilder.In in = criteriaBuilder.in(orderSubqueryRoot.get("type"));

in.value(type);

orderRestrictions=criteriaBuilder.and(orderRestrictions, in);

}//and、or以及判断是否为null，比较(>)的使用(比较可以用于日期比较)

if (hasExpired != null) {orderRestrictions= criteriaBuilder.and(orderRestrictions, criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull(), criteriaBuilder.greaterThan(orderSubqueryRoot.get("expire"), newDate())));

}//not的使用方法(不符合上述过滤条件)，notEqual的使用，

if (isPendingReceive != null) {

restrictions= criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery));

Predicate predicate= criteriaBuilder.and(criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull()

, criteriaBuilder.greaterThan(orderSubqueryRoot.get("expire"), newDate()))

, criteriaBuilder.notEqual(orderSubqueryRoot.get("status"), Order.Status.completed), criteriaBuilder.lessThan(orderSubqueryRoot.get("amountPaid"), orderSubqueryRoot.get("amount")));if(isPendingReceive) {orderRestrictions=criteriaBuilder.and(orderRestrictions, criteriaBuilder.not(predicate));

}

}//多层查询使用if (businessType != null) {

orderRestrictions= criteriaBuilder.and(orderRestrictions, criteriaBuilder.equal(orderSubqueryRoot.get("store").get("business").get("businessType"), businessType));

}

// 拼接过滤条件

orderSubquery.where(orderRestrictions);//和总条件拼接(exists的使用)

restrictions =criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderSubquery));

}criteriaQuery.where(restrictions);　　　　 TypedQuery query = entityManager.createQuery(criteriaQuery);

Entity singleResult = query.getSingleResult();