JPA查询用于@JoinTable

huangapple go评论82阅读模式
英文:

JPA Query for @JoinTable

问题

场景:
我有两个实体,用户(User)和程序(Program)。

@Entity
@Table(name = "user")
public class UserEntity implements Serializable {

   @Id
   public Long id;

   public String firstName;

   public String email;

   @OneToOne(cascade = CascadeType.ALL)
   @JoinTable(
      name = "user_program",
      joinColumns = {
         @JoinColumn(name = "user_id", referencedColumnName = "id")  
      },
      inverseJoinColumns = {
         @JoinColumn(name = "program_id", referencedColumnName = "id")
      }
   )
   public ProgramEntity program;
}

JPQL:

SELECT
   Program.name,
   COUNT(user.id) AS user_count
FROM UserEntity AS user
INNER JOIN ProgramEntity AS program on ________ GROUP BY Program.name

我尝试获取每个程序中的用户数量,但由于JoinTable(中间表)不是实体,所以无法得到结果。有人能提供一个连接联接表的JPQL查询吗?

英文:

Scenario:
I have two entities User and Program

@Entity
@Table(name = "user")
public class UserEntity implements Serializable{

   @Id
   public Long id;

   public String firstName;

   public String email;

   @OneToOne(cascade = CascadeType.ALL)
   @JoinTable(
      name = "user_program",
      joinColumns = {
         @JoinColumn(name = "user_id", referencedColumnName = "id")  
      },
      inverseJoinColumns = {
         @JoinColumn(name = "program_id", referencedColumnName = "id")
      }
   )
   public ProgramEntity program;
}

JPQL:

SELECT
   Program.name,
   COUNT(user.id) AS user_count
FROM UserEntity AS user
INNER JOIN ProgramEntity AS program on ________ GROUP BY Program.name

I tried to get the number of users in each program but I couldn't get the result due to the JoinTable (intermediate table) is not an entity. Can anyone suggest a JPQ to connect the join table?

答案1

得分: 0

你可以使用实体对象进行连接,不必使用 'ON'。对于你的示例,

SELECT prg.name, COUNT(user.id) AS user_count FROM UserEntity AS user INNER JOIN user.program AS prg GROUP BY prg.name
英文:

You can join using the entity object, 'ON' is not necessary. For you example,

SELECT prg.name, COUNT(user.id) AS user_count FROM UserEntity AS user INNER JOIN user.program AS prg GROUP BY prg.name

huangapple
  • 本文由 发表于 2020年10月28日 01:20:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/64559682.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定