Hibernate JOIN FETCH - Objects appear multiple times in Resultset

Question

I am using Spring JPA and Hibernate to build a REST API. I am searching for 2 days, but i didn't find any solution to fix this issue.

In some queries i have multiple JOIN FETCH clauses. When I execute my query i have the parent object multiple times in my result set, in fact exactly as often as the father has children.
Example:

    @Query("SELECT DISTINCT p AS post," +
        " <some subquery>" +
        "FROM Post p  JOIN FETCH p.user u LEFT JOIN FETCH p.linkedUsers INNER JOIN FETCH p.track track  " 
        "WHERE ( (<some condition>) OR p.user = :me) " +
        "ORDER BY p.created DESC")
List<Object []> getData(@Param("me")User me,
                                 Pageable pageable);

For example if i have a post with 2 linked users my post will appear at least 2 times in my result set. If i don't do the JOIN FETCH or an other JOIN the data is loaded lazily. But this is not a goal for me since it causes a bad performance.

Edit: So my question is, how to execute one query where all data is fetched and all posts which met the specified criteria are only ONE time in my resultset.

Edit:

Example object is:

 [{
    "id": 1767,
    "track": {
        "id": 1766,
        "title": "VVS",
        ...
        "streams": [
            {
                "id": 1764,
                 ....
            },
            {
                "id": 1765,
                  ...

            }
        ],
        "isrc": "DEQ021801393"
    },
    "user": {
        "id": 999998,
        "username": "My username",
         ....

    },
    "created": "2018-08-21T22:18:56.451Z",
     ...
    "linked_users": []
},
<this object another time, beacause two stream objects in track>
<many other objects two times>
...
]

Edit:

It turned out, that the subqueries stand in conflict with the "distinct". If i remove them from the query i get distinct posts. If i edit the native sql query and alter the "distinct" to a "distinct on" it works. But i think a "distinct on" clause doesn't exist in hibernate. So any good ideas what to do?

I would appreciate any help :)

What exactly is your question? If you use `JOIN FETCH`, the child objects are directly loaded, so that you don't have to load them anymore afterwards. If you use `JOIN`, child objects are lazy loaded as you pointed out. What are you trying to achieve? — Michael Altenburger, Sep 25 '18 at 09:34
@MichaelAltenburger i want to execute one query where all my data is loaded, that means parent with related childs. So no more select queries should be executed. But if i execute the query above i have one post multiple times in my result set. I want every post to be one time in my result. — SupaMario, Sep 25 '18 at 09:51
Then you could remove the join query completely and specify the relation linkedUsers in Post as `@OneToMany(mappedBy = "xxx", fetch = FetchType.EAGER)`. This way, you should only have each post once in your query but the linked Users are fetched immediately when the query is executed. — Michael Altenburger, Sep 25 '18 at 09:58
@MichaelAltenburger It is an undirectional relation so mappedBy statement is not needed. FetchType.Eager is not very good, since then data is fetched in a new sql query for every relation. — SupaMario, Sep 25 '18 at 10:08

score 0 · Answer 1 · answered Sep 25 '18 at 11:36

0

In jpa 2.1, it support entity graph to solve the "Duplicate and N+1 issues"

@NamedEntityGraph(name = "graph.Order.items", 
               attributeNodes = @NamedAttributeNode(value = "items", subgraph = "items"), 
subgraphs = @NamedSubgraph(name = "items", attributeNodes = @NamedAttributeNode("product")))

Use entity graph to load data:

Map hints = new HashMap();
hints.put("javax.persistence.fetchgraph", graph);

return this.em.find(Order.class, orderId, hints);

More detail:

https://www.thoughts-on-java.org/jpa-21-entity-graph-part-1-named-entity/

answered Sep 25 '18 at 11:36

Huy Nguyen

1,931
1
11
11

i implemented an entitiy graph and annotated my query but it didnt help. i have still the same issue. I can delete the JOIN statements from my query so the graph works, but i still have duplicate objects in my resultset. Any other good idea? – SupaMario Sep 26 '18 at 14:12
Hello, Please update your code and show something about "duplicated object". Maybe we can make it more clearly – Huy Nguyen Sep 26 '18 at 14:30
i updated my text. you can see an example object above. – SupaMario Sep 26 '18 at 15:07

score 0 · Accepted Answer · answered Nov 30 '18 at 14:54

If anyone is interested in how i solved the problem:

After weeks of ignoring this problem i found a solution. I am pretty sure that it cannot be solved by using HQL, so i wrote a native query by using postgreSQLs json_build_object method. All data is produced on database level and fetched as a list of JSON Objects. This has the advantage that the performance of the query is much better.

Query Structure:

SELECT DISTINCT ON(post.created)json(json_build_object('id',...
'user', (SELECT json_build_object('id',... 
            FROM users WHERE users.id = post.user_id)
'track', (SELECT json_build_object('id',....
     'streams',ARRAY(SELECT json_build_object('id'...
FROM
<APPORXIMATELY Same JOINS AS BEFORE>
WHERE
<CONDITIONS>

Execute query from SpringBoot:

 List<JsonNode> decoratedPosts = (List<JsonNode>) entityManager
                .createNativeQuery(NativeQueries.getPostsByUser)
                .setParameter("userId", user.getId())
                .setParameter("me", requestor.getId())
                .setParameter("limit", pageable.getPageSize())
                .setParameter("offset", pageable.getOffset())
                .unwrap(org.hibernate.query.NativeQuery.class)
                .addScalar("json", JsonNodeBinaryType.INSTANCE).getResultList();

To get this work the following dependency is needed (depending on your hibernate version):

    <dependency>
        <groupId>com.vladmihalcea</groupId>
        <artifactId>hibernate-types-52</artifactId>
        <version>2.3.4</version>
    </dependency>

And the hibernate dialect needs to be extended.

public class MariosPostgreSQL94Dialect extends PostgreSQL94Dialect {

public MariosPostgreSQL94Dialect() {
    super();
    this.registerColumnType(Types.OTHER, "json");
}

Hibernate JOIN FETCH - Objects appear multiple times in Resultset

2 Answers2

Linked