无法将每个组最大N的SQL转换为HQL [英] Unable to convert SQL which is meant for greatest N per group to 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;
@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; //< -----------
}
我需要为 Oracle本地SQL可以按如下方式构建。 完全按照预期,但我无法将此SQL转换为HQL。尝试直接在 像下面这样的问题还没有回答,因为它实际上意味着: 我认为,通过HQL无法在Hibernate中实现这一点。我可能需要使用某些DTO类将此SQL分解为适当的两个或多个HQL语句。如果可以通过像上面的SQL这样的单个HQL语句,那么请不要忘记在这里回答,因为我在太我的应用程序的许多地方都有这样的要求。 请看看这个: 即转换为: 这就是我想要的吗? I have two tables in Oracle namely The The I need to fetch only rows for the maximum The Oracle native SQL can be constructed as follows. This works exactly as intended but I couldn't convert this SQL to HQL. Trying to execute directly as HQL in the EDIT: Some questions like the following haven't yet answered to accomplish this as it actually means: 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: which translates to: which is what you want i guess? 这篇关于无法将每个组最大N的SQL转换为HQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! ProductImage $ c $中的每组行(实际上是每组产品)获取最大
prodId
的行c>即每组最多的N个数据库。
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
createQuery()
方法中作为HQL执行,会导致 QuerySyntexException
,意外的令牌位于第一个圆括号处。您能否给我一个提示将这个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
)
product
and product_image
. As the names imply they have a one-to-many relationship from product
to product_image.
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;
}
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; //<--------------
}
prodId
for each group of rows (actually each group of products) in ProductImage
i.e the greatest N per group.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
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?
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
)
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
)