DEV ℧ Developer Diary

[JPA] JPQL - JPQL 함수

20 Aug 2022

해당 포스트는 인프런 김영한님의 자바 ORM 표준 JPA 프로그래밍 - 기본편 을 듣고 정리한 글입니다.

JPQL 함수

기본 함수

  • CONCAT : 여러 문자열을 하나로 합칠때 사용하는 함수
/* String query1 = "SELECT 'A' || 'B' FROM Member m"; */
String query1 = "SELECT CONCAT('A','B') FROM Member m";
List<String> result1 = entityManager.createQuery(query1, String.class)
                .getResultList();

for (String str : result1) {
    System.out.println("concat : " + str);
}

    /* SELECT
        CONCAT('A',
        'B')
    FROM
        Member m */ select
            ('A'||'B') as col_0_0_
        from
            Member member0_

concat : AB
  • SUBSTRING : 문자열을 자를때 사용하는 함수
String query2 = "SELECT SUBSTRING('abcdefg', 2, 3) FROM Member m";
List<String> result2 = entityManager.createQuery(query2, String.class)
                .getResultList();

for (String str : result2) {
    System.out.println("substring : " + str);
}

    /* SELECT
        SUBSTRING('abcdefg',
        2,
        3)
    FROM
        Member m */ select
            substring('abcdefg',
            2,
            3) as col_0_0_
        from
            Member member0_

substring : bcd
  • TRIM : 문자열의 앞 뒤 공백을 자를때 사용하는 함수
String query3 = "SELECT TRIM('   ab  cdefg   ') FROM Member m";
List<String> result3 = entityManager.createQuery(query3, String.class)
        .getResultList();

for (String str : result3) {
    System.out.println("trim : " + str);
}

    /* SELECT
        TRIM('   ab  cdefg   ')
    FROM
        Member m */ select
            trim('   ab  cdefg   ') as col_0_0_
        from
            Member member0_

trim : ab  cdefg
  • LOWER, UPPER : 문자열을 각각 소문자(LOWER), 대문자(UPPER)로 변환할 때 사용하는 함수
String query4 = "SELECT LOWER('ABCDEFG'), UPPER('abcdefg') FROM Member m";
List<Object[]> result4 = entityManager.createQuery(query3)
        .getResultList();

for (Object[] objects : result4) {
    System.out.println("lower : " + objects[0]);
    System.out.println("upper : " + objects[1]);
}

/* SELECT
        LOWER('ABCDEFG'),
        UPPER('abcdefg')
    FROM
        Member m */ select
            lower('ABCDEFG') as col_0_0_,
            upper('abcdefg') as col_1_0_
        from
            Member member0_

lower : abcdefg
upper : ABCDEFG
  • LENGTH : 문자열의 길이를 반환하는 함수
String query5 = "SELECT LENGTH('abcdefggg') FROM Member m";
List<Integer> result5 = entityManager.createQuery(query5, Integer.class)
        .getResultList();

for (Integer integer : result5) {
    System.out.println("length : " + integer);
}

    /* SELECT
        LENGTH('abcdefggg')
    FROM
        Member m */ select
            length('abcdefggg') as col_0_0_
        from
            Member member0_

length : 9
  • LOCATE :전체 문자열에서 검색하고자 하는 문자의 위치를 반환하는 함수
String query6 = "SELECT LOCATE('ggg', 'abcdefggg') FROM Member m";
List<Integer> result6 = entityManager.createQuery(query6, Integer.class)
        .getResultList();

for (Integer integer : result6) {
    System.out.println("locate : " + integer);
}

    /* SELECT
        LOCATE('ggg',
        'abcdefggg')
    FROM
        Member m */ select
            locate('ggg',
            'abcdefggg') as col_0_0_
        from
            Member member0_

locate : 7

  • ABS, SQRT, MOD : 각각 절대값, 제곱근, 나머지를 반환하는 함수

  • SIZE(JPA 용도) : 연관관계에 있는 엔티티의 count수를 반환하는 함수

String query7 = "SELECT SIZE(t.members) FROM Team t";
List<Integer> result7 = entityManager.createQuery(query7, Integer.class)
        .getResultList();

for (Integer integer : result7) {
    System.out.println("size : " + integer);
}

    /* SELECT
        SIZE(t.members)
    FROM
        Team t */ select
            (select
                count(members1_.TEAM_ID)
            from
                Member members1_
            where
                team0_.id = members1_.TEAM_ID) as col_0_0_
        from
            Team team0_

size : 1
  • INDEX (JPA 용도) : List의 값타입 컬렉션에서 @OrderColumn을 사용하여 컬렉션의 위치값을 구할 경우 사용함 (거의 사용되지 않는다.)

기본함수 같은 경우는 Hibernate에 구현체로 등록이 되어있지만 그 외에 DB에 종속되어있지 않은 일반적인 함수들에 대해서는 직접 등록해줘야한다.

사용자 정의 함수 호출

  • 하이버네이트는 사용전 방언에 추가해야 한다.
    • 사용하는 DB 방언을 상속받고, 사용자 정의 함수를 등록한다.

select function(‘group_concat’, i.name) from Item i

등록하는 방법은 프로젝트에서 사용하는 SQL의 방언을 상속받은 후에 생성자에 registerFunction() 함수를 호출하여 등록해 준다. 해당 예시 프로젝트는 H2 Database을 사용하였으므로 H2Dialect를 상속받아 커스텀 함수를 선언 하였다.

  • H2 Database Custom function 등록 예시
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class MyH2Dialect extends H2Dialect {
    public MyH2Dialect() {
        registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
    }
}

이후 persistence.xml에서 커스텀 함수를 추가한 H2 방언인 MyH2Dialect을 기존의 H2Dialect과 교체해 준다.

- <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
+ <property name="hibernate.dialect" value="com.dhaudgkr.jpa07.jpql02.dialect.MyH2Dialect"/>

Member member = new Member();
member.setUsername("관리자1");
member.setAge(10);
member.setType(MemberType.valueOf("USER"));
entityManager.persist(member);

Member member2 = new Member();
member2.setUsername("관리자2");
member2.setAge(10);
member2.setType(MemberType.valueOf("USER"));
entityManager.persist(member2);

entityManager.flush();
entityManager.clear();

String query9 = "SELECT function('group_concat', m.username) FROM Member m";
List<String> result9 = entityManager.createQuery(query9, String.class)
        .getResultList();

for (String str : result9) {
    System.out.println("function : " + str);
}

    /* SELECT
        function('group_concat',
        m.username)
    FROM
        Member m */ select
            group_concat(member0_.username) as col_0_0_
        from
            Member member0_

function : 관리자1,관리자2

group_concat은 n개의 row를 하나의 로우 데이터로 합쳐서 반환하는 함수이다. 해당 함수의 결과가 반환된 것을 확인 할 수 있다.

Hibernate에서는 custom function을 function() 내부에 선언하지 않고 직접 사용 할 수 있도록 지원한다.

SELECT function('group_concat', m.username) FROM Member m

와 같이 function 내부에 선언된 커스텀 함수를

SELECT group_concat(m.username) FROM Member m

처럼 직접적으로 사용할 수 있도록 지원한다.

Related posts