@Override public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder cb) { Root<Account> accounts = query.from(Account.class); Path<Date> expiryDate = accounts.<Date> get("expiryDate"); Predicate customerIsAccountOwner = cb.equal(accounts.<Customer> get("customer"), root); Predicate accountExpiryDateBefore = cb.lessThan(expiryDate, date.toDateTimeAtStartOfDay().toDate()); return cb.and(customerIsAccountOwner, accountExpiryDateBefore); } };
@Override public Long findTaskIdByContentId( Long contentId ) { check(); CriteriaBuilder builder = this.em.getCriteriaBuilder(); CriteriaQuery<Long> query = builder.createQuery(Long.class); Root<TaskImpl> taskRoot = query.from(TaskImpl.class); Root<ContentImpl> contentRoot = query.from(ContentImpl.class); query.select(taskRoot.get(TaskImpl_.id)); Predicate taskContentJoinPred = builder.equal( contentRoot.get(ContentImpl_.id), taskRoot.get(TaskImpl_.taskData).get(TaskDataImpl_.outputContentId)); Predicate contentIdPred = builder.equal( contentRoot.get(ContentImpl_.id), contentId); query.where(builder.and(taskContentJoinPred, contentIdPred)); Query choppedLiver = em.createQuery(query); return (Long) choppedLiver.getSingleResult(); }
protected <T> void applyLimitedInClause(List<Long> ids, CriteriaBuilder builder, Root<T> root, List<Predicate> restrictions) { List<List<Long>> listsOfExcludeIds = Lists.partition(ids, RESTRICT_IN_CLAUSE_MAX_SIZE); List<Predicate> inRestrictions = new ArrayList<Predicate>(); for (List<Long> idSetToExclude : listsOfExcludeIds) { inRestrictions.add(builder.not(root.get("id").in(idSetToExclude))); } restrictions.add(builder.and(inRestrictions.toArray(new Predicate[inRestrictions.size()]))); }
public TaUser getUserByIdAndContactNumber( final long userId, final long contactNumber){ final CriteriaBuilder cb = entityManager.getCriteriaBuilder(); final CriteriaQuery<TaUser> query = cb.createQuery(TaUser.class); final Root<TaUser> root = query.from(TaUser.class); query .where(cb.and( cb.equal(root.get("userId"), userId), cb.equal(root.get("taContact").get("contactNumber"), contactNumber) )); return entityManager.createQuery(query).getSingleResult(); }
Map<String, Object> params = ...; CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); Root<Transaction> r = cq.from(Transaction.class); Predicate p= cb.conjunction(); for (Map.Entry<String, Object> param: params.entrySet()) p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue())); cq.multiselect(r.get("id"), r.get("status"), r.get("created_at")) .where(p) .orderBy(cb.asc(r.get("id"))); List<Tuple> result = em.createQuery(cq).getResultList();
Subquery<VariableInstanceLog> maxIdSubQuery = query.subquery(VariableInstanceLog.class); Root from = maxIdSubQuery.from(VariableInstanceLog.class); maxIdSubQuery.select(builder.max(from.get(VariableInstanceLog_.id))); maxIdSubQuery.groupBy( from.get(VariableInstanceLog_.variableId), from.get(VariableInstanceLog_.processInstanceId)); Attribute varIdField = VariableInstanceLog_.id; Path varValIdPath = table.get(varValField); Predicate varIdPredicate = builder.equal(varVarIdPath, varId); Predicate valPredicate; if( QueryCriteriaType.REGEXP.equals(criteria.getType()) ) { valPredicate = builder.like(varValIdPath, val); } else { valPredicate = builder.equal(varValIdPath, val); predicate = builder.and(varIdPredicate, valPredicate); } else { throw new IllegalStateException("List id [" + getQueryParameterIdNameMap().get(Integer.parseInt(criteria.getListId()))
public <T extends PostModerate> int deleteSpam(EntityManager entityManager, Class<T> postModerateClass) { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaDelete<T> delete = builder.createCriteriaDelete(postModerateClass); Root<T> root = delete.from(postModerateClass); int daysValidityThreshold = (Post.class.isAssignableFrom(postModerateClass)) ? 7 : 3; delete .where( builder.and( builder.equal(root.get("status"), PostStatus.SPAM), builder.lessThanOrEqualTo(root.get("updatedOn"), Timestamp.valueOf(LocalDateTime.now().minusDays(daysValidityThreshold))) ) ); return entityManager.createQuery(delete).executeUpdate(); }
protected void attachSearchCriteria(SearchCriteria searchCriteria, From<?, ? extends Product> product, From<?, ? extends Sku> sku, List<Predicate> restrictions) { CriteriaBuilder builder = em.getCriteriaBuilder(); restrictions.add(builder.equal(pathToUse.get("name").as(String.class), key)); if (max != null) { maxRange = builder.lessThan(pathToUse.get(key).as(BigDecimal.class), max); rangeRestrictions.add(builder.and(minRange, maxRange)); } else { rangeRestrictions.add(minRange);
@Override public List<EPerson> findWithPasswordWithoutDigestAlgorithm(Context context) throws SQLException { CriteriaBuilder criteriaBuilder = getCriteriaBuilder(context); CriteriaQuery criteriaQuery = getCriteriaQuery(criteriaBuilder, EPerson.class); Root<EPerson> ePersonRoot = criteriaQuery.from(EPerson.class); criteriaQuery.select(ePersonRoot); criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.isNotNull(ePersonRoot.get(EPerson_.password)), criteriaBuilder.isNull(ePersonRoot.get(EPerson_.digestAlgorithm)) ) ); return list(context, criteriaQuery, false, EPerson.class, -1, -1); }
EntityManager em = JPA.em(); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<T> criteriaQuery = cb.createQuery(News.class); Root<T> root = criteriaQuery.from(News.class); criteriaQuery.select(root); List<Predicate> ps = new ArrayList<Predicate>(); ps.add(sb.equal(root.get("link", url)); ps.add(sb.equal(root.get("name", "joe")); criteriaQuery.where(cb.and(ps.toArray(new Predicate[0]))); List<News> resultUrl = em.createQuery(criteriaQuery).getResultList();
public static Predicate<BuildRecord> withAttribute(String key, String value) { return (root, query, cb) -> { MapJoin<Object, Object, Object> mapJoinAttributes = root.joinMap(BuildRecord_.attributes.getName()); return query.where(cb.and(cb.equal(mapJoinAttributes.key(), key), cb.equal(mapJoinAttributes.value(), value))).getRestriction(); }; }
public int count(Class<? extends AbstractEntity> entityClass, Predicate... conditions) { CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class).where(criteriaBuilder.and(conditions)); Root<? extends AbstractEntity> root = criteriaQuery.from(entityClass); criteriaQuery.select(criteriaBuilder.count(root)); return (count(criteriaQuery, root)); }
@Override public Long countUsesByCustomer(Order order, Long customerId, Long offerId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<OfferAuditImpl> root = criteria.from(OfferAuditImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); restrictions.add( builder.and( builder.or( builder.notEqual(root.get("orderId"), getOrderId(order)), builder.isNull(root.get("orderId")) ), builder.equal(root.get("customerId"), customerId), builder.equal(root.get("offerId"), offerId) ) ); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); try { return em.createQuery(criteria).getSingleResult(); } catch (Exception e) { LOG.error("Error counting offer uses by customer.", e); return null; } }
} else { ourLog.debug("Searching for resource link with target URL: {}", dt.getValue()); Predicate eq = myBuilder.equal(join.get("myTargetResourceUrl"), dt.getValue()); codePredicates.add(eq); continue; Predicate pidPredicate = myBuilder.equal(join.get("myTargetResourcePid"), next); codePredicates.add(myBuilder.and(pathPredicate, pidPredicate)); subQ.select(subQfrom.get("myId").as(Long.class)); myPredicates.add(myBuilder.equal(myResourceTableRoot.get("myResourceType"), subResourceName)); myPredicates.add(myBuilder.isNull(myResourceTableRoot.get("myDeleted"))); codePredicates.add(myBuilder.and(pathPredicate, pidPredicate));
CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Car> query = builder.createQuery(Car.class); Root<Car> queryRoot = query.from(Car.class); query.select(queryRoot); Path<String> pathToYourField = root.get(yourField); //yourField is a variable containing the field. //You can store all the variables in a list, iterate //over them and do this for each one. query.where(builder.and(builder.equal(pathToYourField, "particularValue"))); //You compare the path against a value. //Rest of the fields / paths TypedQuery<Car> typedQuery = entityManager.createQuery(query); List<Car> cars = typedQuery.getResultList();
Map<SingularAttribute<Transaction, ?>, Object> params = ...; CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); Root<Transaction> r = cq.from(Transaction.class); Predicate p= cb.conjunction(); for (Map.Entry<SingularAttribute<Transaction, ?>, Object> param: params.entrySet()) p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue())); cq.multiselect(r.get(Transaction_.id), r.get(Transaction_.status), r.get(Transaction_.created_at)) .where(p) .orderBy(cb.asc(r.get(Transaction_.id))); List<Tuple> result = em.createQuery(cq).getResultList();
public <R> Predicate createPredicate(QueryCriteria criteria, CriteriaQuery<R> query, CriteriaBuilder builder) { // subquery and root Root<TaskImpl> taskRoot = getRoot(query, TaskImpl.class); Subquery<Long> subQuery = query.subquery(Long.class); Root<TaskVariableImpl> taskVarRoot = subQuery.from(TaskVariableImpl.class); subQuery.select(taskVarRoot.get(TaskVariableImpl_.taskId)); // task variable predicate (in subquery) Predicate taskVariablePredicate = null; String listId = criteria.getListId(); if( TASK_VARIABLE_COMBINED_ID.equals(listId) ) { List<QueryCriteria> taskVarSubCriteriaList = criteria.getCriteria(); int size = taskVarSubCriteriaList.size(); Predicate[] taskVarSubPredicates = new Predicate[size]; for( int i = 0; i < size; ++i ) { taskVarSubPredicates[i] = createSingleTaskVariableCriteriaPredicate( builder, taskVarRoot, taskVarSubCriteriaList.get(i)); } taskVariablePredicate = builder.and(taskVarSubPredicates); } else { taskVariablePredicate = createSingleTaskVariableCriteriaPredicate(builder, taskVarRoot, criteria); } // add predicate to subquery subQuery.where(taskVariablePredicate); // create predicate for actual query that references subquery return taskRoot.get(TaskImpl_.id).in(subQuery); }
@Override public Long countOfferCodeUses(Order order, Long offerCodeId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<OfferAuditImpl> root = criteria.from(OfferAuditImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); restrictions.add( builder.and( builder.or( builder.notEqual(root.get("orderId"), getOrderId(order)), builder.isNull(root.get("orderId")) ), builder.equal(root.get("offerCodeId"), offerCodeId) ) ); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); try { return em.createQuery(criteria).getSingleResult(); } catch (Exception e) { LOG.error("Error counting offer code uses.", e); return null; } }
public static Predicate<BuildRecordPushResult> successForBuildRecord(Integer buildRecordId) { return (root, query, cb) -> cb.and( cb.equal(root.get(BuildRecordPushResult_.buildRecord).get(BuildRecord_.id), buildRecordId), cb.equal(root.get(BuildRecordPushResult_.status), BuildRecordPushResult.Status.SUCCESS ) ); }
CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Entity> criteria = cb.createQuery(Entity.class); Root<Entity> entity = criteria.from(Entity.class); MapJoin<Entity, String, String> mapJoin = entity.joinMap(Entity_.myMap); criteria.where(cb.and(cb.equal(mapJoin.key(), "myKey"), cb.equal(mapJoin.value(), "myValue")));