Home Hibernate N+1 @ManyToMany with paging (Spring Data)
Reply: 0

Hibernate N+1 @ManyToMany with paging (Spring Data)

Rostislav
1#
Rostislav Published in 2017-12-05 21:26:14Z

I have a problem with hibernate and N + 1 on @ManyToMany association. I have the following 3 classes.

@Entity
public class Layout {

@OneToMany(mappedBy="layoutSamplesPK.layout", orphanRemoval=true, 
fetch=FetchType.LAZY)
private List<LayoutSamples> layoutSamples = new ArrayList<LayoutSamples>();
}

LayoutSamples for join table with addition columns and composite key

public class LayoutSamples {

@EmbeddedId
LayoutSamplesPK layoutSamplesPK = new LayoutSamplesPK();

    @Column
    private Integer quantity;

}

LayoutSamplesPK - for Composite key

@Embeddable
public class LayoutSamplesPK  implements Serializable{

/**
 *
 */
private static final long serialVersionUID = 1L;


@ManyToOne
private Tile tile;

@ManyToOne
private Layout layout;

}

And the query in Spring data repository -

@Repository
@Transactional
public interface LayoutSamplesRepository extends JpaRepository<LayoutSamples, LayoutSamplesPK> {

    @Query(value="SELECT ll from LayoutSamples ll left outer join fetch ll.layoutSamplesPK.tile where ll.id.layout.id = :layoutId",
            countQuery = "select count(l) from LayoutSamples l where l.id.layout.id = :layoutId")
    Page<LayoutSamples> getSamples(@Param("layoutId") Integer layoutId, Pageable pageable);


}

The problem is that the hibernate generates too many selects ( N+ 1 ) even that I'm using LEFT JOIN FETCH. I'm using this LayoutSamples for a composite key because I'm using some additional columns in the join table (quantities, Created date and so on..)

The result from the query is

Hibernate: select layoutsamp0_.layout_id as layout_i4_8_, layoutsamp0_.tile_id as tile_id3_8_, layoutsamp0_.created_date as created_1_8_, layoutsamp0_.quantity as quantity2_8_ from layout_samples layoutsamp0_ left outer join tile tile1_ on layoutsamp0_.tile_id=tile1_.id where layoutsamp0_.layout_id=? limit ?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?
Hibernate: select tile0_.id as id1_10_0_, tile0_.long_description as long_des2_10_0_, tile0_.sku as sku3_10_0_ from tile tile0_ where tile0_.id=?

UPDATED

I had to used distinct in the query @Query(value="SELECT distinct ll from LayoutSamples ll left outer join fetch) this was the problem.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.319163 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO