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.