Example usage for javax.persistence.criteria Subquery select

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

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

Introduction

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

Prototype

Subquery<T> select(Expression<T> expression);

Source Link

Document

Specify the item that is to be returned as the subquery result.

Usage

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 v a2s. 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<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:bq.jpa.demo.query.criteria.service.CriteriaService.java

/**
 * subquery: in//from w  ww  .j a v a  2s. co  m
 * 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: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");
    }/* w w w.  j  av  a2s .  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 ww .j av  a  2  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.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: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  av  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(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> forFirmwareModuleVersion(final FirmwareModuleFilterType firmwareModuleFilterType,
        final String firmwareModuleVersion) throws ArgumentNullOrEmptyException {
    if (StringUtils.isEmpty(firmwareModuleVersion)) {
        throw new ArgumentNullOrEmptyException("firmwareModuleVersion");
    }/* w  ww.  ja v  a  2  s . c o  m*/
    if (firmwareModuleFilterType == null) {
        throw new ArgumentNullOrEmptyException("firmwareModuleType");
    }

    return new Specification<Device>() {
        @Override
        public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query,
                final CriteriaBuilder cb) {
            String moduleFieldName = "";
            switch (firmwareModuleFilterType) {
            case COMMUNICATION:
                moduleFieldName = "moduleVersionComm";
                break;
            case FUNCTIONAL:
                moduleFieldName = "moduleVersionFunc";
                break;
            case SECURITY:
                moduleFieldName = "moduleVersionSec";
                break;
            case M_BUS:
                moduleFieldName = "moduleVersionMbus";
                break;
            case MODULE_ACTIVE:
                moduleFieldName = "moduleVersionMa";
                break;
            case ACTIVE_FIRMWARE:
                break;

            default:
                break;
            }

            final Subquery<Long> subquery = query.subquery(Long.class);
            final Root<DeviceFirmware> deviceFirmwareRoot = subquery.from(DeviceFirmware.class);
            subquery.select(deviceFirmwareRoot.get("device").get("id").as(Long.class));
            subquery.where(cb.and(
                    cb.like(cb.upper(deviceFirmwareRoot.get("firmware").<String>get(moduleFieldName)),
                            firmwareModuleVersion.toUpperCase()),
                    cb.equal(deviceFirmwareRoot.<Boolean>get("active"), true)));
            return cb.in(deviceRoot.get("id").as(Long.class)).value(subquery);
        }
    };
}

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)
 *///w w w  .  ja  v  a 2s. com
@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:net.shopxx.dao.impl.OrderDaoImpl.java

public Page<Order> findPage(Order.Type type, Order.Status status, Member member, Goods goods,
        Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint,
        Boolean isAllocatedStock, Boolean hasExpired, Pageable pageable) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
    Root<Order> root = criteriaQuery.from(Order.class);
    criteriaQuery.select(root);//from  w ww. j ava  2  s . c o m
    Predicate restrictions = criteriaBuilder.conjunction();
    if (type != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type));
    }
    if (status != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status));
    }
    if (member != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
    }
    if (goods != null) {
        Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class);
        Root<Product> productSubqueryRoot = productSubquery.from(Product.class);
        productSubquery.select(productSubqueryRoot);
        productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods));

        Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class);
        Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class);
        orderItemSubquery.select(orderItemSubqueryRoot);
        orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root),
                criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery));
    }
    if (isPendingReceive != null) {
        Predicate predicate = criteriaBuilder.and(
                criteriaBuilder.or(root.get("expire").isNull(),
                        criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())),
                criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.completed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.failed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.denied),
                criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")));
        if (isPendingReceive) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isPendingRefunds != null) {
        Predicate predicate = criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.or(
                                criteriaBuilder.and(root.get("expire").isNotNull(),
                                        criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"),
                                                new Date())),
                                criteriaBuilder.equal(root.get("status"), Order.Status.failed),
                                criteriaBuilder.equal(root.get("status"), Order.Status.canceled),
                                criteriaBuilder.equal(root.get("status"), Order.Status.denied)),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)),
                criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"),
                                root.<BigDecimal>get("amount"))));
        if (isPendingRefunds) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isUseCouponCode != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode));
    }
    if (isExchangePoint != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint));
    }
    if (isAllocatedStock != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock));
    }
    if (hasExpired != null) {
        if (hasExpired) {
            restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(),
                    criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date()));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(),
                    criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())));
        }
    }
    criteriaQuery.where(restrictions);
    return super.findPage(criteriaQuery, pageable);
}

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

public Long count(Order.Type type, Order.Status status, Member member, Goods goods, Boolean isPendingReceive,
        Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint, Boolean isAllocatedStock,
        Boolean hasExpired) {/*from w  w  w  . j a va 2s  .c o m*/
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
    Root<Order> root = criteriaQuery.from(Order.class);
    criteriaQuery.select(root);
    Predicate restrictions = criteriaBuilder.conjunction();
    if (type != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type));
    }
    if (status != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status));
    }
    if (member != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
    }
    if (goods != null) {
        Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class);
        Root<Product> productSubqueryRoot = productSubquery.from(Product.class);
        productSubquery.select(productSubqueryRoot);
        productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods));

        Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class);
        Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class);
        orderItemSubquery.select(orderItemSubqueryRoot);
        orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root),
                criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery));
    }
    if (isPendingReceive != null) {
        Predicate predicate = criteriaBuilder.and(
                criteriaBuilder.or(root.get("expire").isNull(),
                        criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())),
                criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.completed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.failed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.denied),
                criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")));
        if (isPendingReceive) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isPendingRefunds != null) {
        Predicate predicate = criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.or(
                                criteriaBuilder.and(root.get("expire").isNotNull(),
                                        criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"),
                                                new Date())),
                                criteriaBuilder.equal(root.get("status"), Order.Status.failed),
                                criteriaBuilder.equal(root.get("status"), Order.Status.canceled),
                                criteriaBuilder.equal(root.get("status"), Order.Status.denied)),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)),
                criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"),
                                root.<BigDecimal>get("amount"))));
        if (isPendingRefunds) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isUseCouponCode != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode));
    }
    if (isExchangePoint != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint));
    }
    if (isAllocatedStock != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock));
    }
    if (hasExpired != null) {
        if (hasExpired) {
            restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(),
                    criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date()));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(),
                    criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())));
        }
    }
    criteriaQuery.where(restrictions);
    return super.count(criteriaQuery, null);
}

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

public List<Order> findList(Order.Type type, Order.Status status, Member member, Goods goods,
        Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint,
        Boolean isAllocatedStock, Boolean hasExpired, Integer count, List<Filter> filters,
        List<net.shopxx.Order> orders) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
    Root<Order> root = criteriaQuery.from(Order.class);
    criteriaQuery.select(root);/* w  w w .  j a va  2  s.  c  om*/
    Predicate restrictions = criteriaBuilder.conjunction();
    if (type != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type));
    }
    if (status != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status));
    }
    if (member != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
    }
    if (goods != null) {
        Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class);
        Root<Product> productSubqueryRoot = productSubquery.from(Product.class);
        productSubquery.select(productSubqueryRoot);
        productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods));

        Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class);
        Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class);
        orderItemSubquery.select(orderItemSubqueryRoot);
        orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root),
                criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery));
    }
    if (isPendingReceive != null) {
        Predicate predicate = criteriaBuilder.and(
                criteriaBuilder.or(root.get("expire").isNull(),
                        criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())),
                criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.completed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.failed),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled),
                criteriaBuilder.notEqual(root.get("status"), Order.Status.denied),
                criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")));
        if (isPendingReceive) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isPendingRefunds != null) {
        Predicate predicate = criteriaBuilder.or(
                criteriaBuilder.and(
                        criteriaBuilder.or(
                                criteriaBuilder.and(root.get("expire").isNotNull(),
                                        criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"),
                                                new Date())),
                                criteriaBuilder.equal(root.get("status"), Order.Status.failed),
                                criteriaBuilder.equal(root.get("status"), Order.Status.canceled),
                                criteriaBuilder.equal(root.get("status"), Order.Status.denied)),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)),
                criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed),
                        criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"),
                                root.<BigDecimal>get("amount"))));
        if (isPendingRefunds) {
            restrictions = criteriaBuilder.and(restrictions, predicate);
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate));
        }
    }
    if (isUseCouponCode != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode));
    }
    if (isExchangePoint != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint));
    }
    if (isAllocatedStock != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock));
    }
    if (hasExpired != null) {
        if (hasExpired) {
            restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(),
                    criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date()));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(),
                    criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())));
        }
    }
    criteriaQuery.where(restrictions);
    return super.findList(criteriaQuery, null, count, filters, orders);
}