java类javax.persistence.criteria.CriteriaQuery的实例源码

public static DeploymentSpec findDeploymentSpecByVirtualSystemProjectAndRegion(EntityManager em, VirtualSystem vs,
        String projectId, String region) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<DeploymentSpec> query = cb.createQuery(DeploymentSpec.class);

    Root<DeploymentSpec> root = query.from(DeploymentSpec.class);

    query = query.select(root)
            .where(cb.equal(root.get("projectId"), projectId),
                    cb.equal(root.get("region"), region),
                    cb.equal(root.get("virtualSystem"), vs));

    try {
        return em.createQuery(query).getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}

public static VM findByOpenstackId(EntityManager em, String id) {

        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<VM> query = cb.createQuery(VM.class);

        Root<VM> root = query.from(VM.class);

        query = query.select(root)
            .where(cb.equal(root.get("openstackId"), id));

        try {
            return em.createQuery(query).getSingleResult();
        } catch (NoResultException nre) {
            return null;
        }
    }

@Override
public Predicate toPredicate(final Root<?> leveringsautorisatieRoot, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
    final Root<Dienstbundel> dienstbundelRoot = query.from(Dienstbundel.class);
    final Root<Dienst> dienstRoot = query.from(Dienst.class);

    // Join dienst op dienstbundel
    final Predicate joinPredicateDienstbundel = dienstbundelRoot.get(ID).in(dienstRoot.get("dienstbundel").get(ID));

    // Join dienst op leveringsautorisatie
    final Predicate joinPredicateLeveringsautorisatie = leveringsautorisatieRoot.get(ID).in(dienstbundelRoot.get(LEVERINGSAUTORISATIE).get(ID));

    // OntvangendePartij
    final Predicate soortDienstPredicate = cb.equal(dienstRoot.get("soortDienstId"), value);

    return cb.and(joinPredicateDienstbundel, joinPredicateLeveringsautorisatie, soortDienstPredicate);
}

public static void main(String[] args) {
    EntityManagerFactory entityManagerFactory = Persistence
            .createEntityManagerFactory("pl.edu.bogdan.training.db.entity");
    EntityManager em = entityManagerFactory.createEntityManager();

    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> from = cq.from(User.class);
    Join<User, Role> join = from.join("role", JoinType.LEFT);
    cq.where(cb.equal(join.get("name"), "ordinary"));
    TypedQuery<User> tq = em.createQuery(cq);
    List<User> users = tq.getResultList();
    for (User u : users) {
        System.out.println(u.getLastName());
    }

    em.getTransaction().commit();
    em.close();
    entityManagerFactory.close();
}

private <S> S aggregate(CriteriaBuilder builder, CriteriaQuery<S> query, Root<E> root, Specification<E> spec, List<Selection<?>> selectionList, LockModeType lockMode) {
    if (selectionList != null) {
        Predicate predicate = spec.toPredicate(root, query, builder);
        if (predicate != null) {
            query.where(predicate);
        }
        query.multiselect(selectionList);
        return (S) em.createQuery(query).setLockMode(lockMode).getSingleResult();
    }
    return null;
}

/**
 * 根据某些属性获取对象L
 * @param name 属性名称
 * @param value 属性值
 * @param lockMode 对象锁类型
 * @return
 */
public T findOneByProperty(String name, Object value, LockModeType lockMode) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<T> query = cb.createQuery(entityClass);
    Root<T> root = query.from(entityClass);
    query.where(cb.equal(QueryFormHelper.getPath(root, name), value));
    TypedQuery<T> typedQuery = em.createQuery(query);
    typedQuery.setLockMode(lockMode);
    try {
        List<T> list = typedQuery.getResultList();
        if (list.isEmpty()) {
            return null;
        } else {
            return list.get(0);
        }
    } catch (NoResultException e) {
        return null;
    }
}

@Override
public List<Community> findApprovedCommunities() {
    LOG.info("findApprovedCommunites()");
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Community> criteria = builder.createQuery(Community.class);
    Root<Community> community = criteria.from(Community.class);
    try {
        criteria.where(builder.equal(community.get("state"), new Enumeration(2)));
    } catch (DatabaseException e1) {
        LOG.error("Enumeration(2).APPROVED konnte nicht erstellt werden!");
    }
    TypedQuery<Community> query = em.createQuery(criteria);
    try {
        List <Community> coms = query.getResultList();
        for(Community c : coms) {
            initializeCom(c);
        }
        return coms;
    } catch (NoResultException e) {
        LOG.error(e.toString());
        return null;
    }
}

public List<PlayerDetails> getPlayersByCity(String city) {
    logger.info("getPlayersByCity");
    List<Player> players = null;

    try {
        CriteriaQuery<Player> cq = cb.createQuery(Player.class);
        if (cq != null) {
            Root<Player> player = cq.from(Player.class);
            Join<Player, Team> team = player.join(Player_.team);

            // Get MetaModel from Root
            //EntityType<Player> Player_ = player.getModel();

            // set the where clause
            cq.where(cb.equal(team.get(Team_.city), city));
            cq.select(player).distinct(true);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

/**
 * Gets the Policy for a given domain and the policy
 *
 * @return - Policy
 * @throws Exception
 */
@Path("/{domainId}/policies/{policyId}")
@GET
public PolicyEntity getPolicy(@PathParam("domainId") Long domainId, @PathParam("policyId") Long policyId)
        throws Exception {

    LOG.info("getting Policy for Policy ID..:" + policyId);

    return this.txControl.supports(new Callable<PolicyEntity>() {

        @Override
        public PolicyEntity call() throws Exception {
            CriteriaBuilder criteriaBuilder = DomainApis.this.em.getCriteriaBuilder();
            CriteriaQuery<PolicyEntity> query = criteriaBuilder.createQuery(PolicyEntity.class);
            Root<PolicyEntity> r = query.from(PolicyEntity.class);
            query.select(r).where(criteriaBuilder.and(criteriaBuilder.equal(r.get("domain").get("id"), domainId),
                    criteriaBuilder.equal(r.get("id"), policyId)));
            List<PolicyEntity> result = DomainApis.this.em.createQuery(query).getResultList();
            if (result.isEmpty()) {
                throw new Exception("Policy or Domain Entity does not exists...");
                //TODO - Add 404 error response - Sudhir
            }
            return result.get(0);
        }
    });
}

private TypedQuery<ConfigurationEntryDto> createQuery(String providerNid, String providerId, CloudTarget targetSpace, String mtaId,
    EntityManager manager) {
    CriteriaBuilder builder = manager.getCriteriaBuilder();
    CriteriaQuery<ConfigurationEntryDto> query = builder.createQuery(ConfigurationEntryDto.class);
    Root<ConfigurationEntryDto> root = query.from(ConfigurationEntryDto.class);

    List<Predicate> predicates = new ArrayList<>();
    if (providerNid != null) {
        predicates.add(builder.equal(root.get(FieldNames.PROVIDER_NID), providerNid));
    }
    if (targetSpace != null && !StringUtils.isEmpty(targetSpace.getSpace())) {
        predicates.add(builder.equal(root.get(FieldNames.TARGET_SPACE), targetSpace.getSpace()));
        if (!StringUtils.isEmpty(targetSpace.getOrg())) {
            predicates.add(builder.equal(root.get(FieldNames.TARGET_ORG), targetSpace.getOrg()));
        }
    }

    if (providerId != null) {
        predicates.add(builder.equal(root.get(FieldNames.PROVIDER_ID), providerId));
    } else if (mtaId != null) {
        predicates.add(builder.like(root.get(FieldNames.PROVIDER_ID), mtaId + ":%"));
    }

    return manager.createQuery(query.select(root).where(predicates.toArray(new Predicate[0])));
}

@Override
public List<PolicyEntity> getPolicyList(String domainId) throws Exception {

    return this.txControl.supports(new Callable<List<PolicyEntity>>() {

        @Override
        public List<PolicyEntity> call() throws Exception {

            DomainEntity result = IsmPolicyApi.this.em.find(DomainEntity.class, Long.parseLong(domainId));
            if (result == null) {
                throw new Exception("Domain Entity does not exists...");
                //TODO - to add RETURN 404 error:Sudhir
            }
            CriteriaBuilder criteriaBuilder = IsmPolicyApi.this.em.getCriteriaBuilder();
            CriteriaQuery<PolicyEntity> query = criteriaBuilder.createQuery(PolicyEntity.class);
            Root<PolicyEntity> r = query.from(PolicyEntity.class);
            query.select(r).where(criteriaBuilder
                    .and(criteriaBuilder.equal(r.get("domain").get("id"), Long.parseLong(domainId))));
            return IsmPolicyApi.this.em.createQuery(query).getResultList();
        }
    });
}

/**
 * Returns a page of objects.
 *
 * @param entityClass The entity class.
 * @param page The page index (zero indexed).
 * @param pageSize The page size.
 * @return A page of objects.
 */
@Override
public <T extends BaseEntity> List<T> readPage(
        final Class<T> entityClass,
        final int page,
        final int pageSize) {

    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<T> cq = cb.createQuery(entityClass);
    final Root<T> root = cq.from(entityClass);
    cq.select(root);
    cq.orderBy(cb.desc(root.get("id")));
    return em.createQuery(cq)
            .setFirstResult(page * pageSize)
            .setMaxResults(pageSize)
            .getResultList();
}

public static ApplianceSoftwareVersion findByApplianceVersionVirtTypeAndVersion(EntityManager em, Long applianceId, String av,
        VirtualizationType vt, String vv) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<ApplianceSoftwareVersion> query = cb.createQuery(ApplianceSoftwareVersion.class);

    Root<ApplianceSoftwareVersion> root = query.from(ApplianceSoftwareVersion.class);

    query = query.select(root)
            .where(cb.equal(root.join("appliance").get("id"), applianceId),
                    cb.equal(cb.upper(root.get("applianceSoftwareVersion")), av.toUpperCase()),
                    cb.equal(root.get("virtualizationType"), vt),
                    cb.equal(cb.upper(root.get("virtualizationSoftwareVersion")), vv.toUpperCase())
                    );

    try {
        return em.createQuery(query).getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}

@Override
public Predicate toPredicate(final Root<?> persoonRoot, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
    final Root<PersoonAdres> persoonAdressenRoot = query.from(PersoonAdres.class);
    final Predicate joinPredicateAdressen = persoonRoot.get(ID).in(persoonAdressenRoot.get(PERSOON).get(ID));
    final Predicate adresPredicate;
    if (likePredicate) {
        final Expression<String> attributeExpression = cb.lower(cb.function(UNACCENT, String.class, persoonAdressenRoot.get(field)));
        final Expression<String> valueExpression =
                cb.lower(
                        cb.function(
                                UNACCENT,
                                String.class,
                                cb.concat(cb.concat(cb.literal(WILDCARD), cb.literal(value.toString())), cb.literal(WILDCARD))));
        adresPredicate = cb.like(attributeExpression, valueExpression);
    } else {
        adresPredicate = cb.equal(persoonAdressenRoot.get(field), value);
    }
    return cb.and(joinPredicateAdressen, adresPredicate);
}

public static Appliance findByModel(EntityManager em, String model) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Appliance> query = cb.createQuery(Appliance.class);

    Root<Appliance> root = query.from(Appliance.class);

    query = query.select(root)
        .where(cb.equal(root.get("model"), model));

    try {
        return em.createQuery(query).getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}

/**
 * Find list of children entities by their parent Id
 *
 * @param parentEntityName
 *            The parent entity name by which id will be queried.
 * @param parentId
 *            Parent entity identifier for which children are queried.
 * @return List of children owned by parent entity
 */
public List<T> findByParentId(String parentEntityName, Long parentId, String... orderby) {

    CriteriaBuilder cb = this.em.getCriteriaBuilder();

    CriteriaQuery<T> query = cb.createQuery(this.clazz);

    Root<T> root = query.from(this.clazz);
    query = query.select(root)
            .distinct(true)
            .where(
            cb.equal(root.join(parentEntityName).get("id"), parentId));

    if (orderby != null) {
        query = query.orderBy(Arrays.stream(orderby)
                .map(f -> cb.asc(root.get(f)))
                .toArray(i -> new Order[i]));
    }

    List<T> list = this.em.createQuery(query).getResultList();

    return list;
}

public static Label findByValue(EntityManager em, String labelValue, Long vcId) {

        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<Label> query = cb.createQuery(Label.class);

        Root<Label> root = query.from(Label.class);

        query = query.select(root)
                .where(cb.equal(root.get("value"), labelValue),
                        cb.equal(root.join("securityGroupMembers").join("securityGroup").join("virtualizationConnector").get("id"), vcId));

        try {
            return em.createQuery(query).getSingleResult();
        } catch (NoResultException nre) {
            return null;
        }
    }

@GET
@Path("/guardian/{guardianEmail}")
@Produces({"application/xml", "application/json"})
@RolesAllowed({"Guardian", "Administrator"})
public Guardian getGuardianByEmail(@PathParam("guardianEmail") String email) {
    logger.log(Level.INFO, "Principal is: {0}", ctx.getCallerPrincipal().getName());

    CriteriaQuery<Guardian> cq = cb.createQuery(Guardian.class);
    Root<Guardian> guardian = cq.from(Guardian.class);

    cq.select(guardian);
    cq.where(cb.equal(guardian.get(Guardian_.email), email));
    cq.distinct(true);

    TypedQuery<Guardian> q = em.createQuery(cq);
    return q.getSingleResult();
}

public static Subnet findByOpenstackId(EntityManager em, String id) {

        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<Subnet> query = cb.createQuery(Subnet.class);

        Root<Subnet> root = query.from(Subnet.class);

        query = query.select(root)
            .where(cb.equal(root.get("openstackId"), id));

        try {
            return em.createQuery(query).getSingleResult();
        } catch (NoResultException nre) {
            return null;
        }
    }

@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
    Predicate resultPredicate = null;
    List<Predicate> predicates = buildPredicates(root, cb);
    if (!predicates.isEmpty()) {
        resultPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

    return resultPredicate;
}

public default List<E> findAll() {
    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
    criteriaQuery.select(criteriaQuery.from(getEntityClass()));
    TypedQuery<E> query = entityManager.createQuery(criteriaQuery);

    return query.getResultList();
}

public default List<E> findRange(Long from, Long to) {
    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
    criteriaQuery.select(criteriaQuery.from(getEntityClass()));
    TypedQuery<E> query = entityManager.createQuery(criteriaQuery);
    query.setMaxResults(Math.toIntExact(to - from + 1));
    query.setFirstResult(Math.toIntExact(from));

    return query.getResultList();
}

@Override
public Predicate toPredicate(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
    final List<Predicate> predicates = new ArrayList<>();

    for (final PredicateBuilder predicateBuilder : predicateBuilders) {
        final Predicate predicate = predicateBuilder.toPredicate(root, query, cb);
        if (predicate != null) {
            predicates.add(predicate);
        }
    }

    return cb.and(predicates.toArray(new Predicate[] {}));
}

private Specification<RelationEntity> getEntityFieldsSpec(EntityId from, String relationType,
    RelationTypeGroup typeGroup, ThingType childType) {
  return new Specification<RelationEntity>() {
    @Override
    public Predicate toPredicate(Root<RelationEntity> root, CriteriaQuery<?> criteriaQuery,
        CriteriaBuilder criteriaBuilder) {
      List<Predicate> predicates = new ArrayList<>();
      if (from != null) {
        Predicate fromIdPredicate = criteriaBuilder.equal(root.get("fromId"),
            UUIDConverter.fromTimeUUID(from.getId()));
        predicates.add(fromIdPredicate);
        Predicate fromEntityTypePredicate = criteriaBuilder.equal(root.get("fromType"), from.getEntityType().name());
        predicates.add(fromEntityTypePredicate);
      }
      if (relationType != null) {
        Predicate relationTypePredicate = criteriaBuilder.equal(root.get("relationType"), relationType);
        predicates.add(relationTypePredicate);
      }
      if (typeGroup != null) {
        Predicate typeGroupPredicate = criteriaBuilder.equal(root.get("relationTypeGroup"), typeGroup.name());
        predicates.add(typeGroupPredicate);
      }
      if (childType != null) {
        Predicate childTypePredicate = criteriaBuilder.equal(root.get("toType"), childType.name());
        predicates.add(childTypePredicate);
      }
      return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    }
  };
}

public static DistributedApplianceInstance findByIpAddress(EntityManager em, String ipAddress) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<DistributedApplianceInstance> query = cb.createQuery(DistributedApplianceInstance.class);

    Root<DistributedApplianceInstance> root = query.from(DistributedApplianceInstance.class);
    query = query.select(root).where(
            cb.equal(root.get("ipAddress"), ipAddress));

    try {
        return em.createQuery(query).getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}

@Override
@Transactional
public CodeSystemEntity findBySystem(String system) {

    CriteriaBuilder builder = em.getCriteriaBuilder();

    CodeSystemEntity codeSystemEntity = null;
    CriteriaQuery<CodeSystemEntity> criteria = builder.createQuery(CodeSystemEntity.class);

    Root<CodeSystemEntity> root = criteria.from(CodeSystemEntity.class);
    List<Predicate> predList = new LinkedList<Predicate>();
    log.trace("FlushMode = "+em.getFlushMode());
    log.trace("Entity Manager Properties = "+ em.getProperties().toString());
    Predicate p = builder.equal(root.<String>get("codeSystemUri"),system);
    predList.add(p);
    Predicate[] predArray = new Predicate[predList.size()];
    predList.toArray(predArray);
    if (predList.size()>0)
    {
        log.trace("Found CodeSystem "+system);
        criteria.select(root).where(predArray);

        List<CodeSystemEntity> qryResults = em.createQuery(criteria).getResultList();

        for (CodeSystemEntity cme : qryResults) {
            codeSystemEntity = cme;
            break;
        }
    }
    if (codeSystemEntity == null) {
        log.info("Not found adding CodeSystem = "+system);
        codeSystemEntity = new CodeSystemEntity();
        codeSystemEntity.setCodeSystemUri(system);

        save(codeSystemEntity);

    }
    return codeSystemEntity;
}

@Override
public <S> S sum(Class<S> resultClass, Specification<E> spec, LockModeType lockMode, List<SingularAttribute<E, ? extends Number>> properties) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<S> query = builder.createQuery(resultClass);
    Root<E> root = query.from(getDomainClass());
    List<Selection<?>> selectionList = Lists.newArrayList();
    for (SingularAttribute<E, ? extends Number> property : properties) {
        selectionList.add(builder.sum(root.get(property)));
    }
    return aggregate(builder, query, root, spec, selectionList, lockMode);
}

/**
 * 多条件查询拍卖数据
 * @param auction
 * @return
 */
public List<Auction> findAll(final Auction auction) {
    // 参数验证
    // 由于通过realmId分区表，为了避免所有分区搜索，参数必须有realmId
    if (null == auction.getRealmId()) {
        throw new IllegalArgumentException("服务器id不能为空");
    }
    // 由于还未对结果分页，不指定条件将有太多数据返回，暂时做限制处理，以后考虑分页
    if (null == auction.getItemId() && null == auction.getOwner()) {
        throw new IllegalArgumentException("物品id或卖家不能同时为空");
    }
    return auctionRepository.findAll((Root<Auction> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {
        List<Predicate> list = new ArrayList<Predicate>();
        if (null != auction.getRealmId()) {
            list.add(criteriaBuilder.equal(root.get("realmId"), auction.getRealmId()));
        }
        if (null != auction.getItemId()) {
            list.add(criteriaBuilder.equal(root.get("itemId"), auction.getItemId()));
        }
        if (null != auction.getBonusList()) {
            list.add(criteriaBuilder.equal(root.get("bonusList"), auction.getBonusList()));
        }
        if (null != auction.getPetSpeciesId()) {
            list.add(criteriaBuilder.equal(root.get("petSpeciesId"), auction.getPetSpeciesId()));
        }
        if (null != auction.getPetBreedId()) {
            list.add(criteriaBuilder.equal(root.get("petBreedId"), auction.getPetBreedId()));
        }
        if (null != auction.getOwner()) {
            list.add(criteriaBuilder.equal(root.get("owner"), auction.getOwner()));
        }
        criteriaQuery.where(list.toArray(new Predicate[list.size()]));
        return null;
    });
}

/**
 * 分页查询
 * @param domainClass 领域类（实体类）
 * @param pageable 分页信息
 * @param <T> 领域类（实体类）范型
 * @return 分页结果信息
 */
public static <T> Page<T> findAll(Class<T> domainClass, Pageable pageable) {
    EntityManager em = getEntityManager(domainClass);
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<T> cq = cb.createQuery(domainClass);
    cq.from(domainClass);
    return findAll(cq, pageable);
}

public List<PlayerDetails> getPlayersByPosition(String position) {
    logger.info("getPlayersByPosition");
    List<Player> players = null;

    try {
        CriteriaQuery<Player> cq = cb.createQuery(Player.class);
        if (cq != null) {
            Root<Player> player = cq.from(Player.class);

            // Get MetaModel from Root
            //EntityType<Player> Player_ = player.getModel();

            // set the where clause
            cq.where(cb.equal(player.get(Player_.position), position));
            cq.select(player);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}