Spring JPQL Query creating N+1 Query

Viewed 45

My JPQL Query returns returns same results 5 more times than I expected. I think there's 'N+1' problems maybe, so I used fetch join at the very first join of my query but it doesn't work.

code from domain Lv2

@ManyToOne
@JoinColumn(name = "level_3_id")
private Lv3 lv3;

code from domain Lv3

@ManyToOne
@JoinColumn(name = "level_2_id")
private Lv2 lv2;

code from domain Title

@ManyToOne
@JoinColumn(name = "title_info_id")
private TitleInfo titleInfo;

jpql query

@Repository
public class Lv4Repository {
    @PersistenceContext
    private EntityManager em;

    public List<Lv4> findLv4ByLv3Id(String level_2_Title, String level_3_Title) {
        return em.createQuery("select l4 from Lv4 l4 join Lv3 as l3 on l4.lv3 = " +

                                    "(select l3 from Lv3 l3 join Lv2 as l2 on l3.lv2 = l2 " +
                                    "join TitleInfo as ti on l2.metaInfo = ti.metaInfo " +
                                    "join Title as t on ti.id = t.titleInfo.id " +
                                    "where t.titleText like concat('%',:level_2_Title,'%'))" +

                                "join TitleInfo as ti on l3.metaInfo = ti.metaInfo " +
                                "join Title as t on ti.id = t.titleInfo.id " +
                                "where t.titleText =: level_3_Title", Lv4.class)
                .setParameter("level_2_Title", level_2_Title)
                .setParameter("level_3_Title", level_3_Title)
                .getResultList();
    }
}

Result

My Expectation

[[94], [ITKC_MO_1237A_0010_010_0010, ITKC_MO_1237A_0010_010_0020,...,ITKC_MO_1237A_0010_010_0940]]

My Result

[[470], [ITKC_MO_1237A_0010_010_0010,...,ITKC_MO_1237A_0010_010_0940,ITKC_MO_1237A_0010_010_0010,...,ITKC_MO_1237A_0010_010_0940,ITKC_MO_1237A_0010_010_0010,...,ITKC_MO_1237A_0010_010_0940,ITKC_MO_1237A_0010_010_0010,...,ITKC_MO_1237A_0010_010_0940,
ITKC_MO_1237A_0010_010_0010,...,ITKC_MO_1237A_0010_010_0940]]
0 Answers
Related