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 per group
- hql query to retrieve top n from each group
- Using HQL with MySQL how can I order the result set before the group by 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.
