从两个表中获取数据

Question

我想在订单列表中获得用户和供应商的详细信息。

@ entity"公共类Orders {

@Id 
@GeneratedValue(strategy = GenerationType.UUID)
private String order_id;
private String amount;
private Date date;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "user_id",referencedColumnName="user_id", nullable = false)
private User user;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "vendor_id",referencedColumnName="vendor_id", nullable = false)
private Vendor vendor;
public String getOrder_id() {
return order_id;
}
public void setOrder_id(String order_id) {
this.order_id = order_id;
}
public String getAmount() {
return amount;
}
public void setAmount(String amount) {
this.amount = amount;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@JsonBackReference(value = "user")
@JsonIdentityReference(alwaysAsId = true)
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@JsonBackReference(value = "vendor")
@JsonIdentityReference(alwaysAsId = true)
public Vendor getVendor() {
return vendor;
}
public void setVendor(Vendor vendor) {
this.vendor = vendor;
}

}'

@Entity
public class User {
@Id 
@GeneratedValue(strategy = GenerationType.UUID)
private String user_id;
private String first_name;
private String last_name;
private String user_name;
private String password;
private Date dob;
private String email_id;
private String mobile;
private String location;

@JsonManagedReference(value = "user")
@OneToMany(mappedBy="user", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Orders> OrderList = new ArrayList<>();

public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public String getEmail_id() {
return email_id;
}
public void setEmail_id(String email_id) {
this.email_id = email_id;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}

@JsonIdentityReference(alwaysAsId = true)
public List<Orders> getOrderList() {
return OrderList;
}
public void setOrderList(List<Orders> orderList) {
OrderList = orderList;
}
@Override
public String toString() {
return "User [first_name=" + first_name + ", last_name=" + last_name + ", user_name=" + user_name
+ ", password=" + password + ", dob=" + dob + ", email_id=" + email_id + ", mobile=" + mobile
+ ", location=" + location + "]";
}



}

@Entity
public class Vendor {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
private String vendor_id;
private String v_name;
private String v_address;
private String v_email;
private String v_password;
private String v_mobile;
private String v_image;
private String package_detail;
private String price;
private String service_name;

@JsonManagedReference(value = "vendor")
@OneToMany(mappedBy="vendor",cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Orders> OrderList = new ArrayList<>();


@JsonIdentityReference(alwaysAsId = true)
public List<Orders> getOrderList() {
return OrderList;
}
public void setOrderList(List<Orders> orderList) {
OrderList = orderList;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getService_name() {
return service_name;
}
public void setService_name(String service_name) {
this.service_name = service_name;
}
public String getVendor_id() {
return vendor_id;
}
public void setVendor_id(String vendor_id) {
this.vendor_id = vendor_id;
}
public String getV_name() {
return v_name;
}
public void setV_name(String v_name) {
this.v_name = v_name;
}
public String getV_address() {
return v_address;
}
public void setV_address(String v_address) {
this.v_address = v_address;
}
public String getV_email() {
return v_email;
}
public void setV_email(String v_email) {
this.v_email = v_email;
}
public String getV_password() {
return v_password;
}
public void setV_password(String v_password) {
this.v_password = v_password;
}
public String getV_mobile() {
return v_mobile;
}
public void setV_mobile(String v_mobile) {
this.v_mobile = v_mobile;
}
public String getV_image() {
return v_image;
}
public void setV_image(String v_image) {
this.v_image = v_image;
}
public String getPackage_detail() {
return package_detail;
}
public void setPackage_detail(String package_detail) {
this.package_detail = package_detail;
}

@Override
public String toString() {
return "Vendor [vendor_id=" + vendor_id + ", v_name=" + v_name + ", v_address=" + v_address + ", v_email="
+ v_email + ", v_password=" + v_password + ", v_mobile=" + v_mobile + ", v_image=" + v_image
+ ", package_detail=" + package_detail + ", price=" + price + ", service_name=" + service_name + "]";
}


}

@Query(value = "select od.* from orders od join vendor ve on(od.vendor_id=ve.vendor_id) where od.vendor_id=?",nativeQuery = true)
public List<Orders> findOrderByVendorId(String vendor_id);

type here

我得到这个作为答案

{
"order_id": "01221903-86ba-41b9-a8b7-0c9498b6441a",
"amount": "150000",
"date": "2023-04-01"
},

和

{
"order_id": "01221903-86ba-41b9-a8b7-0c9498b6441a",
"amount": "150000",
"date": "2023-04-01"
"user_id":"as3454r344r",
"user_name":"xyz",
"v_name":"abc"
},

Answer 1

您应该创建一个自定义响应类，并使用jpql来检索数据。

那边有一个例子;

@Query(value = "select new <your_package_path>.CustomOrderResponse(o.order_id,o.amount,o.date,u.user_id,u.user_name,v.v_name) " +
"from Orders o, User u, Vendor v " +
"where o.User.user_id = u.user_id "+
"and o.Vendor.vendor_id = v.vendor_id"+
"and o.vendor_id=?")
public List<CustomOrderResponse> findOrderByVendorId(String vendor_id);