Hibernate Query to join two table using Jpareposit

Question

Hi all i have a small issue with joining two tables using jparepository using @query but i am getting error. please help me with this.

UserAddress.java

package com.surya_spring.example.Model;

import java.io.Serializable;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;

@Entity
@Table(name = "user_address")
//@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class UserAddress implements Serializable {


/**
 * 
 */
private static final long serialVersionUID = -3570928575182329616L;


/*@ManyToMany(cascade = {CascadeType.ALL},fetch=FetchType.EAGER,mappedBy = "userAddress",targetEntity=UserData.class)*/

@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="user_id")
private UserData userdata;


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

@Column(name = "dr_no")
@NotNull
private String doorNo;


@Column(name = "strt_name")
@NotNull
private String streetName;

@Column(name = "city")
@NotNull
private String city;

@Column(name = "country")
@NotNull
private String country;



/*@OneToOne(cascade=CascadeType.ALL)
@Column(name="user_id")*/
public UserData getUserdata() {
    return userdata;
}

public void setUserdata(UserData userdata) {
    this.userdata = userdata;
}

public Long getAddrid() {
    return addrid;
}

public void setAddrid(Long addrid) {
    this.addrid = addrid;
}

public String getDoorNo() {
    return doorNo;
}

public void setDoorNo(String doorNo) {
    this.doorNo = doorNo;
}

public String getStreetName() {
    return streetName;
}

public void setStreetName(String streetName) {
    this.streetName = streetName;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public String getCountry() {
    return country;
}

public void setCountry(String country) {
    this.country = country;
}

}

UserData.java

package com.surya_spring.example.Model;
import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.NonNull;

@Entity
@Table(name = "user_data")
public class UserData implements Serializable{
/**
 * Serialization ID
 */
private static final long serialVersionUID = 8133309714576433031L;

/*@ManyToMany(targetEntity=UserAddress.class ,cascade= {CascadeType.ALL },fetch=FetchType.EAGER)
@JoinTable(name="userdata",joinColumns= @JoinColumn(name="userid"),inverseJoinColumns = @JoinColumn(name="userid"))
*/


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

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

@Column(name = "user_email")
@NonNull
private String userEmail;

public Long getUserId() {
    return userId;
}

public void setUserId(Long userId) {
    this.userId = userId;
}

public String getUserName() {
    return userName;
}

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

public String getUserEmail() {
    return userEmail;
}

public void setUserEmail(String userEmail) {
    this.userEmail = userEmail;
}
}

Repository:

package com.surya_spring.example.Repository;


import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.surya_spring.example.Model.UserData;

public interface UserDataRepository extends JpaRepository<UserData, Long>{

@Query(" FROM UserData where userId= :id")
public List<UserData> findBySearchTerm(@Param("id") Long id);

}

any one let me know the query to join this both the table to get city name from user_address where user_id=? joining user_data table

Answer 1

If you want to get the city for a user you can do:

@Query("SELECT ua.city FROM UserAddress ua WHERE ua.userdata.userId = ?1")
String findCityByUserId(Long userId);

Note that your entity names are used (like in your java classes) and not the table names in database! You do not have to do the join by yourself as you can use the properties of your domain models to access the related data