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.wookie.beans.jpa.JPAPersistenceManager.java

@SuppressWarnings("unchecked")
public <T extends IBean> T[] findByValues(Class<T> beansInterface, Map<String, Object> values, String orderBy,
        boolean ascending) {
    // validate entity manager transaction
    if (entityManager == null) {
        throw new IllegalStateException("Transaction not initiated or already closed");
    }//  ww  w.j  av a 2s  .  c o m

    // validate bean interface
    Class<? extends IBean> beanClass = BEAN_INTERFACE_TO_CLASS_MAP.get(beansInterface);
    if (beanClass == null) {
        throw new IllegalArgumentException("Invalid bean interface specified");
    }

    // get persistent beans by criteria
    try {
        // construct query criteria
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<? extends IBean> criteriaQuery = criteriaBuilder.createQuery(beanClass);
        Root<? extends IBean> beanRoot = criteriaQuery.from(beanClass);
        if ((values != null) && !values.isEmpty()) {
            Predicate predicate = null;
            for (Map.Entry<String, Object> value : values.entrySet()) {
                Predicate valuePredicate = ((value.getValue() != null)
                        ? criteriaBuilder.equal(beanRoot.get(value.getKey()), value.getValue())
                        : criteriaBuilder.isNull(beanRoot.get(value.getKey())));
                predicate = ((predicate != null) ? criteriaBuilder.and(predicate, valuePredicate)
                        : valuePredicate);
            }
            criteriaQuery.where(predicate);
        }
        if (orderBy != null) {
            criteriaQuery.orderBy(ascending ? criteriaBuilder.asc(beanRoot.get(orderBy))
                    : criteriaBuilder.desc(beanRoot.get(orderBy)));
        }

        // invoke query
        Query query = entityManager.createQuery(criteriaQuery);
        List<? extends IBean> beansList = query.getResultList();
        if ((beansList != null) && !beansList.isEmpty()) {
            return beansList.toArray((T[]) Array.newInstance(beansInterface, beansList.size()));
        }
    } catch (Exception e) {
        logger.error("Unexpected exception: " + e, e);
    }
    return (T[]) Array.newInstance(beansInterface, 0);
}

From source file:it.attocchi.jpa2.JpaController.java

public <T extends Serializable> Long countBy(Class<T> clazz, JPAEntityFilter<T> filter) throws Exception {
    Long res = 0L;//from  w  ww .j  av a2  s . c  o  m

    testClazz(clazz);

    EntityManager em = getEntityManager();

    try {

        if (filter != null) {

            // Source:
            // http://stackoverflow.com/questions/5349264/total-row-count-for-pagination-using-jpa-criteria-api

            CriteriaQuery<T> cq = filter.getCriteria(clazz, getEmf());

            // TypedQuery<T> q = em.createQuery(cq);
            //
            // q.setFirstResult(filter.getLimit() * filter.getPageNumber());
            // q.setMaxResults(filter.getLimit());
            //
            // res = Long.valueOf(q.getResultList().size());

            // CriteriaBuilder qb = em.getCriteriaBuilder();
            // CriteriaQuery<Long> countQuery = qb.createQuery(Long.class);
            // countQuery.select(qb.count(cq.from(clazz)));
            // // cq.where(/*your stuff*/);
            // return em.createQuery(cq).getSingleResult();

            CriteriaBuilder builder = em.getCriteriaBuilder();
            CriteriaQuery<Long> cqCount = builder.createQuery(Long.class);
            cqCount.select(builder.count(cqCount.from(clazz)));

            // Following line if commented causes
            // [org.hibernate.hql.ast.QuerySyntaxException: Invalid path:
            // 'generatedAlias1.enabled' [select count(generatedAlias0) from
            // xxx.yyy.zzz.Brand as generatedAlias0 where (
            // generatedAlias1.enabled=:param0 ) and (
            // lower(generatedAlias1.description) like :param1 )]]
            em.createQuery(cqCount);

            // filter.getCriteria(clazz, getEmf());
            cqCount.where(filter.getWherePredicate(clazz, getEmf()));

            res = em.createQuery(cqCount).getSingleResult();

        } else {
            // res = findAll(clazz);

            CriteriaBuilder qb = em.getCriteriaBuilder();
            CriteriaQuery<Long> cq = qb.createQuery(Long.class);
            cq.select(qb.count(cq.from(clazz)));
            // cq.where(/*your stuff*/);
            res = em.createQuery(cq).getSingleResult();

        }

    } catch (Exception e) {
        throw e;
    } finally {
        // Close the database connection:
        if (!globalTransactionOpen) {
            // if (em.getTransaction().isActive())
            // em.getTransaction().rollback();
            closeEm(); // em.close();
        }
    }

    return res;
}

From source file:org.openmeetings.app.data.conference.Roommanagement.java

public List<Rooms> getAllRooms() {
    try {//from  w  w  w.  j a va2  s. co  m
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Rooms> cq = cb.createQuery(Rooms.class);
        Root<Rooms> c = cq.from(Rooms.class);
        Predicate condition = cb.equal(c.get("deleted"), "false");
        cq.where(condition);
        TypedQuery<Rooms> q = em.createQuery(cq);
        List<Rooms> ll = q.getResultList();
        return ll;
    } catch (Exception ex2) {
        log.error("[getAllRooms]", ex2);
    }
    return null;
}

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

public List<Product> findList(ProductCategory productCategory, Date beginDate, Date endDate, Integer first,
        Integer count) {// ww w. ja  v  a 2  s  .  c  o  m
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
    Root<Product> root = criteriaQuery.from(Product.class);
    criteriaQuery.select(root);
    Predicate restrictions = criteriaBuilder.conjunction();
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), true));
    if (productCategory != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.or(criteriaBuilder.equal(root.get("productCategory"), productCategory),
                        criteriaBuilder.like(root.get("productCategory").<String>get("treePath"),
                                "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                        + ProductCategory.TREE_PATH_SEPARATOR + "%")));
    }
    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));
    }
    criteriaQuery.where(restrictions);
    criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
    return super.findList(criteriaQuery, first, count, null, null);
}

From source file:org.eclipse.hawkbit.repository.jpa.JpaTargetManagement.java

@Override
public Slice<Target> findTargetsAllOrderByLinkedDistributionSet(final Pageable pageable,
        final Long orderByDistributionId, final FilterParams filterParams) {
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    final CriteriaQuery<JpaTarget> query = cb.createQuery(JpaTarget.class);
    final Root<JpaTarget> targetRoot = query.from(JpaTarget.class);

    // select case expression to retrieve the case value as a column to be
    // able to order based on
    // this column, installed first,...
    final Expression<Object> selectCase = cb.selectCase()
            .when(cb.equal(targetRoot.get(JpaTarget_.installedDistributionSet).get(JpaDistributionSet_.id),
                    orderByDistributionId), 1)
            .when(cb.equal(targetRoot.get(JpaTarget_.assignedDistributionSet).get(JpaDistributionSet_.id),
                    orderByDistributionId), 2)
            .otherwise(100);/*from  www. ja v a2 s. c  o m*/
    // multiselect statement order by the select case and controllerId
    query.distinct(true);
    // build the specifications and then to predicates necessary by the
    // given filters
    final Predicate[] specificationsForMultiSelect = specificationsToPredicate(
            buildSpecificationList(filterParams), targetRoot, query, cb);

    // if we have some predicates then add it to the where clause of the
    // multiselect
    if (specificationsForMultiSelect.length > 0) {
        query.where(specificationsForMultiSelect);
    }
    // add the order to the multi select first based on the selectCase
    query.orderBy(cb.asc(selectCase), cb.desc(targetRoot.get(JpaTarget_.id)));
    // the result is a Object[] due the fact that the selectCase is an extra
    // column, so it cannot
    // be mapped directly to a Target entity because the selectCase is not a
    // attribute of the
    // Target entity, the the Object array contains the Target on the first
    // index (case of the
    // multiselect order) of the array and
    // the 2nd contains the selectCase int value.
    final int pageSize = pageable.getPageSize();
    final List<JpaTarget> resultList = entityManager.createQuery(query).setFirstResult(pageable.getOffset())
            .setMaxResults(pageSize + 1).getResultList();
    final boolean hasNext = resultList.size() > pageSize;
    return new SliceImpl<>(Collections.unmodifiableList(resultList), pageable, hasNext);
}

From source file:org.openmeetings.app.data.conference.Roommanagement.java

/**
 * gets a list of all availible rooms/*from w w w .  j  a  va 2s.co m*/
 * 
 * @param user_level
 * @param start
 * @param max
 * @param orderby
 * @param asc
 * @return
 */
public List<Rooms> getRoomsInternatl(int start, int max, String orderby, boolean asc) {
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Rooms> cq = cb.createQuery(Rooms.class);
        Root<Rooms> c = cq.from(Rooms.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<Rooms> q = em.createQuery(cq);
        q.setFirstResult(start);
        q.setMaxResults(max);
        List<Rooms> ll = q.getResultList();
        return ll;
    } catch (Exception ex2) {
        log.error("[getRooms ] ", ex2);
    }
    return null;
}

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

public List<TBusApproveSugEntity> listApplyApproveInfo(DateTime startTime, DateTime endTime,
        PrincipalExt principalExtOrNull) {
    if (startTime == null) {
        startTime = DateTimeUtil.appStartTime;
    }// w w  w. j a va2s .  co  m
    if (endTime == null) {
        endTime = DateTimeUtil.appEndTime;
    }

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TBusApproveSugEntity> cq = cb.createQuery(TBusApproveSugEntity.class);
    Root<TBusApproveSugEntity> approveRoot = cq.from(TBusApproveSugEntity.class);
    approveRoot.fetch(TBusApproveSugEntity_.application);
    approveRoot.fetch(TBusApproveSugEntity_.user, JoinType.LEFT);
    cq.select(approveRoot);

    Predicate predicate = cb.and(
            cb.between(approveRoot.get(TBusApproveSugEntity_.operatedate), new Timestamp(startTime.getMillis()),
                    new Timestamp(endTime.getMillis())),
            cb.or(cb.isNull(approveRoot.get(TBusApproveSugEntity_.updateBySync)),
                    cb.isFalse(approveRoot.get(TBusApproveSugEntity_.updateBySync))));

    cq.where(predicate);

    TypedQuery<TBusApproveSugEntity> query = em.createQuery(cq);

    applySecurityFilter("applications", principalExtOrNull);
    return query.getResultList();
}

From source file:org.apache.openmeetings.data.user.UserManager.java

public User getUserByIdAndDeleted(Long id) throws Exception {
    log.debug("Usermanagement.getUserById");

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> c = cq.from(User.class);
    Predicate condition = cb.equal(c.get("user_id"), id);
    cq.where(condition);
    TypedQuery<User> q = em.createQuery(cq);
    User u = null;//from w  w  w . j  a va2 s  .com
    try {
        u = q.getSingleResult();
    } catch (NoResultException e) {
        // u=null}
    }

    return u;

}

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

public List<Article> findList(ArticleCategory articleCategory, Boolean isPublication,
        Article.GenerateMethod generateMethod, Date beginDate, Date endDate, Integer first, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);//from  w  w w .  j ava 2 s.  c  om
    Predicate restrictions = criteriaBuilder.conjunction();
    if (articleCategory != null) {
        Subquery<ArticleCategory> subquery = criteriaQuery.subquery(ArticleCategory.class);
        Root<ArticleCategory> subqueryRoot = subquery.from(ArticleCategory.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.or(criteriaBuilder.equal(subqueryRoot, articleCategory),
                criteriaBuilder.like(subqueryRoot.<String>get("treePath"),
                        "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId()
                                + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.in(root.get("articleCategory")).value(subquery));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    if (generateMethod != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("generateMethod"), generateMethod));
    }
    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));
    }
    criteriaQuery.where(restrictions);
    return super.findList(criteriaQuery, first, count, null, null);
}

From source file:org.openmeetings.app.data.conference.Roommanagement.java

/**
 * //from   www  .ja v a  2 s  .co  m
 * @param rooms_organisation_id
 * @return
 */
public Rooms_Organisation getRoomsOrganisationById(long rooms_organisation_id) {
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Rooms_Organisation> cq = cb.createQuery(Rooms_Organisation.class);
        Root<Rooms_Organisation> c = cq.from(Rooms_Organisation.class);
        Predicate condition = cb.equal(c.get("rooms_organisation_id"), rooms_organisation_id);
        cq.where(condition);
        TypedQuery<Rooms_Organisation> q = em.createQuery(cq);
        List<Rooms_Organisation> ll = q.getResultList();

        if (ll.size() > 0) {
            return ll.get(0);
        }
    } catch (Exception ex2) {
        log.error("[getRoomsByOrganisation] ", ex2);
    }
    return null;
}