JPQL 기본함수

1. JPQL 기본함수 종류

---

1) CONCAT

여러 문자열을 하나의 문자열로 혹은 여러 컬럼을 하나의 문자열로 합치는 함수

SELECT CONCAT(idx, '::', name) as hero_name FROM hero_collection;

결과) 1::안중근, 2::윤봉길

2) SUBSTRING

SUBSTRING(str,pos,len)의 형태로 사용하며 문자열을 자르는 함수

• str : 원본 문자열
• pos : 시작 위치값
• len : 가져올 길이값

SELECT SUBSTRING('동해물과백두산이',3,4);

결과) 물과백두

3) TRIM

문자열에 공백을 제거하는 함수인데 모든 공백이 아닌 좌우 공백만 제거한다.

SELECT TRIM(' abcdef ');

결과) abcdef

4) LOWER, UPPER

문자를 소문자 대문자로 변경하는 함수

SELECT UPPER('Abcdef');

결과) ABCDEF

5) LENGTH

문자열의 길이를 가져오는 함수

SELECT LENGTH('Hello')

결과) 5

6) LOCATE

str에 있는 문자열 substr의 검색 위치를 정수로 반환하는데,
substr이 str에 없으면 0을 반환하는 함수

LOCATE(substr, str)

str에서 substr이 처음 나타나는 지점의 위치를 가져온다.

SELECT LOCATE('bar', 'foobarbar');

결과) 4

SELECT LOCATE('test', 'foobarbar');

결과) 0

LOCATE(substr, str, pos)

str에서 pos 위치부터 시작해서 substr이 처음 나타나는 지점의 위치를 가져온다.

SELECT LOCATE('bar', 'foobarbar', 5);

결과) 7

7) ABS, SQRT, MOD

ABS

x의 절대값을 구하는 함수

SELECT ABS(-32);

결과) 32

SQRT

x에 대한 제곱근을 반환하는 함수

SELECT SQRT(4);

결과) 2

MOD

MOD(n,m) -> n을 m으로 나눈 나머지를 반환하는 함수

SELECT MOD(365, 2);

결과) 1

8) SIZE, INDEX(JPA 용도)

SIZE

컬렉션의 크기를 구하는 함수이며 JPA에서 쓰인다.

SELECT SIZE(t.members) FROM Team t;

INDEX(별칭)

LIST 타입 컬렉션의 위치값을 구하는 함수이며 JPA에서 쓰인다.

SELECT t.members m WHERE INDEX(m) > 3;

2. 사용자 정의 함수 호출

---

• H2 데이터베이스 경우 H2Dialect 클래스에 JPQL 함수를 정의해 놓았다.

• H2 DB에 존재하는 함수인데 H2Dialect 클래스에 미처 정의하지 못한 함수들이 있다.

• 이런 경우 방언에 함수를 추가해야하며 이 추가한 함수를 사용자 정의 함수라고 한다.
  
  

[MyH2Dialect]

public class MyH2Dialect extends H2Dialect {

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

[persistence.xml]

<property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>
<property name="javax.persistence.jdbc.user" value="sa"/>
<property name="javax.persistence.jdbc.password" value=""/>
<property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/~/test"/>
<!--<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>-->
<property name="hibernate.dialect" value="jpql.MyH2Dialect"/>

[JpaMain]

Member member = new Member();
member.setUsername("관리자");
member.setAge(10);
em.persist(member);

Member member2 = new Member();
member2.setUsername("회원");
member2.setAge(20);
em.persist(member2);

em.flush();
em.clear();

String query = "select function('group_concat', m.username) From Member m";
List<String> resultList = em.createQuery(query, String.class).getResultList();

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

// 결과
s = 관리자,회원