SQL function 호출하기

SQL function은 JPA와 같이 Dialect에 등록된 내용만 호출할 수 있다.(JPQL VS Querydsl 둘다 호출 가능)

 

 

SQL function을 한 번 호출해보자.

• member -> M으로 변경 replace 함수 사용

@Test
public void sqlFunction() {
    List<String> result = queryFactory
            .select(Expressions.stringTemplate(
                    "function('replace', {0}, {1}, {2})",
                    // member라는 단어를 M으로 바꿔서
                    member.username, "member", "M"))
            .from(member)
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

s = M1
s = M2
s = M3
s = M4

/* select
        function('replace',
        member1.username,
        ?1,
        ?2) 
    from
        Member member1 */ select
            replace(member0_.username,
            ?,
            ?) as col_0_0_ 
        from
            member member0_

 

 

 

Class에서 h2Dialect를 검색해서 찾아보자

/**
 * Logback: the reliable, generic, fast and flexible logging framework.
 * Copyright (C) 1999-2015, QOS.ch. All rights reserved.
 *
 * This program and the accompanying materials are dual-licensed under
 * either the terms of the Eclipse Public License v1.0 as published by
 * the Eclipse Foundation
 *
 *   or (per the licensee's choosing)
 *
 * under the terms of the GNU Lesser General Public License version 2.1
 * as published by the Free Software Foundation.
 */
package ch.qos.logback.core.db.dialect;

/**
 * The H2 dialect.
 * 
 * @author Ceki Gülcü
 */
public class H2Dialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "CALL IDENTITY()";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }

}

 

SQLDialect를 들어가보면 나와있다.

/**
 * Logback: the reliable, generic, fast and flexible logging framework.
 * Copyright (C) 1999-2015, QOS.ch. All rights reserved.
 *
 * This program and the accompanying materials are dual-licensed under
 * either the terms of the Eclipse Public License v1.0 as published by
 * the Eclipse Foundation
 *
 *   or (per the licensee's choosing)
 *
 * under the terms of the GNU Lesser General Public License version 2.1
 * as published by the Free Software Foundation.
 */
package ch.qos.logback.core.db.dialect;

/**
 * @author Ceki Gülcü
 *
 */
public interface SQLDialect {
    String getSelectInsertId();
}

 

 

 

h2 dialect에 가보면 replace가 등록이 되어있다.

package org.hibernate.dialect;

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

 

 

 

소문자로도 나타내보면 결과가 잘 나온다.

• 소문자로 변경해서 비교해라.

@Test
public void sqlFunction2() {
    List<String> result = queryFactory
            .select(member.username)
            .from(member)
            .where(member.username.eq(
                    Expressions.stringTemplate("function('lower', {0})", member.username)))
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

/* select
        member1.username 
    from
        Member member1 
    where
        member1.username = function('lower', member1.username) */ select
            member0_.username as col_0_0_ 
        from
            member member0_ 
        where
            member0_.username=lower(member0_.username)





s = member1
s = member2
s = member3
s = member4

 

 

• lower 같은 ansi 표준 함수들은 querydsl이 상당부분 내장하고 있다. 따라서 이렇게 처리해주자.

@Test
    public void sqlFunction2() {
        List<String> result = queryFactory
                .select(member.username)
                .from(member)
//                .where(member.username.eq(
//                        Expressions.stringTemplate("function('lower', {0})", member.username)))

                .where(member.username.eq(member.username.lower()))
                .fetch();

        for (String s : result) {
            System.out.println("s = " + s);
        }
    }

/* select
        member1.username 
    from
        Member member1 
    where
        member1.username = lower(member1.username) */ select
            member0_.username as col_0_0_ 
        from
            member member0_ 
        where
            member0_.username=lower(member0_.username)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! Querydsl >

https://www.inflearn.com/course/Querydsl-%EC%8B%A4%EC%A0%84/dashboard

실전! Querydsl - 인프런 | 강의