| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
Tags
- docker
- 람다식
- yield
- auto.create.topics.enable
- annotation processor
- System.err
- 자바할래
- 합병 정렬
- 상속
- 로컬 클래스
- 함수형 인터페이스
- raw 타입
- 접근지시자
- System.out
- 제네릭 와일드 카드
- Study Halle
- 브릿지 메소드
- 프리미티브 타입
- 정렬
- throwable
- 익명 클래스
- 제네릭 타입
- 바운디드 타입
- 스파르타코딩클럽
- github api
- 자바스터디
- System.in
- 항해99
- Switch Expressions
- junit 5
Archives
- Today
- Total
코딩하는 털보
21.11.25 TIL 본문
오늘의 삽질
org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list
QueryDSL 쿼리 프로젝션을 사용해서 Entity가 아닌 특정 클래스에 SQL로 조회한 데이터를 담으면서 발생한 에러이다.
@Override
public List<RecommendationVo> searchByNumber(User login, int number) {
return queryFactory
.select(new QRecommendationVo(recommendation.type,
new QFollowVo(
user.username,
monsterDatabase.id,
monsterDatabase.imageUrl,
user.monsterCode,
new CaseBuilder()
.when(follow.id.isNull())
.then(Boolean.FALSE)
.otherwise(Boolean.TRUE))
))
.from(recommendation).fetchJoin()
.join(recommendation.user, user).fetchJoin()
.join(user.monster, monster).fetchJoin()
.join(monster.monsterDatabase, monsterDatabase)
.leftJoin(follow)
.on(user.eq(follow.following)
.and(follow.follower.eq(login))
)
.where(recommendation.number.eq(number))
.fetch();
}RecommendationVo에 바로 담을 것이기 때문에 엔티티는 사용할 수 없다. 즉, 이 쿼리는 엔티티 그래프를 참조할 수 없는 쿼리이므로 fetch Join을 제거하고 순수한 join으로 변경하여 해결했다.
참고 : https://www.inflearn.com/questions/23847
쿼리 튜닝
추천 사용자 조회 쿼리 튜닝
HIBERNATE:
SELECT recommenda0_.id AS id1_12_0_,
user1_.id AS id1_14_1_,
recommenda0_.number AS number2_12_0_,
recommenda0_.type AS type3_12_0_,
recommenda0_.user_id AS user_id4_12_0_,
user1_.created_at AS created_2_14_1_,
user1_.disabled AS disabled3_14_1_,
user1_.email AS email4_14_1_,
user1_.monster_id AS monster_9_14_1_,
user1_.monster_code AS monster_5_14_1_,
user1_.provider_type AS provider6_14_1_,
user1_.social_id AS social_i7_14_1_,
user1_.username AS username8_14_1_
FROM recommendation recommenda0_
INNER JOIN USER user1_
ON recommenda0_.user_id = user1_.id
WHERE recommenda0_.number = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ? 조치사항
- ASIS (N+1)
- 인증 관련 컬럼 유니크 제약조건 (social_id)
- QeuryDSL Join, projection
- 인덱스 추가 Recommendation(number)
SELECT Cast(recommenda0_.type AS CHAR) AS col_0_0_,
user1_.username AS col_1_0_,
monsterdat3_.id AS col_2_0_,
monsterdat3_.image_url AS col_3_0_,
user1_.monster_code AS col_4_0_,
CASE
WHEN follow4_.id IS NULL THEN ?
ELSE 1
END AS col_5_0_
FROM recommendation recommenda0_
INNER JOIN USER user1_
ON recommenda0_.user_id = user1_.id
INNER JOIN monster monster2_
ON user1_.monster_id = monster2_.id
INNER JOIN monster_database monsterdat3_
ON monster2_.monster_database_id = monsterdat3_.id
LEFT OUTER JOIN follow follow4_
ON ( user1_.id = follow4_.following_id
AND follow4_.follower_id = ? )
WHERE recommenda0_.number = ? 팔로우 목록 가져오기
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.following_id = user1_.id
WHERE user1_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_0_,
user0_.created_at AS created_2_14_0_,
user0_.disabled AS disabled3_14_0_,
user0_.email AS email4_14_0_,
user0_.monster_id AS monster_9_14_0_,
user0_.monster_code AS monster_5_14_0_,
user0_.provider_type AS provider6_14_0_,
user0_.social_id AS social_i7_14_0_,
user0_.username AS username8_14_0_
FROM USER user0_
WHERE user0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_0_,
user0_.created_at AS created_2_14_0_,
user0_.disabled AS disabled3_14_0_,
user0_.email AS email4_14_0_,
user0_.monster_id AS monster_9_14_0_,
user0_.monster_code AS monster_5_14_0_,
user0_.provider_type AS provider6_14_0_,
user0_.social_id AS social_i7_14_0_,
user0_.username AS username8_14_0_
FROM USER user0_
WHERE user0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_0_,
user0_.created_at AS created_2_14_0_,
user0_.disabled AS disabled3_14_0_,
user0_.email AS email4_14_0_,
user0_.monster_id AS monster_9_14_0_,
user0_.monster_code AS monster_5_14_0_,
user0_.provider_type AS provider6_14_0_,
user0_.social_id AS social_i7_14_0_,
user0_.username AS username8_14_0_
FROM USER user0_
WHERE user0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ?
HIBERNATE:
SELECT user0_.id AS id1_14_,
user0_.created_at AS created_2_14_,
user0_.disabled AS disabled3_14_,
user0_.email AS email4_14_,
user0_.monster_id AS monster_9_14_,
user0_.monster_code AS monster_5_14_,
user0_.provider_type AS provider6_14_,
user0_.social_id AS social_i7_14_,
user0_.username AS username8_14_
FROM USER user0_
WHERE user0_.monster_code = ?
HIBERNATE:
SELECT follow0_.id AS id1_1_,
follow0_.follower_id AS follower2_1_,
follow0_.following_id AS followin3_1_
FROM follow follow0_
LEFT OUTER JOIN USER user1_
ON follow0_.follower_id = user1_.id
LEFT OUTER JOIN USER user2_
ON follow0_.following_id = user2_.id
WHERE user1_.id = ?
AND user2_.id = ?
HIBERNATE:
SELECT monster0_.id AS id1_6_0_,
monster0_.created_at AS created_2_6_0_,
monster0_.exp_point AS exp_poin3_6_0_,
monster0_.level AS level4_6_0_,
monster0_.monster_database_id AS monster_6_6_0_,
monster0_.NAME AS name5_6_0_
FROM monster monster0_
WHERE monster0_.id = ?
HIBERNATE:
SELECT monsterdat0_.id AS id1_9_0_,
monsterdat0_.image_url AS image_ur2_9_0_,
monsterdat0_.level AS level3_9_0_,
monsterdat0_.monster_type AS monster_4_9_0_
FROM monster_database monsterdat0_
WHERE monsterdat0_.id = ? 조치사항
- ASIS (N+1)
- 인증 관련 컬럼 유니크 제약조건 (social_id)
- QeuryDSL Join, projection
- 인덱스 추가 Follow(follower, following)
SELECT user1_.username AS col_0_0_,
monsterdat3_.id AS col_1_0_,
monsterdat3_.image_url AS col_2_0_,
user1_.monster_code AS col_3_0_,
CASE
WHEN follow4_.id IS NULL THEN ?
ELSE 1
END AS col_4_0_
FROM follow follow0_
INNER JOIN USER user1_
ON follow0_.follower_id = user1_.id
INNER JOIN monster monster2_
ON user1_.monster_id = monster2_.id
INNER JOIN monster_database monsterdat3_
ON monster2_.monster_database_id = monsterdat3_.id
LEFT OUTER JOIN follow follow4_
ON ( follow4_.follower_id = ?
AND follow4_.following_id = user1_.id )
WHERE follow0_.following_id = ?
'Diary/Today I Learned' Related Articles
more
Comments