How to get a join in one call using hibernate (N+1 issue)

Question

NOTE

Before reading, what I am trying to find is to avoid N+1 queries on Hibernate

END NOTE

I have 3 tables. Customer, Merchant and Transactions.

Customers can have transactions. A customer can have many transaction but each transaction can only have one merchant.

This is how the transaction table looks like:

private Long transaction_id;
/** removed other stuff for brevity **/
@ManyToOne(optional=false) 
@JoinColumn(name="merchantId", referencedColumnName="merchantId") 
private Merchant merchant;

@ManyToOne(optional=false) 
@JoinColumn(name="customerId", referencedColumnName="customerId") 
private Customer customer;

Table:

`Transaction` (
`sequenceId` bigint(20) NOT NULL AUTO_INCREMENT,
`amountDue` decimal(19,2) DEFAULT NULL,
`merchantId` varchar(255) DEFAULT NULL,
`customerId` varchar(255) NOT NULL
)

`Customer` (
`customerId` bigint(20) NOT NULL AUTO_INCREMENT,
`dateCreated` datetime DEFAULT NULL,
`dateUpdated` datetime DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`customerName` varchar(255) DEFAULT NULL,
`subscriberNum` varchar(255) DEFAULT NULL
 )

`Merchant` (
`sequenceId` bigint(20) NOT NULL AUTO_INCREMENT,
`merchantId` varchar(255) DEFAULT NULL,
`merchantName` varchar(255) DEFAULT NULL
 )

I need to a query where the result would be:

merchantName   Trend Holomes
customerName   Paul Florence
amountDue      20000.00

I can do this in a single query. Basically I can create either a subquery

SELECT 
    (SELECT m.merchantname  
    FROM merchant m WHERE m.id = t.merchantId) AS merchantname,
    (SELECT c.customername 
    FROM customer c WHERE c.id = t.customerId) AS customername,
    t.amountDue
FROM transaction t

or by joining

select m.merchantname, c.customername, t.amountDue
    from transaction t 
    inner join customer c on t.customerId = c.id 
    inner join merchant m on t.merchantId = m.id

I was able to create the output I want using Spring Data JPA + Hibernate but I noticed something strange in the logs. This is how I retrieve the data:

transactionRepository.findAll();

Two select queries are executed.

select customer0_.customerId as sequence1_0_0_, customer0_.dateCreated as dateCrea2_0_0_, customer0_.dateUpdated as dateUpda3_0_0_, customer0_.email as email4_0_0_, customer0_.customerName as customerNam5_0_0_, customer0_.subscriberNum as subscrib9_0_0_ from Customer customer0_ where customer0_.customerId=?

and

select merchant0_.sequenceId as sequence1_2_0_, merchant0_.merchantId as merchant2_2_0_, merchant0_.merchantName as merchant3_2_0_, merchant0_.merchantOptInCode as merchant4_2_0_, merchant0_.merchantOptOutCode as merchant5_2_0_ from Merchant merchant0_ where merchant0_.merchantId=?

Does that mean that every time every time an inner join is used, a select query is executed for each referenced table? Isn't that a bit expensive? What if there are more entities that are needed to be joined?

Is there a way in hibernate to do a single one execution join just like the subquery or inner join above as to avoid sending one select query per referenced entity?

tl;dr?

Does Hibernate create select statements with the foreign key as the parameter for each joined entity?

More foreign key meaning more select statements sent?

Answer 1

Please, don't use findAll() in production code. It can be used for testing purposes only. If you want an optimal query — use projections with aliases(in criteria) or joins (in HQL) with a result transformer. You can use a transformer to set values directly in properties of yours Transaction object. And you need a strategy of using associations. See this for a reference.

Updated

You can use fetching strategies. So you can tweak associations to don't use separate selects. And, please, check you hibernate.properties for value of hibernate.max_fetch_depth.

And one thing else — you can have additional subselects when have not lazy bidirectional associations. But looks like it is not yous case.

Answer 2

Aside from v.ladynev's answer, this is how I solved my issue:

Update

@Repository
public interface TransactionRepository extends PagingAndSortingRepository<TransactionRepository, Long>{

@Query("from Transaction AS t inner join "
            + "fetch t.customer "
            + "inner join "
            + "fetch t.merchant "
            + "where t.optStatus = ?1")
public List<TransactionRepository> findByOptStatus(boolean optStatus);

NOTE 1

My question is try to solve the N+1 issue on Hibernate because of Lazy Loading.

NOTE 2

Please note that it is important to know that I am using Spring Data + Hibernate + Jersey + Jackson. This is probably why the annotation for Fetch.Eager isn't working as it has a lot of stuff in between Hibernate and Spring Data without Spring Boot.

No, I am not using Spring boot as Jackson doesn't play nice with Spring Boot + Jersey.