[spring-jpa] mysql, use json(db) function in @Query

환경: java11, springboot 2.5.6, hibernate-core 5.4.32

 

프로젝트를 작업하다 보면 변화하는 데이터 형태를 수용하기 위하여 sub data 콜롬에 json형태로 데이터를 저장하는 경우가 많이 있다. 화면에는 이 데이터를 꼭.. 보여달라는 요구사항이 많다 보니 mysql의 json관련 함수를 사용하게 될 일이 자주 생긴다.

주로 spring-data-jpa를 통해 데이터를 꺼내오는데 관련 쿼리를 작성하다 NPE를 만나 해결하는 과정을 작성한다.

 

nativeQuery=true일 경우는 기존 쿼리처럼 사용하면 된다.

 @Query(value = "select " +
            "JSON_UNQUOTE(JSON_EXTRACT(data, '$.strClazz')) as clazz, "+
            "JSON_EXTRACT(data, '$.totalScore') as totalScore "+
            "from tbl_user_event e " +
            "where e.gid = :#{#req.gid} " +
            "and e.event_id = :#{#req.eventId} " +
            "order by e.base_date desc " +
            "limit 1 ",
            nativeQuery = true)
Optional<HelloUserRes> getTop1HelloUserInfo(HelloEventReq req);

nativeQuery=true인 경우에 custom dto class로 바로 받기가 어려워 interface & projection의 형태 or map으로 데이터를 받아야 한다. 허나 개인적으로 이 방법을 안 좋아하므로.. 보통은 nativeQuery=false에 dto constructor를 사용하여 데이터를 꺼내는 편이다. 

 

nativeQuery=false일 경우, 'function'을 사용하여 Mysql(혹은 기타 사용하는 데이터베이스)에서 제공하는 함수를 사용할 수 있다고 하여 아래와 같이 작성하였다. 

 @Query(value = "select new com.hello.model.log.LoginHistoryResponse( " +
            "l.logDate, l.gid, l.eventId, l.logType, " +
            "FUNCTION('JSON_EXTRACT', l.extraData, '$.deviceName'), FUNCTION('JSON_EXTRACT', l.extraData, '$.clientIp'), FUNCTION('JSON_EXTRACT', l.extraData, '$.appVersion')) " +
            "from UserEventLog l " +
            "where l.eventId = :#{#request.eventId} " +
            "and l.gid = :#{#request.gid} " +
            "and l.logDate between :#{#request.startDate} and :#{#request.endDate} ")
Page<LoginHistoryResponse> findByEventIdAndGidAndLogDateBetween(LoginHistoryRequest request, Pageable pageable);

 

그런데 서버를 재시작하고 보니 아래와 같이 NPE가 발생하는 게 아닌가? 

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.....
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:96)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66)
	at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:51)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:163)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:87)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:102)
	... 66 common frames omitted
Caused by: java.lang.NullPointerException: null
	at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27)
	at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:434)
	at org.hibernate.hql.internal.ast.tree.SelectClause.initializeColumnNames(SelectClause.java:268)
	at org.hibernate.hql.internal.ast.tree.SelectClause.finishInitialization(SelectClause.java:258)
	at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:253)
	at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1028)
	at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:796)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:694)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330)
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
	at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)
	at com.sun.proxy.$Proxy129.createQuery(Unknown Source)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90)

 

쿼리 검증 시 발생한다는 건 알겠는데 왜 null 일까 뚜렷한 이유를 바로 알기 어려웠다. 느낌적으로 function()에서 발생한다는 촉이 발동하여 관련 자료를 더 찾아보았다.

찾아보니 Hibernate 5.2.18부터 MetadataBuilderContributor라는 인터페이스를 사용할 수 있고 이것을 구현해야 SQL Function을 사용할 수 있다고 한다?!

아래와 같이 구현하고 사용하고자 하는 함수를 add 해준다.

public class SqlMetaBuilderContributor 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("JSON_CONTAINS", new StandardSQLFunction("JSON_CONTAINS", StandardBasicTypes.BOOLEAN))
                .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)"));
    }
}

그리고 이 contributor를 어떻게 주입할까 싶었는데, 아래와 같이 설정값을 추가해주면 된다.

spring.jpa.properties.hibernate.metadata_builder_contributor=com.common.config.SqlMetaBuilderContributor

 

++

nativeQuery=false 인 경우 '->>' 가 먹지 않아서.. JSON_UNQUOTE를 할 때 ->> 가 아닌 function('JSON_UNQUOTE', ~) 형식을 사용해야 한다.

즉, nativeQuery=true 인 경우는 아래와 같이 ->>가 사용 가능하나

@Query(value = "select sum(h.round1) as round1, sum(h.round2) as round2, sum(h.round3) as round3, sum(h.round4) as round4 " +
        "from " +
        "(select "+
        "if(JSON_LENGTH(l.data->>'$.weeks[0].achieves') != 0, 1, 0) round1, "+
        "if(JSON_LENGTH(l.data->>'$.weeks[1].achieves') != 0, 1, 0) round2, "+
        "if(JSON_LENGTH(l.data->>'$.weeks[2].achieves') != 0, 1, 0) round3, "+
        "if(JSON_LENGTH(l.data->>'$.weeks[3].achieves') != 0, 1, 0) round4 "+
        "from hd_user_event as l " +
        "where base_date = '1970-01-01 00:00:00' and event_id = 'hello') as h"
	, nativeQuery = true)
Map<String, Object> getHelloSummary();

nativeQuery=false 면 아래처럼 사용해야 한다..

@Query(value = "select new com.model.log.LoginHistoryResponse( " +
        "l.logDate, l.gid, l.eventId, l.logType, " +
        "FUNCTION('JSON_UNQUOTE', FUNCTION('JSON_EXTRACT', l.extraData, '$.deviceName')), FUNCTION('JSON_UNQUOTE', FUNCTION('JSON_EXTRACT', l.extraData, '$.clientIp')), FUNCTION('JSON_UNQUOTE', FUNCTION('JSON_EXTRACT', l.extraData, '$.appVersion')) ) " +
        "from UserEventLog l " +
        "where l.eventId = :#{#request.eventId} " +
        "and l.gid = :#{#request.gid} " +
        "and l.logDate between :#{#request.startDate} and :#{#request.endDate} ")
Page<LoginHistoryResponse> findByEventIdAndGidAndLogDateBetween(LoginHistoryRequest request, Pageable pageable);

매우 안 이쁘고(가독성이 떨어지고) 긴 쿼리가 아닐 수 없다...ㅠㅅ ㅠ