[JPA] JPQL @Query에 각 DB function() 사용해보기

JPQL에서 기본적으로 지원하는 function만으로는
 비지니스의 조회를 해결하기는 한계가 존재한다.

nativeQuery를 사용하여 fetch까지 할 수 있는
방법을 찾다가 function() 을 찾게되었다.

---

function을 쓰게 된 이유

• AccountUser의 엔티티는 회원을 생일을 LocalDate로 저장되어 있다.

@Getter
@Setter
@Entity
public class AccountUser {

	@Id
	private Long id;
	
	@NotNull
	private LocalDate birth;
}

• 회원 목록 조회시 생년월일 중 월만 맏아서 조회를 하게 되는 기획이 있다.
• MSSQL을 사용하고있어서  SUBSTRING(CONVERT(VARCHAR(8), birth, 112), 5, 2))  CONVERT를 112(20221010) 변환 후 SUBSTRING으로 '10' 을 추출할 예정이였다.

SELECT *
FROM ACCOUNT_USER
WHERE SUBSTRING(CONVERT(VARCHAR(8), birth, 112), 5, 2)) = 10

• 하지만.... accountUser와 @OneToOne 로 필드로 있는 엔티티가 있어서 결국 n+1 문제가 발생해서 native query 사용하는 조건에 fetch join이 필요한 상황이다.

 

문제해결

• 찾아본 결과 function() 을 사용해 JPQL에서 hibernate에 등록된 각 DataBase의 Dialect에 정의된 function을 사용하는 방식이다. 즉, 아래 같은 쿼리를 작성

@Query("SELECT AU " +
        "FROM AccountUser AU " +
        "LEFT JOIN FETCH AU.{entityName} A " +
        "WHERE SUBSTRING(function('CONVERT', varchar, AU.birth, 112), 5,2) = :month")
	List<AccountUser> findByBirthMonthLike(String month);

• 아래와 같은 결과로 쿼리가 생성되었다.

   ...생력
    left outer join
        ...생략
    where
        substring(CONVERT(VARCHAR(8),accountuse0_.birth,112), 5, 2)=?

 

Comment

이 방법을 찾았을 땐 기분이 매우 좋았고 내가 기특했지만... 결국은 기본적인걸 몰랐던 것이다.. 하지만 이렇게 한 발자국씩 앞으로 나아가다보면, 빛을 보지 않을까?! 라는 생각이 든다.

 

더 나아가

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

위 블로그에서는  더 자세히 나와 있다. 그중에 function을 모듈화(?) 할 수 있다다고 하는데 아래 내용입니다.

``` youmakemesmile 님의 글 내용 중````

위에서 이야기한것과 같이 JPQL를 사용하게되는 이유중 하나는 SQL Function이다. JPQL에서는 기본적으로 select 구문의 max, min, count, sum, avg를 제공하며 기본 function으로는 COALESCE, LOWER, UPPER등을 지원하며 자세한 Function은 다음 문서를 참고하면 된다.

public interface UserRepository extends JpaRepository<User, String> {
    @Query(value = "select max(user.id) " +
            "from User user " +
            "where user.deptId is not null")
    String findMaxUserId();
}

이러한 JPQL에서 기본적으로 지원하는 ANSI Query Function만으로는 비지니스의 조회를 해결하기는 한계가 존재한다. MSA에서는 데이터 저장 방법이 각 서비스에 맞게 변화될수 있게 설계되어야 한다고 하지만 현실적으로는 성능과 비용을 생각할때 DataBase에서 제공하는 function을 사용하지 않을 수 없다.

DataBase Function를 사용하는 방식은 JPQL에서 function()을 활용하여 hibernate에 등록된 각 DataBase의 Dialect에 정의된 function을 사용하는 방식이다.

public interface UserRepository extends JpaRepository<User, String> {

    @Query(value = "select function('date_format', :date, '%Y/%m/%d') " +
            "from User user ")
    String findNow(@Param("date") LocalDateTime date);
}

하지만 hinbernate에서 기본적으로 등록되는 function에서도 누락되는 function이 존재한다. 이러한 경우 이전에는 Dialect를 상속받아 구현하는 방식을 사용하였으나 현재에는 MetadataBuilderContributor의 구현체를 구현하는 방식을 제공하고있다.

public class MyMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("JSON_EXTRACT", new StandardSQLFunction("JSON_EXTRACT", StringType.INSTANCE))
                .applySqlFunction("JSON_UNQUOTE", new StandardSQLFunction("JSON_UNQUOTE", StringType.INSTANCE))
                .applySqlFunction("STR_TO_DATE", new StandardSQLFunction("STR_TO_DATE", LocalDateType.INSTANCE))
                .applySqlFunction("MATCH_AGAINST", new SQLFunctionTemplate(DoubleType.INSTANCE, "MATCH (?1) AGAINST (?2 IN BOOLEAN MODE)"));
    }
}

applySqlFunction의 첫번째 파라미터는 JPQL에서 function("함수명") 함수명에 해당하는 등록명이다.

StandardSQLFunction은 기본적인 함수를 등록하기위한 Class로 생성자의 첫번째 파마리터는 실제 DataBase Function명이며 두번째 파라미터는 function의 리턴 타입이다. StandardSQLFunction의 경우 파라미터는 함수에 순서에 맞게 JPQL function('등록 함수명', 파라미터1, 파리마터2 ...) 정의하여 사용하면 된다.

SQLFunctionTemplate은 문법이 존재하는 function를 등록할때 사용가능하며 첫번째 파라미터가 function의 리턴타입이며 두번째 파라미터가 function이다. ?1, ?2와 같이 명시하여 JPQL function()에서 전달되는 파라미터의 순서대로 파싱되어 SQL이 생성된다.

public interface UserRepository extends JpaRepository<User, String> {
    @Query(value = "select user " +
            "from User user " +
            "where function('JSON_UNQUOTE', function('JSON_EXTRACT',user.registerInfo,'$.id')) = 'admin' ")
    List<User> findAllByRegisterAdmin();
}

public interface UserRepository extends JpaRepository<User, String> {
    @Query(value = "select user " +
            "from User user " +
            "where function('MATCH_AGAINST', user.name, :name) >0 ")
    List<User> findAllByName(@Param("name") String name);
}

```