Hibernate - sqlQuery map redundant records while using JOIN on OneToMany

Question

I have @OneToMany association between 2 entities (Entity1 To Entity2).

My sqlQueryString consists of next steps:

select ent1.*, ent2.differ_field from Entity1 as ent1 left outer join Entity2 as ent2 on ent1.item_id = ent2.item_id
Adding some subqueries and writing results to some_field2, some_field3 etc.

Execute:

Query sqlQuery = getCurrentSession().createSQLQuery(sqlQueryString)
                 .setResultTransformer(Transformers.aliasToBean(SomeDto.class));

List list = sqlQuery.list();

and

class SomeDto {
    item_id;
    some_filed1;
    ...
    differ_field;
    ...

}

So the result is the List<SomeDto>

Fields which are highlighted with grey are the same.

So what I want is to group by, for example, item_id and the List<Object> differFieldList would be as aggregation result.

class SomeDto {

 ...fields...

 List<Object> differFieldList;

}

or something like that Map<SomeDto, List<Object>>

I can map it manually but there is a trouble: When I use sqlQuery.setFirstResult(offset).setMaxResults(limit) I retrieve limit count of records. But there are redundant rows. After merge I have less count actually.

Thanks in advance!

With all the work you are doing, why not just go the whole hog and use JPA? — DuncanKinnear, Aug 12 '15 at 22:20
@DuncanKinnear , I'd wish to. But I have a few subqueries in FROM clause. As I read Hibernate(JPA) doesn't support such things — InsFi, Aug 12 '15 at 22:29
@baba , in `FROM` clause? I know only about `WHERE` and `SELECT` clause. — InsFi, Sep 08 '15 at 14:18
@InsFI with Hibernate, you can use HQL which is an abstraction of normal sql and it has the same power that normal SQL has, so yes, you can do inner, outer, left right and upside down joins. — Nikola Yovchev, Sep 08 '15 at 14:40
@baba , [stack](http://stackoverflow.com/questions/7269010/jpa-hibernate-subquery-in-from-clause) so that is wrong answer? — InsFi, Sep 08 '15 at 14:43
@InsFI you can always do a native query with hibernate in which you specify the code and it executes it natively (without hql). But the answer is correct, you can see it also here: http://stackoverflow.com/questions/10624794/hql-is-it-possible-to-perform-an-inner-join-on-a-subquery. — Nikola Yovchev, Sep 08 '15 at 14:50
@baba, so my question is: when i'm using native query how can I group redundant records using Hibernate? =) — InsFi, Sep 08 '15 at 14:53
take a look at this one http://stackoverflow.com/questions/25536868/criteria-distinct-root-entity-vs-projections-distinct — Nikola Yovchev, Sep 08 '15 at 15:05
@baba , yes, You are nearly right but the main problem is that in the native query I select some data from multiple tables. So my result has some fields that don't exist in entity. For example `select a.*, (select ... from ) as filed1.` So I transform result to `SomeDto` by `.setResultTransformer(Transformers.aliasToBean(SomeDto.class));` What I need to do is to specify `ROOT_ENTITY` to help Hibernate to distinct data in that Dto. So it seems like I divide my SomeDto on two parts `ROOT_ENTITY` and `List differFields;` — InsFi, Sep 08 '15 at 15:15

JM Yang · Accepted Answer · 2015-09-13T04:18:59.947

If you would like to store the query results in a collection of this class:

class SomeDto {
 ...fields...
 List<Object> differFieldList;
}

When using sqlQuery.setFirstResult(offset).setMaxResults(n), the number of records being limited is based on the joined result set. After merging the number of records could be less than expected, and the data in List could also be incomplete.

To get the expected data set, the query needs to be broken down into two.

In first query you simply select data from Entity1

select * from Entity1

Query.setFirstResult(offset).setMaxResults(n) can be used here to limit the records you want to return. If fields from Entity2 needs to be used as condition in this query, you may use exists subquery to join to Entity2 and filter by Entity2 fields.

Once data is returned from the query, you can extract item_id and put them into a collection, and use the collection to query Entity 2:

select item_id, differ_field from Entity2 where item_id in (:itemid)

Query.setParameterList() can be used to set the item id collection returned from first query to the second query. Then you will need to manually map data returned from query 2 to data returned from query 1.

This seems verbose. If JPA @OneToMany mapping is configured between the 2 entity objects, and your query can be written in HQL (you said not possible in comment), you may let Hibernate lazy load Entity2 collection for you automatically, in which case the code can be much cleaner, but behind the scenes Hibernate may generate more query requests to DB while lazy loading the entity sitting at Many side.

Yes, I did the same way a few days ago but without lazy. Lazy is not suited in my situatation. My question was : is Hibernate smart enough to do such things in 1 query like it does in HQL. If no, your answer is nearest — InsFi, Sep 13 '15 at 09:00

Vlad Mihalcea · Answer 2 · 2018-01-05T17:34:24.820

The duplicated records are natural from a relational database perspective. To group projection according to Object Oriented principles, you can use a utility like this one:

public void visit(T object, EntityContext entityContext) {
    Class<T> clazz = (Class<T>) object.getClass();
    ClassId<T> objectClassId = new ClassId<T>(clazz, object.getId());
    boolean objectVisited = entityContext.isVisited(objectClassId);
    if (!objectVisited) {
        entityContext.visit(objectClassId, object);
    }
    P parent = getParent(object);
    if (parent != null) {
        Class<P> parentClass = (Class<P>) parent.getClass();
        ClassId<P> parentClassId = new ClassId<P>(parentClass, parent.getId());
        if (!entityContext.isVisited(parentClassId)) {
            setChildren(parent);
        }
        List<T> children = getChildren(parent);
        if (!objectVisited) {
            children.add(object);
        }
    }
}

The code is available on GitHub.

Dear Vlad, I've read many your posts about Hibernate but I really don't understand how your answer feets my question =) Because of my stupidity maybe =) — InsFi, Sep 17 '15 at 14:38
The simplest way is to fork the GitHub repo associated to this article and run the test in debug mode. It's just a simple child to parent association mapping utility. — Vlad Mihalcea, Sep 17 '15 at 15:01

Hibernate - sqlQuery map redundant records while using JOIN on OneToMany

2 Answers2