Hibernate criteria with projection doesn't return the entity on which criteria is implemented

Question

I'm using spring-hibernate and using HibernateDAOSupport class. I have two tables mapped to each other in one-to-many fashion. I'm implementing the below criteria

 DetachedCriteria criteria = getCriteria( "a" )
        .setProjection( Projections.projectionList()
                .add( Projections.groupProperty("a.id" ) )
                .add( Projections.count( "a.id" ), "count" )
                )
        .createCriteria( "huApps", "hu")
        .addOrder( Order.desc( "count" ) )
        ;

this works well and create the below query

select
        this_.id as y0_,
        count(this_.id) as y1_ 
    from
        apps this_ 
    inner join
        huapps huapp1_ 
            on this_.id=huapp1_.appid 
    group by
        this_.id 
    order by
        y1_ desc

In result, it returns a list of object[]. But I want that it should return List<App>(App is the class on which I implemented/created the criteria). I want that it would create the query

select
 this_
    from
        apps this_ 
    inner join
        huapps huapp1_ 
            on this_.id=huapp1_.appid 
    group by
        this_.id 
    order by
        y1_ desc

Please help me in writing the correct criteria. I also tried with sqlProjection() but even that didn't work. Is there any way I can achieve this?

Zoosporangium

Answer 1

You try to add orger for new critheria that is result of function detachedCriteria.createCriteria("huApps", "hu"). This function return the new criteria for class of huApp property.

Try to replace you criteria like this:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(A.class);
detachedCriteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("id"))
            .add(Projections.count("id"), "count")
    );

detachedCriteria.createCriteria("huApps", "hu");
detachedCriteria.addOrder(Order.desc("count"));

List<A> list = detachedCriteria.getExecutableCriteria(getSession()).list();

It works well for me.

Hospitality

Again creating the same query select this_.id as y0_, count(this_.id) as y1_ from ChleonCloudVault.apps this_ inner join ChleonCloudVault.huapps huapp1_ on this_.id=huapp1_.appid group by this_.id order by y1_ desc – Zoosporangium

How do you generate this sql? This string of code detachedCriteria.getExecutableCriteria(getSession()).list()return the instance of List class but I specify the generic of needed type – Hospitality

Yes that returns a List<Object[]>. Every element of the list contains an array of Object(each of size 2). at index=0, id and at index=1, count for that app. And the provided sql is generated by Hibernate. – Zoosporangium

Oh! Maybe I should wear glasses. I'm so sorry for my inattention. But you can not provide your desired request in sql. Sql can get you only field on which you grouping or some aggregation functions like max or count. So result that you get is not hibernate limitation. Your desired request in sql looks like this: SELECT a.* FROM A_TABLE a INNER JOIN (SELECT field, count(*) as score from A_TABLE GROUP BY field) b ON a.field = b.field ORDER BY b.score So you must use sub-query for inner select. – Hospitality

Answer 2

Try calling

DetachedCriteria criteria = getCriteria( "a" )
    .setProjection( Projections.projectionList()
    .add( Projections.groupProperty("a.id" ), "id" )
            .add( Projections.count( "a.id" ), "count" )
            )
    .createCriteria( "huApps", "hu")
    .addOrder( Order.desc( "count" ) )
    .setResultTransformer(Transformers.aliasToBean(App.class))

This should map the properties alias to the fields of the bean that you specify. App will need setters and getters on the appropriate fields

Parade