JPA如何加入这些实体 [英] JPA how to join these entities

问题描述

鉴于下面的JPA实体，我要获取具有至少一个成功状态的请求的所有借项.

Given the JPA entities below, I want to get all Debits which has a Request with at least one succesful status.

可能有许多具有相同debit_id和不同状态的请求

There can be many requests with the same debit_id and different statuses

我应该使用这样的东西还是有更好的做事方法

Should I use something like this or there is a better way of doing things

entityManager.createQuery(从借方d连接d.id中选择c，其中request.status =成功"

@Entity(name = "T_DEBIT")
public class Debit {
  public enum Status { NEW, OLD } 

  @Column(name = "STATUS", nullable = false, length = 20)
  @Enumerated(value = EnumType.STRING)
  private Status status;
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @ManyToOne(optional = false)
  @JoinColumn(name = "ACCOUNT_ID", updatable = false, nullable = false)
  private Account account;

}

其他实体是

@Entity(name = "T_REQUEST")
public class Request{  

  public enum Status { PENDING, FAILED, SUCCESFUL}  

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @ManyToOne(optional = false)
  @JoinColumn(name = "DEBIT_ID", updatable = false, nullable = false)
  private Debit debit;

  @Column(name = "STATUS", nullable = false, length = 20)
  @Enumerated(value = EnumType.STRING)
  private Status status;

 }

如果有任何遗漏，请发表评论，而不是关闭或否决该问题！

Please do comment if anything is missing instead of closing or downvoting the question!

推荐答案

基本上:

select d
from T_DEBIT d
where exists (
    select r
    from T_REQUEST r
    where 
    r.debit.id = d.id and
    r.status = SUCCESSFUL
)

检查JPQL中的枚举语法，我通常不对实体使用枚举，在此示例中可能是错误的.

Check for enum syntax inside JPQL, I do not usually use enums for entities and it could be wrong in this example.

作为样式问题，我将使用实体名称==类名称而不是实体名称==表名称.它使JPQL 不是 SQL更清晰

As a style issue, i would make entity name == class name instead of entity name == table name. It makes the fact that JPQL IS NOT SQL clearer

更新

Spring要求解决类似问题.解决这些问题的方法非常系统:

Spring asks for a solution to a similar problem. The way to work these things out is very systematic:

a)仅使用基本过滤器和以下表达式来重写您的问题:

a) Rewrite your problem, using only basic filters and the following expressions:

1. 存在一些……条件成立"
2. 所有……条件为真"

b)翻译:

1. 这种情况变成exists (select ... where condition)
2. 这种情况变成not exists (select ... where NOT condition)

1. This case becomes exists (select ... where condition)
2. This case becomes not exists (select ... where NOT condition)

在Spring的特定问题中，排除所有成功请求"，目标不是很明确.如果他/她的意思是在没有成功请求的情况下获得所有借方"，那么您可以这样做:

In Spring's particular question, "exclude all succesfull requests", the goal is not very clear. If he/she means "get all debits without successful requests", then you would do:

a)将问题改写为获得所有借方，以使对于所有关联请求，请求状态都不为成功". b)翻译为

a) Rewrite the question as "get all debits such that for all associated requests, request status is not SUCCESSFUL". b) Translate as

select d
from T_DEBIT d
where not exists (
    select r
    from T_REQUEST r
    where 
    -- This is the join condition, so it should not be negated
    r.debit.id = d.id and 
    -- This is the actual filtering condition, negate as this is a FOR ALL
    not (r.status != SUCCESSFUL)
)

然后您可以简化最后一个条件，得到:

Then you could simplify that last condition, getting:

select d
from T_DEBIT d
where not exists (
    select r
    from T_REQUEST r
    where 
    r.debit.id = d.id and
    r.status = SUCCESSFUL
)

这篇关于JPA如何加入这些实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！