CODEWITHSUNDEEP
javaspringhibernatejpaone-to-many
TCBOOne
TCBOOne

Reputation: 13

Spring, JPA and Hibernate multiple queries executed from one sql statement

I'm completely new to Spring, JPA and Hibernate, so apologies if I do not phase this question correctly.

I have been given an application using these frameworks that has multiple performance issues.

I have done numerous searches to no avail - probably due to my inability to phrase my question correctly.

The scenario is (in a nutshell) that when one sql statement is executed via the EntityManager, it in turn invokes a related query per row of the result set. In my case the one sql statement could cause hundreds (and in some cases thousands) of related sql statements to be invoked - killing our server.

A contrived example, could be:

public class Job
{
    public long jobId;
    public String name;
    public List<Transaction> transactions;
} 
public class Transaction
{
    public long transactionId
    public List<Stock> stocks;
}
public class Stock
{
    public long stockId;
    public String name; 
}

The code uses a statement like:
String jpaQuery = "select distinct j from Job j where j.jobId = :jobId order by name asc";
Query query = entityManager.createQuery(jpaQuery);
//set parameters...

//This will return a List<Job>, containing lists of Transaction and Stock objects 
return query.getResultList();

The execution of this single sql statement results in multiple sql statements being executed (which can be viewed in the tomcat log / eclipse console), so they are being invoked by the code/framework. EntityManager has not been extended, so the framework(s) are doing the work.

Now my question - thanks for reading this far...

Within Spring, JPA and Hibernate, what would be the best way to approach this so one call to the database returns the required objects without multiple requests to the database?

My thought of an approach is to have the single sql statement to invoke a stored procedure, that could either return multiple result sets or just one result set containing data for all related objects and have the framework do the rest (instantiate the relevant objects).

What I have no idea on is how to approach this within the Spring / JPA / Hibernate environment. Could someone please point me to resources that would give me examples/ideas on how to approach this? Or keywords to search for?

Version: Spring: 3.0.6.Release Hibernate: 3.5.0-Beta-2

Thanks very much Steve

Upvotes: 1

Views: 9340

Answers (1)

dunni
dunni

Reputation: 44515

First, this is not an SQL statement, it is a JPQL statement, which is a huge difference (not syntax wise, but logic wise).
Second, the reason for the multiple SQL statements is the one-to-many relationshop from job to transaction to stock (it's called N+1 problem). Depending on your use case you could change the relationships to lazy loading. This means, they are only loaded when you need the objects. However, this can cause other problems, if it is not handled correctly. You could also try to use Join Fetching. This will create a single query with a join, instead if issueing one subselect per element in the collection.

Upvotes: 1

Related Questions