自定义查询，用于从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屋！