Example usage for javax.persistence.criteria CriteriaQuery distinct

Introduction

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

Prototype

CriteriaQuery<T> distinct(boolean distinct);

Source Link

Document

Specify whether duplicate query results will be eliminated.

Usage

From source file:org.openmeetings.app.data.basic.dao.LdapConfigDaoImpl.java

public List<LdapConfig> getLdapConfigs(int start, int max, String orderby, boolean asc) {
    try {//  w  ww  . j  a  va  2 s  .  c  o  m
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<LdapConfig> cq = cb.createQuery(LdapConfig.class);
        Root<LdapConfig> c = cq.from(LdapConfig.class);
        Predicate condition = cb.equal(c.get("deleted"), "false");
        cq.where(condition);
        cq.distinct(asc);
        if (asc) {
            cq.orderBy(cb.asc(c.get(orderby)));
        } else {
            cq.orderBy(cb.desc(c.get(orderby)));
        }
        TypedQuery<LdapConfig> q = em.createQuery(cq);
        q.setFirstResult(start);
        q.setMaxResults(max);
        List<LdapConfig> ll = q.getResultList();
        return ll;
    } catch (Exception ex2) {
        log.error("[getLdapConfigs]", ex2);
    }
    return null;
}

---

From source file:com.vladmihalcea.HibernateCriteriaTest.java

private List<ImageProductDTO> getImageProductDTOs() {
    return transactionTemplate.execute(new TransactionCallback<List<ImageProductDTO>>() {
        @Override//  w  w  w .j a va  2s.co  m
        public List<ImageProductDTO> doInTransaction(TransactionStatus transactionStatus) {
            CriteriaBuilder cb = entityManager.getCriteriaBuilder();
            CriteriaQuery<ImageProductDTO> query = cb.createQuery(ImageProductDTO.class);
            Root<Image> imageRoot = query.from(Image.class);
            Join<Image, Product> productJoin = imageRoot.join(Image_.product);
            query.distinct(true);
            List<Predicate> criteria = new ArrayList<Predicate>();
            criteria.add(cb.like(cb.lower(productJoin.get(Product_.name)), "%tv%"));
            criteria.add(cb.gt(imageRoot.get(Image_.index), 0));
            query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
            query.select(cb.construct(ImageProductDTO.class, imageRoot.get(Image_.name),
                    productJoin.get(Product_.name))).orderBy(cb.asc(imageRoot.get(Image_.name)));
            return entityManager.createQuery(query).getResultList();
        }
    });
}

---

From source file:org.openregistry.core.repository.jpa.JpaPersonRepository.java

public List<Person> searchByCriteria(final SearchCriteria searchCriteria) throws RepositoryAccessException {
    final String givenName = searchCriteria.getGivenName();
    final String familyName = searchCriteria.getFamilyName();
    final Date birthDate = searchCriteria.getDateOfBirth();
    final String searchCriteriaName = searchCriteria.getName();

    // search by role criteria
    final String sponsorNetID = searchCriteria.getSponsorNetID();
    final OrganizationalUnit organizationalUnit = searchCriteria.getOrganizationalUnit();
    final Date roleExpDate = searchCriteria.getRoleExpDate();
    final Type roleType = searchCriteria.getAffiliationType();

    final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();

    final CriteriaQuery<JpaPersonImpl> c = criteriaBuilder.createQuery(JpaPersonImpl.class);
    c.distinct(true);
    final Root<JpaPersonImpl> person = c.from(JpaPersonImpl.class);
    final Join<JpaPersonImpl, JpaNameImpl> name = person.join(JpaPersonImpl_.names);
    final Join<JpaPersonImpl, JpaRoleImpl> role = person.join(JpaPersonImpl_.roles);

    //        person.fetch(JpaPersonImpl_.names);
    //        person.fetch(JpaPersonImpl_.roles);
    //        person.fetch(JpaPersonImpl_.identifiers);

    final Predicate pBirthDate;
    if (birthDate != null) {
        pBirthDate = criteriaBuilder.equal(person.get(JpaPersonImpl_.dateOfBirth), birthDate);
    } else {/*from  w  w  w.jav  a 2s  .co m*/
        pBirthDate = null;
    }

    Predicate combined = null;

    if (StringUtils.hasText(givenName) && StringUtils.hasText(familyName)) {
        //          final Predicate pGivenName = criteriaBuilder.equal(name.get(JpaNameImpl_.given), givenName );
        Expression<String> pattern = criteriaBuilder.literal((String) givenName + "%");
        final Predicate pGivenName = criteriaBuilder.like(name.get(JpaNameImpl_.given), pattern);
        final Predicate pFamilyName = criteriaBuilder.equal(name.get(JpaNameImpl_.family), familyName);

        combined = criteriaBuilder.and(pGivenName, pFamilyName);
    } else if (StringUtils.hasText(givenName)) {
        combined = criteriaBuilder.equal(name.get(JpaNameImpl_.given), givenName);
    } else if (StringUtils.hasText(familyName)) {
        combined = criteriaBuilder.equal(name.get(JpaNameImpl_.family), familyName);
    } else if (StringUtils.hasText(searchCriteriaName)) {
        final Predicate pGivenName = criteriaBuilder.equal(name.get(JpaNameImpl_.given), searchCriteriaName);
        final Predicate pFamilyName = criteriaBuilder.equal(name.get(JpaNameImpl_.family), searchCriteriaName);
        combined = criteriaBuilder.or(pGivenName, pFamilyName);
    }

    Predicate pRoleCombined = null;

    if (roleType != null) {
        //final Join<JpaPersonImpl,JpaRoleImpl> role = person.join(JpaPersonImpl_.roles);
        //final Join<JpaPersonImpl,JpaRoleImpl> role = name.join(JpaPersonImpl_.roles);
        pRoleCombined = criteriaBuilder.equal(role.get(JpaRoleImpl_.affiliationType), roleType);
    }

    if (organizationalUnit != null && StringUtils.hasText(organizationalUnit.getName())) {
        Predicate orgUnitP = criteriaBuilder.equal(role.get(JpaRoleImpl_.organizationalUnit),
                organizationalUnit);
        if (pRoleCombined != null)
            pRoleCombined = criteriaBuilder.and(pRoleCombined, orgUnitP);
        else
            pRoleCombined = orgUnitP;
        //pRoleCombined = criteriaBuilder.and(pRoleCombined, orgUnitP);
    }

    if (StringUtils.hasText(sponsorNetID)) {
        Person person1 = findByIdentifier(Type.IdentifierTypes.NETID.name(), sponsorNetID);
        if (person1 != null) {
            Predicate sponsorP = criteriaBuilder.equal(role.get(JpaRoleImpl_.sponsorId), person1.getId());
            if (pRoleCombined != null)
                pRoleCombined = criteriaBuilder.and(pRoleCombined, sponsorP);
            else
                pRoleCombined = sponsorP;
        } else {
            pRoleCombined = criteriaBuilder.or();
        }

    }
    if (roleExpDate != null) {
        Predicate expDateP = criteriaBuilder.between(role.get(JpaRoleImpl_.end), new Date(), roleExpDate);
        if (pRoleCombined != null)
            pRoleCombined = criteriaBuilder.and(pRoleCombined, expDateP);
        else
            pRoleCombined = expDateP;
    }

    Predicate pComplete = criteriaBuilder.and();

    if (pBirthDate != null)
        pComplete = criteriaBuilder.and(pComplete, pBirthDate);
    if (combined != null)
        pComplete = criteriaBuilder.and(pComplete, combined);
    if (pRoleCombined != null)
        pComplete = criteriaBuilder.and(pComplete, pRoleCombined);

    c.select(person).where(pComplete);

    //        if (pBirthDate != null && combined != null) {
    //            c.select(person).where(criteriaBuilder.and(pBirthDate, combined));
    //        } else if (pBirthDate != null) {
    //            c.select(person).where(pBirthDate);
    //        } else {
    //            c.select(person).where(combined);
    //        }

    final List<JpaPersonImpl> persons = this.entityManager.createQuery(c).setMaxResults(MAX_QUERY_LIMIT)
            .getResultList();

    return new ArrayList<Person>(persons);
}

---

From source file:com.vladmihalcea.HibernateCriteriaTest.java

private List<Product> getProducts_Mercifully() {
    return transactionTemplate.execute(new TransactionCallback<List<Product>>() {
        @Override//from   ww w  .ja  v  a 2s  .c  o m
        public List<Product> doInTransaction(TransactionStatus transactionStatus) {
            CriteriaBuilder cb = entityManager.getCriteriaBuilder();
            CriteriaQuery<Product> query = cb.createQuery(Product.class);
            Root<Image> imageRoot = query.from(Image.class);
            Join<Image, Product> productJoin = imageRoot.join(Image_.product);
            query.select(productJoin);
            query.distinct(true);
            List<Predicate> criteria = new ArrayList<Predicate>();
            criteria.add(cb.like(cb.lower(productJoin.get(Product_.name)), "%tv%"));
            criteria.add(cb.gt(imageRoot.get(Image_.index), 0));
            query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
            return entityManager.createQuery(query).getResultList();
        }
    });
}

---

From source file:org.openmeetings.app.data.basic.Configurationmanagement.java

public List<Configuration> getConfigurations(int start, int max, String orderby, boolean asc) {
    try {/* w  w w .j a  va 2  s.c  o m*/
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Configuration> cq = cb.createQuery(Configuration.class);
        Root<Configuration> c = cq.from(Configuration.class);
        Predicate condition = cb.equal(c.get("deleted"), "false");
        cq.where(condition);
        cq.distinct(asc);
        if (asc) {
            cq.orderBy(cb.asc(c.get(orderby)));
        } else {
            cq.orderBy(cb.desc(c.get(orderby)));
        }
        TypedQuery<Configuration> q = em.createQuery(cq);
        q.setFirstResult(start);
        q.setMaxResults(max);
        List<Configuration> ll = q.getResultList();
        return ll;
    } catch (Exception ex2) {
        log.error("[getConfigurations]", ex2);
    }
    return null;
}

---

From source file:org.querybyexample.jpa.GenericRepository.java

/**
 * Find and load a list of E instance.//from   w  w  w .  ja  v a2  s .co m
 *
 * @param entity a sample entity whose non-null properties may be used as
 * search hints
 * @param searchParameters carries additional search information
 * @return the entities matching the search.
 */
@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public List<E> find(E entity, SearchParameters sp) {
    if (sp.hasNamedQuery()) {
        return byNamedQueryUtil.findByNamedQuery(sp);
    }
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<E> criteriaQuery = builder.createQuery(type);
    if (sp.getDistinct()) {
        criteriaQuery.distinct(true);
    }
    Root<E> root = criteriaQuery.from(type);

    // predicate
    Predicate predicate = getPredicate(root, builder, entity, sp);
    if (predicate != null) {
        criteriaQuery = criteriaQuery.where(predicate);
    }

    // left join
    for (SingularAttribute<?, ?> arg : sp.getLeftJoins()) {
        root.fetch((SingularAttribute<E, ?>) arg, JoinType.LEFT);
    }

    // order by
    criteriaQuery.orderBy(orderByUtil.buildJpaOrders(sp.getOrders(), root, builder, sp));

    TypedQuery<E> typedQuery = entityManager.createQuery(criteriaQuery);
    applyCacheHints(typedQuery, sp);
    applyPagination(typedQuery, sp);
    List<E> entities = typedQuery.getResultList();
    log.debug("Returned {} elements", entities.size());

    return entities;
}

---

From source file:ch.puzzle.itc.mobiliar.business.resourcegroup.control.ResourceGroupRepository.java

/**
 *
 * @param name// www.j  a  v a2s  . co m
 * @param resourceTypeId
 * @return
 */
public ResourceGroupEntity loadUniqueGroupByNameAndType(String name, Integer resourceTypeId) {
    ResourceGroupEntity result = null;
    try {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<ResourceGroupEntity> q = cb.createQuery(ResourceGroupEntity.class);
        Root<ResourceGroupEntity> r = q.from(ResourceGroupEntity.class);
        r.fetch("resources");
        Join<ResourceGroupEntity, ResourceEntity> resources = r.join("resources");
        Predicate typePred = cb.equal(resources.get("resourceType").get("id"), resourceTypeId);
        Predicate resNamePred = cb.equal(resources.get("name"), name);

        q.where(cb.and(typePred, resNamePred));

        q.distinct(true);

        result = entityManager.createQuery(q).getSingleResult();
    } catch (NoResultException e) {
        // do nothing
    }
    return result;
}

---

From source file:de.ks.idnadrev.information.view.InformationOverviewDS.java

private List<InformationPreviewItem> getResults(String name, List<String> tagNames, Category category,
        EntityManager em, CriteriaBuilder builder, Class<? extends Information<?>> clazz) {
    CriteriaQuery<InformationPreviewItem> query = builder.createQuery(InformationPreviewItem.class);
    Root<? extends Information<?>> root = query.from(clazz);

    ArrayList<Predicate> filters = new ArrayList<>();
    if (!name.isEmpty()) {
        filters.add(builder.like(builder.lower(root.<String>get(KEY_NAME)), name));
    }//from   w  ww  . j  a  va2 s .  c om
    if (!tagNames.isEmpty()) {
        List<Tag> tags = getTags(tagNames, em);
        SetJoin<TextInfo, Tag> tagJoin = root.joinSet(KEY_TAGS);
        filters.add(tagJoin.in(tags));
    }
    if (category != null) {
        filters.add(builder.equal(root.get(KEY_CATEGORY), category));
    }
    query.distinct(true);

    query.where(filters.toArray(new Predicate[filters.size()]));
    query.select(
            builder.construct(InformationPreviewItem.class, root.get(KEY_NAME), root.get(KEY_CREATIONTIME)));
    List<InformationPreviewItem> resultList = em.createQuery(query).getResultList();
    return resultList;
}

---

From source file:ch.puzzle.itc.mobiliar.business.resourcegroup.control.ResourceTypeDomainService.java

private Query applicationServersForSuggestBox(String input) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<String> q = cb.createQuery(String.class);
    Root<ResourceEntity> r = q.from(ResourceEntity.class);
    Join<ResourceEntity, ResourceTypeEntity> resType = r.join("resourceType");
    Predicate appServerNamePred = cb.like(resType.<String>get("name"),
            DefaultResourceTypeDefinition.APPLICATIONSERVER.name());
    input = input + "%";
    q.where(cb.and(appServerNamePred, cb.like(r.<String>get("name"), input)));
    q.select(r.<String>get("name"));
    q.distinct(true);

    q.orderBy(cb.asc(r.get("name")));

    return entityManager.createQuery(q);
}

---

From source file:org.openmeetings.app.data.user.Organisationmanagement.java

/**
 * //from w ww . j a  v  a  2s .c om
 * @param user_level
 * @return
 */
public List<Organisation> getOrganisations(int start, int max, String orderby, boolean asc) {
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Organisation> cq = cb.createQuery(Organisation.class);
        Root<Organisation> c = cq.from(Organisation.class);
        Predicate condition = cb.equal(c.get("deleted"), "false");
        cq.where(condition);
        cq.distinct(asc);
        if (asc) {
            cq.orderBy(cb.asc(c.get(orderby)));
        } else {
            cq.orderBy(cb.desc(c.get(orderby)));
        }
        TypedQuery<Organisation> q = em.createQuery(cq);
        q.setFirstResult(start);
        q.setMaxResults(max);
        List<Organisation> ll = q.getResultList();
        return ll;
    } catch (Exception ex2) {
        log.error("[getOrganisations]", ex2);
    }
    return null;
}

---

• «
• 1
• 2
• 3
• 4
• »