SQL Function 호출

1. SQL Function 호출

---

SQL Function은 JPA와 같이 Dialect에 등록된 내용만 호출이 가능함.

 

 

 

2. member를 M으로 변경하는 replace 함수 사용

---

    @Test
    public void sqlFunction(){
        List<String> result = queryFactory
                .select(Expressions.stringTemplate(
                        "function('replace', {0}, {1}, {2})",
                        member.username, "member", "M"))
                .from(member)
                .fetch();

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

select
        function('replace',
        member1.username,
        ?1,
        ?2) 
    from
        Member member1

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

• H2Dialect

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

 

 

3. 소문자로 변경.

---

    @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);
        }
    }

소문자로 변경하는 다른 방법

    @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)

 

4. GitHub : 210903 SQL Function

---

GitHub - bsh6463/Querydsl