Criteria 사용하여 질의 하기 #1

Hibernate에서는 HQL에 익숙하지 못하거나 HQL 작성시 발생할 수 있는 오타로 인한 오류를 최소화 하기 위해 org.hibernate.Criteria API를 사용할 수 있도록 합니다.
Creteria API 호출을 통해 특정 객체에 대한 조회가 가능하고 org.hibernate.criterion.Restrictions API 호출을 통해 WHERE문에 해당하는 기본 조회 조건을 정의할 수 있습니다..

쉽게 말해서 select 쿼리입니다.

우편번호 데이터(50353건)로 여러가지 조회를 해봅시다! (Type 4 사용)

Zipcode.java

package com.tistory.antop;

public class Zipcode {
    // 데이터 순서(5)
    private int seq;
    // 우편번호(7)
    private String zipcode;
    // 특별시,광역시,도(4)
    private String sido;
    // 시,군,구(15)
    private String gugun;
    // 읍,면,동(26)
    private String dong;
    // 리(18)
    private String ri;
    // 건물명(40)
    private String bldg;
    // 시작번지(11)
    private String stBunji;
    // 끝번지(9)
    private String edBunji;

    // constructor, getter and setter (private setter seq)

    @Override
    public String toString() {
        // 길어서 생략...
    }
}

Zipcode.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.tistory.antop">
    <class name="Zipcode" table="tbl_zipcode">
        <!-- 데이터 순서(5) -->
        <id name="seq" length="5">
            <generator class="native" />
        </id>
        <!-- 우편번호(7) -->
        <property name="zipcode" length="7" not-null="true" />
        <!-- 특별시,광역시,도(4) -->
        <property name="sido" length="4" not-null="true" />
        <!-- 시,군,구(15) -->
        <property name="gugun" length="15" not-null="true" />
        <!-- 읍,면,동(26) -->
        <property name="dong" length="26" not-null="true" />
        <!-- 리(18)  -->
        <property name="ri" length="18" />
        <!-- 건물명(40) -->
        <property name="bldg" length="40" />
        <!-- 시작번지(11) -->
        <property name="stBunji" column="st_bunji" length="11" />
        <!-- 끝번지(9) -->
        <property name="edBunji" column="ed_bunji" length="9" />
    </class>
</hibernate-mapping>

들어가기 전에 테스트 소스는 아래와 같습니다.

public class App {

    public static void main(String[] args) {
        Session sess = HibernateUtil.getCurrentSession();
        Transaction tx = sess.beginTransaction();

        // processing
  
        tx.commit();
        HibernateUtil.closeSession();
    }
 
    // Zipcode 객체가 들어있는 List를 출력
    private static void display(List<Zipcode> list) {

  

        try {
            int size = list.size();
   
            if(size == 0) {
                throw new Exception("리스트가 없습니다.(0건)");
            }
      
            for(int i=0 ; i < size ; i++) {
                System.out.println(list.get(i).toString());
            }
   
        } catch (NullPointerException e) {
            System.out.println("리스트가 존재하지 않습니다.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}

---

SELECT

select * from tbl_zipcode

Criteria crit = sess.createCriteria(Zipcode.class);
List<Zipcode> zipcodeList = crit.list();

select seq, zipcode, st_bunji, ed_bunji from tbl_zipcode

Criteria crit = sess.createCriteria(Zipcode.class);
  
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.id().as("seq"));
projectionList.add(Projections.property("zipcode").as("zipcode"));
projectionList.add(Projections.property("stBunji").as("stBunji"));
projectionList.add(Projections.property("edBunji").as("edBunji"));
  
crit.setProjection(projectionList);
crit.setResultTransformer(new AliasToBeanResultTransformer(Zipcode.class));
  
List<Zipcode> zipcodeList = crit.list();

 
원하는 칼럼을 뽑아낼 때 쓰는 ProjectionList와 Projections을 사용합니다.(테이블 하나 조회하면서는 안쓸듯...)

해보니까 Projections 에서 꼭 as() 를 사용해서 Zipcode 클래스의 필드명과 이름을 맞춰줘야 하더군요... -0-/

Projections.id() 와 Projections.property() 는 뭐가 다른지 아시겠죠? ㅎㅎ;; id() 가 PK가 되는 필드 입니다.


select count(*) from tbl_zipcode

Criteria crit = sess.createCriteria(Zipcode.class);
crit.setProjection( Projections.rowCount() );
  
List<Integer> zipcodeList = crit.list();
System.out.println("count : " + zipcodeList.get(0));

select count(seq) as count from tbl_zipcode

Criteria crit = sess.createCriteria(Zipcode.class);
crit.setProjection(Projections.count("seq").as("count"));
   
List<Integer> zipcodeList = crit.list();
System.out.println("count : " + zipcodeList.get(0));

---

ORDER BY

select * from tbl_zipcode order by seq desc

Criteria crit = sess.createCriteria(Zipcode.class);
crit.addOrder(Order.desc("seq"));
   
List<Zipcode> zipcodeList = crit.list();

Order.asc(String propertyName) 도 있습니다~ +_+

---

Criteria crit = sess.createCriteria(Zipcode.class);
List<Zipcode> zipcodeList = crit.list();

생략하겠습니다~

WHERE

where문은 Restrictions을 사용합니다. 내용이 많으니 자세한것은 API를 봐주세요~

select * from tbl_zipcode where seq = 1555

crit.add(Restrictions.eq("seq", 1555));

select * from tbl_zipcode where seq = 45

crit.add(Restrictions.idEq(45));    // idEq() 를 사용하면 PK 칼럼으로 검색합니다.

select * from tbl_zipcode where dong like '방화%'

crit.add(Restrictions.like("dong", "방화", MatchMode.START));    // '방화%'

MatchMode 종류는 ANYWHERE, END, EXACT, START 4가지 종류가 있습니다.

딱 보면 아시겠지만 '%방화%', '방화%', '방화', '%방화' 이렇게 됩니다.


select * from tbl_zipcode where sido = '서울' and zipcode like '157%'

crit.add(Restrictions.eq("sido", "서울"));
crit.add(Restrictions.like("zipcode", "157", MatchMode.START)); 

여러개의 조건을 사용할 때에는 계속 .add() 해주면 됩니다.


select * from tbl_zipcode where where (st_bunji = '514' and sido = '서울' and dong = '방화2동')

Map<String, String> m = new Hashtable<String, String>();
m.put("sido", "서울");
m.put("dong", "방화2동");
m.put("stBunji", "514");
  
crit.add(Restrictions.allEq(m));

여러개의 eq를 사용할 경우 Map 을 사용할 수 있습니다.


select * from tbl_zipcode where seq >= 5300

crit.add(Restrictions.ge("seq", new Integer(50300)));

주의 해야 할 것은 2번째 인자는 Object 형태라는 것입니다. +_+;

ge(>=), gt(>), le(<=), lt(<) 가 있습니다.

select * from tbl_zipcode where sido in ('서울', '부산')

String[] in = {"서울", "부산"};
crit.add(Restrictions.in("sido", in));    // Collection 또는 Object[] 사용 가능

select * from tbl_zipcode where ( sido = '서울' and zipcode like '157%' )

crit.add(
    Restrictions.conjunction()    // and 연산으로 그룹
        .add( Restrictions.eq("sido", "서울") )
        .add( Restrictions.like("zipcode", "157", MatchMode.START) )
);

and 조건을 여러개 사용할 경우 그냥 계속 .add() 해도 되지만 Restrictions.conjunction() 로 그룹할 수 있습니다.

Restrictions.conjunction() 은 and 로 그룹하고, Restrictions.disjunction() 은 or 로 그룹합니다.


select * from tbl_zipcode where ( (dong like '방화%' or dong like '%1동') and (zipcode like '157%' or zipcode like '431') )

crit.add(
    Restrictions.conjunction()
        .add(
            Restrictions.disjunction()
                .add(Restrictions.like("dong", "방화", MatchMode.START))
                .add(Restrictions.like("dong", "1동", MatchMode.END))
        )
        .add(
            Restrictions.disjunction()
                .add(Restrictions.like("zipcode", "157", MatchMode.START))
                .add(Restrictions.like("zipcode", "431", MatchMode.START))
        )
);

- 동이 '방화'로 시작하거나 '1동'으로 끝나고 우편번호가 157로 시작하거나 431로 시작하는 데이터를 조회 합니다.
- (동이 '방화'로 시작 or 동이 '1동'으로 끝) and (우편번호가 '157'로 시작 or 우편번호가 '431'로 시작)

정도가 되겠네요... (헷갈리.. ㅠㅠ)


select * from tbl_zipcode where st_bunji between '157' and '158'

crit.add(Restrictions.between("stBunji", "157", "158"));

select * from tbl_zipcode where ( dong like '방%' and dong like '%1동' )

crit.add( Restrictions.and( Restrictions.eq("sido", "서울"), Restrictions.like("dong", "1동", MatchMode.END) ) );

Restrictions.and(조건, 조건) 은 두개의 조건을 and 로 그룹합니다.
Restrictions.or(조건, 조건) 은 두개의 조건을 or 로 그룹합니다.


select * from tbl_zipcode where not ( sido in ('서울', '부산') )

crit.add( Restrictions.not( Restrictions.in("sido", new String[] { "서울", "부산" }) ) );

---

GROUP BY

ProjectionList와 Projections을 사용합니다

select sido, count(sido) as sidoCount from tbl_zipcode group by sido

// 각 시도별 우편번호 갯수를 카운트
ProjectionList projectionList = Projections.projectionList();

projectionList.add( Projections.alias( Projections.count("sido"), "sido_count") );   // count(sido) as sido_count
projectionList.add( Projections.groupProperty("sido").as("sido") );    // group by sido
  
crit.setProjection(projectionList);
crit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
  
List<Map<String, Object>> list = crit.list();
  
for(int i=0 ; i < list.size() ; i++) {
    Map<String, Object> m = (Map<String, Object>) list.get(i);
    System.out.println(m);
}

Projections.groupProperty(String propertyName) 을 이용해서 GROUP BY 를 하고

crit.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); 는 조회해서 나온 결과를 Map 형태로 변환해 줍니다.

이렇게 나온 결과가 Zipcode 클래스 자체 형태가 아니라면 Map 으로 변환해서 처리하면 됩니다.


※ groupPropery(String propertyName) 과 groupProperty(String propertyName).as(String alias) 와는 차이가 있습니다.

select count(sido) as sido_count from tbl_zipcode group by sido

projectionList.add( Projections.groupProperty("sido") );

// 결과
{sido_count=2800}
{sido_count=8170}
{sido_count=3614}
{sido_count=4921}

select count(sido) as sido_count, sido from tbl_zipcode group by sido

projectionList.add( Projections.groupProperty("sido").as("sido") );    // sido 가 select 칼럼에 추가
// 결과
{sido=강원, sido_count=2800}
{sido=경기, sido_count=8170}
{sido=경남, sido_count=3614}

여러 칼럼으로 GROUP BY 하려면 똑같이 Projections.groupProperty() 를 추가 하면 됩니다.

select sido, gugun, count(sido) as sidoCount from tbl_zipcode group by sido, gugun

// 각 시도별 우편번호 갯수를 카운트
ProjectionList projectionList = Projections.projectionList();

projectionList.add( Projections.alias( Projections.count("sido"), "sido_count") );   // count(sido) as sido_count
projectionList.add( Projections.groupProperty("sido").as("sido") );    // group by sido
projectionList.add( Projections.groupProperty("gugun").as("gugun") );    // group by gugun

※ 다른 WHERE(또는 다른 것들..)과 같이 쓸때 Projections에서 alias를 다른 이름으로 사용해야 합니다.

crit.add(Restrictions.eq("sido", "서울"));

  
// 각 시도별 우편번호 갯수를 카운트
ProjectionList projectionList = Projections.projectionList();
projectionList.add( Projections.alias( Projections.count("sido"), "sido_count") );   // count(sido) as sido_count
projectionList.add( Projections.groupProperty("sido").as("sido") );    // group by sido

select count(sido) as sido_count, sido from tbl_zipcode where sido = '서울' group by sido

정도의 쿼리를 예상 했지만 실제로 해보면 잘못된 쿼리라는 에러가 납니다.

이유는 하이버네이트에서 쿼리를 만들때 내부적으로 alias 를 사용하는데,

select count(this_.sido) as y0_, this_.sido as y1_ from tbl_zipcode this_ where y1_=? group by this_.sido

위와 같은 쿼리가 나오게 됩니다. 이상하죠? ㅎㄷ;;

crit.add(Restrictions.eq("sido", "서울"));
  
// 각 시도별 우편번호 갯수를 카운트
ProjectionList projectionList = Projections.projectionList();
projectionList.add( Projections.alias( Projections.count("sido"), "sido_count") );   // count(sido) as sido_count
projectionList.add( Projections.groupProperty("sido").as("g_sido"));    // group by sido

// 결과
{g_sido=서울, sido_count=7807}

위와 같이 Projections 에서 다른 alias 를 사용하면 where 문의 칼럼이 제대로 나옵니다.

select count(this_.sido) as y0_, this_.sido as y1_ from tbl_zipcode this_ where this_.sido=? group by this_.sido

---

HAVING

having 은 Criteria 에서 지원이 안되는 것 같습니다. HQL 을 사용하면 되겠지만 쿼리문을 쓰기 싫으므로...

열시미 찾아서 having 이 지원되게 수정(3.1 rc1 버젼) 된 라이브러리를 찾았습니다! (찾았는데 오류나서 개인적으로 수정 -_-)

hibernate_having.zip

 - 수정된 java 파일 입니다.

hibernate3.jar

 - 하이버네이트 jar 에 통합시킨 파일입니다. ( 3.2.4.GA + 수정된 java(3.1 rc1) )


수정 된 부분은 Criteria 클래스에서 public Criteria addHaving(Criterion criterion); 메소드가 추가되었습니다.


select count(sido) as sido_count, sido from tbl_zipcode group by sido having sido in ('서울', '경기', '부산')

ProjectionList projectionList = Projections.projectionList();
projectionList.add( Projections.alias( Projections.count("sido"), "sido_count") );
projectionList.add( Projections.groupProperty("sido").as("g_sido"));
  
crit.addHaving( Restrictions.in("sido", new String[] {"서울", "경기", "부산"}) );    // having sido

// select count(this_.sido) as y0_, this_.sido as y1_ from tbl_zipcode this_ group by this_.sido having this_.sido in (?, ?, ?)
{g_sido=경기, sido_count=8170}
{g_sido=부산, sido_count=3287}
{g_sido=서울, sido_count=7807}

이 group by 와 having 부분은 이것저것 해보면 아시겠지만 제약(?)이 있군요.. ㅠ_ㅠ

select count(sido) as sido_count, sido, gugun from tbl_zipcode group by sido, gugun having count(sido) > 500

이거 할 줄 아시는분... 젭알!!!!!!! ㅠ_ㅠ

---

간단히 우편번호 검색하는 걸 만들어 봤습니다. ㄷㄷ;

zip_search.war