mark_dj mark_dj - 6 months ago 9
Java Question

Hibernate query causing lots extra unnecassery queries

I am developing a auction site. The problem lies in 3 entities i use:


  • Product (has zero or many ProductBid)

  • ProductBid (has zero or one ProductBidRejection)

  • ProductBidRejection



I use a hibernate query to get the bids:

select pb from ProductBid pb left join pb.rejection pbr where pbr is null and pb.product = :product order by pb.amount desc


This generates this query (via console):

select
productbid0_.id as id4_,
productbid0_.amount as amount4_,
productbid0_.bid_by as bid4_4_,
productbid0_.date as date4_,
productbid0_.product_id as product5_4_
from
product_bids productbid0_
left outer join
product_bid_rejections productbid1_
on productbid0_.id=productbid1_.product_bid_id
where
(
productbid1_.id is null
)
and productbid0_.product_id=?


But for each bid it gets it also generates:

select
productbid0_.id as id3_1_,
productbid0_.date_rejected as date2_3_1_,
productbid0_.product_bid_id as product4_3_1_,
productbid0_.reason as reason3_1_,
productbid0_.rejected_by as rejected5_3_1_,
productbid1_.id as id4_0_,
productbid1_.amount as amount4_0_,
productbid1_.bid_by as bid4_4_0_,
productbid1_.date as date4_0_,
productbid1_.product_id as product5_4_0_
from
product_bid_rejections productbid0_
inner join
product_bids productbid1_
on productbid0_.product_bid_id=productbid1_.id
where
productbid0_.product_bid_id=?


These are my entities:

ProductBid


@Entity
@Table(name = "product_bids")
public class ProductBid
{
@Column(name = "id", nullable = false)
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@JoinColumn(name = "product_id", nullable = false)
@Index(name="product")
@ManyToOne(fetch = FetchType.LAZY)
private Product product;

@Column(name = "amount", nullable = false)
private BigDecimal amount;

@JoinColumn(name = "bid_by", nullable = false)
@Index(name="bidBy")
@ManyToOne(fetch = FetchType.LAZY)
@Fetch(FetchMode.JOIN)
private User bidBy;

@Column(name = "date", nullable = false)
@Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
private DateTime date;

@OneToOne(fetch = FetchType.LAZY, mappedBy = "productBid")
private ProductBidRejection rejection;
}


ProductBidRejection

@Entity
@Table(name = "product_bid_rejections")
public class ProductBidRejection
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
private long id;

@Column(name = "reason", nullable = false, columnDefinition = "TEXT")
private String reason;

@Column(name = "date_rejected", nullable = false)
@Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
private DateTime dateRejected;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "rejected_by", nullable = false)
private User rejectedBy;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "product_bid_id", nullable = false)
@Fetch(FetchMode.JOIN)
private ProductBid productBid;


}

Answer

Its because you have @Fetch(FetchMode.JOIN) on ProductBid. So for each of the ProductBidRejections you retrieve, it also loads a ProductBid.

UPDATE

Try this query. It will get distinct pb and eagerly fetch the PBR

select distinct pb from ProductBid pb left join fetch pb.rejection pbr where pbr is null and pb.product = :product order by pb.amount desc