Example usage for javax.persistence.criteria CriteriaQuery where

List of usage examples for javax.persistence.criteria CriteriaQuery where

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.criteria.*
  5. CriteriaQuery
  6. where

Introduction

In this page you can find the example usage for javax.persistence.criteria CriteriaQuery where.

Prototype

CriteriaQuery<T> where(Predicate... restrictions);

Source Link

Document

Modify the query to restrict the query result according to the conjunction of the specified restriction predicates.

Usage

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void addPredicateId(Set<Long> thePids) {
    if (thePids == null || thePids.isEmpty()) {
        return;/*from   w  ww. jav a  2  s .  c o m*/
    }

    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceTable> from = cq.from(ResourceTable.class);
    cq.select(from.get("myId").as(Long.class));

    List<Predicate> predicates = new ArrayList<Predicate>();
    predicates.add(builder.equal(from.get("myResourceType"), myResourceName));
    predicates.add(from.get("myId").in(thePids));
    createPredicateResourceId(builder, cq, predicates, from.get("myId").as(Long.class));
    createPredicateLastUpdatedForResourceTable(builder, from, predicates);

    cq.where(toArray(predicates));

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    doSetPids(q.getResultList());
}

From source file:com.yunguchang.data.ApplicationRepository.java

public List<TRsDriverinfoEntity> listAllCandidateDrivers(String[] applicationIds, String carId, String keyword,
        Integer offset, Integer limit, PrincipalExt principalExt) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TRsDriverinfoEntity> cq = cb.createQuery(TRsDriverinfoEntity.class);
    Root<TRsDriverinfoEntity> driverRoot = cq.from(TRsDriverinfoEntity.class);
    Root<TAzCarinfoEntity> carRoot = cq.from(TAzCarinfoEntity.class);
    cq.select(driverRoot);// w  ww  .j  a va2 s.  c om

    Subquery<TBusScheduleCarEntity> overlapScheduleCarSubQuery = cq.subquery(TBusScheduleCarEntity.class);

    applyOverlapScheduleCarSubquery(overlapScheduleCarSubQuery, applicationIds, carRoot, driverRoot, cb,
            principalExt);

    Subquery<TRsDriverinfoEntity> subqueryLicense = cq.subquery(TRsDriverinfoEntity.class);
    Root<TAzJzRelaEntity> licenseMapping = subqueryLicense.from(TAzJzRelaEntity.class);
    subqueryLicense.select(driverRoot);
    subqueryLicense.where(

            cb.equal(carRoot.get(TAzCarinfoEntity_.xszcx), licenseMapping.get(TAzJzRelaEntity_.xszcx)),
            cb.equal(licenseMapping.get(TAzJzRelaEntity_.jzyq),
                    driverRoot.get(TRsDriverinfoEntity_.drivecartype)));

    Predicate predicate = cb.and(cb.equal(carRoot.get(TAzCarinfoEntity_.id), carId),
            cb.equal(driverRoot.get(TRsDriverinfoEntity_.department), carRoot.get(TAzCarinfoEntity_.sysOrg)),
            cb.equal(driverRoot.get(TRsDriverinfoEntity_.enabled), "1"),
            cb.or(cb.exists(subqueryLicense), cb.isNull(driverRoot.get(TRsDriverinfoEntity_.drivecartype))

            ),

            cb.not(cb.exists(overlapScheduleCarSubQuery))

    );

    if (keyword != null) {
        predicate = cb.and(predicate,
                cb.or(cb.like(driverRoot.get(TRsDriverinfoEntity_.drivername), "%" + keyword + "%")));
    }

    cq.where(predicate);
    cq.orderBy(cb
            .asc(cb.function("casttogbk", String.class, cb.trim(driverRoot.get(TRsDriverinfoEntity_.drivername))

    )));
    TypedQuery<TRsDriverinfoEntity> query = em.createQuery(cq);
    if (offset != null) {
        query.setFirstResult(offset);
    }

    if (limit != null) {
        query.setMaxResults(limit);
    }

    return query.getResultList();

}

From source file:org.apereo.portal.persondir.dao.jpa.JpaLocalAccountDaoImpl.java

@Override
public List<ILocalAccountPerson> getPeople(LocalAccountQuery query) {
    final EntityManager entityManager = this.getEntityManager();
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    final CriteriaQuery<LocalAccountPersonImpl> criteriaQuery = cb.createQuery(LocalAccountPersonImpl.class);
    final Root<LocalAccountPersonImpl> accountRoot = criteriaQuery.from(LocalAccountPersonImpl.class);
    final CollectionJoin<LocalAccountPersonImpl, LocalAccountPersonAttributeImpl> attributes = accountRoot
            .join(LocalAccountPersonImpl_.attributes);
    final ListJoin<LocalAccountPersonAttributeImpl, String> attributeValues = attributes
            .join(LocalAccountPersonAttributeImpl_.values);

    //Due to the joins multiple rows are returned for each result
    criteriaQuery.distinct(true);/*w ww  . j a  v a 2 s  .  c o  m*/
    criteriaQuery.select(accountRoot);

    final List<Predicate> whereParts = new LinkedList<Predicate>();
    final Map<Parameter<String>, String> params = new LinkedHashMap<Parameter<String>, String>();

    // if a username has been specified, append it to the query
    if (query.getName() != null) {
        whereParts.add(cb.equal(accountRoot.get(LocalAccountPersonImpl_.name), this.nameParameter));
        params.put(this.nameParameter, query.getName());
    }

    //Build Predicate for each attribute being queried
    int paramCount = 0;
    for (Map.Entry<String, List<String>> entry : query.getAttributes().entrySet()) {
        final List<String> values = entry.getValue();
        if (values == null) {
            continue;
        }

        //For each value create a Predicate checking the attribute name and value together
        for (final String value : values) {
            if (StringUtils.isBlank(value)) {
                continue;
            }

            //Create Parameter objects for the name and value, stick them in the params map for later use
            final ParameterExpression<String> nameParam = this.createParameterExpression(String.class,
                    "attrName" + paramCount);
            final ParameterExpression<String> valueParam = this.createParameterExpression(String.class,
                    "attrValue" + paramCount);

            params.put(nameParam, entry.getKey());
            params.put(valueParam, "%" + value.toLowerCase() + "%");

            //Build the and(eq, like) predicate and add it to the list of predicates for the where clause
            whereParts.add(cb.and(cb.equal(attributes.get(LocalAccountPersonAttributeImpl_.name), nameParam),
                    cb.like(cb.lower(attributeValues.as(String.class)), valueParam)));

            paramCount++;
        }
    }

    //Add the Predicates to the where clause
    criteriaQuery.where(cb.or(whereParts.toArray(new Predicate[whereParts.size()])));

    //Create the query
    final TypedQuery<LocalAccountPersonImpl> jpaQuery = this.createCachedQuery(criteriaQuery);

    //Add all of the stored up parameters to the query
    for (Map.Entry<Parameter<String>, String> entry : params.entrySet()) {
        final Parameter<String> parameter = entry.getKey();
        final String value = entry.getValue();
        jpaQuery.setParameter(parameter, value);
    }

    final List<LocalAccountPersonImpl> accounts = jpaQuery.getResultList();
    return new ArrayList<ILocalAccountPerson>(accounts);
}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

public IBundleProvider search(final SearchParameterMap theParams) {
    myParams = theParams;//  ww w . j a  va  2  s .co  m
    StopWatch w = new StopWatch();

    doInitializeSearch();

    DateRangeParam lu = theParams.getLastUpdated();

    // Collection<Long> loadPids;
    if (theParams.getEverythingMode() != null) {

        Long pid = null;
        if (theParams.get(BaseResource.SP_RES_ID) != null) {
            StringParam idParm = (StringParam) theParams.get(BaseResource.SP_RES_ID).get(0).get(0);
            pid = BaseHapiFhirDao.translateForcedIdToPid(myResourceName, idParm.getValue(), myForcedIdDao);
        }

        if (theParams.containsKey(Constants.PARAM_CONTENT) || theParams.containsKey(Constants.PARAM_TEXT)) {
            List<Long> pids = mySearchDao.everything(myResourceName, theParams);
            if (pids.isEmpty()) {
                return doReturnProvider();
            }

            doSetPids(pids);

        } else {
            CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
            CriteriaQuery<Tuple> cq = builder.createTupleQuery();
            Root<ResourceTable> from = cq.from(ResourceTable.class);
            List<Predicate> predicates = new ArrayList<Predicate>();
            if (pid != null) {
                predicates.add(builder.equal(from.get("myId"), pid));
            }
            predicates.add(builder.equal(from.get("myResourceType"), myResourceName));
            predicates.add(builder.isNull(from.get("myDeleted")));
            cq.where(builder.and(SearchBuilder.toArray(predicates)));

            Join<Object, Object> join = from.join("myIncomingResourceLinks", JoinType.LEFT);
            cq.multiselect(from.get("myId").as(Long.class), join.get("mySourceResourcePid").as(Long.class));

            TypedQuery<Tuple> query = myEntityManager.createQuery(cq);
            Set<Long> pids = new HashSet<Long>();
            for (Tuple next : query.getResultList()) {
                pids.add(next.get(0, Long.class));
                Long nextLong = next.get(1, Long.class);
                if (nextLong != null) {
                    pids.add(nextLong);
                }
            }
            doSetPids(pids);

        }

    } else if (theParams.isEmpty()) {

        TypedQuery<Long> query = createSearchAllByTypeQuery(lu);
        doSetPids(query.getResultList());

    } else {

        if (mySearchDao == null) {
            if (theParams.containsKey(Constants.PARAM_TEXT)) {
                throw new InvalidRequestException(
                        "Fulltext search is not enabled on this service, can not process parameter: "
                                + Constants.PARAM_TEXT);
            } else if (theParams.containsKey(Constants.PARAM_CONTENT)) {
                throw new InvalidRequestException(
                        "Fulltext search is not enabled on this service, can not process parameter: "
                                + Constants.PARAM_CONTENT);
            }
        } else {
            List<Long> searchResultPids = mySearchDao.search(myResourceName, theParams);
            if (searchResultPids != null) {
                if (searchResultPids.isEmpty()) {
                    return doReturnProvider();
                }
                doSetPids(searchResultPids);
            }
        }

        if (!theParams.isEmpty()) {
            searchForIdsWithAndOr(theParams, lu);
        }

    }

    // // Load _include and _revinclude before filter and sort in everything mode
    // if (theParams.getEverythingMode() != null) {
    // if (theParams.getRevIncludes() != null && theParams.getRevIncludes().isEmpty() == false) {
    // loadPids.addAll(loadReverseIncludes(loadPids, theParams.getRevIncludes(), true,
    // theParams.getEverythingMode()));
    // loadPids.addAll(loadReverseIncludes(loadPids, theParams.getIncludes(), false, theParams.getEverythingMode()));
    // }
    // }

    if (doHaveNoResults()) {
        return doReturnProvider();
    }

    // Handle _lastUpdated
    if (lu != null) {
        filterResourceIdsByLastUpdated(lu);

        if (doHaveNoResults()) {
            return doReturnProvider();
        }
    }

    // Handle sorting if any was provided
    processSort(theParams);

    ourLog.info(" {} on {} in {}ms", new Object[] { myResourceName, theParams, w.getMillisAndRestart() });
    return doReturnProvider();
}

From source file:net.groupbuy.dao.impl.ProductDaoImpl.java

public Long count(Member favoriteMember, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isGift,
        Boolean isOutOfStock, Boolean isStockAlert) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
    Root<Product> root = criteriaQuery.from(Product.class);
    criteriaQuery.select(root);//from w  ww .j a v a  2  s  .  c  om
    Predicate restrictions = criteriaBuilder.conjunction();
    if (favoriteMember != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.join("favoriteMembers"), favoriteMember));
    }
    if (isMarketable != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
    }
    if (isList != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
    }
    if (isTop != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
    }
    if (isGift != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isGift"), isGift));
    }
    Path<Integer> stock = root.get("stock");
    Path<Integer> allocatedStock = root.get("allocatedStock");
    if (isOutOfStock != null) {
        if (isOutOfStock) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock),
                    criteriaBuilder.greaterThan(stock, allocatedStock)));
        }
    }
    if (isStockAlert != null) {
        Setting setting = SettingUtils.get();
        if (isStockAlert) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
        }
    }
    criteriaQuery.where(restrictions);
    return super.count(criteriaQuery, null);
}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void processSort(final SearchParameterMap theParams) {

    // Set<Long> loadPids = theLoadPids;
    if (theParams.getSort() != null && isNotBlank(theParams.getSort().getParamName())) {
        List<Order> orders = new ArrayList<Order>();
        List<Predicate> predicates = new ArrayList<Predicate>();
        CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = builder.createTupleQuery();
        Root<ResourceTable> from = cq.from(ResourceTable.class);

        createPredicateResourceId(builder, cq, predicates, from.get("myId").as(Long.class));

        createSort(builder, from, theParams.getSort(), orders, predicates);

        if (orders.size() > 0) {

            // TODO: why do we need the existing list for this join to work?
            Collection<Long> originalPids = doGetPids();

            LinkedHashSet<Long> loadPids = new LinkedHashSet<Long>();
            cq.multiselect(from.get("myId").as(Long.class));
            cq.where(toArray(predicates));
            cq.orderBy(orders);//  w w w . jav a 2 s .c  o  m

            TypedQuery<Tuple> query = myEntityManager.createQuery(cq);

            for (Tuple next : query.getResultList()) {
                loadPids.add(next.get(0, Long.class));
            }

            ourLog.debug("Sort PID order is now: {}", loadPids);

            ArrayList<Long> pids = new ArrayList<Long>(loadPids);

            // Any ressources which weren't matched by the sort get added to the bottom
            for (Long next : originalPids) {
                if (loadPids.contains(next) == false) {
                    pids.add(next);
                }
            }

            doSetPids(pids);
        }
    }

}

From source file:ca.uhn.fhir.jpa.dao.BaseFhirResourceDao.java

private Set<Long> addPredicateToken(String theParamName, Set<Long> thePids,
        List<? extends IQueryParameterType> theList) {
    if (theList == null || theList.isEmpty()) {
        return thePids;
    }/*from   ww  w.  j a  v a2 s  .co m*/

    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceIndexedSearchParamToken> from = cq.from(ResourceIndexedSearchParamToken.class);
    cq.select(from.get("myResourcePid").as(Long.class));

    List<Predicate> codePredicates = new ArrayList<Predicate>();
    for (IQueryParameterType nextOr : theList) {
        if (nextOr instanceof TokenParam) {
            TokenParam id = (TokenParam) nextOr;
            if (id.isText()) {
                return addPredicateString(theParamName, thePids, theList);
            }
        }

        Predicate singleCode = createPredicateToken(nextOr, theParamName, builder, from);
        codePredicates.add(singleCode);
    }

    Predicate masterCodePredicate = builder.or(codePredicates.toArray(new Predicate[0]));

    Predicate type = builder.equal(from.get("myResourceType"), myResourceName);
    Predicate name = builder.equal(from.get("myParamName"), theParamName);
    if (thePids.size() > 0) {
        Predicate inPids = (from.get("myResourcePid").in(thePids));
        cq.where(builder.and(type, name, masterCodePredicate, inPids));
    } else {
        cq.where(builder.and(type, name, masterCodePredicate));
    }

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    return new HashSet<Long>(q.getResultList());
}

From source file:ca.uhn.fhir.jpa.dao.BaseFhirResourceDao.java

private Set<Long> addPredicateComposite(RuntimeSearchParam theParamDef, Set<Long> thePids,
        List<? extends IQueryParameterType> theNextAnd) {
    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceTable> from = cq.from(ResourceTable.class);
    cq.select(from.get("myId").as(Long.class));

    IQueryParameterType or = theNextAnd.get(0);
    if (!(or instanceof CompositeParam<?, ?>)) {
        throw new InvalidRequestException("Invalid type for composite param (must be "
                + CompositeParam.class.getSimpleName() + ": " + or.getClass());
    }//from  w  w w.j  av  a 2 s . co m
    CompositeParam<?, ?> cp = (CompositeParam<?, ?>) or;

    RuntimeSearchParam left = theParamDef.getCompositeOf().get(0);
    IQueryParameterType leftValue = cp.getLeftValue();
    Predicate leftPredicate = createCompositeParamPart(builder, from, left, leftValue);

    RuntimeSearchParam right = theParamDef.getCompositeOf().get(1);
    IQueryParameterType rightValue = cp.getRightValue();
    Predicate rightPredicate = createCompositeParamPart(builder, from, right, rightValue);

    Predicate type = builder.equal(from.get("myResourceType"), myResourceName);
    if (thePids.size() > 0) {
        Predicate inPids = (from.get("myResourcePid").in(thePids));
        cq.where(builder.and(type, leftPredicate, rightPredicate, inPids));
    } else {
        cq.where(builder.and(type, leftPredicate, rightPredicate));
    }

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    return new HashSet<Long>(q.getResultList());

}

From source file:net.groupbuy.dao.impl.MessageDaoImpl.java

public Long count(Member member, Boolean read) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Message> criteriaQuery = criteriaBuilder.createQuery(Message.class);
    Root<Message> root = criteriaQuery.from(Message.class);
    criteriaQuery.select(root);//from w w w . j a  va  2s  .c  o  m
    Predicate restrictions = criteriaBuilder.conjunction();
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNull(root.get("forMessage")),
            criteriaBuilder.equal(root.get("isDraft"), false));
    if (member != null) {
        if (read != null) {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.and(criteriaBuilder.equal(root.get("sender"), member),
                                    criteriaBuilder.equal(root.get("senderDelete"), false),
                                    criteriaBuilder.equal(root.get("senderRead"), read)),
                            criteriaBuilder.and(criteriaBuilder.equal(root.get("receiver"), member),
                                    criteriaBuilder.equal(root.get("receiverDelete"), false),
                                    criteriaBuilder.equal(root.get("receiverRead"), read))));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.and(criteriaBuilder.equal(root.get("sender"), member),
                                    criteriaBuilder.equal(root.get("senderDelete"), false)),
                            criteriaBuilder.and(criteriaBuilder.equal(root.get("receiver"), member),
                                    criteriaBuilder.equal(root.get("receiverDelete"), false))));
        }
    } else {
        if (read != null) {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.and(criteriaBuilder.isNull(root.get("sender")),
                                    criteriaBuilder.equal(root.get("senderDelete"), false),
                                    criteriaBuilder.equal(root.get("senderRead"), read)),
                            criteriaBuilder.and(criteriaBuilder.isNull(root.get("receiver")),
                                    criteriaBuilder.equal(root.get("receiverDelete"), false),
                                    criteriaBuilder.equal(root.get("receiverRead"), read))));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.and(criteriaBuilder.isNull(root.get("sender")),
                                    criteriaBuilder.equal(root.get("senderDelete"), false)),
                            criteriaBuilder.and(criteriaBuilder.isNull(root.get("receiver")),
                                    criteriaBuilder.equal(root.get("receiverDelete"), false))));
        }
    }
    criteriaQuery.where(restrictions);
    return super.count(criteriaQuery, null);
}

From source file:ca.uhn.fhir.jpa.dao.BaseFhirResourceDao.java

private Set<Long> addPredicateLanguage(Set<Long> thePids, List<List<? extends IQueryParameterType>> theList) {
    if (theList == null || theList.isEmpty()) {
        return thePids;
    }//from   w  ww  .  j a v a2s. co m
    if (theList.size() > 1) {
        throw new InvalidRequestException(
                "Language parameter can not have more than one AND value, found " + theList.size());
    }

    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceTable> from = cq.from(ResourceTable.class);
    cq.select(from.get("myId").as(Long.class));

    Set<String> values = new HashSet<String>();
    for (IQueryParameterType next : theList.get(0)) {
        if (next instanceof StringParam) {
            String nextValue = ((StringParam) next).getValue();
            if (isBlank(nextValue)) {
                continue;
            }
            values.add(nextValue);
        } else {
            throw new InternalErrorException("Lanugage parameter must be of type "
                    + StringParam.class.getCanonicalName() + " - Got " + next.getClass().getCanonicalName());
        }
    }

    if (values.isEmpty()) {
        return thePids;
    }

    Predicate typePredicate = builder.equal(from.get("myResourceType"), myResourceName);
    Predicate langPredicate = from.get("myLanguage").as(String.class).in(values);
    Predicate masterCodePredicate = builder.and(typePredicate, langPredicate);

    if (thePids.size() > 0) {
        Predicate inPids = (from.get("myId").in(thePids));
        cq.where(builder.and(masterCodePredicate, inPids));
    } else {
        cq.where(masterCodePredicate);
    }

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    return new HashSet<Long>(q.getResultList());
}