java - 连接表时形成的 JPQL 查询不正确

Question

假设我有 2 个实体:应用和组织。它们是多对多相关的

组织:

@Entity
@Table(name = "organizations")
public class Organization
{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "organizationIdGenerator")
    private long id;  // primary key

    @AttributeOverride(name = "id", column = @Column(name = "organization_id", unique = true, updatable = false))
    @Embedded
    private OrganizationId organizationId;

    @AttributeOverride(name = "id", column = @Column(name = "application_id"))
    @CollectionTable(name = "organization_applications")
    @ElementCollection
    private Set<ApplicationId> applications = new HashSet<>();  // there is no explicit @Many-to-many connection between applications and organizations

    private String name;

    // other data
}

应用:

@Entity
@Table(name = "applications")
public class Application
{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "applicationIdGenerator")
    private long id; // primary key

    @AttributeOverride(name = "id", column = @Column(name = "application_id", unique = true, updatable = false))
    @Embedded
    private ApplicationId applicationId;

    private String label;

    // other data 
}

由于它们是多对多相关的，因此在关系数据库中我有以下表格:

• organizations
• applications
• organization_applications

表organization_applications使用以下 liquibase-migration 创建:

<createTable tableName="organization_applications">
  <column name="organization_id" type="bigint">
    <constraints nullable="false" foreignKeyName="organization_application_organization_id_fk"
                 referencedTableName="organizations" referencedColumnNames="id"/>
  </column>
  <column name="application_id" type="varchar(50)">
    <constraints nullable="false"/>
  </column>
</createTable>

<小时/>

我想要做的是在一个查询中获取特定组织的应用程序 ( List<Application> )

<小时/>

我可以通过获取特定组织的 applicationId(假设organizationId = 1)并将它们与 applications 连接起来来做到这一点查询如下:

select apps.application_id, apps.label 
    from organization_applications org_apps
    join applications apps 
        on (apps.application_id = org_apps.application_id and org_apps.organization_id = 1)

但是当我尝试使用 JPQL 查询来完成此操作时，如下所示:

SELECT new List(o.applications) FROM Organization o WHERE o.organizationId.id = 1

我收到以下查询，然后出现异常:

Hibernate: 
    /* SELECT
        new List(o.applications) 
    FROM
        Organization o 
    WHERE
        o.organizationId.id = :org_id */ select
            . as col_0_0_          // <-- problem here. why is there just a dot?
        from
            organizations organizati0_ 
        inner join
            organization_applications applicatio1_ 
                on organizati0_.id=applicatio1_.organization_id 
        where
            organizati0_.organization_id=?

SqlExceptionHelper   : SQL Error: 20000, SQLState: 42X01
SqlExceptionHelper   : Syntax error: Encountered "." at line 1, column 102.

似乎在这样的关系中，实体通过自定义 ID 字段(applicationId、organiationId)相关，hibernate 无法创建查询。

知道如何实现这个目标吗？

提前致谢!

<小时/>

更新:

ApplicationId 包含一些验证，如下所示:

public class ApplicationId
{
    private String id;

    // constructor and getters
}

OrganizationId 看起来完全一样

Answer 1

@Query("SELECT a FROM Application a WHERE a.applicationId IN " +
        "(SELECT apps FROM Organization o JOIN o.applications apps WHERE o.organizationId = :organization_id) ORDER BY label ASC")