Spring JPA中包含一个filter子句的OneToMany问题 [英] problems with OneToMany including a filter clause in spring jpa

问题描述

当在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

    1. 找到一个TKBData对象，该对象具有一个关联的column对象，该对象的name是column1
    2. 一旦确定哪个TKBData具有至少一个name为column1的column对象，然后它将返回其所有相关的column对象在JPA中进行控制. (请参阅我对另一个问题的回答问题).另一种方法是编写本机sql并返回自定义非实体对象
    3. 例如，您将TKBDATA_1与column1和column2关联，还将TKBDATA_2与column3关联.
    4. 运行查询时，它将忽略TKBDATA_2并决定返回TKBDATA_1，因为它具有至少一个column对象且name = column2. 但是之后，您无法控制要为TKBDATA_1返回的关联的column对象，并且JPA将返回所有关联的列对象
    5. 如果不确定原因，请阅读有关休眠会话的信息.它如何提供内存中任何关联条目的唯一表示形式.它是其dirty checking和repeatable read
    的基础
    1. Find a TKBData object where it has an associated column object for which name is column1
    2. Once its decided which TKBData has at least one column object for which name is column1, then it will return all its associated column 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
    3. For example, you have TKBDATA_1 with column1 and column2 associated, you also have TKBDATA_2 with column3 associated.
    4. When you run your query, it will ignore TKBDATA_2 and decides to return TKBDATA_1 as it has atleast one column object with name= column2. But after that you don't have control over which associated column objects to return for TKBDATA_1 and JPA will return all associated column objects
    5. 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 and repeatable read

  • 如下更新您的@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问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！