如何使用联接定义 JPA 存储库查询 [英] How To Define a JPA Repository Query with a Join

问题描述

我想使用带有 @Query 注释的 Jpa 存储库进行 Join 查询.

I would like to make a Join query using Jpa repository with annotation @Query.

我有两张桌子:

 table user 
 with iduser,user_name 

和:

 table area 
 with idarea, area_name and iduser

原生查询是:

 SELECT
 u.user_name 
 FROM
  user as u 
  INNER JOIN area as a ON a.iduser = u.iduser
  WHERE
  a.idarea = 4

现在我有一个 Table Hibernate 实体用户和区域

Now I have a Table Hibernate entity User and Area

所以我尝试使用 UserRespository

So I tried with UserRespository

@Query(SELECT  u.userName FROM  User u 
  INNER JOIN Area a ON a.idUser = u.idUser
  WHERE
  a.idArea = :idArea)
List<User> findByIdarea(@Param("idArea") Long idArea);

日志说:

意外令牌:

有什么想法吗?

我的表实体

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

    private static final long serialVersionUID = 1L;

    private Long idUser;
    private String userName;

    @Id @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="iduser")
    public Long getIdUser() {
        return idUser;
    }

    public void setIdUser(Long idUser) {
        this.idUser = idUser;
    }

    @Column(name="user_name")
    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }
}

#AREA table

@Entity
@Table(name="area")
public class Area  implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long idArea;
    private String areaName;
    private Long idUser;

    @Id @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="idarea")
    public Long getIdArea() {
        return idArea;
    }

    public void setIdArea(Long idArea) {
        this.idArea = idArea;
    }

    @Column(name="area_name")
    public String getAreaName() {
        return areaName;
    }

    public void setAreaName(String areaName) {
        this.areaName = areaName;
    }

    @Column(name="iduser")
    public Long getIdUser() {
        return idUser;
    }

    public void setIdUser(Long idUser) {
        this.idUser = idUser;
    }       
}

推荐答案

您遇到此问题有两个原因.

You are experiencing this issue for two reasons.

• JPQL 查询无效.
• 您尚未在实体之间创建底层 JPQL 查询可以使用的关联.

在 JPQL 中执行连接时，您必须确保尝试连接的实体之间存在底层关联.在您的示例中，您缺少 User 和 Area 实体之间的关联.为了创建这个关联，我们必须在 User 类中添加一个区域字段并建立适当的 JPA 映射.我在下面附上了用户的来源.(请注意我将映射移动到字段)

When performing a join in JPQL you must ensure that an underlying association between the entities attempting to be joined exists. In your example, you are missing an association between the User and Area entities. In order to create this association we must add an Area field within the User class and establish the appropriate JPA Mapping. I have attached the source for User below. (Please note I moved the mappings to the fields)

User.java

@Entity
@Table(name="user")
public class User {

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

    @Column(name="user_name")
    private String userName;

    @OneToOne()
    @JoinColumn(name="idarea")
    private Area area;

    public Long getIdUser() {
        return idUser;
    }

    public void setIdUser(Long idUser) {
        this.idUser = idUser;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Area getArea() {
        return area;
    }

    public void setArea(Area area) {
        this.area = area;
    }
}

一旦建立了这种关系，您就可以在@Query 声明中引用该区域对象.@Query 注释中指定的查询必须遵循正确的语法，这意味着您应该省略 on 子句.请参阅以下内容:

Once this relationship is established you can reference the area object in your @Query declaration. The query specified in your @Query annotation must follow proper syntax, which means you should omit the on clause. See the following:

@Query("select u.userName from User u inner join u.area ar where ar.idArea = :idArea")

在查看您的问题时，我还建立了 User 和 Area 实体之间的双向关系.这里是Area实体建立双向关系的来源.

While looking over your question I also made the relationship between the User and Area entities bidirectional. Here is the source for the Area entity to establish the bidirectional relationship.

Area.java

@Entity
@Table(name = "area")
public class Area {

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

    @Column(name="area_name")
    private String areaName;

    @OneToOne(fetch=FetchType.LAZY, mappedBy="area")
    private User user;

    public Long getIdArea() {
        return idArea;
    }

    public void setIdArea(Long idArea) {
        this.idArea = idArea;
    }

    public String getAreaName() {
        return areaName;
    }

    public void setAreaName(String areaName) {
        this.areaName = areaName;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

这篇关于如何使用联接定义 JPA 存储库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！