how to get unique values from a column using queryDsl predicate

Question

I am trying to get a unique value from a column say "designation" from a table "employee_register". I dont know how to acheive this using the query Dsl predicate. Can anyone help me with this

Solution 1

You can call distinct() on the Query object. For example (JPA + QueryDSL):

@Test
@Transactional
public void testQueryDSLDistinct() throws Exception {
    log.debug("testQueryDSLDistinct started");
    JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);
    QEmployeeRegister er = QEmployeeRegister.employeeregister;
    List<EmployeeRegister> tuples = queryFactory.select(
            Projections.bean(EmployeeRegister.class, er.designation)).distinct()
            .from(er).limit(10).fetch();
    for (EmployeeRegister record: tuples) {
        System.out.println(record.getDesignation());
    }
}

Solution 2

I've found this while going through this link http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-four-jpa-criteria-queries .A similar question was raised by a viewer called raghu and below is the author's answer to the question.May be this one would be helpful to others

Author's answer

You have two options for implementing this:

Use the DISTINCT keyword of JPQL when you are creating query by using the @NamedQuery or @Query annotation. Call the disctinct() method of the CriteriaQuery class in your specification builder method (The toPredicate() method of the Specification interface gets a reference of the CriteriaQuery object as a parameter).

JPQL Example:

SELECT DISTINCT p FROM Person p WHERE...

Criteria API with Specification Builder:

public class PersonSpecifications {
public static Specification lastNameIsLike(final String searchTerm) {

return new Specification () {
@Override
public Predicate toPredicate(Root personRoot, CriteriaQuery< ?> query,CriteriaBuilder cb) {
query.distinct(true);
//Build Predicate
}
};
}
}

In your case, I would add the following method to the CustomerRepository interface (or whatever your repository interface is):

@Query("SELECT DISTINCT c.lastName FROM Customer c")
public List<String> findLastNames();