JPWH book says that a join operation in criteria query with Hibernate produces on clause with foreign key constraint. I've added these foreign key constraints on my database:
alter table BID add foreign key (BIDDER_ID) references USER (ID)
alter table BID add foreign key (ITEM_ID) REFERENCES ITEM (ID)
alter table ITEM add foreign key (SELLER_ID) REFERENCES ITEM (ID)
So I expected this criteria query:
private static void implicitJoin(EntityManager em) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
cb.equal(
b.get("item").get("seller").get("username"), "johndoe")
);
TypedQuery<Item> query = em.createQuery(criteria);
List<Item> results = query.getResultList();
}
translated into something like:
select b.* from BID b inner join ITEM i on b.ITEM_ID = i.ID inner join
USER u on i.SELLER_ID = u.ID where u.USERNAME = 'johndoe';
But I get this SQL statement:
/* select generatedAlias0 from Bid as generatedAlias0 where generatedAlias0.item.seller.username=:param0 */ select bid0_.BID_ID as BID_ID1_0_, bid0_.BIDDER_ID as BIDDER_I2_0_, bid0_.ITEM_ID as ITEM_ID3_0_ from BID bid0_ cross join ITEM item1_ cross join USER user2_ where bid0_.ITEM_ID=item1_.id and item1_.SELLER_ID=user2_.id and user2_.username=?
How can I put bid0_.ITEM_ID=item1_.id and item1_.SELLER_ID=user2_.id
into on clause? Is it possible? Here are entities:
@Entity
@Table(name = "BID")
public class Bid implements Serializable {
@Id @GeneratedValue
@Column(name = "BID_ID")
private Long id = null;
@ManyToOne
@JoinColumn(name = "ITEM_ID", nullable = false, updatable = false, insertable = false)
private Item item;
@ManyToOne
@JoinColumn(name = "BIDDER_ID", nullable = false, updatable = false)
private User bidder;
}
@Entity
@Table(name = "ITEM")
public class Item implements Serializable {
@Id
private Long id = null;
private String name;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "SELLER_ID", nullable = false)
private User seller;
@OneToMany(mappedBy = "item", fetch = FetchType.LAZY)
private List<Bid> bids;
}
@Entity
@Table(name = "USER")
public class User implements Serializable {
@Id
private Long id = null;
private String firstname;
private String lastname;
private String username; // Unique and immutable
}