问题描述
当在spring jpa中使用@OneToMany关系时,我目前在MYSQL8/H2测试用例中得到意外结果.我想使用JPQL过滤我的TKBData表中的TKBColumn表列表.我希望得到一个带有过滤后的TKBColumn的TKBData表,但我总是得到带有ALL TKBColumn(未过滤)的TKBData表.当我使用SQL命令时,它会起作用!
I currently get unexpected results in my MYSQL8/H2 test-case when using on a @OneToMany relationship in spring jpa. I want to filter in a list of TKBColumn-tables inside my TKBData table using JPQL. I expect to get one TKBData-table with the filtered TKBColumn but I always get the TKBData-table with ALL TKBColumn (unfiltered). When I using a SQL command it works!
我不知道这里的问题是什么,为什么它总是给我TKBData表,里面总是所有TKBColumn表.
I got no Idea whats the problem here, why it always give me the TKBData-table with always ALL TKBColumn-tables inside.
本地查询(有效)
SELECT d.id,c.name FROM TKBDATA d LEFT JOIN TKBDATA_TKBCOLUMN dc ON d.ID = dc.TKBDATA_ID LEFT JOIN TKBCOLUMN c ON c.ID = dc.COLUMNS_ID WHERE c.name = 'column1';
输出
ID NAME
7b6ec910-3e53-40a3-9221-ee60e75c8d67 column1
JPQL查询(不起作用):
JPQL Query (Not works):
select d from TKBData d LEFT JOIN d.columns c WHERE c.name = :name
输出:
id: e892bc28-c35f-4fc8-9b09-387f97a758d8, name:column1
id: 069cc76b-3487-4ad8-a4ae-6568694e2287, name:column2
表"TKBData"
Table 'TKBData'
public class TKBData {
@Id
@Builder.Default
private String id = UUID.randomUUID().toString();
...
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@Builder.Default
private Set<TKBColumn> columns = Sets.newHashSet();
...
}
表"TKBColumn"
Table 'TKBColumn'
public class TKBColumn {
@Id
@Builder.Default
private String id = UUID.randomUUID().toString();
...
}
Spring数据存储库
Spring Data Repository
@Service
public interface KBDataRepository extends CrudRepository<TKBData, String>, KBDataCustomRepository {
@Query("select d from TKBData d LEFT JOIN d.columns c WHERE c.name = :name")
public TKBData filterByColumn(@Param("name") String name);
}
Spring JPA生成的H2表(相关)
Spring JPA Generated H2 Tables (relevant)
CREATE CACHED TABLE "PUBLIC"."TKBCOLUMN"(
"ID" VARCHAR(255) NOT NULL,
"NAME" VARCHAR(255),
...
)
CREATE CACHED TABLE "PUBLIC"."TKBDATA_TKBCOLUMN"(
"TKBDATA_ID" VARCHAR(255) NOT NULL,
"COLUMNS_ID" VARCHAR(255) NOT NULL
)
CREATE CACHED TABLE "PUBLIC"."TKBDATA"(
"ID" VARCHAR(255) NOT NULL,
...
)
在测试类开始时生成的表的相关内容
Relevant Content of tables which are generated at the start of the test class
Table: TKBDATA
ID
726004cf-5cab-4b1d-bb3f-466ba22622e9
Table: TKBDATA_TKBCOLUMN
TKBDATA_ID COLUMNS_ID
726004cf-5cab-4b1d-bb3f-466ba22622e9 7b4e4ea8-4ff9-4668-8882-67ff93b595ca
726004cf-5cab-4b1d-bb3f-466ba22622e9 d670e813-0466-48a8-be54-ee992cf28462
Table: TKBCOLUMN
ID DATAORDER NAME OWNERID
d670e813-0466-48a8-be54-ee992cf28462 0 column1 16e01046-9a84-4651-98d8-4e3e358212eb
7b4e4ea8-4ff9-4668-8882-67ff93b595ca 1 column2 16e01046-9a84-4651-98d8-4e3e358212eb
有关更多信息,您可以在这里找到github存储库:
https://github.com/fo0 /ScrumTool
For more informations you can find the github repository here:
https://github.com/fo0/ScrumTool
解决此问题的方法是使用本地查询,这是因为JPA的设计以及它如何与对象一起使用,这就是为什么我的用例恰好存在此问题.
The solution for this was to use a native query, because of the design of JPA and how it works with objects, thats why my use-case has exactly this problem.
推荐答案
-
select d from TKBData d JOIN d.columns c WHERE c.name = column1
的含义是Meaning of
select d from TKBData d JOIN d.columns c WHERE c.name = column1
is- 找到一个TKBData对象,该对象具有一个关联的
column
对象,该对象的name
是column1
- 一旦确定哪个TKBData具有至少一个
name
为column1
的column
对象,然后它将返回其所有相关的column
对象在JPA中进行控制. (请参阅我对另一个问题的回答问题).另一种方法是编写本机sql并返回自定义非实体对象 - 例如,您将
TKBDATA_1
与column1
和column2
关联,还将TKBDATA_2
与column3
关联. - 运行查询时,它将忽略
TKBDATA_2
并决定返回TKBDATA_1
,因为它具有至少一个column
对象且name
=column2
. 但是之后,您无法控制要为TKBDATA_1
返回的关联的column
对象,并且JPA将返回所有关联的列对象 - 如果不确定原因,请阅读有关休眠会话的信息.它如何提供内存中任何关联条目的唯一表示形式.它是其
dirty checking
和repeatable read
的基础
- Find a TKBData object where it has an associated
column
object for whichname
iscolumn1
- Once its decided which TKBData has at least one
column
object for whichname
iscolumn1
, then it will return all its associatedcolumn
objects which you don't have control over in JPA. ( see My answer to another question ). Alternative is to write native sql and return custom non entity objects - For example, you have
TKBDATA_1
withcolumn1
andcolumn2
associated, you also haveTKBDATA_2
withcolumn3
associated. - When you run your query, it will ignore
TKBDATA_2
and decides to returnTKBDATA_1
as it has atleast onecolumn
object withname
=column2
. But after that you don't have control over which associatedcolumn
objects to return forTKBDATA_1
and JPA will return all associated column objects - If you are not sure of the reason, read about hibernate session.How it provides unique presentation of any associated entry in memory. It is the foundation for its
dirty checking
andrepeatable read
- 找到一个TKBData对象,该对象具有一个关联的
-
如下更新您的
@OneToMany
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true) @Builder.Default @JoinTable(name = "TKBDATA_TKBCOLUMN", joinColumns = @JoinColumn(name = "TKBDATA_ID"), inverseJoinColumns = @JoinColumn(name = "COLUMNS_ID")) private Set<TKBColumn> columns = Sets.newHashSet();
-
关于JPA查询语言,我想从查询的角度考虑内存对象的集合.
When it comes to JPA query language, I would like to think in terms of query a collection of in-memory objects.
因此,现在尝试根据对象描述以下两个查询的含义.
So now try to describe the meaning of the following two queries in terms of objects.
select d from TKBData d LEFT JOIN d.columns c WHERE c.name = :name
vs
select d from TKBData d JOIN d.columns c WHERE c.name = :name
-
别忘了与sql不同,在sql中,您在这里选择了要选择TKBData对象并限制要返回的TKBData对象的任何列.
Don't forget unlike in sql where you are select any columns here you have said you want to select TKBData objects and restricting which TKBData objects to return.
因此,要获得与本机sql相同的结果,请使用第二个JPA查询
So to achieve the same result as of your native sql, use the second JPA query
注意:
即使您在sql查询中使用了左联接,它实际上也是内部联接sql查询,因为您还对该联接上最右边的表应用了
where
条件.Even though you used a left join in your sql query, it is effectively an inner join sql query because you also applied a
where
condition to the most right table on that join.这篇关于Spring JPA中包含一个filter子句的OneToMany问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
-
-