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:org.apache.openmeetings.data.user.UserManager.java

/**
 * query for a list of users//from w w w  .  j a v  a2 s.com
 * 
 * @param users_id
 * @param user_level
 * @param start
 * @param max
 * @param orderby
 * @return
 */
public SearchResult<User> getUsersList(long user_level, int start, int max, String orderby, boolean asc) {
    try {
        if (authLevelUtil.checkAdminLevel(user_level)) {
            SearchResult<User> sresult = new SearchResult<User>();
            sresult.setObjectName(User.class.getName());
            sresult.setRecords(usersDao.count());

            // get all users
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<User> cq = cb.createQuery(User.class);
            Root<User> c = cq.from(User.class);
            Predicate condition = cb.equal(c.get("deleted"), false);
            cq.where(condition);
            cq.distinct(asc);
            if (asc) {
                cq.orderBy(cb.asc(c.get(orderby)));
            } else {
                cq.orderBy(cb.desc(c.get(orderby)));
            }
            TypedQuery<User> q = em.createQuery(cq);
            q.setFirstResult(start);
            q.setMaxResults(max);
            List<User> ll = q.getResultList();
            sresult.setResult(ll);
            return sresult;
        }
    } catch (Exception ex2) {
        log.error("[getUsersList] " + ex2);
    }
    return null;
}

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

protected TagList getTags(Class<? extends IBaseResource> theResourceType, IIdType theResourceId) {
    String resourceName = null;// w  ww  .  ja  v a 2s. c o  m
    if (theResourceType != null) {
        resourceName = toResourceName(theResourceType);
        if (theResourceId != null && theResourceId.hasVersionIdPart()) {
            IFhirResourceDao<? extends IBaseResource> dao = getDao(theResourceType);
            BaseHasResource entity = dao.readEntity(theResourceId);
            TagList retVal = new TagList();
            for (BaseTag next : entity.getTags()) {
                retVal.add(next.getTag().toTag());
            }
            return retVal;
        }
    }

    Set<Long> tagIds = new HashSet<Long>();
    findMatchingTagIds(resourceName, theResourceId, tagIds, ResourceTag.class);
    findMatchingTagIds(resourceName, theResourceId, tagIds, ResourceHistoryTag.class);
    if (tagIds.isEmpty()) {
        return new TagList();
    }
    {
        CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
        CriteriaQuery<TagDefinition> cq = builder.createQuery(TagDefinition.class);
        Root<TagDefinition> from = cq.from(TagDefinition.class);
        cq.where(from.get("myId").in(tagIds));
        cq.orderBy(builder.asc(from.get("mySystem")), builder.asc(from.get("myCode")));
        TypedQuery<TagDefinition> q = myEntityManager.createQuery(cq);
        q.setMaxResults(getConfig().getHardTagListLimit());

        TagList retVal = new TagList();
        for (TagDefinition next : q.getResultList()) {
            retVal.add(next.toTag());
        }

        return retVal;
    }
}

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

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, net.osxx.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }/*  w  w w.  ja  v a2s.c  o  m*/
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

From source file:com.dp2345.dao.impl.ProductDaoImpl.java

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, com.dp2345.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }//from  w ww  .jav  a2 s  . c o  m
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

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

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, net.shopxx.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }/*from   w  w w . j a va  2 s  .com*/
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

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

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, net.groupbuy.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }/*from  www  . ja va  2s  .c  om*/
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

From source file:net.shopxx.dao.impl.CouponCodeDaoImpl.java

public Long count(Coupon coupon, Member member, Boolean hasBegun, Boolean hasExpired, Boolean isUsed) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<CouponCode> criteriaQuery = criteriaBuilder.createQuery(CouponCode.class);
    Root<CouponCode> root = criteriaQuery.from(CouponCode.class);
    criteriaQuery.select(root);//from  w  ww .j av a2s  .co  m
    Predicate restrictions = criteriaBuilder.conjunction();
    Path<Coupon> couponPath = root.get("coupon");
    if (coupon != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(couponPath, coupon));
    }
    if (member != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
    }
    if (hasBegun != null) {
        if (hasBegun) {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(couponPath.get("beginDate").isNull(),
                            criteriaBuilder.lessThanOrEqualTo(couponPath.<Date>get("beginDate"), new Date())));
        } else {
            restrictions = criteriaBuilder.and(restrictions, couponPath.get("beginDate").isNotNull(),
                    criteriaBuilder.greaterThan(couponPath.<Date>get("beginDate"), new Date()));
        }
    }
    if (hasExpired != null) {
        if (hasExpired) {
            restrictions = criteriaBuilder.and(restrictions, couponPath.get("endDate").isNotNull(),
                    criteriaBuilder.lessThanOrEqualTo(couponPath.<Date>get("endDate"), new Date()));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(couponPath.get("endDate").isNull(),
                            criteriaBuilder.greaterThan(couponPath.<Date>get("endDate"), new Date())));
        }
    }
    if (isUsed != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUsed"), isUsed));
    }
    criteriaQuery.where(restrictions);
    return super.count(criteriaQuery, null);
}

From source file:com.creditcloud.common.entities.dao.AbstractReadDAO.java

/**
 * list entity by CriteriaInfo//from   w w w . jav a 2 s  . c o  m
 *
 * @param criteriaInfo
 * @return PagedResult
 */
public PagedResult<T> list(CriteriaInfo criteriaInfo) {
    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery cq = cb.createQuery(entityClass);
    Root<T> userRoot = cq.from(entityClass);
    cq.select(userRoot);
    ParamInfo paramInfo = criteriaInfo.getParamInfo();
    PageInfo pageInfo = criteriaInfo.getPageInfo();
    SortInfo sortInfo = criteriaInfo.getSortInfo();

    //build query for paramInfo
    if (paramInfo != null) {
        Set<Predicate> andCriteria = new HashSet();
        Set<Predicate> orCriteria = new HashSet();

        for (ParamItem item : paramInfo.getParamItems()) {
            Predicate predicate;
            if (item.getValue() instanceof String) {
                //fuzy search for string
                String regExp = "%" + item.getValue() + "%";
                predicate = cb.like((Expression) (userRoot.get(item.getFieldName())), regExp);
            } else {
                predicate = cb.equal((userRoot.get(item.getFieldName())), item.getValue());
            }

            switch (item.getOperator()) {
            case AND:
                andCriteria.add(predicate);
                break;
            case OR:
                orCriteria.add(predicate);
                break;
            }
        }
        if (orCriteria.size() > 0) {
            Predicate or = cb.or(orCriteria.toArray(new Predicate[orCriteria.size()]));
            andCriteria.add(or);
        }
        if (andCriteria.size() > 0) {
            Predicate and = cb.and(andCriteria.toArray(new Predicate[andCriteria.size()]));
            cq.where(and);
        }
    }

    //build query for sortInfo
    Set<Order> orderPredicate = new HashSet<>();
    if (sortInfo != null) {
        for (SortItem item : sortInfo.getSortItems()) {
            if (item.isDescending()) {
                orderPredicate.add(cb.desc(userRoot.get(item.getFieldName())));
            } else {
                orderPredicate.add(cb.asc(userRoot.get(item.getFieldName())));
            }
        }
    }
    if (orderPredicate.size() > 0) {
        cq.orderBy(orderPredicate.toArray(new Order[orderPredicate.size()]));
    }

    TypedQuery<T> query = em.createQuery(cq);
    //set result range
    if (pageInfo != null) {
        query.setFirstResult(pageInfo.getOffset());
        query.setMaxResults(pageInfo.getSize());
    }

    int totalSize;
    if (paramInfo != null && paramInfo.getParamItems().size() > 0) {
        totalSize = count(paramInfo);
    } else {
        totalSize = count();
    }

    return new PagedResult(query.getResultList(), totalSize);
}

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

protected TagDefinition getTag(TagTypeEnum theTagType, String theScheme, String theTerm, String theLabel) {
    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<TagDefinition> cq = builder.createQuery(TagDefinition.class);
    Root<TagDefinition> from = cq.from(TagDefinition.class);

    //@formatter:off
    if (isNotBlank(theScheme)) {
        cq.where(builder.and(builder.equal(from.get("myTagType"), theTagType),
                builder.equal(from.get("mySystem"), theScheme), builder.equal(from.get("myCode"), theTerm)));
    } else {/*from   www.j av  a 2  s .  c  om*/
        cq.where(builder.and(builder.equal(from.get("myTagType"), theTagType),
                builder.isNull(from.get("mySystem")), builder.equal(from.get("myCode"), theTerm)));
    }
    //@formatter:on

    TypedQuery<TagDefinition> q = myEntityManager.createQuery(cq);
    try {
        return q.getSingleResult();
    } catch (NoResultException e) {
        TagDefinition retVal = new TagDefinition(theTagType, theScheme, theTerm, theLabel);
        myEntityManager.persist(retVal);
        return retVal;
    }
}

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

public List<TBusApplyinfoEntity> getApplicationWithDetailByScheduleId(String scheduleId,
        PrincipalExt principalExt) {//from w w w.  j  a v a2s. c  om
    applySecurityFilter("applications", principalExt);

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TBusApplyinfoEntity> cq = cb.createQuery(TBusApplyinfoEntity.class);
    Root<TBusApplyinfoEntity> applyRoot = cq.from(TBusApplyinfoEntity.class);
    Fetch<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleFetch = applyRoot
            .fetch(TBusApplyinfoEntity_.schedule, JoinType.LEFT);
    applyRoot.fetch(TBusApplyinfoEntity_.fleet);
    applyRoot.fetch(TBusApplyinfoEntity_.passenger);
    applyRoot.fetch(TBusApplyinfoEntity_.coordinator);
    applyRoot.fetch(TBusApplyinfoEntity_.department);
    Join<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleJoin = (Join<TBusApplyinfoEntity, TBusScheduleRelaEntity>) scheduleFetch;
    Predicate predicate = cb.conjunction();
    predicate = cb.and(predicate, cb.equal(scheduleJoin.get(TBusScheduleRelaEntity_.uuid), scheduleId));
    cq.where(predicate);
    return em.createQuery(cq).getResultList();
}