There is N+1 problem, while joining multiple entities with @OneToOne Association (Hibernate 5.2)
@Entity
@Table(name = "COMMON_CARDS")
public class CommonCards {
@Id
@Column(name = "card_number")
private String card_number;
@Column(name = "card_id")
private String card_id;
@Column(name = "bank_C")
private String bankC;
@Column(name = "centre_Id")
private String centreId;
@Column(name = "bank_Id")
private String bankId;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CARD_NUMBER")
private StipCards stipCards;
@OneToOne(fetch = FetchType.LAZY)
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumn(name = "CARD_NUMBER", insertable = false, updatable = false)
private CardsExceptions cardsExceptions;
/*getters and setters*/
@Entity
@Table(name = "STIP_CARDS")
public class StipCards {
@Id
@Column(name = "CARD_NUMBER")
private String card_number;
@Column(name = "CENTRE_ID")
private String CENTRE_ID;
@Column(name = "Expiry_Date_1")
private String expriryDate1;
@Column(name = "Expiry_Date_2")
private String expriryDate2;
@Column(name = "stat_code_1")
private String statCode1;
@Column(name = "stat_code_2")
private String statCode2;
/*getters and setters*/
@Entity
@Table(name = "CARDS_EXCEPTIONS")
public class CardsExceptions {
@Id
@Column(name = "CARD_NUMBER")
private String cardNumber;
@Column(name = "ACTION_CODE")
private String actionCode;
/*getters and setters*/
here is implementation code to get list of records from database
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<CommonCards> criteria = builder.createQuery(CommonCards.class);
Root<CommonCards> root = criteria.from(CommonCards.class);
Join<Object, Object> joinStipCards = (Join<Object, Object>) root.fetch("stipCards", JoinType.INNER);
Join<Object, Object> joinException = (Join<Object, Object>) root.fetch("cardsExceptions", JoinType.LEFT);
Predicate predicate = builder.and(root.get("card_id").in(cardIds));
predicate = builder.and(predicate, builder.and(builder.equal(joinStipCards.get("CENTRE_ID"), root.get("centreId"))));
criteria.where(predicate);
List<CommonCards> commonCards = session.createQuery(criteria).getResultList();
for (CommonCards card : commonCards) {
System.out.println(card.toString());
}
This code generates two select statement
FIRST SQL
SELECT commoncard0_.card_number AS card_number1_5_0_
,stipcards1_.CARD_NUMBER AS CARD_NUMBER1_2_1_
,cardsexcep2_.CARD_NUMBER AS CARD_NUMBER1_1_2_
,commoncard0_.bank_C AS bank_C2_5_0_
,commoncard0_.bank_Id AS bank_Id3_5_0_
,commoncard0_.card_id AS card_id4_5_0_
,commoncard0_.CARD_NUMBER AS CARD_NUMBER1_5_0_
,commoncard0_.centre_Id AS centre_Id5_5_0_
,stipcards1_.CENTRE_ID AS CENTRE_ID2_2_1_
,stipcards1_.Expiry_Date_1 AS Expiry_Date_3_2_1_
,stipcards1_.Expiry_Date_2 AS Expiry_Date_4_2_1_
,stipcards1_.stat_code_1 AS stat_code_5_2_1_
,stipcards1_.stat_code_2 AS stat_code_6_2_1_
,cardsexcep2_.ACTION_CODE AS ACTION_CODE2_1_2_
FROM COMMON_CARDS commoncard0_
INNER JOIN STIP_CARDS stipcards1_
ON commoncard0_.CARD_NUMBER = stipcards1_.CARD_NUMBER
LEFT OUTER JOIN CARDS_EXCEPTIONS cardsexcep2_
ON commoncard0_.CARD_NUMBER = cardsexcep2_.CARD_NUMBER
WHERE (commoncard0_.card_id IN (?,?,?,?))
AND stipcards1_.CENTRE_ID = commoncard0_.centre_Id
SECOD SQL
This second statement is generated when some record exists in to first 2 table but does not exists in to cards_exception table(entity).
number of query execution depends on how many record was not found in to cards_exceptions table.
SELECT cardsexcep0_.CARD_NUMBER AS CARD_NUMBER1_1_0_
,cardsexcep0_.ACTION_CODE AS ACTION_CODE2_1_0_
FROM CARDS_EXCEPTIONS cardsexcep0_
WHERE cardsexcep0_.CARD_NUMBER = ?
Please explain why does it happens?
What to change to fix the problem?