Hibernate: Select N rows, but with only unique values in one column

Question

I want to implement this with Hibernate criteria.

Let's say I have data like(from the link):

ID  Name            City            Birthyear
1   Egon Spengler   New York        1957
2   Mac Taylor      New York        1955
3   Sarah Connor    Los Angeles     1959
4   Jean-Luc Picard La Barre        2305
5   Ellen Ripley    Nostromo        2092
6   James T. Kirk   Riverside       2233
7   Henry Jones     Chicago         1899

And I want to implement this with Hibernate(from the link):

SELECT P.*, COUNT(*) AS ct
   FROM people P
   JOIN (SELECT MIN(Birthyear) AS Birthyear
              FROM people 
              GROUP by City) P2 ON P2.Birthyear = P.Birthyear
   GROUP BY P.City
   ORDER BY P.Birthyear ASC 
   LIMIT 10;

If I have an entity:

@Entity
@Table(name = "people")
public class People {
    @Id
    private int id;

    @Column
    private String name;

    @Column
    private String city;

    @Column 
    int birthyear;
}

Then I can make a criteria without self join part(this might not work):

Criteria criteria = sessionFactory.getCurrentSession()
   .createCriteria(People.class, "people")
   .setProjection(Projections.projectionList()
        .add(Projections.property("people.id"))
        .add(Projections.property("people.name"))
        .add(Projections.property("people.city"))
        .add(Projections.groupProperty("people.city)))
    .addOrder(Order.asc("people.birthyear"));

How to implement self join part?

not making fun, but why don't you use HQL it's much more simple — Marco Aviles, Feb 01 '12 at 05:26
Because I want add more restrictions dynamically. HQL is bad on that. Though, I'd thank if you give me the answer in HQL way. — Sanghyun Lee, Feb 01 '12 at 05:46
@Sangdol, Hi buddy.. I think you may got the answer for this question by now. If not, let me know, i'll try my best. But i've another question, according to this comment http://stackoverflow.com/posts/comments/51993306?noredirect=1, he said you've written `AlianToBeanNestedResultTransformer` for `OneToMany (aka Collections)` relationships.. If so, could you possibly share it? I'm in desperate of `AliasToBeanNestedResultTransformer` for `OneToMany` collections.. — The Coder, Aug 17 '15 at 16:06

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I tried with HQL, and is still a little complex, here you can find detail.

I found it easier(but maybe not so elegant) to do it with a native query.

String hql="SELECT P.*, COUNT(*) FROM people P JOIN 
            (SELECT MIN(Birthyear) AS Birthyear FROM people GROUP by City) P2 ON P2.Birthyear = P.Birthyear
             GROUP BY P.City ORDER BY P.Birthyear ASC";

SQLQuery createSQLQuery = sessionFactory.getCurrentSession().createSQLQuery(hql);
List list = createSQLQuery.list();

Like I said, maybe it's not the most elegant solution, but still seems faster to create and fetch data.

Hope it helps

Hibernate: Select N rows, but with only unique values in one column

1 Answers1