使用Jparepository连接两个表的Hibernate查询

Question

大家好，我在使用jparepository使用@query连接两个表时遇到了一个小问题，但我得到了错误。请帮我做这个。

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

}

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

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

}

Answer 1

如果您想要获得用户的城市，可以执行以下操作：

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

请注意，使用的是实体名（就像在java类中一样），而不是数据库中的表名！您不必自己进行联接，因为您可以使用域模型的属性来访问相关数据