CODEWITHSUNDEEP
hibernatejpaspring-data-jpa
adeelmahmood
adeelmahmood

Reputation: 2431

How to reduce number of queries for ManyToOne (jpa/hibernate)

I am using spring data jpa with a legacy database schema and having some problems with mapping one of the relationships. There are 2 entities in the database buyer and employee. One employee can have multiple buyers so its a ManyToOne relationship from buyer to employee.

This is how I have my jpa/hibernate entities configured:

@Entity
@Table(name = "BUYER")
public class BuyerEntity {
   @Id
   @Column(name = "BUYER")
   private Long id;

   @ManyToOne
   @JoinColumn(name = "EMPLOYEE_ID")
   private EmployeeEntity employee;
....

and 

@Entity
@Table(name = "EMPLOYEE")
public class EmployeeEntity {

    @Id
    @Column(name = "EMPLOYEE_ID")
    private Long id;
....

Basically, the buyer table has the foreign key Employee_Id pointing back to the primary key of the employee table.

When I do a lookup for buyers, jpa/hibernate first does a query to get all buyers and then for each buyer, it runs another query to retrieve the corresponding employee information. This is extremely inefficient because we can easily retrieve the employee information along with the buyer information by adding the employee table columns in the first query.

So instead of first doing a query that looks like this:

select buyer.id from buyer join employee ...

and then for each buyer do another query:

select employee.* from employee where employee.id = ?

if we can suggest jpa/hibernate to do a single query like this:

select buyer.*, employee.* from buyer join employee ...

Any help would be appreciated.

Upvotes: 6

Views: 2512

Answers (1)

Naros
Naros

Reputation: 21103

You should be able to issue the following query to accomplish your goal:

SELECT b FROM BuyerEntity b JOIN FETCH b.employee

You'll notice here I am applying a JOIN FETCH which tells Hibernate not only to join the EmployeeEntity table with the BuyerEntity table but to also fetch the data into the relation as part of a single query to the database.

If you want to use JPA Criteria API

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<BuyerEntity> query = cb.createQuery( BuyerEntity.class );
Root<BuyerEntity> root = query.from( BuyerEntity.class );

// specify join-fetch
root.fetch( "employee", JoinType.INNER );
query.select( root );

List<BuyerEntity> results = entityManager.createQuery( query ).getResultList();

Upvotes: 1

Related Questions