0

i'm implementing a search routine with hibernate criteria to find registered users. Each user can have one or more addresses. i've build the following structure and i add it the restirctions i need to query the db :

ricerca = s.createCriteria(User.class).createAlias("addresses","a",Criteria.LEFT_JOIN);

when i search by user data (name, surname, ssn, etc ) i got a record for each known address the user has provided (see below a simplified version of the hibernate query i've tested with a db client)

    select *
from User this_ left outer join Address i1_ 
on this_.ID=i1_.User where lower(this_.Name) like '%mario%' order by i1_.ID desc

Mario has got three addresses, and the db client returns three records : that's fine for me, but hibernate returns three object of type User, each one with the addresses collection complete with all addresses. It's like when you miss to put a distinct in a query. Can i redesign the query so that i get one address per row (still three results for the same User but with a single distinct address) ? Note : A user could haven't provided any address.

1 Answers1

1

Would the solution described here work for you?

It calls for using distinct projection on user id to get user ids and then hydrate them as needed.

ricerca.setProjection(Projections.distinct(Projections.property("id")))
Community
  • 1
  • 1
Alex Gitelman
  • 24,429
  • 7
  • 52
  • 49
  • ok the projection finds all distinct user ids, then i have to "hydrate" them : this my first hibernate project,i suppose it means to retrieve each user by Id. I'm been obliged to remove an order by clause on the addresses collection from the User mapping file , which caused hibernate to generate a wrong sql query . There's a way to apply the ordering to the detached User entity with hibernate or i'll have to write the address comparator ? thank you for your answer –  Oct 12 '11 at 09:38