使用Spring-Data Repositories和JPQL Query的JPA Discriminator

I have two entities in MySQL as below. The primary key of nnm_tran is a composite of id and source. The primary key of bargains is actually a foreign key link to the nnm_tran table
使用Spring-Data Repositories和JPQL Query的JPA Discriminator

我在MySQL中有两个实体,如下所示。 nnm_tran的主键是id和source的组合。讨价还价的主要关键是实际上是nnm_tran表的外键链接

I'm trying to use JPA inheritance to represent these.

我正在尝试使用JPA继承来表示这些。

nnm_tran entity

@Entity
@Table(name = "nnm_tran")
@IdClass(CommonTransactionKey.class)
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "bargain_flag", discriminatorType = DiscriminatorType.CHAR)
@DiscriminatorValue("N")
public class CommonTransaction {

    @Id
    @Column(name = "id", nullable = false)
    private String transactionId;

    @Column(name = "plan_number", nullable = false)
    private String planNumber;

    @Column(name = "tran_date")
    private LocalDateTime transactionDatetime;

    @Column(name = "bargain_flag")
    private String bargainFlag;
    ...
}

bargains entity

@Data
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "bargains")
@DiscriminatorValue("B")
@PrimaryKeyJoinColumns({ @PrimaryKeyJoinColumn(name = "nnm_tran_id", referencedColumnName = "id"), @PrimaryKeyJoinColumn(name = "nnm_tran_source", referencedColumnName = "source") })
public class Bargain extends CommonTransaction implements Serializable {

    @Column(name = "unit_price")
    private BigDecimal unitPrice;

    @Column(name = "client_price")
    private BigDecimal clientPrice;
    ...
}

I think so far this is all hooked up correctly. My problem comes when I attach a spring-data repository with a custom query.

我认为到目前为止这一切都是正确的。当我附加一个带有自定义查询的spring-data存储库时,我的问题出现了。

Repository

public interface CommonTransactionRepository extends CrudRepository<CommonTransaction, CommonTransactionKey> {

    @Query("select t from CommonTransaction t left join IoPlan p ON t.planNumber = p.planNumber "
        + "where (p.planNumber is NULL or p.planNumber = '') "
        + "and t.transactionDatetime between ?1 and ?2 "
        + "and t.cancelled = false")
    public Iterable<CommonTransaction> findOrphanedTransactionsByTranDate(LocalDateTime fromDate, LocalDateTime toDate);
   ...
}

When this gets proxied and the method is executed it generates the SQL statement
SELECT DISTINCT nnm_tran.bargain_flag FROM nnm_tran t1 LEFT OUTER JOIN io_plan t0 ON (t1.plan_number = t0.plan_number) WHERE ((((t0.plan_number IS NULL) OR (t0.plan_number = ?)) AND (t1.tran_date BETWEEN ? AND ?)) AND (t1.CANCELLED = ?))

当它被代理并且该方法被执行时,它生成SQL语句SELECT DISTINCT nnm_tran.bargain_flag FROM nnm_tran t1 LEFT OUTER JOIN io_plan t0 ON(t1.plan_number = t0.plan_number)WHERE((((t0.plan_number IS NULL)OR( t0.plan_number =?))AND(t1.tran_date BETWEEN?AND?))AND(t1.CANCELLED =?))

The issue with this is that the nnm_tran table is aliased to t1 but the discriminator column is referencing the full table name nnm_tran.bargain_flag The result is a lovely

这个问题是nnm_tran表别名为t1但是鉴别器列引用了完整的表名nnm_tran.bargain_flag结果很可爱

UnitOfWork(17171249)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'nnm_tran.bargain_flag' in 'field list'

Question here is, am I doing something wrong or is this a bug in spring-data and/or eclipselink?

这里的问题是,我做错了什么,或者这是spring-data和/或eclipselink中的错误?

Versions: spring-data 1.7.2, Eclipselink 2.5.2, MySQL 5.6.28

版本:spring-data 1.7.2,Eclipselink 2.5.2,MySQL 5.6.28

1 个解决方案

#1

Using @manish's sample app as a starting point I started layering back on the complexity that was missing and quickly stumbled across the thing causing the rogue SQL. It was down to the join I had performed in the JPQL

使用@manish的示例应用程序作为起点,我开始重新分析缺少的复杂性,并迅速发现导致流氓SQL的事情。这归结于我在JPQL中执行的连接

NOTE: If you've come here from the future then ignore the remainder of this answer and instead use @Chris's comment instead.

注意:如果你从未来来到这里,那么忽略这个答案的其余部分,而是使用@Chris的评论。

Most of the time I don't need to look at or even think about the IoPlan table that can be seen in the @Query

大多数时候我不需要查看甚至考虑可以在@Query中看到的IoPlan表

@Query("select t from CommonTransaction t left join IoPlan p ON t.planNumber = p.planNumber "
    + "where (p.planNumber is NULL or p.planNumber = '') "
    + "and t.transactionDatetime between ?1 and ?2 "
    + "and t.cancelled = false")

and so this table is not a part of the CommonTransaction entity as a field. Even the result of this query doesn't really care because it's looking only as a one off for CommonTransaction with no associated join in the IoPlan table.

所以这个表不是CommonTransaction实体的一部分作为字段。即使是这个查询的结果也没有真正关心,因为它只是作为CommonTransaction的一次性,在IoPlan表中没有关联的连接。

When I added the join back in to the sample app from @manish it all broke in the same way my app has in EclipseLink, but broke in a different way for Hibernate. Hibernate requires a field for you to join with, which if you ask me defeats the purpose of writing the join in the @Query. In fact in Hibernate you have to define the join purely in JPA so you might as well then use dot notation to access it in the JPQL.

当我将连接添加回来自@manish的示例应用程序时,它的所有内容都与我的应用程序在EclipseLink中的方式相同,但是以不同的方式打破了Hibernate。 Hibernate需要一个字段供您加入,如果您要求我打败在@Query中编写连接的目的。实际上在Hibernate中你必须纯粹在JPA中定义连接,所以你也可以使用点符号在JPQL中访问它。

Anyway, going along with this idea I tried adding a dummy field to hold an IoPlan in my CommonTransaction entity and it almost worked. It defaulted some of the join logic but it was closer

无论如何,继续这个想法,我尝试添加一个虚拟字段来保存我的CommonTransaction实体中的IoPlan,它几乎起作用。它默认了一些连接逻辑,但它更接近

SELECT DISTINCT t1.bargain_flag FROM nnm_tran t1 LEFT OUTER JOIN io_plan t0 ON ((t0.ID = t1.IOPLAN_ID) AND (t1.plan_number = t0.plan_number)) WHERE ((((t0.plan_number IS NULL) OR (t0.plan_number = ?)) AND (t1.tran_date BETWEEN ? AND ?)) AND (t1.CANCELLED = ?))

In this case t1.IOPLAN_ID and t0.ID don't exist. So I ended up defining the entire join in my CommonTransaction entity

在这种情况下,t1.IOPLAN_ID和t0.ID不存在。所以我最终在我的CommonTransaction实体中定义了整个连接

    @OneToOne
    @JoinColumn(insertable = false, updatable = false, name = "plan_number", referencedColumnName = "plan_number")
    private IoPlan ioPlan;

and voila, it started working. It's not pretty and now I have a redundant join condition

瞧,它开始工作了。它不漂亮,现在我有一个冗余连接条件

LEFT OUTER JOIN io_plan t1 
ON ((t1.plan_number = t0.plan_number) AND (t0.plan_number = t1.plan_number))

but I can fix that. It's still annoying that I have to define a field for it whatsoever, I don't actually want or need it there, not to mention that the result from this query is returning CommonTransaction entities that have no IoPlan so the field will be permanently null.

但我可以解决这个问题。仍然很烦人,我必须为它定义一个字段,我实际上并不想要或不需要它,更不用说这个查询的结果是返回没有IoPlan的CommonTransaction实体,因此该字段将永久为null。

#1