1

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.

rac3b3nn0n
  • 861
  • 2
  • 12
  • 26
  • Check this http://stackoverflow.com/questions/12032748/hibernate-criteria-projection-distinct – Jay May 07 '14 at 11:39
  • Thanks for the reply @Jay ... how will the value be returned though? At the moment I'm doing something like List at the moment, how will that change?!? Thanks – rac3b3nn0n May 07 '14 at 11:42
  • Can you print that list and see what it prints ? – Jay May 07 '14 at 11:50
  • Can you clarify what the difference is between this and the question you asked a week ago? At first look, they seem very similar. – Dawood ibn Kareem May 07 '14 at 11:53
  • The difference is, the previous question I asked was running a distinct query based on userType so that I could retrieve the result like --> admin,co-ordinator,developer. Whereas, in this question, I want the corresponding id to be returned along with the distinct userTypes so that my result will include something of this sort --> user_id,user_type – rac3b3nn0n May 07 '14 at 12:04
  • Oh, right, I see. Sorry. – Dawood ibn Kareem May 07 '14 at 12:05
  • I'm just looking at http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch17.html#querycriteria-projection and it looks like you should be able to do `c.setProjection(Projections.projectionList().add(Projections.groupProperty("user_id").add(Projections.groupProperty("user_type")));` I think this will return you a list of maps. I haven't done precisely what you're doing though, so I'm far from certain. You could try this and see how it goes. – Dawood ibn Kareem May 07 '14 at 12:11
  • @DavidWallace I am trying something similar, what I've done is PRojectionList projList = Projections.projectionList(); projList.add(Projections.property("userId")); projList.add(Projections.property("userType"));c.setProjection(Projections.distinct(projList)); After doing this, I just checked the output of my List userTypeList and the output does show user_id,user_type ... however, it seems to throw java.lang.ClassCastException when it goes to the for loop, I'm assuming the reason is that user_id is Integer and user_type is String and both are under List. Am I right? – rac3b3nn0n May 07 '14 at 12:24
  • What is the full ClassCastException error ? What it expects instead of String ? – Jay May 07 '14 at 12:29
  • 1
    Hibernate will return you a list of Object arrays. i.e., List. The first element in each array would be user_id and the second would be user_type. You are getting a ClasscastException because you are expecting a String and hibernate is returning you a Object[] instance. – Priyesh May 07 '14 at 12:44
  • 1
    Can you show us the code that gives you the `ClassCastException`? As Priyesh says, you'll get a list of `Object[]`, which you'll have to unpack, then cast each `Object` individually. It may be helpful to use a debugger as you write your code, to just check on the types of everything that comes back. – Dawood ibn Kareem May 07 '14 at 12:50
  • Thanks Priyesh, @DavidWallace the code is, ProjectionList projList = Projections.projectionList(); projList.add(Projections.property("userId")); projList.add(Projections.property("userType")); c.setProjection(Projections.disti‌​nct(projList)); List userTypeList = c.list(); for(String userType : userTypeList) { if(!userType.equalsIgnoreCase("")) { returnVal.add(userType); } } The ClassCastException occurs at the for loop. The full exception states, java.lang.ClassCastException: (Ljava.lang.Objectl cannot be cast to java.lang.String – rac3b3nn0n May 07 '14 at 13:45
  • Right. As Priyesh said, `userTypeList` will be `List` not `List`. Then you'll need to iterate through it with an `Object[]`, not a `String`. Like `for(Object[] userTypeObject : userTypeList)` and so on. Then to use it, the user id will be `(Integer) userTypeObject[0]` and the user type will be `(String) userTypeObject[1]`. – Dawood ibn Kareem May 07 '14 at 14:04
  • Alright ... Thanks, I'll take a look into it and will get back to you. Thanks DavidWallace and @Priyesh – rac3b3nn0n May 07 '14 at 14:58
  • @DavidWallace okay this would sound silly but I am not able to get values out of the `Object userType` inside for-loop. This is what I've done, `for(Object userType : userTypeList) { Not really sure what to write here to get the values out, if I need to print the both values out, how can I?}` I am not very familiar with extended for-loops sorry, I still prefer using the traditional for loop ... – rac3b3nn0n May 08 '14 at 11:19
  • 1
    `Integer userId = (Integer) userType[0]; String type = (String) userType[1]; System.out.println( "The user id is " + userId + " and the user type is " + type );` – Dawood ibn Kareem May 08 '14 at 11:50
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/52297/discussion-between-rac3b3nn0n-and-david-wallace) – rac3b3nn0n May 08 '14 at 11:53

1 Answers1

0

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",""));

madhu pathy
  • 429
  • 2
  • 6