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]]