[Exception] could not extract ResultSet; SQL [n/a];

JPA  에서  NativeQuery를 실행해서 DTO Mapping 을 시도 했지만, 아래와 같은 오류가 발생했습니다.

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

 오류의 발생 - 첫번째 시도

CommentRepository.java

package octopus.bbs.comment.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import octopus.bbs.comment.dto.CommentDto;
import octopus.bbs.comment.dto.TCommentM;

// https://hackids.tistory.com/129 참조
//
// @Repository : JpaRepository를 사용하면 @Repository를 사용하지 않아도 됨.
public interface CommentRepository extends JpaRepository<TCommentM, Long> {
    String SELECT_BY_POSTID_DTO = "select " +
            " a.id, a.post_id, a.contents, " +
            " a.crt_id, u1.user_nm as crt_nm, a.crt_dt, " +
            " a.mdf_id, u2.user_nm as mdf_nm, a.mdf_dt " +
            " from t_comment_m a " +
            " left join t_user_m u1 " +
            " on a.crt_id = u1.user_id " +
            " left join t_user_m u2 " +
            " on a.mdf_id = u2.user_id " +
            " where a.post_id = ?1";

    @Query(value = SELECT_BY_POSTID_DTO, nativeQuery = true)
    List<CommentDto> findAllByPostId(Long postId);

}

아래와 같은 Exception이 발생하였습니다.

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [octopus.bbs.comment.dto.CommentDto]

"쿼리 결과에 대해  Convert를 할 수 없다"라는 메시지 입니다. 일반적으로 JPA는 Entity 객체를 받아야 하는데, DTO 객체를 리턴했으니 오류가 발생하는 것이 이해는 됩니다.

---

오류의 발생 - 두번째 시도

CommentRepository.java

package octopus.bbs.comment.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import octopus.bbs.comment.dto.CommentDto;
import octopus.bbs.comment.dto.TCommentM;

// https://hackids.tistory.com/129 참조
//
// @Repository : JpaRepository를 사용하면 @Repository를 사용하지 않아도 됨.
public interface CommentRepository extends JpaRepository<TCommentM, Long> {
    String SELECT_BY_POSTID_DTO = "select " +
            " new octopus.bbs.comment.dto.CommentDto( a.id, a.post_id, a.contents, " +
            " a.crt_id, u1.user_nm as crt_nm, a.crt_dt, " +
            " a.mdf_id, u2.user_nm as mdf_nm, a.mdf_dt ) " +
            " from t_comment_m a " +
            " left join t_user_m u1 " +
            " on a.crt_id = u1.user_id " +
            " left join t_user_m u2 " +
            " on a.mdf_id = u2.user_id " +
            " where a.post_id = ?1";

    @Query(value = SELECT_BY_POSTID_DTO, nativeQuery = true)
    List<CommentDto> findAllByPostId(Long postId);

}

CommentDto.java

package octopus.bbs.comment.dto;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@Builder
public class CommentDto {
    private Long   id;
    private Long   postId;
    private String contents;
    
    private String crtNm;
    private String mdfNm;
    
    private String modalWriter;
    private String modalContent;
    
    private String        crtId; // 생성자
    private LocalDateTime crtDt; // 생성일자
    private String        mdfId; // 수정자
    private LocalDateTime mdfDt; // 수정일
    
    private String createDate; // 생성일자
    private String modifyDate; // 수정일
    
    public void parseDate(LocalDateTime crtDt, LocalDateTime mdfDt) {
        this.createDate = crtDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        this.modifyDate = mdfDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    
    public TCommentM toEntity() {
        return TCommentM.builder().postId(postId).contents(contents)
                .crtId(getCrtId()).mdfId(getMdfId()).build();
    }
        
    public CommentDto(TCommentM comment) {
        this.id       = comment.getId();
        this.postId   = comment.getPostId();
        this.contents = comment.getContents();
        this.crtId    = comment.getCrtId();
        this.crtDt    = comment.getCrtDt();
        this.mdfId    = comment.getMdfId();
        this.mdfDt    = comment.getMdfDt();
    }
}

이번에는 객체는 Convert 하기 위해 객체를 생성해야 한다고 합니다.

https://velog.io/@youmakemesmile/Spring-Data-JPA-JPQL-%EC%82%AC%EC%9A%A9-%EB%B0%A9%EB%B2%95Query-nativeQuery-DTO-Mapping-function

[Spring Data JPA] JPQL 사용 방법(@Query & nativeQuery & DTO Mapping & function)

이번에는 아래와 같은 Exception이 발생하였습니다.

SQL Error: 0, SQLState: 42601

오류: 구문 오류, "." 부근

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

ResultSet을 생성하지 못해서 발생하는 오류로 보여집니다. 쿼리의 결과를 CommentDto의 Constructor를 생성하지 못해서 발생하는 오류로 보여집니다. 그래서 아래와 같이 변경했습니다.

사실 "구문 오류" 라고 보여지는 이유는 모르겠습니다.

CommentDto.java

package octopus.bbs.comment.dto;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@Builder
public class CommentDto {
    private Long   id;
    private Long   postId;
    private String contents;
    
    private String crtNm;
    private String mdfNm;
    
    private String modalWriter;
    private String modalContent;
    
    private String        crtId; // 생성자
    private LocalDateTime crtDt; // 생성일자
    private String        mdfId; // 수정자
    private LocalDateTime mdfDt; // 수정일
    
    private String createDate; // 생성일자
    private String modifyDate; // 수정일
    
    public void parseDate(LocalDateTime crtDt, LocalDateTime mdfDt) {
        this.createDate = crtDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        this.modifyDate = mdfDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    
    public TCommentM toEntity() {
        return TCommentM.builder().postId(postId).contents(contents)
                .crtId(getCrtId()).mdfId(getMdfId()).build();
    }
    
    public CommentDto(Long id, Long postId, String contents 
            , String crtId, String crtNm, LocalDateTime crtDt
            , String mdfId, String mdfNm, LocalDateTime mdfDt ) {
        this.id       = id;
        this.postId   = postId;
        this.contents = contents;
        this.crtId    = crtId;
        this.crtNm    = crtNm;
        this.mdfId    = mdfId;
        this.mdfNm    = mdfNm;
        parseDate(crtDt, mdfDt);
    }
    
    public CommentDto(TCommentM comment) {
        this.id       = comment.getId();
        this.postId   = comment.getPostId();
        this.contents = comment.getContents();
        this.crtId    = comment.getCrtId();
        this.crtDt    = comment.getCrtDt();
        this.mdfId    = comment.getMdfId();
        this.mdfDt    = comment.getMdfDt();
    }
}

결과는 변하지 않았습니다.

SQL Error: 0, SQLState: 42601

오류: 구문 오류, "." 부근

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

아무래도 이건 뭔가 잘못 된것 같습니다. 일단..JpaRepository를 상속받는 interface 객체인 CommentRepository 객체의 NativeQuery 에서 객체 Convert 가 이루어지지 않는 것 같습니다. 이건 확인 필요..!!!

---

그래서 다른 방법을 찾았습니다. 첫번째 오류 발생 시 Tuple 이라는 단어가 보였는데.. 이걸 이용할 수 있을 것 같습니다.

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [octopus.bbs.comment.dto.CommentDto]

CommentRepository.java

package octopus.bbs.comment.repository;

import java.util.List;

import javax.persistence.Tuple;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import octopus.bbs.comment.dto.TCommentM;

// https://hackids.tistory.com/129 참조
//
// @Repository : JpaRepository를 사용하면 @Repository를 사용하지 않아도 됨.
public interface CommentRepository extends JpaRepository<TCommentM, Long> {
    String SELECT_BY_POSTID_DTO = "select " +
            " a.id, a.post_id, a.contents, " +
            " a.crt_id, u1.user_nm as crt_nm, a.crt_dt, " +
            " a.mdf_id, u2.user_nm as mdf_nm, a.mdf_dt " +
            " from t_comment_m a " +
            " left join t_user_m u1 " +
            " on a.crt_id = u1.user_id " +
            " left join t_user_m u2 " +
            " on a.mdf_id = u2.user_id " +
            " where a.post_id = ?1";
  
    @Query(value = SELECT_BY_POSTID_DTO, nativeQuery = true)
    List<Tuple> findAllByPostId(Long postId);

}

CommentDto.java

package octopus.bbs.comment.dto;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@Builder
public class CommentDto {
    private Long   id;
    private Long   postId;
    private String contents;
    
    private String crtNm;
    private String mdfNm;
    
    private String modalWriter;
    private String modalContent;
    
    private String        crtId; // 생성자
    private LocalDateTime crtDt; // 생성일자
    private String        mdfId; // 수정자
    private LocalDateTime mdfDt; // 수정일
    
    private String createDate; // 생성일자
    private String modifyDate; // 수정일
    
    public void parseDate(LocalDateTime crtDt, LocalDateTime mdfDt) {
        this.createDate = crtDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        this.modifyDate = mdfDt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    
    public TCommentM toEntity() {
        return TCommentM.builder().postId(postId).contents(contents)
                .crtId(getCrtId()).mdfId(getMdfId()).build();
    }
    
    public CommentDto(Long id, Long postId, String contents 
            , String crtId, String crtNm, LocalDateTime crtDt
            , String mdfId, String mdfNm, LocalDateTime mdfDt ) {
        this.id       = id;
        this.postId   = postId;
        this.contents = contents;
        this.crtId    = crtId;
        this.crtNm    = crtNm;
        this.mdfId    = mdfId;
        this.mdfNm    = mdfNm;
        parseDate(crtDt, mdfDt);
    }
    
    public CommentDto(TCommentM comment) {
        this.id       = comment.getId();
        this.postId   = comment.getPostId();
        this.contents = comment.getContents();
        this.crtId    = comment.getCrtId();
        this.crtDt    = comment.getCrtDt();
        this.mdfId    = comment.getMdfId();
        this.mdfDt    = comment.getMdfDt();
    }
}

CommentService.java

 

 

package octopus.bbs.comment.service;

import java.sql.Timestamp;
import java.util.Collections;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

import javax.persistence.Tuple;
import javax.transaction.Transactional;

import org.modelmapper.ModelMapper;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import octopus.base.config.ModelMapperConfig;
import octopus.base.model.Pagination;
import octopus.base.model.PagingListResult;
import octopus.bbs.comment.dto.CommentDto;
import octopus.bbs.comment.dto.CommentSearchDto;
import octopus.bbs.comment.dto.TCommentM;
import octopus.bbs.comment.repository.CommentRepository;

@Service
@RequiredArgsConstructor
@Slf4j
public class CommentService {
    
    private final CommentRepository commentRepository;
    private final ModelMapperConfig modelMapperConfig;
    
    /**
     * 댓글 리스트 조회
     * 
     * @param postId - 게시글 번호 (FK)
     * @return 특정 게시글에 등록된 댓글 리스트
     */
    public List<CommentDto> findAllComment(final Long postId) {
        
        List<Tuple>      tupleComments = commentRepository.findAllByPostId(postId);
        List<CommentDto> comments      = tupleComments.stream()
                .map(t -> new CommentDto(
                        Long.valueOf(t.get(0, Integer.class)),
                        Long.valueOf(t.get(1, Integer.class)),
                        t.get(2, String.class),
                        t.get(3, String.class),
                        t.get(4, String.class),
                        (t.get(5, Timestamp.class)).toLocalDateTime(),
                        t.get(6, String.class),
                        t.get(7, String.class),
                        (t.get(8, Timestamp.class)).toLocalDateTime()))
                .collect(Collectors.toList());
        
        log.debug("comments :: {}", comments);
        
        return comments;
    }
}

정상적으로 조회되네요.

select

a.id,

a.post_id,

a.contents,

a.crt_id,

u1.user_nm as crt_nm,

a.crt_dt,

a.mdf_id,

u2.user_nm as mdf_nm,

a.mdf_dt

from

t_comment_m a

left join

t_user_m u1

on a.crt_id = u1.user_id

left join

t_user_m u2

on a.mdf_id = u2.user_id

where

a.post_id = 30

Connection ID:231 | Excution Time:1 ms

Excution Time:1 ms

Call Stack :

--------------------------------------

DEBUG 23-06-16 01:57:480[http-nio-9999-exec-1] [▶ octopus.bbs.posts.service.BoardService.delete ◀][209]: - comments :: [CommentDto(super=octopus.bbs.comment.dto.CommentDto@b2895e42, id=1, postId=30, contents=댓글1, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@5d39880c, id=2, postId=30, contents=댓글2, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@7e9b1d6, id=3, postId=30, contents=댓글3, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@b299dba0, id=4, postId=30, contents=댓글4, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@5d4a056a, id=5, postId=30, contents=댓글5, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@7fa2f34, id=6, postId=30, contents=댓글6, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@b2aa58fe, id=7, postId=30, contents=댓글7, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@5d5a82c8, id=8, postId=30, contents=댓글8, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@80aac92, id=9, postId=30, contents=댓글9, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@2f14a83, id=10, postId=30, contents=댓글10, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39), CommentDto(super=octopus.bbs.comment.dto.CommentDto@ada1744d, id=11, postId=30, contents=댓글11, crtNm=어드민, mdfNm=어드민, modalWriter=null, modalContent=null, crtId=admin, crtDt=null, mdfId=admin, mdfDt=null, createDate=2023-06-16 01:57:39, modifyDate=2023-06-16 01:57:39)]

---

Tuple 을 배웠습니다.