JPA에서 Native SQL Function 사용하는 방법 (MetadataBuilder)

JPA SQL function 사용하는 방법

How To Use Native SQL Functions in JPA Queries?

 

 

JPA 동작 방식과 Dialect

SQL은 모든 DBMS에서 공통적으로 사용하는 표준 SQL인 ANSI SQL 외에 DBMS Vender인 MS-SQL, MySQL, Oracle, PostgreSQL 등에서 제공하는 SQL이 있는데요.

각각의 DBMS Vender에서 사용되는 SQL은 문법과 함수가 조금씩 다른 경우가 있는데, 이처럼 SQL 표준에서 벗어난 특정 Vender별 기능을 Dialect(방언)이라고 합니다.

 

/*

Oracle에서는 문자열을 자르는 함수로 SUBSTR()가 사용되는 반면, MySQL에서는 SUBSTRING()도 사용할 수 있다거나, Oracle에서는 ID 값을 증가시키기 위해 Sequence를 사용하는 반면 MySQL에서는 AutoIncrement를 사용하는 등의 차이를 예로 들 수 있습니다.

*/

 

JPA의 동작 원리를 살펴보면 어플리케이션에서 직접 JDBC 레벨의 SQL을 작성하는 것이 아니라 JPA가 직접 SQL을 작성하고 실행하는데요. 때문에 사용하는 DBMS에 맞는 Dialect를 JPA에게 알려주게 되면 JPA는 해당 Dialect를 참고하여 각 DBMS에 맞는 구현체를 제공하게 됩니다.

(Dialect는 사용하는 DB의 종류와 버전에 따라 다릅니다.)

 

 

Dialect

(JPA에서는 Dialect라는 추상화된 방언 클래스를 제공하고, 각 DBMS에 맞는 구현체를 제공합니다.)

 

이러한 방식으로 인해 만약 DBMS가 MySQL에서 MariaDB로 변경되었을 때, 설정된 Dialect만 MySQL Dialect에서 MariaDB Dialect로 변경해주면 정상적으로 어플리케이션을 동작할 수 있게 됩니다.

 

 

 

Spring Boot Dialect 설정 방법

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDB103Dialect

(방법 1)

spring.jpa.database=mysql
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

(방법 2)

 

Dialect 설정은 .properties(또는 .yml) 파일에 다음과 같은 두 가지 방식으로 설정이 가능합니다.

(2가지 방식의 정확한 차이점은 잘 모르겠어 아직 파악 중이며, dialect 설정이 조금 더 직접적인 방언 설정인 것 같습니다.)

 

추가로 방언 설정에 대해서는 함께 보시면 좋을 것 같은 글(dialect 설정을 꼭 해야 하는가?)이 있어 포스팅 맨 하단에 함께 첨부해놓았습니다. 참고해보셔도 좋을 것 같습니다.

 

 

 

---

 

1. Dialect 확장을 통해 Native SQL Function을 사용하는 방법

public class CustomMariaDBDialect extends MariaDBDialect {
    public CustomMariaDBDialect() {
        super();
    }
    
    this.registerFunction("JSON_CONTAINS", new StandardSQLFunction("JSON_CONTAINS", StandardBasicTypes.BOOLEAN));
    this.registerFunction("JSON_QUOTE", new StandardSQLFunction("JSON_QUOTE", StandardBasicTypes.STRING));    
}

Hibernate에서 제공하는 방언을 상속받은 클래스를 생성하고, 사용을 원하는 Native SQL Function을 예시와 같이 추가합니다.

(예시 코드에서 사용된 MariaDBDialect는 예시일 뿐이고 실제 해당 프로젝트에서 사용되는 Dialect를 상속받은 클래스를 생성합니다.)

 

spring.jpa.properties.hibernate.dialect=com.example.project.database.CustomMariaDBDialect

그리고 기존에 .properties 파일 또는 .yml 파일에 등록된 다음 설정에 해당 CustomDialect 클래스를 설정합니다.

(com.example.project.database는 CustomMariaDBDialect 클래스가 존재하는 패키지의 예시입니다.)

 

 

 

2. MetadataBuilder를 사용하는 방법

public class ApplySQLFunction implements MetadataBuilderContributor {
    
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("JSON_CONTAINS", new StandardSQLFunction("JSON_CONTAINS", StandardBasicTypes.BOOLEAN));
        metadataBuilder.applySqlFunction("JSON_QUOTE", new StandardSQLFunction("JSON_QUOTE", StandardBasicTypes.STRING));
    }
}

다음과 같이 MetadataBuilderContributor interface를 상속받아 MetadataBuilder를 통해 Native SQL Function을 등록하는 방법도 있는데요.

(MetadataBuilder 클래스는 이번에 처음 보게 된 클래스인데 구글링해도 아직 많은 정보가 있지는 않았습니다.)

 

spring.jpa.properties.hibernate.metadata_builder_contributor=com.example.project.database.ApplySQLFunction

.properties 또는 .yml 파일에 다음과 같은 옵션을 통해 해당 클래스가 적용되도록 설정할 수 있습니다.

(위와 마찬가지로 com.example.proejct.database는 해당 클래스가 존재하는 경로의 예시입니다.)

 

해당 방식의 장점은 Dialect를 확장한 클래스를 적용하는 것이 아니라 MetadataBuilder를 사용하여 Dialect와는 완전 별개의 클래스를 적용하는 것이기 때문에 Dialect가 변경되었을 때 영향을 받지 않는다는 장점이 있습니다.

 

 

 

---

 

QueryDsl 동작 여부 확인

@Override
public Boolean isContains() {
  return queryFactory
      .select(Expressions.booleanTemplate("JSON_CONTAINS({0}, {1}, {2})",
          member.history, Expressions.stringTemplate("JSON_QUOTE({0})", "TEST"), "$.test"))
      .from(member)
      .where(member.idx.eq(1L))
      .fetchOne();
}

 

querydsl에 적용하여 동작 여부를 확인하였는데요. 위 두 방식 모두 등록된 Native SQL Function이 정상적으로 동작하는 것을 확인할 수 있었습니다.

 

/*

예시로 사용된 함수는 json 필드에 해당 데이터가 포함되어 있는지 확인할 수 있는 JSON_CONTAINS 함수입니다.

JSON_CONTAINS(JSON 데이터, 확인하려고 하는 데이터, JSON 데이터의 key명) 형식으로 사용되며, 이때 확인하려고 하는 데이터 입력에서 JSON_QUOTE('값') 형식으로 넣어주어야 오류 없이 동작합니다.

JSON_CONTAINS 함수의 결과로는 확인하려고 하는 데이터가 존재하면 1(true)을 아니면 0(false)을 반환합니다.

*/

 

 

 

 

< 위에 언급한 Dialect 설정에 관한 글 >

https://2dongdong.tistory.com/66

 

 

< 다른 참고 자료 >

https://firework-ham.tistory.com/106

https://kapentaz.github.io/jpa/Spring-Data-JPA에서-SQL-Function-사용하기/#