네이티브 쿼리

쿼리를 직접 작성후 수행 가능

List<Object[]> resultList = em.createNativeQuery(
        "select u.user_id, TO_CHAR(o.order_date, 'YYYYMMDD') from user u inner join orders o on o.user_id = u.user_id"
).getResultList();

for (Object[] objects : resultList) {
    System.out.println(objects[0] + "/" + objects[1]);
}


-- 쿼리 실행 결과
    select
        u.user_id,
        TO_CHAR(o.order_date, 'YYYYMMDD') 
    from user u 
    inner join orders o 
        on o.user_id = u.user_id

 

 

쿼리 수행결과를 DTO로 생성하는 방법

Object Array로 생성

for (Object[] objects : resultList) {
    objects[0]; // object값을 DTO에 매핑하면 된다.
    objects[1]; // object값을 DTO에 매핑하면 된다.
}

ResultSetMapping으로 생성

Entity에 SqlResultSetMapping으로 DTO와 매핑될 메타 정보를 선언후 사용

Entity 내용이 불필요하게 길어지므로 사용을 추천하지 않음.

@SqlResultSetMapping(
        name="ProductOrderedMemberMapping",
        classes = @ConstructorResult(
                targetClass = ProductOrderedMemberDTO.class,
                columns = {
                        @ColumnResult(name="productId", type = Long.class),
                        @ColumnResult(name="productName", type = String.class),
                        @ColumnResult(name="price", type = Integer.class),
                        @ColumnResult(name="stockAmount", type = Integer.class),
                        @ColumnResult(name="orderId", type = Long.class),
                        @ColumnResult(name="memberId", type = Long.class),
                        @ColumnResult(name="memberName", type = String.class),
                })
)
@Entity
@Table(name = "product")
public class Product {
    // ...
}
public class ProductOrderedMemberDTO {
    // ...
    public ProductOrderedMemberDTO(Long productId, String productName, int price, int stockAmount,
                                   Long orderId, Long memberId, String memberName) {
        this.productId = productId;
        this.productName = productName;
        this.price = price;
        this.stockAmount = stockAmount;
        this.orderId = orderId;
        this.memberId = memberId;
        this.memberName = memberName;
   }
    // ...
}

// 결과 매핑
    String sql = "SELECT \"product\".\"product_id\" AS productId, \"product\".\"name\" AS productName, \"product\".\"price\", \n" +
            "    \"product\".\"stock_amount\" AS stockAmount, \"order\".\"order_id\" AS orderId,\n" +
            "    \"member\".\"member_id\" AS memberId, \"member\".\"name\" AS memberName\n" +
            "FROM \"product\" \n" +
            "    LEFT JOIN \"order\" \n" +
            "        ON \"product\".\"product_id\" = \"order\".\"PRODUCT_ID\"\n" +
            "    LEFT JOIN \"member\" \n" +
            "        ON \"order\".\"MEMBER_ID\" = \"member\".\"member_id\"";
    Query nativeQuery = em.createNativeQuery(sql, "ProductOrderedMemberMapping");
    List<ProductOrderedMemberDTO> products = nativeQuery.getResultList();

 

QLRM(Query Language Result Mappger)

의존성 주입

compile group: 'com.zaxxer', name: 'HikariCP', version: '3.3.1'

DTO 생성

@ToString
@Getter
@AllArgsConstructor
public class UserOrderDate {
    private BigInteger userId;  // Long 타입 사용시 매핑 에러가 발생한다..
    private String orderDate;
}

쿼리 수행 후 DTO 매핑

Query nativeQuery = em.createNativeQuery(
        "select u.user_id, TO_CHAR(o.order_date, 'YYYYMMDD') from user u inner join orders o on o.user_id = u.user_id"
);
JpaResultMapper jpaResultMapper = new JpaResultMapper();
jpaResultMapper.list(nativeQuery, UserOrderDate.class).forEach(userOrderDate -> System.out.println(userOrderDate.toString()));