[JPA/Java] JPQL 기본함수 및 사용자 정의 함수 호출

JPQL은 웬만한 DBMS가 제공하는 기본적인 함수들을 제공하고 있다.

함수명	용도
CONCAT	문자열 이어 붙이기
SUBSTRING	부분 문자열 만들기
TRIM	빈 공백 없애기
LOWER, UPPER	소문자, 대문자
LENGTH	문자열의 길이
LOCATE	문자열의 위치(Integer 값)
ABS, SQRT, MOD	절대값, 제곱근, 나누기
SIZE, INDEX(JPA 용도)	컬렉션(또는 엔티티)의 전체 크기, 컬렉션의 위치

 

예시) CONCAT

String jpql = "";

// uninject language를 적용하면 된다. 방법 : 해당 문자열 내에서 ALT + ENTER 누르면 안내문이 나옴
jpql = "select 'a' || 'b' From Member m";

List<String> resultList = em.createQuery(jpql, String.class)
                    .getResultList();

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

{"originWidth":343,"originHeight":199,"style":"alignCenter","caption":"a

jpql = "select concat('a', 'b') from Member m";

CONCAT

 

예시)  SUBSTRING

jpql = "select substring(m.username, 2, 3) from Member m";

SUBSTRING

 

예시) LOCATE  : 위치값을 반환하기 때문에 Integer.class

jpql = "select locate('de', 'abcdefg') from Member m";
List<Integer> resultList = em.createQuery(jpql, Integer.class)
        .getResultList();

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

LOCATE

예시) SIZE : 정수값 반환이라 Integer.class

// ALT + ENTER 눌러서 uninject language 에 대한 설정이 가능하다
jpql = "select size(t.members) from Team t";

List<Integer> resultList = em.createQuery(jpql, Integer.class)
        .getResultList();

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

SIZE

 

예시) INDEX : 일반적으로 사용할 수 있는건 아닌데, 사용을 추천하지 않는다.

리스트의 값 타입을 넣어서 사용할 때 쓴다. 

리스트에서 중간에 값이 빠질 때 Null 로 데이터가 들어온다.

 

사용자 정의 함수를 호출하는 방법이 있다.

 

하이버네이트에는 사용자 정의 함수 호출을 사용하기 전에, persistence.xml에 등록해주는 과정이 필요하다.

 

dialect 패키지 밑에 MyH2Dialect.java 를 생성해준다.

 

경로 설정

package dialect;

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

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
    <persistence-unit name="hello">
        <properties>
            <!-- 필수 속성 -->
            <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/~/jpaStudy2"/>
<!--            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>-->
            <property name="hibernate.dialect" value="dialect.MyH2Dialect"/>
            <!--<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle12cDialect"/>-->

            <!-- 옵션 -->
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.use_sql_comments" value="true"/>
            <property name="hibernate.jdbc.batch_size" value="10"/>
            <property name="hibernate.hbm2ddl.auto" value="create" />
        </properties>
    </persistence-unit>
</persistence>

 

기존에 활성화 되어있었던 H2Dialect 를 비활성화해주고, 새롭게 추가한 MyH2Dialect 를 추가한다.

 

그러면 이제 사용자 정의 함수를 사용할 준비가 되었다.

 

JpaMain.java

Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername("관리자");
member.setAge(10);
member.setTeam(team);
member.setType(MemberType.ADMIN);
em.persist(member);

Member member2 = new Member();
member2.setUsername("회원");
member2.setAge(20);
member2.setTeam(team);
member2.setType(MemberType.USER);
em.persist(member2);

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

String jpql = "";

jpql = "select function('group_concat', m.username) from Member m";

List<String> resultList = em.createQuery(jpql, String.class)
        .getResultList();

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

 

쿼리 조회 결과를 문자열로 이어붙인 결과이다.

이렇게 하면 사용자 정의 함수를 호출할 수 있다.