java JPA + Hibernate - 父表和子表之间的内部连接

JPA + Hibernate - Inner Join between parent and child tables

提问by JavaJuggler

EDIT (Completely reformulated approach):

编辑（完全重新制定的方法）：

I'm trying to promote the use of JPA in a new project but I'm struggling with a supposedly trivial problem: An INNER JOIN between two tables (parent and child).

我正在尝试在一个新项目中推广 JPA 的使用，但我正在努力解决一个所谓的小问题：两个表（父表和子表）之间的 INNER JOIN。

I will provide only the essential info and leave all the rest out. Please feel free to ask more info if it's needed. There are two tables LANGUAGE and MESSAGE_RESOURCE, where the parent table is LANGUAGE (Primary Key ID_LANGUAGE) and the child table has a Foreign Key to the parent table also named ID_LANGUAGE.

我将只提供必要的信息，其余的都没有。如果需要，请随时询问更多信息。有两个表 LANGUAGE 和 MESSAGE_RESOURCE，其中父表是 LANGUAGE（主键 ID_LANGUAGE），子表具有父表的外键，也称为 ID_LANGUAGE。

The Language (parent) class:

语言（父）类：

@Entity
@Table(name = "PF_LANGUAGE")
public class Language {
    @Id
    @Column(name = "ID_LANGUAGE", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int idLanguage;

    @OneToMany(mappedBy="language",targetEntity=MessageResource.class, fetch=FetchType.EAGER)
    private Collection<MessageResource> messageResources;
}

The child class:

子类：

@Entity
@Table(name = "PF_MESSAGE_RESOURCE")
public class MessageResource {

    @Id
    @Column(name = "ID_MESSAGE_RESOURCE", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int idMessageResource;

    @ManyToOne(optional=false)
    @JoinColumn(name="ID_LANGUAGE")
    private Language language;
}

I'm fetching the results with a named query:

我正在使用命名查询获取结果：

entityManager.createNamedQuery("select l, r from Language l join l.messageResources r");

This results in a result Object array where each entry contains one Language, MessageResource pair. The problem is that this is getting done in separate queries.

这会产生一个结果对象数组，其中每个条目包含一个 Language, MessageResource 对。问题是这是在单独的查询中完成的。

I can see in debug output that the first query is an INNER JOIN between both tables, containing the columns from both tables in the output, so this should be enough. But JPA is doing 2 additional queries (the number of LANGUAGE records) fetching the child table values for each parent table again, which should be not necessary.

我可以在调试输出中看到第一个查询是两个表之间的 INNER JOIN，在输出中包含来自两个表的列，所以这应该足够了。但是 JPA 正在执行 2 个额外的查询（LANGUAGE 记录的数量）以再次获取每个父表的子表值，这应该不是必需的。

First query which is enough to get all data:

第一个查询足以获取所有数据：

select
   language0_.ID_LANGUAGE as ID1_5_0_,
   messageres1_.ID_MESSAGE_RESOURCE as ID1_4_1_,
   language0_.CODE as CODE5_0_,
   language0_.DATE_INS  as DATE3_5_0_,
   language0_.DESCRIPTION  as DESCRIPT4_5_0_,
   messageres1_.DATE_INS  as DATE2_4_1_,
   messageres1_.KEY as KEY4_1_,
   messageres1_.ID_LANGUAGE as ID5_4_1_,
   messageres1_.VALUE as VALUE4_1_ 
from
   PF_LANGUAGE language0_ 
inner join
   PF_MESSAGE_RESOURCE messageres1_ 
on language0_.ID_LANGUAGE=messageres1_.ID_LANGUAGE

Two redundant queries like the following are also run against the database after the first inner join (they are run once for each LANGUAGE table records):

在第一次内部联接之后，还会对数据库运行如下两个冗余查询（它们对每个 LANGUAGE 表记录运行一次）：

select
    messageres0_.ID_LANGUAGE as ID5_5_1_,
    messageres0_.ID_MESSAGE_RESOURCE as ID1_1_,
    messageres0_.ID_MESSAGE_RESOURCE as ID1_4_0_,
    messageres0_.DATE_INS  as DATE2_4_0_,
    messageres0_.KEY as KEY4_0_,
    messageres0_.ID_LANGUAGE as ID5_4_0_,
    messageres0_.VALUE as VALUE4_0_ 
from
    PF_MESSAGE_RESOURCE messageres0_ 
where
    messageres0_.ID_LANGUAGE=?

I need to eliminate the two redundant additional queries generated by JPA. The first inner join is enough to get all data.

我需要消除 JPA 生成的两个多余的附加查询。第一个内连接足以获取所有数据。

Need help with this one. Any clues?

需要这方面的帮助。有什么线索吗？

回答by Adrian Shum

I am not sure if it is the actual solution, but my experience with eager fetch is bad. It is always fetching in some way that I am not expected.

我不确定这是否是实际的解决方案，但我对 Eager fetch 的体验很糟糕。它总是以某种我不期望的方式获取。

Your query is weird, it doesn't make sense that you are selecting both Language and MessageResource

您的查询很奇怪，您同时选择 Language 和 MessageResource 是没有意义的

You may have a try:

你可以试试：

Remove fetch=FetchType.EAGERin Language's relationship to MessageResource, and change the query to something

fetch=FetchType.EAGER在 Language 与 MessageResource 的关系中删除，并将查询更改为某些内容

select l from Language l join fetch l.messageResources where ....

It should give you the Language, and the aggregated message resources of that instance, all in one SQL.

它应该在一个 SQL 中为您提供该实例的语言和聚合消息资源。

Something off-topic, I wonder why you are having MessageResource.language being insertable=false, updatable=false. It seems contradicting as in Language, you specified the relationship mapped by this field but you are now making it non-insertable/updatable.

一些题外话，我想知道为什么你有 MessageResource.language 是insertable=false, updatable=false. 这似乎与语言中的矛盾，您指定了由该字段映射的关系，但您现在使其不可插入/可更新。

回答by antoni.rasul

Try changing fetch=FetchType.EAGERfor messageResources to fetch=FetchType.LAZYSince you are explicitly joining, changing FetchTypeto LAZYmight help.

尝试将fetch=FetchType.EAGERmessageResources更改为fetch=FetchType.LAZY由于您明确加入，更改FetchType为LAZY可能会有所帮助。

回答by axtavt

I don't quite understand why do you expect inner joinin this case.

我不太明白你为什么期望inner join在这种情况下。

optional = "false"at @ManyToOnemeans that every MessageResourcemust have a Language, but it does not imply that every Languagemust have at least one MessageResource, therefore left joinwhen loading Languageis correct in this case - Languagethat you load may have no MessageResources associated with it.

optional = "false"at@ManyToOne意味着每个人都MessageResource必须有一个Language，但这并不意味着每个人都Language必须至少有一个MessageResource，因此在这种情况下left join加载Language正确时 -Language您加载的可能没有MessageResource与之关联的 s 。

EDIT:The purpose of find()is to load an object with the given id if it exists in the database. If you want something semantically different (for example, to load all Languages that have at least one MessageResource), you need to write a query for it, such as select l from Language l join l.messageResources.

编辑：目的find()是加载具有给定 id 的对象（如果它存在于数据库中）。如果你想要一些语义不同的东西（例如，加载所有Language至少有一个 的 s MessageResource），你需要为它编写一个查询，例如select l from Language l join l.messageResources.

回答by Shailendra

There does not seem to be a standard way by using standard annotation to force generation of inner query (JPA is a standard and you are using an implementation provider For e.g., Hibernate, TopLink, Open JPA etc). Different JPA implementations use different join strategy. For more details please go through thisand this.

似乎没有使用标准注释强制生成内部查询的标准方法（JPA 是一个标准，您正在使用实现提供程序，例如 Hibernate、TopLink、Open JPA 等）。不同的 JPA 实现使用不同的连接策略。有关更多详细信息，请查看此和此。