Sorting and Cross Join Problem – Spring Data JPA


Affected Version

Spring boot – 1.2.2
Spring data JPA version – 1.7.2
Spring data common – 1.9.2

Problem Statement

When you use custom query using @Query in your spring data JPA repository and pass sort parameter, where sort parameter is a child attribute; the query generated uses cross join instead of left outer join. This gives unexpected result.

Problem Sample


@Query("SELECT e FROM EventApprovalRequest e WHERE e.approvalRequestType = (:approvalRequestType) "
+ "AND e.status = (:status)")
Page getEventsForApproval(
@Param("approvalRequestType") int approvalRequestType, @Param("status") int status, Pageable pageable);

Solution

Pre-define left outer join in your query.

@Query("SELECT e FROM EventApprovalRequest e LEFT OUTER JOIN e.event LEFT OUTER JOIN e.event.director WHERE e.approvalRequestType = (:approvalRequestType) "
+ "AND e.status = (:status)")
Page getEventsForApproval(
@Param("approvalRequestType") int approvalRequestType, @Param("status") int status, Pageable pageable);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s