Background Image
기타
2009.04.01 08:30

connect by 구현 방법

조회 수 52371 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

 

Connect by 대체 방법

소개: Oracle connect by와 같은 계층구조 함수를 대체할 방법들을 소개한다..

 

2009-03-30 컨설팅팀 손승일, CUBRID

적용 대상: CUBRID2008, JDK1.6.0_04 이상

 Connect by 대체 방법

본 문서는 Oracle connect by와 같은 계층구조 함수를 대체할 방법으로 SQL을 사용하는 방법과 Java stored procedure을 이용하는 방법을 예제를 통해 설명하고자 한다.

본 문서에서 제시하는 방법은 connect by의 모든 기능을 대체하지는 못하며 단지 계층구조 처리를 위한 방법만을 설명한다.

이 문서에 사용되는 JDBC Program 방법 및 Java stored procedure의 상세한 내용에 대해서는 매뉴얼 또는 별도의 기술문서를 참고하기 바란다.

테이블 및 데이터 생성

계층구조 테스트를 위한 테스트 테이블 및 데이터를 생성한다.

create table dept(dept_id varchar(10), par_id varchar(10));

insert into dept values('001','');

insert into dept values('0011','001');

insert into dept values('0012','001');

insert into dept values('00111','0011');

insert into dept values('00112','0011');

insert into dept values('00121','0012');

insert into dept values('00122','0012');

insert into dept values(‘001121’,’00112’);

 

SQL 사용

나타내고자 하는 depth 만큼 자기 참조 방식으로 조인을 늘려 union all로 질의를 생성하여 구현한다.

Top down

select b.* from (

select 1,a.* from dept a where a.dept_id='001'

union all

select  2,a.* from dept a, dept b where a.par_id = b.dept_id and b.dept_id='001'

union ALL

select  3,a.* from dept a, dept b, dept c where a.par_id = b.dept_id and

b.par_id = c.dept_id and c.dept_id='001'

union ALL

select  4,a.* from dept a, dept b, dept c, dept d where a.par_id = b.dept_id and

b.par_id = c.dept_id and c.par_id = d.dept_id and d.dept_id='001') b

 

=== <Result of SELECT Command in Line 1> ===

 

          b_1  dept_id               par_id

=========================================================

            1  '001'                 ''

            2  '0011'                '001'

            2  '0012'                '001'

            3  '00111'               '0011'

            3  '00112'               '0011'

            3  '00121'               '0012'

            3  '00122'               '0012'

            4  '001121'              '00112'

Bottom up

select b.* from (

select 1,a.* from dept a where a.dept_id='001121'

union all

select  2,a.* from dept a, dept b where a.dept_id = b.par_id and b.dept_id='001121'

union ALL

select  3,a.* from dept a, dept b, dept c where a.dept_id = b.par_id and

b.dept_id = c.par_id and c.dept_id='001121'

union ALL

select  4,a.* from dept a, dept b, dept c, dept d  where a.dept_id = b.par_id and

b.dept_id = c.par_id and c.dept_id = d.par_id and d.dept_id='001121') b

 

=== <Result of SELECT Command in Line 1> ===

 

          b_1  dept_id               par_id

=========================================================

            1  '001121'              '00112'

            2  '00112'               '0011'

            3  '0011'                '001'

            4  '001'                 ''

 

Java SP를 이용

함수 재귀호출 방법을 사용하고 Java stored procedure을 통해 구현한다.

Java Stored Procedure

재귀호출을 통해 계층구조로 데이터를 추출할 Java Stored Procedure는 다음과 같다.

import java.util.*;

import java.sql.*;

 

public class spConnectby { 

    // top down

    public static String[] connectbyDown(String i_par_id) throws Exception

    {

              Connection conn = null;

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              String[] result = null;

              try {

                                  Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

                                  conn = DriverManager.getConnection("jdbc:default:connection:");

                                  // 재귀호출을 위해 auto commit false 설정

                                  conn.setAutoCommit(false);

                                  int i_lev = 0;

                                 

                                  // 재귀호출에 의해 추출된 Vector 데이터를 받을 in out 파라미터 변수.

                                  Vector[] io_retv = new Vector[1];

                                  io_retv[0] = new Vector<String[]>();

                                                                                    

                                  int r_lev = 0;

                                  String r_dept_id = "";

                                  String ret_val = "";

                                  // 0 level 데이터 추출

                                  String query = "select ? lev, dept_id " +

                                                                                                    "from dept where dept_id=?";

                                  pstmt = conn.prepareStatement(query);

                                  pstmt.setInt(1, i_lev);

                                  pstmt.setString(2, i_par_id);

                                  rs = pstmt.executeQuery();

                                  while(rs.next()){

                                                      r_lev = rs.getInt("lev");

                                                      r_dept_id = rs.getString("dept_id");

                                                      // 추출된 데이터를 문자열로 생성하여 Vector에 저장

                                                      ret_val = r_lev + "        " + r_dept_id;

                                                      io_retv[0].addElement(ret_val);

                                  }                   

                                  // 1 level 부터 데이터 추출

                                  connectbyDown(conn,i_par_id,i_lev,io_retv);

                                 

                                  conn.commit();

                                  //추출된 Vector 데이터를 반환할 sting 배열로 변환

                                  if (io_retv[0].size() != 0){

                                                      result = new String[io_retv[0].size()];

                                                      io_retv[0].copyInto(result);

                                  }

              }catch ( SQLException e ) {

                  e.printStackTrace();

              }catch ( Exception e ) {

                  e.printStackTrace();

              }finally {

                                                      if (rs != null) rs.close();

                  if (pstmt != null) pstmt.close();

                  if (conn != null) conn.close();

              }

              return result;

    }

    // 재귀호출을 통해 1 level 이상의 데이터를 추출한다.

    // 파라미터 : 생성된 connection, par_id, level, 추출된 데이터가 담긴 Vector in out 파라미터

    private static void connectbyDown(Connection conn, String i_par_id, int i_lev, Vector[] io_retv)throws Exception {

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              try{

                                  String query = "";

                                  String r_dept_id = "";

                                  String r_s_dept_id = "";

                                  int r_lev = 0;

                                  String ret_val="";             

                                 

                                  // par_id i_par_id인 데이터 조회

                                  query = "select ?+1 lev, dept_id, lpad(dept_id,length(dept_id) + ((?+1)*2),'-') s_dept_id " +

                                                                                                    "from dept where par_id=?";

                                  pstmt = conn.prepareStatement(query);

                                  pstmt.setInt(1, i_lev);

                                  pstmt.setInt(2, i_lev);

                                  pstmt.setString(3, i_par_id);

                                 

                                  rs = pstmt.executeQuery();

                                 

                                  while(rs.next()){

                                                     

                                                      r_lev = rs.getInt("lev");

                                                      r_dept_id = rs.getString("dept_id");

                                                      r_s_dept_id = rs.getString("s_dept_id");

                                                     

                                                      // 추출된 데이터를 문자열로 생성하여 Vector에 저장

                                                      ret_val = r_lev + "        " + r_s_dept_id;

                                                      io_retv[0].addElement(ret_val);

                                                      i_lev = i_lev + 1;

                                                     

                                                      // 하위 level 재귀호출, par_id가 추출된 r_dept_id인 데이터 조회

                                                      connectbyDown(conn,r_dept_id,i_lev,io_retv);

                                                      i_lev = i_lev - 1;

                                  }

              }catch ( SQLException e ) {

                  e.printStackTrace();

              }catch ( Exception e ) {

                  e.printStackTrace();

              }finally {

                  if (rs != null) rs.close();

                  if (pstmt != null) pstmt.close();

              }                                       

    }

    // bottom up

    public static String[] connectbyUp(String i_dept_id) throws Exception

    {

              Connection conn = null;

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              String[] result = null;

              try {

                                  Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

                                  conn = DriverManager.getConnection("jdbc:default:connection:");

                                  // 재귀호출을 위해 auto commit false 설정

                                  conn.setAutoCommit(false);

                                  int i_lev = 0;

                                 

                                  // 재귀호출에 의해 추출된 Vector 데이터를 받을 in out 파라미터 변수.

                                  Vector[] io_retv = new Vector[1];

                                  io_retv[0] = new Vector<String[]>();

                                 

                                  int r_lev = 0;

                                  String r_dept_id = "";

                                  String ret_val = "";

                                  String r_par_id = "";

                                  // 0 level 데이터 추출

                                  String query = "select ? lev, dept_id, par_id " +

                                                                                                    "from dept where dept_id=?";

                                  pstmt = conn.prepareStatement(query);

                                  pstmt.setInt(1, i_lev);

                                  pstmt.setString(2, i_dept_id);

                                  rs = pstmt.executeQuery();

                                  while(rs.next()){

                                                      r_lev = rs.getInt("lev");

                                                      r_dept_id = rs.getString("dept_id");

                                                      r_par_id = rs.getString("par_id");

                                                      // 추출된 데이터를 문자열로 생성하여 Vector에 저장

                                                      ret_val = r_lev + "        " + r_dept_id;

                                                      io_retv[0].addElement(ret_val);

                                  }

                                  // 1 level 부터 데이터 추출, dept_id가 추출된 r_par_id인 데이터 조회

                                  connectbyUp(conn,r_par_id,i_lev,io_retv);

                                 

                                  conn.commit();

                                  //추출된 Vector 데이터를 반환할 sting 배열로 변환

                                  if (io_retv[0].size() != 0){

                                                      result = new String[io_retv[0].size()];

                                                      io_retv[0].copyInto(result);

                                  }

              }catch ( SQLException e ) {

                  e.printStackTrace();

              }catch ( Exception e ) {

                  e.printStackTrace();

              }finally {

                                                      if (rs != null) rs.close();

                  if (pstmt != null) pstmt.close();

                  if (conn != null) conn.close();

              }

              return result;

    }

    // 재귀호출을 통해 1 level 이상의 데이터를 추출한다.

    // 파라미터 : 생성된 connection, dept_id, level, 추출된 데이터가 담긴 Vector in out 파라미터

    private static void connectbyUp(Connection conn, String i_dept_id, int i_lev, Vector[] io_retv)throws Exception {

              PreparedStatement pstmt = null;

              ResultSet rs = null;

              try{

                                  String query = "";

                                  String r_par_id = "";

                                  String r_s_dept_id = "";

                                  int r_lev = 0;

                                  String ret_val="";             

                                  // dept_id i_dept_id인 데이터 조회

                                  query = "select ?+1 lev, par_id, lpad(dept_id,length(dept_id) + ((?+1)*2),'-') s_dept_id " +

                                                                                                    "from dept where dept_id=?";

                                  pstmt = conn.prepareStatement(query);

                                  pstmt.setInt(1, i_lev);

                                  pstmt.setInt(2, i_lev);

                                  pstmt.setString(3, i_dept_id);

                                 

                                  rs = pstmt.executeQuery();

                                 

                                  while(rs.next()){

                                                     

                                                      r_lev = rs.getInt("lev");

                                                      r_par_id = rs.getString("par_id");

                                                      r_s_dept_id = rs.getString("s_dept_id");

                                                     

                                                      // 추출된 데이터를 문자열로 생성하여 Vector에 저장

                                                      ret_val = r_lev + "        " + r_s_dept_id;

                                                      io_retv[0].addElement(ret_val);

                                                      i_lev = i_lev + 1;

                                                     

                                                      // 하위 level 재귀호출, dept_id가 추출된 r_par_id인 데이터 조회

                                                      connectbyUp(conn,r_par_id,i_lev,io_retv);

                                                      i_lev = i_lev - 1;

                                  }

              }catch ( SQLException e ) {

                  e.printStackTrace();

              }catch ( Exception e ) {

                  e.printStackTrace();

              }finally {

                  if (rs != null) rs.close();

                  if (pstmt != null) pstmt.close();

              }                                       

    }

}

위 소스를 spConnectby.java로 생성하고 컴파일 한 후 loadjava db_name spConnectby.class 를 실행하여 자바클래스를 DB에 로드 한 후 아래 구문과 같이 DB에 등록한다.

create function connectby_up(i_dept_id varchar) return sequence as language java

name 'spConnectby.connectbyUp(java.lang.String) return java.lang.String[]';

 

create function connectby_down(i_par_id varchar) return sequence as language java

name 'spConnectby.connectbyDown(java.lang.String) return java.lang.String[]';

 

데이터 확인

Java sp를 호출하여 계층구조 데이터를 가져오는 간단한 예제이다.

import java.sql.*;

import cubrid.jdbc.driver.*;

 

public class selConnectby{

  public static void main(String[] args) throws Exception {

    Connection conn = null;

    CallableStatement cstmt= null;

 

    try{

        Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

        conn = DriverManager.getConnection("jdbc:CUBRID:localhost:33000:demodb:::","","");

                                                      // 입력 받은 코드에 해당하는 데이터 조회

                                                      // bottom up

                                                      if (args.length !=0 && "up".equals(args[0])) {

            cstmt = conn.prepareCall("?=CALL connectby_up(?)");

            cstmt.registerOutParameter(1, Types.JAVA_OBJECT);      

            cstmt.setString(2, args[1]);

            cstmt.execute();

            // connect by의 결과는 String 배열로 리턴되어 진다.

            String[] result = (String[]) cstmt.getObject(1);

                                                                      for (int i=0;i < result.length;i++){

                System.out.println(result[i]);

            }

            cstmt.close();

                                                      }               else if (args.length !=0 && "down".equals(args[0])){

                                                                      // top down

                                                                      cstmt = conn.prepareCall("?=CALL connectby_down(?)");

            cstmt.registerOutParameter(1, Types.JAVA_OBJECT);      

            cstmt.setString(2, args[1]);

            cstmt.execute();

            // connect by의 결과는 String 배열로 리턴되어 진다.

            String[] result = (String[]) cstmt.getObject(1);              

                                                                     for (int i=0;i < result.length;i++){

                System.out.println(result[i]);

            }

            cstmt.close();

                                                      }

    } catch (Exception e) {

        e.printStackTrace();

    } finally {

                                 if ( cstmt != null ) cstmt.close();

        if ( conn != null ) conn.close();

    }

  }

}

위 소스를 컴파일 한 후 실행 한 결과는 아래와 같다.

Bottom up 의 경우는 인자로 up과 조회하고자 하는 dept_id를 입력하다.

c:test>java -classpath c:testcubrid_jdbc.jar; selConnectby up 00112

0       00112

1       --0011

2       ----001

위는 Oracle의 아래 질의와 동일하다.

select dept_id

from dept

start with dept_id = ‘00112’

   connect by prior par_id = dept_id;

Top down 의 경우는 인자로 down과 조회하고자 하는 dept_id를 입력한다.

c:test>java -classpath c:testcubrid_jdbc.jar; selConnectby down 001

0       001

1       --0011

2       ----00111

2       ----00112

1       --0012

2       ----00121

2       ----00122

위는 Oracle의 아래 질의와 동일 하다.

select dept_id

from dept

start with dept_id = ‘001’

   connect by prior dept_id = par_id;

 

이상은 Oracle connect by의 기본 기능인 계층 구조로 가져오는 예제이다. 좀 더 다양한 기능은 이 예제를 기본으로 하여 좀 더 구체화 시켜야 할 것이다.


  1. CCI-API 에서 OID 사용 예제

    Date2009.07.01 CategoryCCI/DB API By남재우 Views24861
    Read More
  2. CCI-API 사용 예제

    Date2009.07.01 CategoryCCI/DB API By남재우 Views25103
    Read More
  3. CUBRID_기술문서_CUBRID PHP 함수 unisql_ 계열 cubrid_로 변환하기

    Date2009.06.23 CategoryPHP By남재우 Views22355
    Read More
  4. cubrid_fetch_all 함수 추가하기

    Date2009.06.16 CategoryPHP By남재우 Views20828
    Read More
  5. Oracle의 SYS_CONNECT_BY_PATH 함수 흉내내기

    Date2009.06.15 Category기타 ByCUBRID_DEV Views26526
    Read More
  6. CUBRID에서 log4sql을 사용하자

    Date2009.06.12 CategoryJava By정병주 Views44302
    Read More
  7. 오래 걸리는 질의 및 동일 시간에 수행 중인 질의 추출하기

    Date2009.05.26 Category기타 By손승일 Views20058
    Read More
  8. jsp용 jdbc 샘플입니다.

    Date2009.05.25 CategoryJava By시난 Views47606
    Read More
  9. iBATIS framework with CUBRID

    Date2009.05.19 CategoryJava By웁쓰 Views54546
    Read More
  10. CUBRID와 타DBMS의 python 사용방법 비교

    Date2009.05.19 Category기타 Bycubebridge Views29520
    Read More
  11. SQuirreL SQL Client에서 CUBRID 사용하기

    Date2009.04.18 CategoryJava ByPrototype Views34533
    Read More
  12. Power*Architect 에서 CUBRID 사용하기

    Date2009.04.18 CategoryJava ByPrototype Views23879
    Read More
  13. AquaDataStudio 에서 CUBRID 사용하기

    Date2009.04.18 CategoryJava ByPrototype Views23666
    Read More
  14. CUBRID Manager 사용시 로그인에 대하여

    Date2009.04.01 CategoryInstall Byadmin Views42038
    Read More
  15. Spring framework 사용 예제

    Date2009.04.01 CategoryJava Byadmin Views53119
    Read More
  16. connect by 구현 방법

    Date2009.04.01 Category기타 Byadmin Views52371
    Read More
  17. python 에서 broker_log_top 사용

    Date2009.04.01 Category기타 Byadmin Views17830
    Read More
  18. 오라클 개발자를 위한 CUBRID 개발 안내

    Date2009.04.01 Category기타 Byadmin Views49817
    Read More
  19. HP-UX에서 CUBRID 사용을 위한 kernel 설정값 조정

    Date2009.04.01 CategoryLinux Byadmin Views54135
    Read More
  20. cron을 이용한 백업 자동화

    Date2009.04.01 CategoryLinux Byadmin Views33118
    Read More
Board Pagination Prev 1 2 3 4 5 6 7 8 9 Next
/ 9

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales