JPQL 기본 함수
JPQL의 기본 함수 사용법과 사용자 정의 함수 등록 방법을 예제와 함께 자세히 설명합니다.
* JPQL 기본 함수
-CONCAT
-SUBSTRING
-TRIM
-LOWER, UPPER
-LENGTH
-LOCATE (인덱스 함수와 비슷하다.)
-- Result : 4
select locate('de', 'abcdefg') from Member m
-SIZE
-- Team 에 속한 Member 들의 Collection 의 크기를 나타낸다.
select size(t.members) from Team t
-Custom 함수 등록
1. Dialect 파일 생성
package jpql;
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));
}
}
2. 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/~/test"/>
<!-- <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>-->
<property name="hibernate.dialect" value="jpql.MyH2Dialect"/>
<!-- 옵션 -->
<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.hbm2ddl.auto" value="create" />
</properties>
</persistence-unit>
</persistence>
3. 실행 예제
public static void main(String[] args) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
try {
Member member = new Member();
member.setUsername("heejae");
member.setAge(20);
em.persist(member);
String query = "select function('group_concat', m.username) from Member m";
List<String> result = em.createQuery(query, String.class).getResultList();
for(String r : result) {
System.out.printf("r : " + r);
}
tx.commit();
} catch (Exception e) {
tx.rollback();
} finally {
if(em.isOpen()) {
em.close();
}
}
if(emf.isOpen()) {
emf.close();
}
}
이것도 읽어보세요