Example usage for javax.persistence.criteria Subquery where

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

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

Introduction

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

Prototype

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

Source Link

Document

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

Usage

From source file:dao.jpa.TestJpaDao.java

@Test
@Transactional//  ww w.  j  a va 2s .  co  m
public void testCopy() {
    EntityManager em = bookDao.getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> criteria = cb.createQuery(Book.class);

    // Fetch join
    Root<Book> root = criteria.from(Book.class);
    Path<String> path = root.join("author").<String>get("name");
    root.fetch("author");
    criteria.select(root);

    // SubQuery
    Subquery<String> sq = criteria.subquery(String.class);
    Root<Author> author = sq.from(Author.class);
    sq.select(author.<String>get("name"));
    sq.where(cb.equal(author.<String>get("name"), "Rod"));

    criteria.where(cb.in(path).value(sq));

    CriteriaQuery<Book> copy = cb.createQuery(Book.class);
    JpaUtils.copyCriteria(criteria, copy);

    List<Book> copyBooks = em.createQuery(copy).getResultList();
    List<Book> books = em.createQuery(criteria).getResultList();
    assertEquals(books, copyBooks);
}

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

public List<Article> findList(ArticleCategory articleCategory, Tag tag, Boolean isPublication, Integer count,
        List<Filter> filters, List<Order> orders) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);//from   w ww.  j a va2 s.com
    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 (tag != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    criteriaQuery.where(restrictions);
    if (orders == null || orders.isEmpty()) {
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")),
                criteriaBuilder.desc(root.get("createDate")));
    }
    return super.findList(criteriaQuery, null, count, filters, orders);
}

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

public Page<Article> findPage(ArticleCategory articleCategory, Tag tag, Boolean isPublication,
        Pageable pageable) {/*from   w w w  . j ava  2  s .  c  om*/
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);
    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 (tag != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    criteriaQuery.where(restrictions);
    if (pageable == null
            || ((StringUtils.isEmpty(pageable.getOrderProperty()) || pageable.getOrderDirection() == null)
                    && CollectionUtils.isEmpty(pageable.getOrders()))) {
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")),
                criteriaBuilder.desc(root.get("createDate")));
    }
    return super.findPage(criteriaQuery, pageable);
}

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  a  v a2 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:com.vladmihalcea.HibernateCriteriaTest.java

private List<Product> getProducts_Mercilessly() {
    return transactionTemplate.execute(new TransactionCallback<List<Product>>() {
        @Override//  w w  w.ja v a2s .  co m
        public List<Product> doInTransaction(TransactionStatus transactionStatus) {
            CriteriaBuilder cb = entityManager.getCriteriaBuilder();
            CriteriaQuery<Product> query = cb.createQuery(Product.class);
            Root<Product> product = query.from(Product.class);
            query.select(product);
            query.distinct(true);

            List<Predicate> criteria = new ArrayList<Predicate>();
            criteria.add(cb.like(cb.lower(product.get(Product_.name)), "%tv%"));

            Subquery<Long> subQuery = query.subquery(Long.class);
            Root<Image> infoRoot = subQuery.from(Image.class);
            Join<Image, Product> productJoin = infoRoot.join(Image_.product);
            subQuery.select(productJoin.<Long>get(Product_.id));

            subQuery.where(cb.gt(infoRoot.get(Image_.index), 0));
            criteria.add(cb.in(product.get(Product_.id)).value(subQuery));
            query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
            return entityManager.createQuery(query).getResultList();
        }
    });
}

From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java

@Override
public Specification<Device> forManufacturer(final Manufacturer manufacturer)
        throws ArgumentNullOrEmptyException {
    if (manufacturer == null) {
        throw new ArgumentNullOrEmptyException("manufacturer");
    }//  w w w . j a va  2s .c o  m

    return new Specification<Device>() {
        @Override
        public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query,
                final CriteriaBuilder cb) {

            final Subquery<Long> subquery = query.subquery(Long.class);
            final Root<DeviceModel> deviceModelRoot = subquery.from(DeviceModel.class);
            subquery.select(deviceModelRoot.get("id").as(Long.class));
            subquery.where(cb.equal(cb.upper(deviceModelRoot.get("manufacturerId").<String>get("name")),
                    manufacturer.getName().toUpperCase()));
            return cb.in(deviceRoot.get("deviceModel").get("id").as(Long.class)).value(subquery);
        }
    };
}

From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java

@Override
public Specification<Device> isManagedExternally(final Boolean isManagedExternally)
        throws ArgumentNullOrEmptyException {
    if (isManagedExternally == null) {
        throw new ArgumentNullOrEmptyException("isManagedExternally");
    }/*from w  w w.j  a  v a2  s.  c  om*/

    return new Specification<Device>() {

        @Override
        public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query,
                final CriteriaBuilder cb) {
            final Subquery<Long> subquery = query.subquery(Long.class);
            final Root<DeviceAuthorization> deviceAuthorizationRoot = subquery.from(DeviceAuthorization.class);
            subquery.select(cb.countDistinct(deviceAuthorizationRoot));
            subquery.where(cb.equal(deviceAuthorizationRoot.get("device"), deviceRoot.<Long>get("id")));
            if (isManagedExternally) {
                return cb.greaterThan(subquery, Long.valueOf(1));
            } else {
                return cb.lessThanOrEqualTo(subquery, Long.valueOf(1));
            }
        }
    };
}

From source file:org.osiam.resource_server.storage.dao.ResourceDao.java

public <T extends ResourceEntity> SearchResult<T> search(Class<T> clazz, ParseTree filterTree, int count,
        int startIndex, String sortBy, String sortOrder, FilterParser<T> filterParser) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<T> resourceQuery = cb.createQuery(clazz);
    Root<T> resourceRoot = resourceQuery.from(clazz);

    Subquery<Long> internalIdQuery = resourceQuery.subquery(Long.class);
    Root<T> internalIdRoot = internalIdQuery.from(clazz);
    internalIdQuery.select(internalIdRoot.get(ResourceEntity_.internalId));

    if (filterTree != null && filterTree.getChildCount() > 0) {
        Predicate predicate = filterParser.createPredicateAndJoin(filterTree, internalIdRoot);
        internalIdQuery.where(predicate);
    }//  w  w w.j a  va2  s .  c o  m

    resourceQuery.select(resourceRoot)
            .where(cb.in(resourceRoot.get(ResourceEntity_.internalId)).value(internalIdQuery));

    // TODO: evaluate if a User-/GroupDao supplied default sortBy field is possible
    Expression<?> sortByField = resourceRoot.get(ResourceEntity_.id);

    if (sortBy != null && !sortBy.isEmpty()) {
        sortByField = filterParser.createSortByField(sortBy, resourceRoot);
    }

    // default order is ascending
    Order order = cb.asc(sortByField);

    if (sortOrder.equalsIgnoreCase("descending")) {
        order = cb.desc(sortByField);
    }

    resourceQuery.orderBy(order);

    TypedQuery<T> query = em.createQuery(resourceQuery);
    query.setFirstResult(startIndex);
    query.setMaxResults(count);

    List<T> results = query.getResultList();

    long totalResult = getTotalResults(clazz, internalIdQuery);

    return new SearchResult<>(results, totalResult);
}

From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java

@Override
public Specification<Device> forOrganisation(final Organisation organisation)
        throws ArgumentNullOrEmptyException {
    if (organisation == null) {
        throw new ArgumentNullOrEmptyException("organisation");
    }//from w  w  w  .j a  v a 2s.co m

    return new Specification<Device>() {
        @Override
        public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query,
                final CriteriaBuilder cb) {

            final Subquery<Long> subquery = query.subquery(Long.class);
            final Root<DeviceAuthorization> deviceAuthorizationRoot = subquery.from(DeviceAuthorization.class);
            subquery.select(deviceAuthorizationRoot.get("device").get("id").as(Long.class));
            subquery.where(cb.equal(deviceAuthorizationRoot.get("organisation"), organisation.getId()));

            return cb.in(deviceRoot.get("id")).value(subquery);
        }
    };
}

From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java

@Override
public Specification<Device> forOwner(final String organisation) throws ArgumentNullOrEmptyException {
    if (organisation == null) {
        throw new ArgumentNullOrEmptyException("owner");
    }//from  w  w  w. j a v a  2s. com

    return new Specification<Device>() {
        @Override
        public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query,
                final CriteriaBuilder cb) {

            final Subquery<Long> subquery = query.subquery(Long.class);
            final Root<DeviceAuthorization> deviceAuthorizationRoot = subquery.from(DeviceAuthorization.class);
            subquery.select(deviceAuthorizationRoot.get("device").get("id").as(Long.class));
            subquery.where(cb.and(
                    cb.like(cb.upper(deviceAuthorizationRoot.get("organisation").<String>get("name")),
                            organisation.toUpperCase()),
                    cb.equal(deviceAuthorizationRoot.get("functionGroup"),
                            DeviceFunctionGroup.OWNER.ordinal())));
            return cb.in(deviceRoot.get("id")).value(subquery);
        }
    };
}