自定义查询,用于从Spring Data Jpa中的多个表中获取数据 [英] Custom Query for fetching data from multiple tables in spring Data Jpa
问题描述
实体正在关注
产品表
@Entity
public class Product implements Serializable {
/*@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;*/
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@NotNull(message = "Product name must not be null")
@NotEmpty
private String name;
@ManyToOne
@JoinColumn(name="category_id")
private Category category;
@ManyToMany(mappedBy = "productlist")
private List<OrderDetail> orderDetail =new ArrayList<OrderDetail>();
//getters setter
OrderDetail表
@Entity
public class OrderDetail {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@ManyToOne
@JoinColumn(name="purchased_By")
private user PurchasedBy;
@ManyToMany
private Set<Product> productlist = new HashSet<Product>();
这些实体生成的表名为"order_detail_productlist" 以及如下order_detail_id和productlist_id的字段
These entities generating table named as 'order_detail_productlist' and fields as following order_detail_id and productlist_id
我正在mysql编辑器中运行以下查询,并且正在运行
I am running following query in mysql editor and that is working
select u.id, r.name from order_detail u inner join order_detail_productlist ur on(u.id=ur.order_detail_id) inner join product r on(ur.productlist_id=r.id) where u.id="?"
但是当我在带有@Query注释的spring存储库中运行时,这给了我异常.我尝试根据实体将Order_detail的名称更改为OrderDetail,但是在两种情况下都是相同的例外.
but when i run in spring repository with @Query annotation that is giving me exception. I have tried to change name of Order_detail to OrderDetail according to entities but same exception in both case.
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [select r.name from com.example.Domain.OrderDetail u inner join order_detail_productlist ur on(u.id=ur.order_detail_id) inner join Product r on(ur.productlist_id=r.id) where u.id= :id ]
我想要什么. 我正在尝试以这种方式使用.
what i want . i am trying to use in this way .
public final static String product_ordered ="select r.name from OrderDetail u inner join order_detail_productlist ur " +
"on(u.id=ur.order_detail_id) inner join Product r" +
" on(ur.productlist_id=r.id)" +
" where u.id= :id ";
@Query(product_ordered)
public List<Product> findById(@Param("id") int id);
我想从多个表中获取数据,例如订单等产品.
i want to get data from multiple tables , like products that are orderes etc .
推荐答案
您的查询不是有效的HQL查询,冬眠理解.您可以使用本机SQL查询,但是使用HQL可以轻松实现上述用例.在此之前,让我们为ManytoMany关联使用正确的注释映射:
Your query is not a valid HQL query, which hiberate understands. You can use a native SQL query, but the use case mentioned can be easily achieved with HQL. Before that, lets use the proper annotations mappings for ManytoMany association:
@Entity
@Table(name = "order_detail")
public class OrderDetail {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@ManyToOne
@JoinColumn(name="purchased_By")
private user PurchasedBy;
@ManyToMany
@JoinTable(
name="order_detail_productlist",
joinColumns=@JoinColumn(name="order_detail_id", referencedColumnName="id"),
inverseJoinColumns=@JoinColumn(name="productlist_id", referencedColumnName="id"))
private Set<Product> productlist = new HashSet<Product>();
产品:
@Entity
@Table(name ="product")
public class Product implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@NotNull(message = "Product name must not be null")
@NotEmpty
@Column(name = "name", nullable = false)
private String name;
@ManyToOne
@JoinColumn(name="category_id")
private Category category;
@ManyToMany(mappedBy = "productlist")
private List<OrderDetail> orderDetail =new ArrayList<OrderDetail>();
然后查询:
public final static String product_ordered ="Select p from Product p Join p.orderDetail od Where od.id = :id";
@Query(product_ordered)
public List<Product> findById(@Param("id") int id);
此处是从JPA开始的初学者友好资源
Here is a beginner friendly resource to start with JPA
这篇关于自定义查询,用于从Spring Data Jpa中的多个表中获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!