Java Code Examples for javax.persistence.criteria.CriteriaQuery#distinct()

public static Specification<Board> findByFilter(final Pagination pagination) {
    return (Root<Board> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        String keyword = pagination.getKeyword().toLowerCase();
        query.distinct(true);

        if (pagination.filterMatcher(Pagination.FilterType.USER)) {
            return cb.like(cb.lower(root.get(pagination.getFilter()).get("name")), "%" + keyword + "%");

        } else if (pagination.filterMatcher(Pagination.FilterType.TITLE)) {
            return cb.like(cb.lower(root.get(pagination.getFilter())), "%" + keyword + "%");

        } else if (pagination.filterMatcher(Pagination.FilterType.CONTENT)) {
            return cb.like(cb.lower(root.get(pagination.getFilter())), "%" + keyword + "%");

        } else if (pagination.filterMatcher(Pagination.FilterType.COMMENTS)) {
            return cb.like(cb.lower(root.join(pagination.getFilter()).get("content")), "%" + keyword + "%");
        }
        return null;
    };
}
 

@Override
public List<VoteDataEmbeddedId> getBallotIdList() {
	final CriteriaQuery<VoteDataEmbeddedId> criteria = getCriteriaBuilder().createQuery(
			VoteDataEmbeddedId.class);
	final Root<VoteData> root = criteria.from(VoteData.class);
	criteria.select(root.get(VoteData_.embeddedId));
	criteria.distinct(true);
	return getEntityManager().createQuery(criteria).getResultList();
}
 

/**
 * @param fetchingVirtualGuests eagerly load virtual guests
 * @param fetchingGroups eagerly load server groups
 * @return a list of all systems
 */
public static List<Server> list(boolean fetchingVirtualGuests, boolean fetchingGroups) {
    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<Server> criteria = builder.createQuery(Server.class);
    Root r = criteria.from(Server.class);
    if (fetchingVirtualGuests) {
        r.fetch("virtualGuests", JoinType.LEFT);
    }
    if (fetchingGroups) {
        r.fetch("groups", JoinType.LEFT);
    }
    criteria.distinct(true);
    return getSession().createQuery(criteria).getResultList().stream()
            .collect(Collectors.toList());

}
 

/**
 * Return the data count base on the criteria query
 *
 * @param session : hibernate session
 * @param root : entity root which is further used for getting sub filed path from it and set in
 *     criteria where clause. Ex: Root<ProjectEntity> projectRoot =
 *     criteriaQuery.from(ProjectEntity.class);
 * @param criteria : Hibernate criteria query reference for further process
 * @param <T> : T = entity name like ProjectEntity, DatasetEntity, ExperimentEntity etc.
 * @return {@link Long} : total records count
 */
public static <T> long count(Session session, Root<T> root, CriteriaQuery<T> criteria) {
  final CriteriaBuilder builder = session.getCriteriaBuilder();
  final CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);

  countCriteria.select(builder.count(root));
  countCriteria.getRoots().addAll(criteria.getRoots());

  final Predicate whereRestriction = criteria.getRestriction();
  if (whereRestriction != null) {
    countCriteria.where(whereRestriction);
  }

  final Predicate groupRestriction = criteria.getGroupRestriction();
  if (groupRestriction != null) {
    countCriteria.having(groupRestriction);
  }

  countCriteria.groupBy(criteria.getGroupList());
  countCriteria.distinct(criteria.isDistinct());
  return session.createQuery(countCriteria).getSingleResult();
}
 

public Region getRegionByCodeOrName(final String code) {
    final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();

    final CriteriaQuery<JpaRegionImpl> c = criteriaBuilder.createQuery(JpaRegionImpl.class);
    c.distinct(true);
    final Root<JpaRegionImpl> region = c.from(JpaRegionImpl.class);
    c.where(criteriaBuilder.or(criteriaBuilder.equal(region.get(JpaRegionImpl_.code), code), criteriaBuilder.like(region.get(JpaRegionImpl_.name), code)));

    try {
        return this.entityManager.createQuery(c).getSingleResult();
    }
    catch (final Exception e) {
        log.debug(e.getMessage(), e);
        return null;
    }
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    query.distinct(true);
    if (!Number.class.isAssignableFrom(query.getResultType())) { // do not join in count queries
        for (String path : pathsToFetch){
            root.fetch(path, joinType);
        }
    }
    return null;
}
 

/**
 * This overridden method ...
 *
 * @see org.kuali.rice.kew.rule.dao.RuleDelegationDAO#search(String, Long, String, java.util.Collection, String, String, Boolean, java.util.Map, java.util.Collection)
 */
public List<RuleDelegationBo> search(String parentRuleBaseVaueId, String parentResponsibilityId, String docTypeName, String ruleTemplateId,
        String ruleDescription, Collection<String> workgroupIds,
        String principalId, String delegationType, Boolean activeInd,
        Map extensionValues, Collection actionRequestCodes) {
    // TODO jjhanso - THIS METHOD NEEDS JAVADOCS
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<RuleDelegationBo> criteriaQuery = criteriaBuilder.createQuery(RuleDelegationBo.class);
    Root<RuleDelegationBo> root = criteriaQuery.from(RuleDelegationBo.class);
    List<javax.persistence.criteria.Predicate> predicates = new ArrayList<javax.persistence.criteria.Predicate>();
    if (StringUtils.isNotBlank(delegationType) && !delegationType.equals(KewApiConstants.DELEGATION_BOTH)) {
        predicates.add(criteriaBuilder.equal(root.get("delegationTypeCode"), delegationType));
    }
    if (StringUtils.isNotBlank(parentResponsibilityId) && StringUtils.isNumeric(parentResponsibilityId)) {
        predicates.add(criteriaBuilder.equal(root.get("responsibilityId"),parentResponsibilityId));
    }

    if (StringUtils.isNotBlank(parentRuleBaseVaueId) && StringUtils.isNumeric(parentRuleBaseVaueId)) {
        predicates.add(criteriaBuilder.in(root.get("responsibilityId")).value(getRuleResponsibilitySubQuery(new Long(parentRuleBaseVaueId),criteriaQuery)));
    }
    Subquery<RuleBaseValues> ruleBaseValuesSubQuery = getRuleBaseValuesSubQuery(docTypeName, ruleTemplateId, ruleDescription, workgroupIds,principalId, activeInd,extensionValues,actionRequestCodes,criteriaQuery);
    if(ruleBaseValuesSubQuery != null){
        predicates.add(criteriaBuilder.in(root.get("delegateRuleId")).value(ruleBaseValuesSubQuery));
    }
    criteriaQuery.distinct(true);
    TypedQuery<RuleDelegationBo> typedQuery = getEntityManager().createQuery(criteriaQuery);
    typedQuery.setMaxResults(KewApiConstants.DELEGATE_RULE_LOOKUP_MAX_ROWS_RETURNED);

    return typedQuery.getResultList();
}
 

/**
 * This overridden method ...
 *
 * @see org.kuali.rice.kew.rule.dao.RuleDelegationDAO#search(String, Long, Long, String, String, String, String, Boolean, java.util.Map, String)
 */
@Override
public List<RuleDelegationBo> search(String parentRuleBaseVaueId, String parentResponsibilityId, String docTypeName, String ruleId,
        String ruleTemplateId, String ruleDescription, String workgroupId,
        String principalId, String delegationType, Boolean activeInd,
        Map extensionValues, String workflowIdDirective) {
    // TODO jjhanso - THIS METHOD NEEDS JAVADOCS
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<RuleDelegationBo> criteriaQuery = criteriaBuilder.createQuery(RuleDelegationBo.class);
    Root<RuleDelegationBo> root = criteriaQuery.from(RuleDelegationBo.class);
    List<javax.persistence.criteria.Predicate> predicates = new ArrayList<javax.persistence.criteria.Predicate>();
    if (StringUtils.isNotBlank(delegationType) && !delegationType.equals(KewApiConstants.DELEGATION_BOTH)) {
        predicates.add(criteriaBuilder.equal(root.get("delegationTypeCode"), delegationType));
    }
    if (StringUtils.isNotBlank(parentResponsibilityId) && StringUtils.isNumeric(parentResponsibilityId)) {
        predicates.add(criteriaBuilder.equal(root.get("responsibilityId"),parentResponsibilityId));
    }

    if (StringUtils.isNotBlank(parentRuleBaseVaueId) && StringUtils.isNumeric(parentRuleBaseVaueId)) {
        predicates.add(criteriaBuilder.in(root.get("responsibilityId")).value(getRuleResponsibilitySubQuery(new Long(parentRuleBaseVaueId),criteriaQuery)));
    }
    Subquery<RuleBaseValues> ruleBaseValuesSubQuery = getRuleBaseValuesSubQuery(docTypeName, ruleId, ruleTemplateId, ruleDescription, workgroupId,principalId, activeInd,extensionValues, workflowIdDirective,criteriaQuery);
    if(ruleBaseValuesSubQuery != null){
        predicates.add(criteriaBuilder.in(root.get("delegateRuleId")).value(ruleBaseValuesSubQuery));
    }
    criteriaQuery.distinct(true);
    javax.persistence.criteria.Predicate[] preds = predicates.toArray(
            new javax.persistence.criteria.Predicate[predicates.size()]);
    criteriaQuery.where(preds);
    TypedQuery<RuleDelegationBo> typedQuery = getEntityManager().createQuery(criteriaQuery);
    typedQuery.setMaxResults(KewApiConstants.DELEGATE_RULE_LOOKUP_MAX_ROWS_RETURNED);

    return typedQuery.getResultList();
}
 

private List<Integer> getDependentMilestoneIds(CriteriaBuilder cb, String id) {
    CriteriaQuery<Integer> buildQuery = cb.createQuery(Integer.class);

    Root<Artifact> artifact = buildQuery.from(Artifact.class);
    SetJoin<Artifact, BuildRecord> build = artifact.join(Artifact_.dependantBuildRecords);
    buildQuery.where(cb.equal(artifact.get(Artifact_.id), Integer.valueOf(id)));
    buildQuery.select(build.get(BuildRecord_.productMilestone).get(ProductMilestone_.id));
    buildQuery.distinct(true);

    List<Integer> resultList = em.createQuery(buildQuery).getResultList();
    return resultList;
}
 

private Optional<Integer> getBuildInMilestone(CriteriaBuilder cb, String id) {
    CriteriaQuery<Integer> buildQuery = cb.createQuery(Integer.class);

    Root<Artifact> artifact = buildQuery.from(Artifact.class);
    buildQuery.where(cb.equal(artifact.get(Artifact_.id), Integer.valueOf(id)));
    buildQuery.select(
            artifact.get(Artifact_.buildRecord).get(BuildRecord_.productMilestone).get(ProductMilestone_.id));
    buildQuery.distinct(true);

    List<Integer> resultList = em.createQuery(buildQuery).getResultList();

    return resultList.stream().findFirst();
}
 

@Override
public List<String> getIdList() {
	final CriteriaQuery<String> criteria = getCriteriaBuilder().createQuery(String.class);
	final Root<CommitteeDocumentData> root = criteria.from(CommitteeDocumentData.class);
	criteria.select(root.get(CommitteeDocumentData_.id));
	criteria.distinct(true);
	return getEntityManager().createQuery(criteria).getResultList();
}
 

@Override
public Predicate toPredicate(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
    final Node rootNode = parseRsql(rsql);
    query.distinct(true);

    final JpqQueryRSQLVisitor<A, T> jpqQueryRSQLVisitor = new JpqQueryRSQLVisitor<>(root, cb, enumType,
            virtualPropertyReplacer, database, query);
    final List<Predicate> accept = rootNode.<List<Predicate>, String> accept(jpqQueryRSQLVisitor);

    if (!CollectionUtils.isEmpty(accept)) {
        return cb.and(accept.toArray(new Predicate[accept.size()]));
    }
    return cb.conjunction();

}
 

private int preFetch(int amount) {
    EntityManager em = MCREntityManagerProvider.getCurrentEntityManager();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MCRJob> cq = cb.createQuery(MCRJob.class);
    Root<MCRJob> root = cq.from(MCRJob.class);

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(cb.equal(root.get("status"), MCRJobStatus.NEW));
    if (action != null) {
        predicates.add(cb.equal(root.get("action"), action));
    }
    cq.where(cb.and(predicates.toArray(new Predicate[] {})));
    cq.orderBy(cb.asc(root.get("added")));
    cq.distinct(true);

    TypedQuery<MCRJob> query = em.createQuery(cq);
    query.setMaxResults(amount);

    List<MCRJob> jobs = query.getResultList();

    int i = 0;
    for (MCRJob job : jobs) {
        if (job.getParameters().isEmpty()) {
            continue;
        }

        i++;
        preFetch.add(job.clone());
        em.detach(job);
    }
    LOGGER.debug("prefetched {} jobs", i);
    return i;
}
 

/**
 * Builds iterator for jobs with given {@link MCRJobStatus} or <code>null</code> for all jobs.
 */
public Iterator<MCRJob> iterator(MCRJobStatus status) {
    if (!running) {
        List<MCRJob> empty = Collections.emptyList();
        return empty.iterator();
    }
    EntityManager em = MCREntityManagerProvider.getCurrentEntityManager();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MCRJob> cq = cb.createQuery(MCRJob.class);
    Root<MCRJob> root = cq.from(MCRJob.class);

    List<Predicate> predicates = new ArrayList<>();
    if (status != null) {
        predicates.add(cb.equal(root.get("status"), status));
    }
    if (action != null) {
        predicates.add(cb.equal(root.get("action"), action));
    }
    cq.where(cb.and(predicates.toArray(new Predicate[] {})));
    cq.orderBy(cb.asc(root.get("added")));
    cq.distinct(true);

    TypedQuery<MCRJob> query = em.createQuery(cq);

    return query.getResultList().iterator();
}
 

@Decision(EXISTS)
public boolean exists(Parameters params, RestContext context, EntityManager em) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Application> query = cb.createQuery(Application.class);
    Root<Application> applicationRoot = query.from(Application.class);
    query.where(cb.equal(applicationRoot.get("name"), params.get("name")));

    List<ResourceField> embedEntities = some(params.get("embed"), embed -> new ResourceFilter().parse(embed))
            .orElse(Collections.emptyList());

    EntityGraph<Application> applicationGraph = em.createEntityGraph(Application.class);
    applicationGraph.addAttributeNodes("name", "description", "virtualPath", "passTo", "topPage");

    if (embedEntities.stream().anyMatch(r -> r.getName().equalsIgnoreCase("realms"))) {
        applicationRoot.fetch("realms", JoinType.LEFT);
        query.distinct(true);
        applicationGraph.addSubgraph("realms")
                .addAttributeNodes("name", "description", "url");

    }

    Application application = em.createQuery(query)
            .setHint("javax.persistence.cache.storeMode", CacheStoreMode.REFRESH)
            .setHint("javax.persistence.fetchgraph", applicationGraph)
            .getResultStream().findAny().orElse(null);
    if (application != null) {
        context.putValue(application);
    }
    return application != null;
}
 

public static Specification<Board> findByAll(final String keyword) {
    return (Root<Board> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        String keywordLCase = keyword.toLowerCase();
        query.distinct(true);
        return cb.or(
                cb.like(cb.lower(root.get(Pagination.FilterType.USER.toString().toLowerCase()).get("name")),
                        "%" + keywordLCase + "%"),
                cb.like(cb.lower(root.get(Pagination.FilterType.TITLE.toString().toLowerCase())),
                        "%" + keywordLCase + "%"),
                cb.like(cb.lower(root.get(Pagination.FilterType.CONTENT.toString().toLowerCase())),
                        "%" + keywordLCase + "%")
        );
    };
}
 

public static <T> Specification<T> toSpecification(final String rsqlQuery, final boolean distinct, final Map<String, String> propertyPathMapper) {
	log.debug("toSpecification({},distinct:{},propertyPathMapper:{})", rsqlQuery, distinct, propertyPathMapper);
	return new Specification<T>() {
		public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
			query.distinct(distinct);
			if (StringUtils.hasText(rsqlQuery)) {
				Node rsql = new RSQLParser(RSQLOperators.supportedOperators()).parse(rsqlQuery);
				return rsql.accept(new RSQLJPAPredicateConverter(cb, propertyPathMapper), root);
			} else
				return null;
		}
	};
}
 

@Override
public Slice<AssignedSoftwareModule> findAllOrderBySetAssignmentAndModuleNameAscModuleVersionAsc(
        final Pageable pageable, final long orderByDistributionId, final String searchText, final Long typeId) {

    final List<AssignedSoftwareModule> resultList = new ArrayList<>();
    final int pageSize = pageable.getPageSize();
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    // get the assigned software modules
    final CriteriaQuery<JpaSoftwareModule> assignedQuery = cb.createQuery(JpaSoftwareModule.class);
    final Root<JpaSoftwareModule> assignedRoot = assignedQuery.from(JpaSoftwareModule.class);
    assignedQuery.distinct(true);
    final ListJoin<JpaSoftwareModule, JpaDistributionSet> assignedDsJoin = assignedRoot
            .join(JpaSoftwareModule_.assignedTo);
    // build the specifications and then to predicates necessary by the
    // given filters
    final Predicate[] specPredicate = specificationsToPredicate(buildSpecificationList(searchText, typeId),
            assignedRoot, assignedQuery, cb,
            cb.equal(assignedDsJoin.get(JpaDistributionSet_.id), orderByDistributionId));
    // if we have some predicates then add it to the where clause of the
    // multi select
    assignedQuery.where(specPredicate);
    assignedQuery.orderBy(cb.asc(assignedRoot.get(JpaSoftwareModule_.name)),
            cb.asc(assignedRoot.get(JpaSoftwareModule_.version)));
    // don't page the assigned query on database, we need all assigned
    // software modules to filter
    // them out in the unassigned query
    final List<JpaSoftwareModule> assignedSoftwareModules = entityManager.createQuery(assignedQuery)
            .getResultList();
    // map result
    if (pageable.getOffset() < assignedSoftwareModules.size()) {
        assignedSoftwareModules
                .subList((int) pageable.getOffset(),
                        Math.min(assignedSoftwareModules.size(), pageable.getPageSize()))
                .forEach(sw -> resultList.add(new AssignedSoftwareModule(sw, true)));
    }

    if (assignedSoftwareModules.size() >= pageSize) {
        return new SliceImpl<>(resultList);
    }

    // get the unassigned software modules
    final CriteriaQuery<JpaSoftwareModule> unassignedQuery = cb.createQuery(JpaSoftwareModule.class);
    unassignedQuery.distinct(true);
    final Root<JpaSoftwareModule> unassignedRoot = unassignedQuery.from(JpaSoftwareModule.class);

    Predicate[] unassignedSpec;
    if (!assignedSoftwareModules.isEmpty()) {
        unassignedSpec = specificationsToPredicate(buildSpecificationList(searchText, typeId), unassignedRoot,
                unassignedQuery, cb, cb.not(unassignedRoot.get(JpaSoftwareModule_.id).in(
                        assignedSoftwareModules.stream().map(SoftwareModule::getId).collect(Collectors.toList()))));
    } else {
        unassignedSpec = specificationsToPredicate(buildSpecificationList(searchText, typeId), unassignedRoot,
                unassignedQuery, cb);
    }

    unassignedQuery.where(unassignedSpec);
    unassignedQuery.orderBy(cb.asc(unassignedRoot.get(JpaSoftwareModule_.name)),
            cb.asc(unassignedRoot.get(JpaSoftwareModule_.version)));
    final List<JpaSoftwareModule> unassignedSoftwareModules = entityManager.createQuery(unassignedQuery)
            .setFirstResult((int) Math.max(0, pageable.getOffset() - assignedSoftwareModules.size()))
            .setMaxResults(pageSize).getResultList();
    // map result
    unassignedSoftwareModules.forEach(sw -> resultList.add(new AssignedSoftwareModule(sw, false)));

    return new SliceImpl<>(resultList);
}
 

@Override
   public List<RuleBaseValues> search(String docTypeName, String ruleId, String ruleTemplateId, String ruleDescription, String groupId, String principalId, Boolean delegateRule, Boolean activeInd, Map extensionValues, String workflowIdDirective) {
       CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
       CriteriaQuery<RuleBaseValues> cq = cb.createQuery(RuleBaseValues.class);
       Root<RuleBaseValues> root = cq.from(RuleBaseValues.class);
       List<javax.persistence.criteria.Predicate> predicates = getSearchCriteria(root,cq,docTypeName, ruleTemplateId, ruleDescription, delegateRule, activeInd, extensionValues);

       if (ruleId != null) {
           predicates.add(cb.equal(root.get("id"),ruleId));
       }
       if (groupId != null) {
           predicates.add(cb.in(root.get("id")).value(getRuleResponsibilitySubQuery(
                   groupId, cq)));
       }
       Collection<String> kimGroupIds = new HashSet<String>();
       Boolean searchUser = Boolean.FALSE;
       Boolean searchUserInWorkgroups = Boolean.FALSE;

       if ("group".equals(workflowIdDirective)) {
           searchUserInWorkgroups = Boolean.TRUE;
       } else if (StringUtils.isBlank(workflowIdDirective)) {
           searchUser = Boolean.TRUE;
           searchUserInWorkgroups = Boolean.TRUE;
       } else {
           searchUser = Boolean.TRUE;
       }

       if (!org.apache.commons.lang.StringUtils.isEmpty(principalId) && searchUserInWorkgroups) {
           Principal principal = null;

           principal = KimApiServiceLocator.getIdentityService().getPrincipal(principalId);

           if (principal == null)
           {
           	throw new RiceRuntimeException("Failed to locate user for the given principal id: " + principalId);
           }
           kimGroupIds = KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(principalId);
       }
       Subquery<RuleResponsibilityBo> subquery = addResponsibilityCriteria(cq,kimGroupIds, principalId, searchUser, searchUserInWorkgroups);

       if(subquery != null){
           predicates.add(cb.in(root.get("id")).value(subquery));
       }
       cq.distinct(true);
       javax.persistence.criteria.Predicate[] preds = predicates.toArray(
               new javax.persistence.criteria.Predicate[predicates.size()]);
       cq.where(preds);
       TypedQuery<RuleBaseValues> q = getEntityManager().createQuery(cq);

       return q.getResultList();
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
	query.distinct(distinctQuery);
	queryContext.putLazyVal(alias, (r) -> r.join(pathToJoinOn, joinType));
	return null;
}