无法将每个组最大N的SQL转换为HQL

问题描述

我在Oracle中有两个表，即 product 和 product_image 。由于名称暗示它们具有产品至 product_image的一对多关系。

product 实体：

  @Entity 
 @Table（name =PRODUCT，catalog =，schema =WAGAFASHIONDB）
 public class Product implements java.io.Serializable 
 {
 private static最终的长串serialVersionUID = 1L; 
 @Id 
 @Basic（可选= false）
 @Column（name =PROD_ID，nullable = false，precision = 35，scale = 0）
 @SequenceGenerator（name =productIdSequence，sequenceName =PRODUCT_SEQ，allocationSize = 1，initialValue = 1）
 @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =productIdSequence）
 private long prodId; 
 @Column（name =PROD_NAME，length = 50）
 private String prodName; 
 @Column（name =PROD_CODE，length = 50）
 private String prodCode; 
 @Lob 
 @Column（name =PROD_DESC）
 private String prodDesc; 
 @Basic（可选= false）
 @Column（name =MARKET_PRICE，nullable = true，precision = 35，scale = 2）
 private BigDecimal marketPrice; 
 @Basic（可选= false）
 @Column（name =SALE_PRICE，nullable = true，precision = 35，scale = 2）
 private BigDecimal salePrice; 
 @Column（name =PROD_FEATURED）
 private short prodFeatured; 
 @Column（name =EXPRESS_DELIVERY）
私人Short expressDelivery; 
 @Basic（可选= false）
 @Column（name =PROD_WEIGHT，nullable = true，precision = 35，scale = 2）
 private BigDecimal prodWeight; 
 @Column（name =PROD_OCCASSION，length = 50）
 private String prodOccassion; 
 @Basic（可选= false）
 @Column（name =QTY_AVAILABLE，nullable = true）
私人BigInteger qtyAvailable; 
 @Column（name =LATEST）
私人短期最新; 
 @Column（name =VISIBLE）
 private short可见; 
 @JoinTable（name =PRODUCT_SIZE，joinColumns = {
 @JoinColumn（name =PROD_ID，referencedColumnName =PROD_ID）}，inverseJoinColumns = {
 @JoinColumn（name = SIZE_ID，referencedColumnName =SIZE_ID）}）
 @ManyToMany（fetch = FetchType.LAZY）
 private Set< SizeTable> sizeTableSet; 
 @ManyToMany（mappedBy =productSet，fetch = FetchType.LAZY）
 private Set< Color> colourSet; 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< Measurement> measurementSet; 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< Wish> wishSet; 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< Cart> cartSet; 
 
 
 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< ProductImage> productImageSet; //< -------- 
 
 
 
 @OneToMany（cascade = CascadeType.ALL，mappedBy =product，fetch = FetchType.LAZY）
私人套餐< OrderItem> orderItemSet; 
 @JoinColumn（name =SUB_CAT_ID，referencedColumnName =SUB_CAT_ID）
 @ManyToOne（fetch = FetchType.LAZY）
私人子类别subCatId; 
 @JoinColumn（name =FABRIC_ID，referencedColumnName =FABRIC_ID，nullable = false）
 @ManyToOne（可选= false，fetch = FetchType.LAZY）
私有结构fabricId; 
 @JoinColumn（name =BRAND_ID，referencedColumnName =BRAND_ID）
 @ManyToOne（fetch = FetchType.LAZY）
私人品牌brandId; 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< Inquiry> inquirySet; 
 @OneToMany（mappedBy =prodId，fetch = FetchType.LAZY）
 private Set< Rating> ratingSet; 
 
  

ProductImage 实体：

  @Entity 
 @Table（name =PRODUCT_IMAGE，catalog =，schema =WAGAFASHIONDB ）
 public class ProductImage implements java.io.Serializable 
 {
 private static final long serialVersionUID = 1L; 
 @Id 
 @Basic（可选= false）
 @Column（name =PROD_IMAGE_ID，nullable = false，precision = 35，scale = 0）
 @SequenceGenerator（name =productIdSequence，sequenceName =PRODUCT_IMAGE_SEQ，allocationSize = 1，initialValue = 1）
 @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =productIdSequence）
 private long prodImageId; 
 @Lob 
 @Column（name =PROD_IMAGE）
 private String prodImage; 
 @JoinColumn（name =PROD_ID，referencedColumnName =PROD_ID）
 
 
 
 @ManyToOne（fetch = FetchType.LAZY）
 private Product PRODID; //< ----------- 
} 
  

我需要为 ProductImage 的行c>即每组最多的N个数据库。

Oracle本地SQL可以按如下方式构建。

  SELECT pi.prod_image_id，pi.prod_id，pi.prod_image 
 FROM product_image pi 
 INNER JOIN（选择max（pi.prod_image_id）AS prod_image_id，pi.prod_id FROM product_image pi GROUP BY pi.prod_id）prod_image 
 ON pi.prod_image_id = prod_image.prod_image_id 
  

完全按照预期，但我无法将此SQL转换为HQL。尝试直接在 createQuery（）方法中作为HQL执行，会导致 QuerySyntexException ，意外的令牌位于第一个圆括号处。您能否给我一个提示将这个SQL转换为HQL？

/ p>

像下面这样的问题还没有回答，因为它实际上意味着：

我认为，通过HQL无法在Hibernate中实现这一点。我可能需要使用某些DTO类将此SQL分解为适当的两个或多个HQL语句。如果可以通过像上面的SQL这样的单个HQL语句，那么请不要忘记在这里回答，因为我在太我的应用程序的许多地方都有这样的要求。

解决方案

请看看这个：

  select 
 pi.prodImageId，
 pi.prodId 
 from 
 ProductImage pi 
其中
 pi.prodImageId（
 select max（pis.prodImageId） 
 from Product p 
内部连接p.productImageSet pis 
 group by p.prodId 
）
  

即转换为：

  select 
 productima0_.prodImageId as col_0_0_，
 productima0_.prodId_prodId作为col_1_0_，
 product1_.prodId作为prodId18_，
 product1_.name作为name18_ 
 from 
 ProductImage productima0_ 
内部连接
产品product1_ on productima0_.prodId_prodId = product1_.prodId 
其中
 productima0_.prodImag eId in（
 select max（productima3_.prodImageId）
 from 
产品product2_内部连接ProductImage productima3_上的product2_.prodId = productima3_.prodId_prodId 
 group by product2_.prodId 
 ）
  

这就是我想要的吗？

I have two tables in Oracle namely product and product_image. As the names imply they have a one-to-many relationship from product to product_image.

The product entity:

@Entity
@Table(name = "PRODUCT", catalog = "", schema = "WAGAFASHIONDB")
public class Product  implements java.io.Serializable
{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "PROD_ID", nullable = false, precision = 35, scale = 0)
    @SequenceGenerator(name = "productIdSequence", sequenceName = "PRODUCT_SEQ", allocationSize=1, initialValue=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productIdSequence")
    private Long prodId;
    @Column(name = "PROD_NAME", length = 50)
    private String prodName;
    @Column(name = "PROD_CODE", length = 50)
    private String prodCode;
    @Lob
    @Column(name = "PROD_DESC")
    private String prodDesc;
    @Basic(optional = false)
    @Column(name = "MARKET_PRICE", nullable = true, precision = 35, scale = 2)
    private BigDecimal marketPrice;
    @Basic(optional = false)
    @Column(name = "SALE_PRICE", nullable = true, precision = 35, scale = 2)
    private BigDecimal salePrice;
    @Column(name = "PROD_FEATURED")
    private Short prodFeatured;
    @Column(name = "EXPRESS_DELIVERY")
    private Short expressDelivery;
    @Basic(optional = false)
    @Column(name = "PROD_WEIGHT", nullable = true, precision = 35, scale = 2)
    private BigDecimal prodWeight;
    @Column(name = "PROD_OCCASSION", length = 50)
    private String prodOccassion;
    @Basic(optional = false)
    @Column(name = "QTY_AVAILABLE", nullable = true)
    private BigInteger qtyAvailable;
    @Column(name = "LATEST")
    private Short latest;
    @Column(name = "VISIBLE")
    private Short visible;
    @JoinTable(name = "PRODUCT_SIZE", joinColumns = {
        @JoinColumn(name = "PROD_ID", referencedColumnName = "PROD_ID")}, inverseJoinColumns = {
        @JoinColumn(name = "SIZE_ID", referencedColumnName = "SIZE_ID")})
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<SizeTable> sizeTableSet;
    @ManyToMany(mappedBy = "productSet", fetch = FetchType.LAZY)
    private Set<Colour> colourSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Measurement> measurementSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Wish> wishSet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Cart> cartSet;



    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<ProductImage> productImageSet;             //<--------



    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.LAZY)
    private Set<OrderItem> orderItemSet;
    @JoinColumn(name = "SUB_CAT_ID", referencedColumnName = "SUB_CAT_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    private Subcategory subCatId;
    @JoinColumn(name = "FABRIC_ID", referencedColumnName = "FABRIC_ID", nullable = false)
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Fabric fabricId;
    @JoinColumn(name = "BRAND_ID", referencedColumnName = "BRAND_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    private Brand brandId;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Inquiry> inquirySet;
    @OneToMany(mappedBy = "prodId", fetch = FetchType.LAZY)
    private Set<Rating> ratingSet;
}

The ProductImage entity:

@Entity
@Table(name = "PRODUCT_IMAGE", catalog = "", schema = "WAGAFASHIONDB")
public class ProductImage  implements java.io.Serializable
{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "PROD_IMAGE_ID", nullable = false, precision = 35, scale = 0)
    @SequenceGenerator(name = "productIdSequence", sequenceName = "PRODUCT_IMAGE_SEQ", allocationSize=1, initialValue=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productIdSequence")
    private Long prodImageId;
    @Lob
    @Column(name = "PROD_IMAGE")
    private String prodImage;
    @JoinColumn(name = "PROD_ID", referencedColumnName = "PROD_ID")



    @ManyToOne(fetch = FetchType.LAZY)
    private Product prodId;           //<--------------
}

I need to fetch only rows for the maximum prodId for each group of rows (actually each group of products) in ProductImage i.e the greatest N per group.

The Oracle native SQL can be constructed as follows.

SELECT pi.prod_image_id, pi.prod_id, pi.prod_image
FROM product_image pi
INNER JOIN (select max(pi.prod_image_id) AS prod_image_id, pi.prod_id FROM product_image pi GROUP BY pi.prod_id) prod_image
ON pi.prod_image_id=prod_image.prod_image_id

This works exactly as intended but I couldn't convert this SQL to HQL. Trying to execute directly as HQL in the createQuery() method causes the QuerySyntexException, unexpected token at the very first opening parentheses. Could you please given me a hint to convert this SQL to HQL?

---

EDIT:

Some questions like the following haven't yet answered to accomplish this as it actually means:

• Best way to use hibernate for complex queries like top n pergroup
• hql query to retrieve top n from each group
• Using HQL with MySQL how can I order the result set before the groupby so the right record is picked?

I think, there is no way to achieve this in Hibernate through HQL. I may need to break this SQL to appropriate two or more HQL statements using some DTO classes. If it is possible through a single HQL statement like the SQL above, then please don't forget answer here, since I have such requirements at too many places in my application.

解决方案

Take a look at this:

select
    pi.prodImageId,
    pi.prodId
from
    ProductImage pi
where
    pi.prodImageId in (
    select max(pis.prodImageId)
    from Product p
        inner join p.productImageSet pis
    group by p.prodId
)

which translates to:

select
   productima0_.prodImageId as col_0_0_,
   productima0_.prodId_prodId as col_1_0_,
   product1_.prodId as prodId18_,
   product1_.name as name18_
from
   ProductImage productima0_
inner join
   Product product1_ on productima0_.prodId_prodId=product1_.prodId
where
   productima0_.prodImageId in (
      select max(productima3_.prodImageId)
      from
         Product product2_ inner join ProductImage productima3_ on product2_.prodId=productima3_.prodId_prodId
      group by product2_.prodId
   )

which is what you want i guess?

这篇关于无法将每个组最大N的SQL转换为HQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！