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;
}
}
This question is related to
java
jpa
orm
repository
jpql
You are experiencing this issue for two reasons.
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;
}
}
Source: Stackoverflow.com