0

Background :

My application is using JPA Criteria to fetch the data from DB and a permanence issue in identified related to too many SQL calls(for child entities) to DB.

Take an example here:

ENTITY_A  (parent) with 500 item types record

ENTITY_B (child-1) with 1000 item details record

ENTITY_C (child-2) with 1000 item details record

ENTITY_D (child-3) with 1000 item details record

As per requirement, We need to fetch data for parent entity along with its child(all entities). Child Entities are already mapped with fetch=FetchType.LAZY in parent Entity.

Problem :

When we try to fetch 500 item types(ENTITY_A) , then the number of SQL calls are invoked in following manner :

  • 1 Call for Entity_A List(to fetch all 500 records)
  • 500 SQL calls for Entity_B List (to fetch records for the linked parent ID)
  • 500 SQL calls for Entity_C List (to fetch records for the linked parent ID)
  • 500 SQL calls for Entity_D List (to fetch records for the linked parent ID)

I know one alternate is to switch from Criteria to SQL Procedure call (which will return all the data finally with a single SQL call in form of SQL Types )

Is there an alternate way using which we can limit the SQL calls to DB in this manner?

Arun Kumar
  • 6,534
  • 13
  • 40
  • 67
  • 1
    Some ideas how to alleviate the N+1 selects problem in [this thread](https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-hibernate). – Mick Mnemonic Jun 27 '17 at 16:03
  • 1
    With criteria queries, you can use `root.fetch()` with the child entities in order to load them in the same query. The difference between a regular join and a fetch join is explained here: https://stackoverflow.com/questions/17431312/difference-between-join-and-join-fetch-in-hibernate. Also, see here: https://stackoverflow.com/questions/17306655/using-the-jpa-criteria-api-can-you-do-a-fetch-join-that-results-in-only-one-joi for an example. – crizzis Jun 28 '17 at 09:32

1 Answers1

0

Well there is some concept of Lazy Loading in Hibernate

eg: 

@JoinColumn(name = "USER_ID", referencedColumnName = "ID")
@ManyToOne(fetch = FetchType.LAZY)
private User userId;


@OneToMany(fetch=FetchType.LAZY)
private List<User> lstUser;

whenever parent is called. If fetch type is set to LAZY. Query will be fired only when you try to access that entity else it wont

MyTwoCents
  • 7,284
  • 3
  • 24
  • 52
  • I need Child entity data always. Even with Lazy mode while calling child entity getter method, it will fire 'N' number of queries (if 'N' number of parent record are fetched). My question is can we limit Child entity call to only 1 SQL call. – Arun Kumar Jun 28 '17 at 07:50
  • No its not possible to get all 500 record in 1 Query. – MyTwoCents Jun 28 '17 at 11:06