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

问题描述

我想使用带有注释@Query的Jpa存储库进行连接查询。

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

现在我有一个表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);

日志说：

意外令牌：

unexpected token:

请问任何想法？

我的桌子实体

#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类中添加一个Area字段并建立适当的JPA Mapping。我在下面附上了User的来源。 （请注意我将映射移到了字段中）

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")

在查看您的问题时，我还将用户和区域实体之间的关系设为双向。以下是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;
    }
}

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