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"，则它可以正常工作.但是我认为休眠中不存在与众不同"子句.那么有什么好主意怎么办?
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?
我将不胜感激:)
推荐答案
如果有人对我如何解决问题感兴趣:
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>
休眠方言需要扩展.
public class MariosPostgreSQL94Dialect extends PostgreSQL94Dialect {
public MariosPostgreSQL94Dialect() {
super();
this.registerColumnType(Types.OTHER, "json");
}
这篇关于Hibernate JOIN FETCH-对象在结果集中出现多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
