Querydsl 사용시 Mysql에서 생성한 Function 호출하는 방법

Querydsl 사용할 때 Mysql에서 생성한 Function을 호출해야할 경우가 있습니다. 어떻게 호출해야하는지 정리하겠습니다.

모든 소스는 github에 있습니다.

저 같은 경우는 기존 프로젝트를 MyBatis로 진행하여 Mapper.xml 에 쿼리를 작성하였습니다. 그리고 이제는 Spring Data JPA와 Querydsl 을 사용하기로 결정되었습니다. 그래서 과거에 Mysql function을 사용하던 것들을 자바 코드에서 해결을 해야했습니다. 처음엔 mysql function의 기능을 자바 코드로 옮길 생각이었으나, 그러면 관리포인트가 2개로 늘어나기때문에 querydsl에서 function 호출하는 방법이 필요했습니다. 그래서 정리하게 되었습니다.

 

MySQL Function 만들기

create
    function CreatePrefix(val varchar(255)) returns varchar(255)
BEGIN
    DECLARE rtn VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    IF val IS NULL THEN
        RETURN NULL;
    END IF;

    SET rtn = (SELECT CONCAT('TEST_', val));
    RETURN rtn;
END;

 

간단한 function을 하나 만들어줍니다.

function의 역할은 Prefix로 "TEST_"를 붙혀줍니다.

정상적으로 생성되었는지 테스트합니다.

select CreatePrefix(name)  from staff

결과

 

Project 설정

전 Mysql을 사용하고 있습니다. 

그래서 jpa.database-platform을 MySQL57Dialect 클래스로 설정하였습니다.

내부 소스를 들여다보면...

/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * License: GNU Lesser General Public License (LGPL), version 2.1 or later.
 * See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
 */
package org.hibernate.dialect;

import java.sql.Types;

import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.StaticPrecisionFspTimestampFunction;
import org.hibernate.type.StandardBasicTypes;

/**
 * @author Gail Badner
 */
public class MySQL57Dialect extends MySQL55Dialect {
	public MySQL57Dialect() {
		super();

		// For details about MySQL 5.7 support for fractional seconds
		// precision (fsp): http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
		// Regarding datetime(fsp), "The fsp value, if given, must be
		// in the range 0 to 6. A value of 0 signifies that there is
		// no fractional part. If omitted, the default precision is 0.
		// (This differs from the standard SQL default of 6, for
		// compatibility with previous MySQL versions.)".

		// The following is defined because Hibernate currently expects
		// the SQL 1992 default of 6 (which is inconsistent with the MySQL
		// default).
		registerColumnType( Types.TIMESTAMP, "datetime(6)" );

		// MySQL 5.7 brings JSON native support with a dedicated datatype.
		// For more details about MySql new JSON datatype support, see:
		// https://dev.mysql.com/doc/refman/5.7/en/json.html
		registerColumnType( Types.JAVA_OBJECT, "json" );

		// MySQL also supports fractional seconds precision for time values
		// (time(fsp)). According to SQL 1992, the default for <time precision>
		// is 0. The MySQL default is time(0), there's no need to override
		// the setting for Types.TIME columns.

		// For details about MySQL support for timestamp functions, see:
		// http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

		// The following are synonyms for now(fsp), where fsp defaults to 0 on MySQL 5.7:
		// current_timestamp([fsp]), localtime(fsp), localtimestamp(fsp).
		// Register the same StaticPrecisionFspTimestampFunction for all 4 functions.
		final SQLFunction currentTimestampFunction = new StaticPrecisionFspTimestampFunction("now", 6 );

		registerFunction( "now", currentTimestampFunction );
		registerFunction( "current_timestamp", currentTimestampFunction );
		registerFunction( "localtime", currentTimestampFunction );
		registerFunction( "localtimestamp", currentTimestampFunction );

		// sysdate is different from now():
		// "SYSDATE() returns the time at which it executes. This differs
		// from the behavior for NOW(), which returns a constant time that
		// indicates the time at which the statement began to execute.
		// (Within a stored function or trigger, NOW() returns the time at
		// which the function or triggering statement began to execute.)
		registerFunction( "sysdate", new StaticPrecisionFspTimestampFunction( "sysdate", 6 ) );

		// from_unixtime(), timestamp() are functions that return TIMESTAMP that do not support a
		// fractional seconds precision argument (so there's no need to override them here):

		registerFunction( "weight_string", new StandardSQLFunction( "weight_string", StandardBasicTypes.STRING ) );

		registerFunction( "to_base64", new StandardSQLFunction( "to_base64", StandardBasicTypes.STRING ) );
		registerFunction( "from_base64", new StandardSQLFunction( "from_base64", StandardBasicTypes.STRING ) );
	}

	/**
	 * @see <a href="https://dev.mysql.com/worklog/task/?id=7019">MySQL 5.7 work log</a>
	 * @return supports IN clause row value expressions
	 */
	public boolean supportsRowValueConstructorSyntaxInInList() {
		return true;
	}
}

 

해당 클래스는 mysql에서 사용하는 function, columnType을 등록해주는 클래스입니다. 여기서 registerFunction 메소드를 사용할 것입니다.

 

registerFunction

해당 메소드를 이용해 Mysql에서 생성항 Function을 등록하여 querydsl에서 사용할 수 있게 할 것입니다. 그러기 위해선 MySQL57Dialect 클래스를 상속받아 설정해야합니다.

 

MySQL dataplatform 클래스를 생성

package com.example.querydsl.configuration;

import org.hibernate.dialect.MySQL57Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class MysqlCustomDialect extends MySQL57Dialect {

    public MysqlCustomDialect() {
        super();
        registerFunction(
                "CreatePrefix",
                new StandardSQLFunction("createPrefix", StandardBasicTypes.STRING)
        );
    }
}

registerFunction( "MySQL에 등록해놓은 Function이름", Querydsl에서 사용할 alias명과 리턴 타입 )

 

application.yaml 설정

--- #local
spring:
  profiles: local
  datasource:
    url: jdbc:mysql://..../testdb?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
    username: root
    password: test1234!
    driver-class-name: com.mysql.jdbc.Driver

  jpa:
    database: mysql
    generate-ddl: false
    open-in-view: false
    hibernate:
      ddl-auto: none     # 항상 조심!!
    properties:
      hibernate:
    database-platform: com.example.querydsl.configuration.MysqlCustomDialect # 이 부분에 생성한 클래스파일을 기입

 jpa.database-platform : 이 부분에 위에서 생성한 클래스를 경로와 함께 기입합니다.

 

 

Querydsl 작성

public StoreVo findByName(String name) {
    return jpaQueryFactory
            .select(Projections.fields(StoreVo.class,
                    store.id
                    , Expressions.stringTemplate("createPrefix({0})", store.name).as("name")
                    , store.address
                    ))
            .from(store)
            .where(store.name.eq(name))
            .limit(1L)
            .fetchOne();
}

Expressions.stringTemplate("createPrefix({0})", store.name).as("name") // 이 부분입니다.

createPrefix : MysqlCustomDialect 클래스에서 설정한 메서드명을 입력합니다. 그리고 넘겨야할 파라미터를 세팅합니다.

• 1개일 경우 : ({0})   , argument1
• 2개일 경우 : ({0}, {1}), argument1, argument2

 

검증

@Test
void querydsl_Mysql내부함수_call() {
    //given
    final String name = "스토어6";

    //when
    StoreVo store = storeRepositorySupport.findByName(name);

    //then
    assertThat(store.getName()).isEqualTo("TEST_" + name);
}

정상 실행됩니다.

 

결과

Hibernate: 
    /* select
        store.id,
        createPrefix(store.name) as name,
        store.address 
    from
        Store store 
    where
        store.name = ?1 */ select
            store0_.id as col_0_0_,
            createPrefix(store0_.name) as col_1_0_,
            store0_.address as col_2_0_ 
        from
            store store0_ 
        where
            store0_.name=? limit ?

쿼리 실행 시 createPrefix가 실행되는 것을 로그를 통해 확인하실 수 있습니다.