Hibernate JOIN FETCH - 对象在结果集中出现多次 [英] Hibernate JOIN FETCH - Objects appear multiple times in Resultset

问题描述

我正在使用 Spring JPA 和 Hibernate 来构建 REST API.我正在寻找 2 天，但我没有找到解决此问题的任何解决方案.

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.

在某些查询中，我有多个 JOIN FETCH 子句.当我执行查询时，我的结果集中有多次父对象，实际上与父亲有孩子一样频繁.
示例:

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);

例如，如果我有一个包含 2 个链接用户的帖子，我的帖子将在我的结果集中出现至少 2 次.如果我不执行 JOIN FETCH 或其他 JOIN，则数据会延迟加载.但这不是我的目标，因为它会导致表现不佳.

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.

所以我的问题是，如何执行一个查询来获取所有数据并且所有满足指定条件的帖子在我的结果集中只有一次.

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.

示例对象是:

 [{
    "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>
...
]

事实证明，子查询与独特"相冲突.如果我从查询中删除它们，我会得到不同的帖子.如果我编辑本机 sql 查询并将distinct"更改为distinct on"，它就可以工作.但我认为hibernate中不存在distinct on"子句.那么有什么好主意该怎么做?

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 :)

推荐答案

如果有人对我如何解决问题感兴趣:

If anyone is interested in how i solved the problem:

在忽略这个问题数周后，我找到了解决方案.我很确定它不能通过使用 HQL 来解决，所以我使用 postgreSQLs json_build_object 方法编写了一个本机查询.所有数据都在数据库级别生成并作为 JSON 对象列表获取.这样做的好处是查询的性能要好得多.

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.

查询结构:

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>

从 SpringBoot 执行查询:

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>

而且需要扩展hibernate方言.

And the hibernate dialect needs to be extended.

public class MariosPostgreSQL94Dialect extends PostgreSQL94Dialect {

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

这篇关于Hibernate JOIN FETCH - 对象在结果集中出现多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！