[SpringBoot] NativeSQL / JPQL CASE WHEN 로직 구현(1)

안녕하세요. 회사와 함께 성장하고 싶은 KOSE입니다.

 

이번 포스팅은 SpringBoot의 Data JPA에서 제공하는 NativeSQL, JPQL을 활용하여 CASE WHEN을 활용한 집계 함수를 적용하는 과정을 정리하고자 합니다. (수정)

 

Data Jpa를 활용하면 다양한 로직을 구현할 수 있습니다. find(), delete(), save(), update() 등 CRUD 로직을 간단한 jpa 용법을 활용하여 구현할 수 있습니다. 하지만, 복잡한 쿼리를 구현해야 할 때 사용자 정의 메서드를 구현해야 할 필요성이 있습니다.

 

이번에 구현하는 비즈니스 로직은, 다음과 같습니다.

A라는 게임을 참여한 사용자들의 게임 결과를 바탕으로, 사용자 id,  게임에서 이긴 횟수, 게임에서 진 횟수, 게임에서 이긴 비율, 마지막 게임 참여 시간의 데이터를 가져와서 클라이언트에 제공해야 합니다.

 

이때는, SQL의 case when, 집계 함수, round(), group by 등을 활용하여 구현하여야 합니다.

따라서, 비즈니스 로직을 구현하기 위한 클래스와 h2 raw query, Data Jpa의 NativeSQL을 활용하여 해당 비즈니스 로직을 구현하는 과정을 정리하도록 하겠습니다.

 

1. Domain, Repository, Service

 

@Entity
@Getter
@AllArgsConstructor
@NoArgsConstructor
public class Game extends BaseEntity {

    @Id @GeneratedValue
    @Column(name = "game_id")
    private Long id;

    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "member_id")
    private Member host;

    public Game(Member host) {
        this.host = host;
    }

    public static Game of (Member host) {
        return new Game(host);
    }
}

@Entity
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "gameresult")
public class GameResult extends BaseEntity {

    @Id @GeneratedValue
    @Column(name = "game_result_id")
    private Long id;

    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "game_id")
    private Game game;

    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "join_member_id")
    private Member member;

    @Enumerated(STRING)
    private Result result;

    @Builder
    public GameResult(Game game, Member member, Result result) {
        this.game = game;
        this.member = member;
        this.result = result;
    }
}

@Repository
public interface GameResultRepository extends JpaRepository<GameResult, Long> {

    
    // to do
    
    }

@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class GameResultService {

    private final GameResultRepository repository;

    public Page<AllMemberGameResultAnalysisInterface> fetchAllMembersGameResultAnalysis(Pageable pageable) {
        return repository.fetchAllMembersGameResultAnalysis(pageable);
    }

}

구현해야 하는, Repository는 먼저 설명을 위해 //to do 빈칸으로 작성하였습니다. 

 

가상의 환경을 구현하기 위해, initDb라는 클래스를 작성하여, SpringBoot가 런타임 될 때, 임시 데이터를 입력하였습니다.

gameresult 테이블을 확인하면, 우리가 구현해야 하는 비즈니스 로직에 해당하는 데이터들이 입력된 것을 확인할 수 있습니다. 

비즈니스 로직을 구현하기 위해, Result 컬럼의 데이터를 상황에 맞게 정제하고, 그룹화하는 쿼리를 구현해야 합니다.

 

 

2. Raw Query 작성하기

바로 nativeQuery를 작성하면 좋지만, h2 데이터베이스 콘솔을 활용하여 구현해야 하는 로직을 먼저 작성하여 베이스 라인을 구축하였습니다.

 

- case when 

sql 에서는 조건에 따라 서로 다른 값을 반환할 수 있도록 case 표현식을 제공합니다. 자바에서의 if else if else와 비슷한 용법으로 sql 내에서 활용할 수 있는 구문입니다.

 

- group by

집계 함수를 활용해야 한다면, group by를 활용하여, 특정 컬럼을 기준으로 그룹화하여 작성할 수 있습니다.

 

- round()

라운드 함수는 반올림 기능을 제공하는 함수로, round(칼럼명/표현식, n)으로 작성하며, 해당 표현식의 결과를 n(소수점)의 자리까지 반올림하는 기능을 제공합니다.

소수점을 나타낼 때 데이터가 정수형이라면, 표현식 * 1.0을 하여 해당 타입을 실수형으로 바꿔주어야 합니다.

 

이를 바탕으로, Sql 쿼리를 작성하면 다음과 같습니다. 목표가 이긴 횟수, 진 횟수, 이긴 비율을 구해야 하므로 이겼을 경우의 케이스를 count 한 값, 진 케이스를 count 한 값, 이긴 횟수를 count 한 결과에서 총 result 개수를 나누고 * 100 한 후, 첫째 자리까지 반올림하여 표현하였습니다.

select  
join_member_id,
count(case when result = 'WIN' then 1 end) win, 
count(case when result = 'LOSE' then 1 end) lose,
round(count(case when result = 'WIN' then 1 end) / (count(result) * 1.0) * 100,  1) as rate,
max(modified_at) as lastjointime
from gameresult
group by join_member_id;

 

그 결과 비즈니스 로직에서 구현해야하는 쿼리를 작성할 수 있습니다.

 

3. Data Jpa에 적용하기

 

-  Wrapper.class를 적용할 interface를 구현하기

처음에는 native query로 구현할 때, Dto에 매핑되도록 구현하였습니다. 하지만, 매핑 에러가 반복되었고, 구글링 결과 인터페이스로 선언한 Dto에 매핑하여야 한다는 설명이 있었습니다. 따라서, 인터페이스를 Dto로 활용하기 위해 작성하였습니다.

public interface AllMemberGameResultAnalysisInterface {

    Long getId();
    Long getWin();
    Long getLose();
    Double getRate();
    LocalDateTime getLastjointime();

}

public interface AllMemberGameResultAnalysisJpqlInterface {

    Long getId();
    Long getWin();
    Long getLose();
    Double getRate();
    LocalDateTime getLastJoinGameTime();

}

이를 바탕으로 Repository에 native 쿼리를 구현한 결과는 다음과 같습니다.

count() -> sum()으로 수정하여 작성하였고, 매핑하는 과정에서 일반 jpql과 달리 실제 데이터베이스에 적용된 컬럼명을 적어줘야 하는 한계가 존재했습니다. 만약 paging 처리를 해야 한다면 @Query() 안에 countQuery = "" 로직을 구현해야 합니다.

@Repository
public interface GameResultRepository extends JpaRepository<GameResult, Long> {

	@Query(value =
            "select g.member.id as id, " +
                    "sum(case when (g.result = 'WIN') then 1 else 0 end) as win, " +
                    "sum(case when g.result = 'LOSE' then 1 else 0 end) as lose, " +
                    "round(sum(case when (g.result = 'WIN') then 1 else 0 end) / (count(g.result) * 1.0) * 100, 1) as rate, " +
                    "max(g.modifiedAt) as lastJoinGameTime " +
                    "from GameResult g " +
                    "join Member m on g.member.id = m.id " +
                    "group by g.member.id")
    Page<AllMemberGameResultAnalysisJpqlInterface> fetchAllMembersGameResultAnalysisJpql(Pageable pageable);



    @Query(value =
            "select g.join_member_id as id, " +
                    "sum(case when (g.result = 'WIN') then 1 else 0 end) as win, " +
                    "sum(case when g.result = 'LOSE' then 1 else 0 end) as lose, " +
                    "round(sum(case when (g.result = 'WIN') then 1 else 0 end) / (count(g.result) * 1.0) * 100, 1) as rate, " +
                    "max(g.modified_at) as lastjointime " +
                    "from GameResult g " +
                    "join Member m on g.join_member_id = m.member_id " +
                    "group by g.join_member_id " +
                    "order by g.join_member_id ASC",
            countQuery = "select count(*) from GameResult g",
            nativeQuery = true)
    Page<AllMemberGameResultAnalysisInterface> fetchAllMembersGameResultAnalysis(Pageable pageable);

}

 

4. 테스트 하기

(@BeforEach에 사용한 구문은 제거하였습니다)

 

@SpringBootTest
@Transactional
class GameResultServiceTest {

    @Autowired
    EntityManager em;

    @Autowired
    GameResultService gameResultService;
    
    @Test
    @DisplayName("각 회원의 이긴 횟수, 진 횟수, 승리 확률을 페이징하여 가져 온다.(JPQL)")
    public void fetchAllMemberGameResultAnalysisJpql() throws Exception {
        //given
        Pageable page = PageRequest.of(0, 10);

        //when
        Page<AllMemberGameResultAnalysisJpqlInterface> result = gameResultService.fetchAllMembersGameResultAnalysisJpql(page);

        //then
        assertThat(result.getContent().size()).isEqualTo(10);
        assertThat(result.getContent().get(0).getWin()).isEqualTo(1L);
        assertThat(result.getContent().get(1).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(2).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(3).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(4).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(2).getRate()).isEqualTo(20.0);
        assertThat(result.getContent().get(2).getLastJoinGameTime()).isBefore(LocalDateTime.now());

    }

    

    @Test
    @DisplayName("각 회원의 이긴 횟수, 진 횟수, 승리 확률을 페이징하여 가져 온다.")
    public void fetchAllMemberGameResultAnalysis() throws Exception {
        //given
        Pageable page = PageRequest.of(0, 10);
        
        //when
        Page<AllMemberGameResultAnalysisInterface> result = gameResultService.fetchAllMembersGameResultAnalysis(page);
        
        //then
        assertThat(result.getContent().size()).isEqualTo(10);
        assertThat(result.getContent().get(0).getWin()).isEqualTo(1L);
        assertThat(result.getContent().get(1).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(2).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(3).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(4).getLose()).isEqualTo(4L);
        assertThat(result.getContent().get(2).getRate()).isEqualTo(20.0);
        assertThat(result.getContent().get(2).getLastjointime()).isBefore(LocalDateTime.now());

    }

}

페이징 작업을 수행하기 위해 Pageable 구현체인 PageRequest를 활용하여 테스트한 결과, 이상 없이 원하는 비즈니스 로직을 구현할 수 있었습니다.

 

5. 한계 및 DataJpa 장점

실제 Native Query를 작성하는 과정에서 많은 에러가 발생하였습니다.

기본적인 컬럼명이 맞지 않아 에러가 발생하였고, 자바에서 추구하는 camel case 용법이 아니라, 데이터베이스에서 사용하는 snake case 방식으로 직접 쿼리를 작성해야 하다 보니 Dto 역할을 하는 interface의 메서드와 매핑이 되지 않는 문제가 발생하였습니다. 또한, 컴파일 시점에 에러를 확인할 수 없어서 테스트를 돌리는 과정을 반복하는 문제가 발생하였습니다.

 

DataJpa에서 제공하는 @Query를 활용하여, JPQL을 작성하니, 앞에서 발생한 에러를 해결할 수 있었고, Wrapper로 선언한 인터페이스가 nativeQuery에서는 camel case를 적용하기 어려웠는데, 인터페이스명까지 camelCase 형태로 mapping 될 수 있었습니다. 마지막에 쿼리가 나가는 것을 확인하면 다음과 같습니다.

 

select
        gameresult0_.join_member_id as col_0_0_,
        sum(case 
            when gameresult0_.result='WIN' then 1 
            else 0 
        end) as col_1_0_,
        sum(case 
            when gameresult0_.result='LOSE' then 1 
            else 0 
        end) as col_2_0_,
        round(sum(case 
            when gameresult0_.result='WIN' then 1 
            else 0 
        end)/(count(gameresult0_.result)*1.0)*100,
        1) as col_3_0_,
        max(gameresult0_.modified_at) as col_4_0_ 
    from
        gameresult gameresult0_ 
    inner join
        member member1_ 
            on (
                gameresult0_.join_member_id=member1_.member_id
            ) 
    group by
        gameresult0_.join_member_id limit ?

 

처음 글을 작성하였을 때에는, JPQL 작성 시 에러가 났었는데, JPQL도 인터페이스를 Dto로 받으니 에러가 해결되었습니다.!

역시 JPA는 정말 대단하고 아름다운 것 같습니다.ㅎㅎ!

읽어주셔서 감사드립니다.!