Example usage for javax.persistence.criteria CriteriaQuery select

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

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

Introduction

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

Prototype

CriteriaQuery<T> select(Selection<? extends T> selection);

Source Link

Document

Specify the item that is to be returned in the query result.

Usage

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

public <T extends Serializable> int getItemCount(Class<T> classObj) throws Exception {
    int returnValue = 0;

    EntityManager em = getEntityManager();

    try {//from www  .  j  a va 2s .  co m

        // StringBuffer hsqlQuery = new StringBuffer();
        // hsqlQuery.append("select count(*) from ");
        // hsqlQuery.append(classObj.getCanonicalName());
        // hsqlQuery.append(" as o");
        // Query q = em.createQuery(hsqlQuery.toString());
        //
        // returnValue = ((Long) q.getSingleResult()).intValue();

        CriteriaBuilder qb = em.getCriteriaBuilder();
        CriteriaQuery<Long> cq = qb.createQuery(Long.class);
        cq.select(qb.count(cq.from(classObj)));

        Long res = em.createQuery(cq).getSingleResult();

        return res.intValue();

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

}

From source file:dk.dma.msinm.service.CategoryService.java

/**
 * Looks up an category by name//from  ww  w  .  ja va 2  s.c o m
 * @param name the name to search for
 * @param lang the language. Optional
 * @param parentId the parent ID. Optional
 * @return The matching category, or null if not found
 */
public Category findByName(String name, String lang, Integer parentId) {
    // Sanity check
    if (StringUtils.isBlank(name)) {
        return null;
    }

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Category> categoryQuery = builder.createQuery(Category.class);

    Root<Category> categoryRoot = categoryQuery.from(Category.class);

    // Build the predicate
    PredicateHelper<Category> predicateBuilder = new PredicateHelper<>(builder, categoryQuery);

    // Match the name
    Join<Category, CategoryDesc> descs = categoryRoot.join("descs", JoinType.LEFT);
    predicateBuilder.like(descs.get("name"), name);
    // Optionally, match the language
    if (StringUtils.isNotBlank(lang)) {
        predicateBuilder.equals(descs.get("lang"), lang);
    }

    // Optionally, match the parent
    if (parentId != null) {
        categoryRoot.join("parent", JoinType.LEFT);
        Path<Category> parent = categoryRoot.get("parent");
        predicateBuilder.equals(parent.get("id"), parentId);
    }

    // Complete the query
    categoryQuery.select(categoryRoot).distinct(true).where(predicateBuilder.where());

    // Execute the query and update the search result
    List<Category> result = em.createQuery(categoryQuery).getResultList();

    return result.size() > 0 ? result.get(0) : null;
}

From source file:org.jdal.dao.jpa.JpaDao.java

/**
 * Build CriteriaQuery using declared JpaCriteriaBuilder in filterMap
 * @param page//ww w  .ja  va 2s . c o m
 * @return a CriteriaQuery from filter
 */
@SuppressWarnings("unchecked")
private <K> CriteriaQuery<K> getCriteria(Page<K> page) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<K> c = (CriteriaQuery<K>) cb.createQuery();
    Filter filter = null;
    if (page.getFilter() instanceof Filter) {
        filter = (Filter) page.getFilter();
        JpaCriteriaBuilder<K> jcb = (JpaCriteriaBuilder<K>) criteriaBuilderMap.get(filter.getFilterName());
        if (jcb != null) {
            if (log.isDebugEnabled())
                log.debug("Found JpaCriteriaBuilder for filter: " + filter.getFilterName());
            // build criteria
            c = jcb.build(c, cb, filter);
        } else {
            log.error("No CriteriaBuilder found for filter name [" + filter.getFilterName() + "]");
            c.from(entityClass);
        }
    } else {
        c.select((Selection<? extends K>) c.from(getEntityClass()));
    }

    return c;
}

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;//  w ww . j  a  v 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:in.bookmylab.jpa.JpaDAO.java

public List<ResourceBooking> searchResourceBooking(BookingSearchInput si) {
    EntityManager em = emf.createEntityManager();
    try {//from w  w w.j  a v  a 2  s  .co  m
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<ResourceBooking> cq = cb.createQuery(ResourceBooking.class);
        Root<ResourceBooking> s = cq.from(ResourceBooking.class);
        List<Predicate> pred = new ArrayList<Predicate>();
        if (si.userId != null) {
            pred.add(cb.equal(s.<User>get("user").<Integer>get("userId"), si.userId));
        }
        if (si.bookingDateFrom != null) {
            pred.add(cb.greaterThanOrEqualTo(s.<Date>get("bookingDate"), si.bookingDateFrom));
        }
        if (si.bookingDateTo != null) {
            pred.add(cb.lessThanOrEqualTo(s.<Date>get("bookingDate"), si.bookingDateTo));
        }
        if (!StringUtils.isEmpty(si.lab)) {
            pred.add(cb.equal(s.<String>get("lab"), si.lab));
        }
        if (!StringUtils.isEmpty(si.status)) {
            pred.add(cb.equal(s.<String>get("status"), si.status));
        }
        cq.select(s).where(pred.toArray(new Predicate[] {}));
        return em.createQuery(cq).getResultList();
    } finally {
        em.close();
    }
}

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

public TBusEvaluateinfoEntity getRateByByApplicationIdAndCarId(String applicationId, String carId,
        PrincipalExt principalExt) {//from ww w  .  j ava  2s.c  o  m
    applySecurityFilter("applications", principalExt);

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TBusEvaluateinfoEntity> cq = cb.createQuery(TBusEvaluateinfoEntity.class);
    Root<TBusEvaluateinfoEntity> rateRoot = cq.from(TBusEvaluateinfoEntity.class);
    cq.select(rateRoot);
    cq.where(
            cb.equal(rateRoot.get(TBusEvaluateinfoEntity_.application).get(TBusApplyinfoEntity_.uuid),
                    applicationId),
            cb.equal(rateRoot.get(TBusEvaluateinfoEntity_.car).get(TAzCarinfoEntity_.id), carId));
    return Iterables.getFirst(em.createQuery(cq).getResultList(), null);

}

From source file:dk.dma.msinm.service.AreaService.java

/**
 * Looks up an area by name//from   w w  w  .  j av a 2  s. c  o m
 * @param name the name to search for
 * @param lang the language. Optional
 * @param parentId the parent ID. Optional
 * @return The matching area, or null if not found
 */
public Area findByName(String name, String lang, Integer parentId) {
    // Sanity check
    if (StringUtils.isBlank(name)) {
        return null;
    }

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Area> areaQuery = builder.createQuery(Area.class);

    Root<Area> areaRoot = areaQuery.from(Area.class);

    // Build the predicate
    PredicateHelper<Area> predicateBuilder = new PredicateHelper<>(builder, areaQuery);

    // Match the name
    Join<Area, AreaDesc> descs = areaRoot.join("descs", JoinType.LEFT);
    predicateBuilder.like(descs.get("name"), name);
    // Optionally, match the language
    if (StringUtils.isNotBlank(lang)) {
        predicateBuilder.equals(descs.get("lang"), lang);
    }

    // Optionally, match the parent
    if (parentId != null) {
        areaRoot.join("parent", JoinType.LEFT);
        Path<Area> parent = areaRoot.get("parent");
        predicateBuilder.equals(parent.get("id"), parentId);
    }

    // Complete the query
    areaQuery.select(areaRoot).distinct(true).where(predicateBuilder.where());

    // Execute the query and update the search result
    List<Area> result = em.createQuery(areaQuery).getResultList();

    return result.size() > 0 ? result.get(0) : null;
}

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

private TSysOrgEntity findRightFleetOfCoordinator(String coordinatorId) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TSysOrgEntity> cq = cb.createQuery(TSysOrgEntity.class);
    Root<TBusBusinessRelaEntity> mapRoot = cq.from(TBusBusinessRelaEntity.class);

    Root<TSysUserEntity> userRoot = cq.from(TSysUserEntity.class);

    cq.select(mapRoot.get(TBusBusinessRelaEntity_.fleet));

    Path<TSysOrgEntity> coordinatorOrg = userRoot.get(TSysUserEntity_.department);
    cq.where(cb.or(cb.and(cb.like(coordinatorOrg.get(TSysOrgEntity_.orgid), "001%"

    ), cb.equal(mapRoot.get(TBusBusinessRelaEntity_.fleet), coordinatorOrg)),
            cb.and(cb.notLike(coordinatorOrg.get(TSysOrgEntity_.orgid), "001%"),
                    cb.equal(mapRoot.get(TBusBusinessRelaEntity_.busOrg), coordinatorOrg))

    ),// www  .ja  v  a2s.c o  m

            cb.equal(userRoot.get(TSysUserEntity_.userid), coordinatorId)

    );

    return Iterables.getFirst(em.createQuery(cq).getResultList(), null);
}

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

public List<TAzCarinfoEntity> listAllCandidateCars(String[] applicationIds, String keyword, Integer offset,
        Integer limit, PrincipalExt principalExt) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TAzCarinfoEntity> cq = cb.createQuery(TAzCarinfoEntity.class);
    Root<TAzCarinfoEntity> carRoot = cq.from(TAzCarinfoEntity.class);
    carRoot.fetch(TAzCarinfoEntity_.driver, JoinType.LEFT);

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

    applyOverlapScheduleCarSubquery(overlapScheduleCarSubQuery, applicationIds, carRoot, null, cb,
            principalExt);//w w  w  .j  av  a2  s .c o  m
    Predicate predicate = cb.and(cb.equal(carRoot.get(TAzCarinfoEntity_.clzt), "02"),
            cb.or(cb.equal(carRoot.get(TAzCarinfoEntity_.repairingState), RepairingState.NONE.id()),
                    cb.isNull(carRoot.get(TAzCarinfoEntity_.repairingState))

            ), cb.not(cb.exists(overlapScheduleCarSubQuery))

    );

    if (keyword != null) {
        predicate = cb.and(predicate,
                cb.or(cb.like(carRoot.get(TAzCarinfoEntity_.sysOrg).get(TSysOrgEntity_.orgname),
                        "%" + keyword + "%"),
                        cb.like(carRoot.get(TAzCarinfoEntity_.cphm), "%" + keyword + "%")));
    }

    cq.where(predicate);
    cq.orderBy(cb.asc(carRoot.get(TAzCarinfoEntity_.cphm)));
    TypedQuery<TAzCarinfoEntity> query = em.createQuery(cq);
    if (offset != null) {
        query.setFirstResult(offset);
    }

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

    applySecurityFilter("cars", principalExt);

    return query.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);

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

    applyOverlapScheduleCarSubquery(overlapScheduleCarSubQuery, applicationIds, carRoot, driverRoot, cb,
            principalExt);/*from w  w  w  . j  a v a  2 s .c  o  m*/

    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();

}