SpringBoot with JPAプロジェクト(M:N)3.ページ処理、コメントスコア、カウント、メンバーの削除
📚 勉強した本:コード学習を用いたSpring Boot Webプロジェクト
▼githubアドレス:https://github.com/qkralswl689/LearnFromCode/tree/main/mreview2022
1.ページ処理された各映画の平均点数/コメント数を得る
現在のテーブル関係:映画と映画のイメージは1:Nの関係です
import com.example.mreview2022.entity.Movie;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
public interface MovieRepository extends JpaRepository<Movie,Long> {
@Query("select m, mi, avg(coalesce(r.grade,0)), count(distinct r) from Movie m " +
"left outer join MovieImage mi on mi.movie = m " +
"left outer join Review r on r.movie = m group by m ")
Page<Object[]> getListPage(Pageable pageable);
}
1-2.Testimport com.example.mreview2022.entity.Movie;
import com.example.mreview2022.entity.MovieImage;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.test.annotation.Commit;
import org.springframework.test.context.web.WebAppConfiguration;
import javax.transaction.Transactional;
import java.util.Arrays;
import java.util.UUID;
import java.util.stream.IntStream;
@SpringBootTest
public class MovieRepositoryTests {
@Autowired
private MovieRepository movieRepository;
@Autowired
private MovieImageRepository imageRepository;
@Test
public void testListPage(){
PageRequest pageRequest = PageRequest.of(0,10, Sort.by(Sort.Direction.DESC,"mno"));
Page<Object[]> result = movieRepository.getListPage(pageRequest);
for (Object[] objects : result.getContent()){
System.out.println(Arrays.toString(objects));
}
}
}
Hibernate:
select
movie0_.mno as col_0_0_,
movieimage1_.inum as col_1_0_,
avg(coalesce(review2_.grade,
0)) as col_2_0_,
count(distinct review2_.reviewnum) as col_3_0_,
movie0_.mno as mno1_1_0_,
movieimage1_.inum as inum1_2_1_,
movie0_.moddate as moddate2_1_0_,
movie0_.regdate as regdate3_1_0_,
movie0_.title as title4_1_0_,
movieimage1_.img_name as img_name2_2_1_,
movieimage1_.movie_mno as movie_mn5_2_1_,
movieimage1_.path as path3_2_1_,
movieimage1_.uuid as uuid4_2_1_
from
movie movie0_
left outer join
movie_image movieimage1_
on (
movieimage1_.movie_mno=movie0_.mno
)
left outer join
review review2_
on (
review2_.movie_mno=movie0_.mno
)
group by
movie0_.mno
order by
movie0_.mno desc limit ?
2.特定の映画のすべての画像と平均スコア/コメント数2-1.リポジトリの作成
「レビュー」に関連するコンテンツをleft outer joinで処理し、reviewとMovieを結合したcount()、avg()などの関数を使用します.
映画画像によるgroupbyの実行
import com.example.mreview2022.entity.Movie;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface MovieRepository extends JpaRepository<Movie,Long> {
//... 생략
@Query("select m, mi ,avg(coalesce(r.grade,0)), count(r)" +
" from Movie m left outer join MovieImage mi on mi.movie = m " +
" left outer join Review r on r.movie = m " +
" where m.mno = :mno group by mi")
List<Object[]> getMovieWithAll(@Param("mno") Long mno); // 특정 영화 조회
}
2-2.Testimport com.example.mreview2022.entity.Movie;
import com.example.mreview2022.entity.MovieImage;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.test.annotation.Commit;
import org.springframework.test.context.web.WebAppConfiguration;
import javax.transaction.Transactional;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
import java.util.stream.IntStream;
@SpringBootTest
public class MovieRepositoryTests {
@Autowired
private MovieRepository movieRepository;
@Autowired
private MovieImageRepository imageRepository;
@Test
public void testGetMovieWithAll(){
List<Object[]> result = movieRepository.getMovieWithAll(93L);
System.out.println(result);
for (Object[] arr : result){
System.out.println(Arrays.toString(arr));
}
}
}
この映画の評論点数は平均2.0で、評論数は4つであることがわかる.
Hibernate:
select
movie0_.mno as col_0_0_,
movieimage1_.inum as col_1_0_,
avg(coalesce(review2_.grade,
0)) as col_2_0_,
count(review2_.reviewnum) as col_3_0_,
movie0_.mno as mno1_1_0_,
movieimage1_.inum as inum1_2_1_,
movie0_.moddate as moddate2_1_0_,
movie0_.regdate as regdate3_1_0_,
movie0_.title as title4_1_0_,
movieimage1_.img_name as img_name2_2_1_,
movieimage1_.movie_mno as movie_mn5_2_1_,
movieimage1_.path as path3_2_1_,
movieimage1_.uuid as uuid4_2_1_
from
movie movie0_
left outer join
movie_image movieimage1_
on (
movieimage1_.movie_mno=movie0_.mno
)
left outer join
review review2_
on (
review2_.movie_mno=movie0_.mno
)
where
movie0_.mno=?
group by
movieimage1_.inum
[[Ljava.lang.Object;@758ac46, [Ljava.lang.Object;@4d2f8ee7, [Ljava.lang.Object;@593e594f, [Ljava.lang.Object;@3a1e5fa7]
[Movie(mno=93, title=Movie...93), MovieImage(inum=279, uuid=df02795c-267d-4fc2-97ca-8f4c6c9fc519, imgName=test0.jpg, path=null), 2.0, 4]
[Movie(mno=93, title=Movie...93), MovieImage(inum=280, uuid=80ecff4c-f3a3-48b4-a1e4-4e72328fbd71, imgName=test1.jpg, path=null), 2.0, 4]
[Movie(mno=93, title=Movie...93), MovieImage(inum=281, uuid=90f7a31d-8b0d-46c7-928b-169fd332e51f, imgName=test2.jpg, path=null), 2.0, 4]
[Movie(mno=93, title=Movie...93), MovieImage(inum=282, uuid=0a5dbfae-ee2c-443f-a767-0e0adbcd613b, imgName=test3.jpg, path=null), 2.0, 4]
3.特定映画のすべてのコメントと会員のニックネーム3-1.リポジトリの作成
@EntityGraph:エンティティの特定のプロパティを一緒にロードすることを示す宣言です.
->EAGERをロードするには、特定の機能を実行するときのみ指定できます.
-attributePaths:ロード設定を変更するプロパティの名前を配列として付けます.
-type:@EntityGraphの適用方法設定
-FAATCH属性値は、AttributePathsで指定された属性EAGERによって処理され、残りの属性はLAZYFH CJFLである
-LOAD属性値をattributePathsで指定されたEAGERとして処理し、残りのシンボルクラスを
import com.example.mreview2022.entity.Movie;
import com.example.mreview2022.entity.Review;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface ReviewRepository extends JpaRepository<Review,Long> {
// Review 처리시 @EntityGraph 적용해 Member도 같이 로딩
@EntityGraph(attributePaths = {"member"},type = EntityGraph.EntityGraphType.FETCH)
List<Review> findByMovie(Movie movie);
}
3-2.Testimport com.example.mreview2022.entity.Member;
import com.example.mreview2022.entity.Movie;
import com.example.mreview2022.entity.Review;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import java.util.stream.IntStream;
@SpringBootTest
public class ReviewRepositoryTests {
@Autowired
private ReviewRepository reviewRepository;
@Test
public void testGetMovieReviews(){
Movie movie = Movie.builder().mno(92L).build();
List<Review> result = reviewRepository.findByMovie(movie);
result.forEach(movieReview -> {
System.out.println(movieReview.getReviewnum());
System.out.println("\t" + movieReview.getGrade());
System.out.println("\t" + movieReview.getText());
System.out.println("\t" + movieReview.getMember().getEmail());
System.out.println("----------------------------");
});
}
}
Hibernate:
select
review0_.reviewnum as reviewnu1_3_0_,
member1_.mid as mid1_0_1_,
review0_.moddate as moddate2_3_0_,
review0_.regdate as regdate3_3_0_,
review0_.grade as grade4_3_0_,
review0_.member_mid as member_m6_3_0_,
review0_.movie_mno as movie_mn7_3_0_,
review0_.text as text5_3_0_,
member1_.moddate as moddate2_0_1_,
member1_.regdate as regdate3_0_1_,
member1_.email as email4_0_1_,
member1_.nickname as nickname5_0_1_,
member1_.pw as pw6_0_1_
from
review review0_
left outer join
m_member member1_
on review0_.member_mid=member1_.mid
where
review0_.movie_mno=?
18
1
이 영화에 대한 느낌 ...18
r14@zerock.org
----------------------------
99
2
이 영화에 대한 느낌 ...99
r42@zerock.org
----------------------------
4.会員の削除と取引の処理M:N(複数対複数)関係を個別のマッピング・テーブルとして組織し、エンティティとして扱う場合は、「名詞」のテーブルを削除する際に、マッピング・テーブルから削除する必要があります.
->トランザクションとして管理する必要があります
4-1.リポジトリの作成
import com.example.mreview2022.entity.Member;
import com.example.mreview2022.entity.Movie;
import com.example.mreview2022.entity.Review;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface ReviewRepository extends JpaRepository<Review,Long> {
@Modifying //insert,update,delete 쿼리에서 벌크 연산시 사용한다
@Query("delete from Review mr where mr.member = :member") // 비효율을 막기위해 where절 지정
void deleteByMember(@Param("member") Member member);
}
4-2.Testimport com.example.mreview2022.entity.Member;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Commit;
import javax.transaction.Transactional;
import java.util.stream.IntStream;
@SpringBootTest
public class MemberRepositoryTests {
@Autowired
private MemberRepository memberRepository;
@Autowired
private ReviewRepository reviewRepository;
@Commit // 테스트코드 실행성공 후 DB에서도 업데이트 된 결과를 확인하기위해 사용
@Transactional
@Test
public void testDeleteMember(){
Long mid = 1L; //Member의 mid
Member member = Member.builder().mid(mid).build();
// 순서 주의
reviewRepository.deleteByMember(member);
memberRepository.deleteById(mid);
}
}
Hibernate: //review 테이블에서 하나씩 삭제
delete
from
review
where
member_mid=?
Hibernate:
select
member0_.mid as mid1_0_0_,
member0_.moddate as moddate2_0_0_,
member0_.regdate as regdate3_0_0_,
member0_.email as email4_0_0_,
member0_.nickname as nickname5_0_0_,
member0_.pw as pw6_0_0_
from
m_member member0_
where
member0_.mid=?
Hibernate: // 마지막으로 m_member 테이블 삭제
delete
from
m_member
where
mid=?```
Reference
この問題について(SpringBoot with JPAプロジェクト(M:N)3.ページ処理、コメントスコア、カウント、メンバーの削除), 我々は、より多くの情報をここで見つけました https://velog.io/@alswl689/SpringBoot-with-JPA-프로젝트MN-3テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol