How To Define a JPA Repository Query with a Join

Question

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

I have two tables:

 table user 
 with iduser,user_name 

and:

 table area 
 with idarea, area_name and iduser

The native query is:

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

Now I have a Table Hibernate entity User and Area

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

The Log says:

unexpected token:

Any Idea, please?

My table Entity

#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;
    }       
}

Answer 1

You are experiencing this issue for two reasons.

• The JPQL Query is not valid.
• You have not created an association between your entities that the underlying JPQL query can utilize.

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;
    }
}

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

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;
    }
}