How do you create a Distinct query in HQL

Question

Is there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a valid keywork for HQL, but I am looking for the HQL equivalent of the SQL keyword "distinct".

Answer 1

Here's a snippet of hql that we use. (Names have been changed to protect identities)

String queryString = "select distinct f from Foo f inner join foo.bars as b" +
                " where f.creationDate >= ? and f.creationDate < ? and b.bar = ?";
        return getHibernateTemplate().find(queryString, new Object[] {startDate, endDate, bar});

Answer 2

You can also use Criteria.DISTINCT_ROOT_ENTITY with Hibernate HQL query as well.

Example:

Query query = getSession().createQuery("from java_pojo_name");
query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return query.list();

Answer 3

Suppose you have a Customer Entity mapped to CUSTOMER_INFORMATION table and you want to get list of distinct firstName of customer. You can use below snippet to get the same.

Query distinctFirstName = session.createQuery("select ci.firstName from Customer ci group by ci.firstName");
Object [] firstNamesRows = distinctFirstName.list();

I hope it helps. So here we are using group by instead of using distinct keyword.

Also previously I found it difficult to use distinct keyword when I want to apply it to multiple columns. For example I want of get list of distinct firstName, lastName then group by would simply work. I had difficulty in using distinct in this case.

Answer 4

You can you the distinct keyword in you criteria builder like this.

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Orders> query = builder.createQuery(Orders.class);
Root<Orders> root = query.from(Orders.class);
query.distinct(true).multiselect(root.get("cust_email").as(String.class));

And create the field constructor in your model class.

Answer 5

I had some problems with result transformers combined with HQL queries. When I tried

final ResultTransformer trans = new DistinctRootEntityResultTransformer();
qry.setResultTransformer(trans);

it didn't work. I had to transform manually like this:

final List found = trans.transformList(qry.list());

With Criteria API transformers worked just fine.

Answer 6

My main query looked like this in the model:

@NamedQuery(name = "getAllCentralFinancialAgencyAccountCd", 
    query = "select distinct i from CentralFinancialAgencyAccountCd i")

And I was still not getting what I considered "distinct" results. They were just distinct based on a primary key combination on the table.

So in the DaoImpl I added an one line change and ended up getting the "distinct" return I wanted. An example would be instead of seeing 00 four times I now just see it once. Here is the code I added to the DaoImpl:

@SuppressWarnings("unchecked")
public List<CacheModelBase> getAllCodes() {

    Session session = (Session) entityManager.getDelegate();
    org.hibernate.Query q = session.getNamedQuery("getAllCentralFinancialAgencyAccountCd");
    q.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); // This is the one line I had to add to make it do a more distinct query.
    List<CacheModelBase> codes;
    codes = q.list();
    return codes;       
}

I hope this helped! Once again, this might only work if you are following coding practices that implement the service, dao, and model type of project.