Spring Data JPA - 2

@Querydsl

- JPQL을 코드를 작성할 수 있도록 도와주는 빌더 API

- 소스코드로 SQL문을 문장려이 아닌 코드로 작성하기 때문에 컴파일러의 도움을 받을 수 있다.

- 소스 작성 시 오타가 발생하면 개발자엑 ㅔ오타가 있음을 바로 알려준다. 또한 동적으로 쿼리를 생성해주는 큰 장점이 있다.

 

스프링 부트 2.6.2 은 querydsl 버전 5.0.0 적용

pom.xml 안에서의 dependency 와 plugins 를 추가한다.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com</groupId>
    <artifactId>shop</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shop</name>
    <description>shop</description>
    <properties>
        <java.version>11</java.version>
        <querydsl.version>5.0.0</querydsl.version>
    </properties>
    <dependencies>
        ...
        <!-- querydsl -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        ...
    </dependencies>

    <build>
        <plugins>
            ...
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>            
            ...
        </plugins>
    </build>

</project>

 

- 메이븐에서 clean -> install -> compile 처리한다

 

- generated-sources 폴더를 sources 폴더 변경처리하여 소스코드로 인식할수 있도록 변경

 

@PersistenceContext
EntityManager em;

@Test
@DisplayName("QueryDsl select test")
public void queryDslTest(){
    this.createItemTest();
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    QItem qItem = QItem.item;
    JPAQuery<Item> query = queryFactory.selectFrom(qItem)
            .where(qItem.itemSellStatus.eq(ItemSellStatus.SELL))
            .where(qItem.itemDetail.like("%"+"테스트 상품 상세 설명"+"%"))
            .orderBy(qItem.price.desc());

    List<Item> itemList = query.fetch();

    for (Item item : itemList) {
        System.out.println(item.toString());
    }
}

 

위와 같이 해당 클래스가 필요하다고 나와서 maven에 아래와 같이 추가한다

Maven Repository: com.google.guava » guava » 31.0.1-jre (mvnrepository.com)

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>31.0.1-jre</version>
</dependency>

 

결과)

Hibernate: 
    select
        item0_.item_id as item_id1_0_,
        item0_.item_detail as item_det2_0_,
        item0_.item_nm as item_nm3_0_,
        item0_.item_sell_status as item_sel4_0_,
        item0_.price as price5_0_,
        item0_.reg_time as reg_time6_0_,
        item0_.stock_number as stock_nu7_0_,
        item0_.update_time as update_t8_0_ 
    from
        item item0_ 
    where
        item0_.item_sell_status=? 
        and (
            item0_.item_detail like ? escape '!'
        ) 
    order by
        item0_.price desc

 

Querydsl 문법은 공식문서인 아래 사이트로 대체한다. 

최신버전(5.0.0.M1) : Querydsl Reference Guide

한국어버전(4.0.1) : Querydsl - 레퍼런스 문서

5.0.0 버전업시 변경사항 : [Querydsl] 5.0.0 업데이트 변경 사항

---

- QuerydslPredicateExecutor 활용

- finBy.. 메소드명이 너무 길어지면 읽기 힘들기때문에 QuerydslPredicateExecutor 를 이용하여 조건등을 걸수있다.

public interface ItemRepository extends JpaRepository<Item, Long>, QuerydslPredicateExecutor<Item> {
    List<Item> findByItemNm(String itemNm);

    @Query("select i from Item i where i.itemDetail like %:itemDetail% order by i.price desc")
    List<Item> findByItemDetail(@Param("itemDetail") String itemDetail);

    @Query(value = "select * from Item i where i.item_detail like CONCAT('%',:itemDetail,'%') order by i.price desc", nativeQuery = true)
    List<Item> findByItemDetailByNative(@Param("itemDetail") String itemDetail);
}

 

public void createItemTest2(){
    for(int i=0; i<=5; i++) {
        Item item = new Item();
        item.setItemNm("테스트 상품"+i);
        item.setPrice(10000+i);
        item.setItemDetail("테스트 상품 상세 설명"+i);
        item.setItemSellStatus(ItemSellStatus.SELL);
        item.setStockNumber(100);
        item.setRegTime(LocalDateTime.now());
        item.setUpdateTime(LocalDateTime.now());
        itemRepository.save(item);
    }
    for(int i=6; i<=10; i++) {
        Item item = new Item();
        item.setItemNm("테스트 상품"+i);
        item.setPrice(10000+i);
        item.setItemDetail("테스트 상품 상세 설명"+i);
        item.setItemSellStatus(ItemSellStatus.SOLD_OUT);
        item.setStockNumber(0);
        item.setRegTime(LocalDateTime.now());
        item.setUpdateTime(LocalDateTime.now());
        itemRepository.save(item);
    }
}

@Test
@DisplayName("QueryDsl select test2")
public void queryDslTest2(){
    this.createItemTest2();
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    QItem item = QItem.item;
    String itemDetail = "테스트 상품 상세 설명";
    int price = 10008;
    String itemSellStat = "SOLD_OUT";

    booleanBuilder.and(item.itemDetail.like("%"+itemDetail+"%"));
    booleanBuilder.and(item.price.gt(price));

    if(StringUtils.equals(itemSellStat, ItemSellStatus.SELL)){
        booleanBuilder.and(item.itemSellStatus.eq(ItemSellStatus.SELL));
    } else if(StringUtils.equals(itemSellStat, ItemSellStatus.SOLD_OUT)){
        booleanBuilder.and(item.itemSellStatus.eq(ItemSellStatus.SOLD_OUT));
    }

    Pageable pageable = PageRequest.of(0, 5);
    Page<Item> itemPagingResult = itemRepository.findAll(booleanBuilder, pageable);
    System.out.println("total elements : "+itemPagingResult.getTotalElements());

    List<Item> resultItemList = itemPagingResult.getContent();
    for(Item resultItem : resultItemList){
        System.out.println(resultItem.toString());
    }
}

 

결과)