설정만 해주면 된다.

내 어플리케이션은 application.yml에서 설정 파일을 가지고 있음.

show-sql: true

hibernate.format_sql: true

​

설정 해 주면 된다.

​https://napsis.blog.me/221687528772

상황(cause)

JPA로 잘 사용하다가 쿼리를 짜게 되었다.

Need a customized query.

문제(problem)

기존 테이블에 없는 컬럼을 만들어서 리턴을 해주고 싶은데 JPA를 사용하면 불가능하다.

I want to return a custom column but JPA does not assist it if a table does not have a column.

해결

NamedNativeQuery를 사용.

Using NamedNativeQuery.

Entity Table

위와 같이 Transient 어노테이션이 있는 replyCount를 리턴에 사용하고 싶었다..

찾아본 결과 @SqlResultSetMapping,@ConstructorResult, @NamedNativeQuery 를 사용하면 된다.

사용예)

/**
 * A Message.
 */
@Entity
@Table(name = "message")
@NamedNativeQuery(
    name = "Message.findAllBy",
    query = "SELECT id, title, (...) as replyCount from message where title like concat('%', :title , '%')"
    ,resultSetMapping = "Message.findAllBy"
)
@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "Message.findAllBy",
        classes = {
            @ConstructorResult(
                targetClass = MessageThread.class,
                columns = {
                    @ColumnResult(name = "id",type = Long.class),
                    @ColumnResult(name = "title", type = String.class),
                    @ColumnResult(name = "replyCount", type = Integer.class)
                }
            )
        }
    )
})

public class Message implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    @Enumerated(EnumType.STRING)
    @Column(name = "type", nullable = false)
    private MessageType type;

    @Size(max = 1000)
    @Column(name = "title", length = 1000)
    private String title;

    @Column(name = "is_deleted")
    private Boolean isDeleted;

    @Size(max = 10000)
    @Column(name = "content", length = 10000)
    private String content;

    @Column(name = "created_date")
    private ZonedDateTime createdDate;

    @Column(name = "is_noticed")
    private Boolean isNoticed;

    @Column(name = "updated_date")
    private ZonedDateTime updatedDate = ZonedDateTime.now();

    @Transient
    private Integer replyCount;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    .... Getter , Setter.....

}

/**
 * A Message Thread.
 */

public class MessageThread implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;

    private MessageType type;

    private String title;

    private Boolean isDeleted;

    private String content;

    private ZonedDateTime createdDate;

    private Boolean isNoticed;

    private ZonedDateTime updatedDate = ZonedDateTime.now();

    private Integer replyCount;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }


    .... Getter , Setter.....


    public MessageThread (Long id, String title, Integer replyCount){
        this.id = id; this.title = title;
        this.replyCount = replyCount;
    }

}

여기서 중요한 부분이 생성자다.

public MessageThread (Long id, String title, Integer replyCount){
        this.id = id; this.title = title;
        this.replyCount = replyCount;
    }

리턴을 원하는 컬럼을 생성자에 넣어줘야 받아올 수 있다.

Repository에서 파라미터 등을 넣어서 불러주고,

public interface MessageRepository extends JpaRepository<Message, Long> {
List<MessageThread> findAllBy(@Param("title") String title);
}

Test코드를 돌려 보면,

@Rollback
@Transactional
public class MessageServiceTest extends TestHelper {
    @Autowired
    private MessageRepository messageThreadRepository;

    @Test
    public void testGetListPlayerMessage() {

        List<MessageThread> result = messageThreadRepository.findAllBy("nice");
        result.forEach(p->{
            System.out.println("replyCount:" + p.getReplyCount());
        });
    }
}

결과

replyCount:1
replyCount:1
replyCount:1
replyCount:8
replyCount:6
replyCount:2
replyCount:2
replyCount:2

유의할 점

NamedNativeQuery의 name, resultSetMapping, SqlResultSetMapping의 name과 repository의 함수명은 통일해 주는게 정신건강에 좋다.

https://blog.naver.com/napsis/221455599089

Environment]

Spring - boot

JPA

MariaDB

DB 설정이 utf8mb4라면 스프링 설정을 체크해보시길... application.yml을 사용해서 위와 같습니다.

If your DB setting is utf8mb4, you check spring setting.

I added hikari setting - connectionInitSql.

<200 OK,{id=11111, senderId=trusted-server, brandId=MYBRAND, type=TEMPLATE, senderIp=192.168.0.1, templateId=1017, players=[{id=1270218, playerId=newplayer, brandId= MYBRAND, isRead=null, messageId=11111}], messageParams=[{id=745210, name=nickname, value=class, messageId=11111}, {id=745211, name=BonusAmount, value=1, messageId=11111}]},{X-Content-Type-Options=[nosniff], X-XSS-Protection=[1; mode=block], Cache-Control=[no-cache, no-store, max-age=0, must-revalidate], Pragma=[no-cache], Expires=[0], X-Application-Context=[Hermes:swagger,dev:8080], Content-Type=[application/json;charset=UTF-8], Transfer-Encoding=[chunked], Date=[Thu, 22 Nov 2018 17:27:06 GMT]}>

Object responseEntity = messageService.sendMessageRelease(new Long(3));
ObjectWriter ow = new ObjectMapper().writer().withDefaultPrettyPrinter();
String json = ow.writeValueAsString(responseEntity);

JsonNode resultObjectList = bpJsonHttpMessageConverter.getObjectMapper().readTree(json);

{

  "headers" : {

    "X-Content-Type-Options" : [ "nosniff" ],

    "X-XSS-Protection" : [ "1; mode=block" ],

    "Cache-Control" : [ "no-cache, no-store, max-age=0, must-revalidate" ],

    "Pragma" : [ "no-cache" ],

    "Expires" : [ "0" ],

    "X-Application-Context" : [ "Hermes:swagger,dev:8080" ],

    "Content-Type" : [ "application/json;charset=UTF-8" ],

    "Transfer-Encoding" : [ "chunked" ],

    "Date" : [ "Thu, 22 Nov 2018 17:27:06 GMT" ]

  },

  "body" : {

    "id" : 11111,

    "senderId" : "trusted-server",

    "brandId" : "MYBRAND",

    "type" : "TEMPLATE",

    "senderIp" : "192.168.0.1",

    "templateId" : 1017,

    "players" : [ {

      "id" : 1270218,

      "playerId" : "newplayer",

      "brandId" : "MYBRAND",

      "isRead" : null,

      "messageId" : 11111


    } ],

    "messageParams" : [ {

      "id" : 745210,

      "name" : "nickname",

      "value" : "class",

      "messageId" : 11111


    }, {

      "id" : 745211,

      "name" : "BonusAmount",

      "value" : 1,

      "messageId" : 11111

    } ]

  },

  "statusCode" : "OK",

  "statusCodeValue" : 200


}

import lombok.extern.slf4j.Slf4j;

import org.apache.http.HttpStatus;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.annotation.Rollback;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.test.web.servlet.MockMvc;

import org.springframework.test.web.servlet.ResultActions;

import org.springframework.test.web.servlet.request.MockHttpServletRequestBuilder;

import org.springframework.test.web.servlet.setup.MockMvcBuilders;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.web.context.WebApplicationContext;

import static org.assertj.core.api.Assertions.assertThat;

import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.delete;



@Slf4j

@SpringBootTest

@RunWith(SpringRunner.class)

@Rollback

@Transactional

public class PlayerControllerTest {



    @Autowired

    private WebApplicationContext context;



    private MockMvc mockMvc;



    @Before

    public void setup() {

        mockMvc = MockMvcBuilders.webAppContextSetup(context).build();

    }



    @Test

    public void testDeleteAll() throws Exception {

        String Authorization = "cG1fZGV1OmZwZW15a3FoZTA==";

        MockHttpServletRequestBuilder request = delete("/api/url").header("Authorization", Authorization);



        ResultActions result = mockMvc.perform(request);

        assertThat(result.andReturn().getResponse().getStatus()).isEqualTo(HttpStatus.SC_OK);

        assertThat(result.andReturn().getResponse().getContentAsString()).isNotEmpty();

    }



    @Test

    public void testDeleteAll_WithInvalidKey() throws Exception {

        String Authorization = "invalidAccessKey";

        MockHttpServletRequestBuilder request = delete("/api/url").header("Authorization", Authorization);



        ResultActions result = mockMvc.perform(request).accept(TestUtil.;

        assertThat(result.andReturn().getResponse().getStatus()).isEqualTo(HttpStatus.SC_UNAUTHORIZED);

    }



    @Test

    public void testDeleteAll_NoKey() throws Exception {

        MockHttpServletRequestBuilder request = delete("/api/url");



        ResultActions result = mockMvc.perform(request).accept(TestUtil.APPLICATION_JSON_UTF8)
            .contentType(MediaType.APPLICATION_JSON).content(TestUtil.convertObjectToJsonBytes(messageIds_02)))
            .andExpect(status().isBadRequest());;

        assertThat(result.andReturn().getResponse().getStatus()).isEqualTo(HttpStatus.SC_BAD_REQUEST);

    }


}

@Profile({"default", "local", "dev", "stage"})

로 Profile을 설정해 주면 된다.

1. maven repository를 설정해주자.

<!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger-ui -->
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger-ui</artifactId>
    <version>2.7.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger2</artifactId>
    <version>2.7.0</version>

swagger-ui 가 없으면 JSon으로 봐야 하므로 넣어준다. 없어도 된다.

이제 Docket를 빈으로 등록해야한다.

config 패키지를 만들어서 SwaggerConfiguration을 만들어 주었다.

이제 간단히 돌려보면, mainController의 내용..

아래는 swagger 화면이다.

보충을 더 하자면, @ApiOperation, @ApiParam, @ApiModel이 있다.

이걸 추가해 준뒤, 화면을 가보면,

이렇게 내용이 추가된다.

나머지는 검색해 보면 나옴.... ㅎ