Develop/JPA
[자바 ORM 표쥰 JPA 프로그래밍] 24일차 - QueryDSL(2)
자라선
2021. 11. 15. 23:03
예시로 사용된 엔티티
더보기
@Entity
public class Item {
@Id
@GeneratedValue
@Column(name = "ITEM_ID")
private Long id;
private String name; //이름
private int price; //가격
private int stockQuantity; //재고수량
//Getter, Setter
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getStockQuantity() {
return stockQuantity;
}
public void setStockQuantity(int stockQuantity) {
this.stockQuantity = stockQuantity;
}
@Override
public String toString() {
return "Item{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
'}';
}
}
@Entity
@Table(name = "ORDERS")
public class Order {
@Id
@GeneratedValue
@Column(name = "ORDER_ID")
private Long id;
@ManyToOne
@JoinColumn(name = "MEMBER_ID")
private Member member; //주문 회원
@OneToMany(mappedBy = "order")
private List<OrderItem> orderItems = new ArrayList<OrderItem>();
private Date orderDate; //주문시간
public void addOrderItem(OrderItem orderItem) {
orderItems.add(orderItem);
orderItem.setOrder(this);
}
//Getter, Setter
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Member getMember() {
return member;
}
public List<OrderItem> getOrderItems() {
return orderItems;
}
public void setOrderItems(List<OrderItem> orderItems) {
this.orderItems = orderItems;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderDate=" + orderDate +
'}';
}
@Entity
@Table(name = "ORDER_ITEM")
public class OrderItem {
@Id
@GeneratedValue
@Column(name = "ORDER_ITEM_ID")
private Long id;
@ManyToOne
@JoinColumn(name = "ITEM_ID")
private Item item; //주문 상품
@ManyToOne
@JoinColumn(name = "ORDER_ID")
private Order order; //주문
private int orderPrice; //주문 가격
private int count; //주문 수량
//Getter, Setter
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Item getItem() {
return item;
}
public void setItem(Item item) {
this.item = item;
}
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
public int getOrderPrice() {
return orderPrice;
}
public void setOrderPrice(int buyPrice) {
this.orderPrice = buyPrice;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
@Override
public String toString() {
return "OrderItem{" +
"id=" + id +
", buyPrice=" + orderPrice +
", count=" + count +
'}';
}
}1. 검색조건
검색조건이 너무많아서 예시 몇개만 작성한다.
하나하나 설명하기 너무 힘들다..
JPAQuery query = new JPAQuery(em);
QItem item = QItem.item;
List<Item> list = query.from(item)
.where(item.name.eq("좋은상품").and(item.price.gt(20000))) // price > 20000
.list(item);
/**
* select
* item
* from
* Item item
* where
* item.name = ?1
* and item.price > ?2
*/
QItem item = QItem.item;
List<Item> list = query.from(item)
.where(item.price.between(10000, 20000) // between 10000 and 20000
.and(item.name.contains("상품1")) // like '%상품1%'
.and(item.name.startsWith("고급"))) // like '고급%'
.list(item);
QItem item = QItem.item;
List<Item> list = query.from(item)
.where(item.price.gt(20000))
.orderBy(item.price.desc(), item.stockQuantity.asc())
.offset(10).limit(20) // 페이징 처리
.list(item);
/**
* select
* item
* from
* Item item
* where
* item.price > ?1
* order by
* item.price desc,
* item.stockQuantity asc
*/
QOrder order = QOrder.order;
QMember member = QMember.member;
QOrderItem orderItem = QOrderItem.orderItem;
query.from(order)
.join(order.member, member) // 2번째 파라미터는 별칭을 지정
.leftJoin(order.orderItems, orderItem)
.list(order);
/**
* select
* order1
* from
*
* Order order1 inner join
* order1.member as member1
* left join
* order1.orderItems as orderItem
*/
QOrder order = QOrder.order;
QMember member = QMember.member;
QOrderItem orderItem = QOrderItem.orderItem;
query.from(order)
.leftJoin(order.orderItems, orderItem)
.on(orderItem.count.gt(2))
.list(order);
/**
* select
* order1
* from
*
* Order order1 left join
* order1.orderItems as orderItem with orderItem.count > ?1
*/
query.from(order)
.leftJoin(order.orderItems, orderItem).fetch() // 패치조인 적용
.list(order);
서브쿼리 적용
QItem item = QItem.item;
QItem itemSub = new QItem("itemSub"); // 서브쿼리용 별칭을 생성
query.from(item)
.where(item.price.eq(
new JPASubQuery().from(itemSub).unique(itemSub.price.max())
)) // 서브쿼리 적용
.list(item);
/**
* select
* item
* from
* Item item
* where
* item.price = (
* select
* max(itemSub.price)
* from
* Item itemSub
* )
*/ query.from(item)
.where(item.in(
new JPASubQuery().from(itemSub)
.where(item.name.eq(itemSub.name))
.list(itemSub)
)) // 여러 건 적용 서브쿼리 적용
.list(item);
DISTINCT
query.distinct().from(item)...
2. 프로젝션
select 절에 조회의 대상 컬럼을 지정하는 것을 의미한다.
QItem item = QItem.item;
List<String> result = query.from(item).list(item.name); // ITEM 엔티티의 NAME 필드만 조회
for(String name : result){
System.out.println("name = " + name);
}
여러 필드를 작성하면 Tuple 객체로 반환받는다.
List<Tuple> result = query.from(item).list(item.name, item.price);
// List<Tuple> result = query.from(item).list(new QTuple(item.name, item.price)); 같음
for (Tuple tuple : result) {
System.out.println("tuple = " + tuple.get(item.name));
System.out.println("tuple = " + tuple.get(item.price));
}
DTO를 작성하여 객체로 값을 받을 수 있다.
public class ItemDTO {
private String username;
private int price;
public ItemDTO() {
}
public ItemDTO(String username, int price) {
this.username = username;
this.price = price;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
} QItem item = QItem.item;
List<ItemDTO> result = query.from(item)
.list(Projections.bean(ItemDTO.class, item.name.as("username"), item.price)); // ItemDTO의 필드가 username 이기 때문에 as 메소드로 별칭을 맞춰줌