休眠左联接获取-仅获取第一个表的ID列表 [英] Hibernate left join fetch - get only an ID list of the first table

问题描述

我有以下运行良好的HQL查询，但是它返回完整FooD对象的列表.我只需要FooD对象的ID，因为我需要更快的查询.请注意，在Hibernate映射中，FooD与FooB具有多对一的关系.

I have the following HQL query which works fine, however it returns a list of full FooD objects. I only need the ID of the FooD objects as I need to have faster query. Please not that in Hibernate mappings, FooD has a many-to-one relationship with FooB.

hqlQuery = "from FooD d left join fetch d.bill where d.ts < :ts"

然后我尝试使用相同的HQL查询来获取ID:

I have then tried to get only the ID using the same kind of HQL query:

hqlQuery = "SELECT d.id from FooD d left join fetch d.bill where d.ts < :ts"

我得到了一个查询指定加入获取，但选择列表中没有所获取的关联的所有者".

I got a "query specified join fetching, but the owner of the fetched association was not present in the select list".

然后我将查询转换为常规Oracle SQL，仅获得FooD.ID:

I have then converted the query to regular Oracle SQL to get only FooD.ID:

sqlQuery = "SELECT d.id from FooD d LEFT OUTER JOIN FooB b on d.foodId=b.id where d.ts < :ts"

然后我已经像这样映射了FooD和FooB对象:

I have then mapped FooD and FooB objects like this:

sqlQuery.addEntity(FooD.class);
sqlQuery.addEntity(FooB.class);

，然后通过调用获取结果列表:

and then get the resulting list by calling:

hSession.createSQLQuery(sql).setTimestamp("ts", ts).list();

但是出现以下错误:意外令牌:在第1行附近".

有人使用Hibernate在FooB上进行左外部联接时，只知道如何获得FooD的ID吗?

Does someone know how to do get only the ID of FooD when doing a left outer join on FooB using Hibernate?

推荐答案

更新:

我没有测试，但这应该可以解决问题

I didn't test it, but this should do the trick

SELECT d.id from FooD d inner join d.bill where d.ts < :ts

在添加LEFT时，将其隐式地作为外部联接，并且如果您只需要通过键联接，则无需初始化Bill

when you add LEFT you make it an outer join implicitly, and there is no need to initialize bill if all you need is to join by keys

Hibernate要求对象必须位于select子句中，以便对其进行任何急切的联接提取

Hibernate requires the object to be in the select clause to do any eager join fetches on it

但是，由于d.bill上没有select或where子句，为什么仍然需要获取它?

But since you have no select or where clauses on d.bill, why do you need to fetch it anyway?

如果您只需要id，为什么不这样做，就没有理由进行冗余连接:

If all you need is the id, why not do this, there is no reason for the redundant join:

hqlQuery = "SELECT d.id from FooD d where d.ts < :ts" 

这篇关于休眠左联接获取-仅获取第一个表的ID列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！