CODEWITHSUNDEEP
javasqlhibernatedistinct
rac3b3nn0n
rac3b3nn0n

Reputation: 881

Hibernate - Distinct Query returning two columns result

Sometime back I asked the question regarding how to do a Distinct query using Hibernate. Now that I'm past that milestone, there is another thing that I require. And that is, given the table, 

---------------------------------------
| user_id  |  user_name  | user_type  |
---------------------------------------
|    1     | mark taylor | admin      |
|    2     | bill paxton |co-ordinator|
|    1     | tony brooks | admin      |
|    3     | ali jahan   | developer  |
---------------------------------------

I want to create a distinct query which returns the distinct user_type along with it's corresponding user_id. Please do note that the user_id for a particular user_type is same. So for example,

admin = 1
co-ordinator = 2
developer = 3

So the return I'm expecting is somewhat like a ArrayList or that sort which contains both values like 

user_id,user_type

The code I've written to get Distinct UserType is as follows and I'm hoping there could be some modification to it to get the desired result. 

public List<String> findDistinctUserName() throws HibernateException {
    List<String> returnVal = new ArrayList<String>();

    Criteria c = this.createCriteria();
    c.setProjection(Projections.distinct(Projections.property("userType")));
    c.addOrder(Order.asc("userType"));

    List<String> userTypeList = c.list();

    for(String userType : userTypeList) {
        if(!userType.equalsIgnoreCase("")) {
            returnVal.add(userType);
        }
    }

    return returnVal;
}

Thank you for your answers in advance.

Upvotes: 1

Views: 2109

Answers (1)

madhu pathy
madhu pathy

Reputation: 474

Try this:
criteria.setProjection(Projections.distinct(Projections.property("userType")), "userType");

Also u don't have to check for blank strings, try this:

criteria.add(Restrictions.ne("userType",""));

Upvotes: 0

Related Questions