Example usage for javax.persistence.criteria CriteriaQuery subquery

Introduction

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

Prototype

<U> Subquery<U> subquery(Class<U> type);

Source Link

Document

Create a subquery of the query.

Usage

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  .  jav  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(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> forManufacturer(final Manufacturer manufacturer)
        throws ArgumentNullOrEmptyException {
    if (manufacturer == null) {
        throw new ArgumentNullOrEmptyException("manufacturer");
    }/* www  .  j  ava2 s.  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 .  ja v  a  2 s.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<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: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 2  s  .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.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);
        }
    };
}

From source file:bq.jpa.demo.query.criteria.service.CriteriaService.java

/**
 * subquery: in//from  w w  w . j  ava  2s .  com
 * SELECT e FROM jpa_query_employee e WHERE e IN (SELECT emp FROM jpa_query_project p JOIN p.employees pe WHERE p.name = :projectname)
 */
@Transactional
public void doWhere1() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e = c.from(Employee.class);

    // subquery
    Subquery<Employee> sq = c.subquery(Employee.class);
    Root<Project> p = sq.from(Project.class);
    Join<Project, Employee> pe = p.join("employees");
    sq.select(pe).where(cb.equal(p.get("name"), cb.parameter(String.class, "projectname")));

    //
    c.select(e).where(cb.in(e).value(sq));
}

From source file:bq.jpa.demo.query.criteria.service.CriteriaService.java

/**
 * subquery(equivalent to dowhere1) : exists
 * SELECT e FROM jpa_query_employee e WHERE EXISTS (SELECT p FROM e.projects p WHERE p.name = :projectname)
 *///  ww  w.ja  v  a 2s.c  o  m
@Transactional
public void doWhere2() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e = c.from(Employee.class);

    // subquery
    Subquery<Project> sq = c.subquery(Project.class);
    // Root<Project> p = sq.from(Project.class);
    Root<Employee> se = sq.correlate(e);
    Join<Employee, Project> p = se.join("projects");
    sq.select(p).where(cb.equal(p.get("name"), cb.parameter(String.class, "projectname")));

    //
    c.select(e).where(cb.exists(sq));
}

From source file:com.expressui.core.dao.security.query.RelatedUsersQuery.java

@Override
public List<Predicate> buildCriteria(CriteriaBuilder builder, CriteriaQuery<User> query, Root<User> user) {
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (hasValue(role)) {
        ParameterExpression<Role> roleExp = builder.parameter(Role.class, "role");
        predicates.add(builder.equal(user.join("userRoles").get("role"), roleExp));
    }//w  ww.  j  av  a 2 s .  c  om

    if (hasValue(doesNotBelongToRole)) {
        Subquery<User> subquery = query.subquery(User.class);
        Root userRole = subquery.from(UserRole.class);
        ParameterExpression<Role> roleExp = builder.parameter(Role.class, "doesNotBelongToRole");
        subquery.select(userRole.get("user")).where(builder.equal(userRole.get("role"), roleExp));
        predicates.add(builder.not(user.in(subquery)));
    }

    return predicates;
}

From source file:com.expressui.core.dao.security.query.RoleQuery.java

@Override
public List<Predicate> buildCriteria(CriteriaBuilder builder, CriteriaQuery<Role> query, Root<Role> role) {
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (hasValue(name)) {
        ParameterExpression<String> nameExp = builder.parameter(String.class, "name");
        predicates.add(builder.like(builder.upper(role.<String>get("name")), nameExp));
    }/*w ww.  ja  v  a 2s.  c o  m*/

    if (hasValue(doesNotBelongToUser)) {
        Subquery<Role> subquery = query.subquery(Role.class);
        Root<UserRole> userRole = subquery.from(UserRole.class);
        ParameterExpression<User> userExp = builder.parameter(User.class, "doesNotBelongToUser");
        subquery.select(userRole.<Role>get("role")).where(builder.equal(userRole.get("user"), userExp));
        predicates.add(builder.not(role.in(subquery)));
    }

    return predicates;
}

From source file:com.expressui.core.dao.security.query.UserQuery.java

@Override
public List<Predicate> buildCriteria(CriteriaBuilder builder, CriteriaQuery<User> query, Root<User> user) {
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (hasValue(loginName)) {
        ParameterExpression<String> loginNameExp = builder.parameter(String.class, "loginName");
        predicates.add(builder.like(builder.upper(user.<String>get("loginName")), loginNameExp));
    }/*from  w w w .  ja  v a2  s  .  co  m*/

    if (hasValue(doesNotBelongToRole)) {
        Subquery<User> subquery = query.subquery(User.class);
        Root userRole = subquery.from(UserRole.class);
        ParameterExpression<Role> role = builder.parameter(Role.class, "doesNotBelongToRole");
        subquery.select(userRole.get("user")).where(builder.equal(userRole.get("role"), role));
        predicates.add(builder.not(user.in(subquery)));
    }

    return predicates;
}

From source file:com.vladmihalcea.HibernateCriteriaTest.java

private List<Product> getProducts_Mercilessly() {
    return transactionTemplate.execute(new TransactionCallback<List<Product>>() {
        @Override// w  ww  .  j ava 2 s . c  om
        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();
        }
    });
}