sql - Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns

Question

Using Hibernate's Criteria, I want to execute the equivalent of:

select distinct uspscity, state from citycomplete where USPSCITY = 'HOUSTON'

I thought doing the following would yield the results I wanted:

ProjectionList projList = new ProjectionList();
projList.add(Projections.distinct(Projections.property("id.state")));
projList.add(Projections.distinct(Projections.property("id.uspsCity")));
criteria.setProjection(projList);

But, what this actually does is execute something like:

select distinct uspscity, distinct state from citycomplete where USPSCITY = 'HOUSTON'

Which throws an error, obviously.

Other than not using Criteria, is there a solution for this?

Thanks,

Brandon

Answer 1

ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("id.state"));
projList.add(Projections.property("id.uspsCity"));
criteria.setProjection(Projections.distinct(projList));