SpringBoot with JPA 프로젝트(M:N) 3.페이징처리,리뷰평점,개수,회원삭제

mingki·2022년 3월 8일
JPAJavaM:NSpringbootmysqlsql
0

SpringBoot & JPA

목록 보기
22/26
post-thumbnail

📚 공부한 책 : 코드로배우는 스프링 부트 웹프로젝트
❤️ github 주소 : https://github.com/qkralswl689/LearnFromCode/tree/main/mreview2022

1.페이지 처리되는 영화별 평균 점수/리뷰 개수 구하기

현재 테이블 관계 : 영화(movie)와 영화이미지(movie_image)는 1:N 관계이다

  • JPQL에서 'group by'를 적용해 리뷰의 개수, 리뷰이 평균 평점을 구한다
  • 영화와 리뷰를 이용해 페이징 처리한다

1-1.repository 작성

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.Test

import 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.repository 작성

리뷰(Review)와 관련된 것은 left outer join 으로 처리, Review 와 Movie 조인후 count(),avg() 등 함수를 사용해
영화이미지(MovieImage) 별로 group by 를 실행한다

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.Test

import 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.repository 작성

@EntityGraph : 엔티티의 특정한 속성을 같이 로딩하도록 표시하는 어노테이션
-> 특정 기능을 수행할 때만 EAGER 로딩을 하도록 지정할 수 있다

  • @EntityGraph 는 attriibutePaths 속성과 type 속성을 가지고 있다
    - attriibutePaths : 로딩 설정을 변경하고 싶은 속성의 이름을 배열로 명시
    - type : @EntityGraph를 어떤방식으로 적용할 것인지 설정
    - FATCH 속성값은 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.Test

import 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.repository 작성

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.Test

import 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=?```
profile
mingki
비전공초보개발자
이전 포스트

SpringBoot with JPA 프로젝트(M:N) 2.entity 생성,Mapping table 작성 및 데이터 생성

다음 포스트

SpringBoot with JPA 프로젝트(M:N) 4.이미지 업로드,썸네일,이미지삭제

0개의 댓글