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의 기본 기능인 계층 구조로 가져오는 예제이다. 좀 더 다양한 기능은 이 예제를 기본으로 하여 좀 더 구체화 시켜야 할 것이다.


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
166 기타 자주 발생하는 큐브리드(cubrid) 에러 메시지 정리 admin 2011.07.14 81365
165 Linux 리눅스에서 top 명령어를 통한 CPU 점유율 확인 및 측정하기 file 정훈 2017.06.02 79925
164 Java CUBRID와 Oracle의 Clob 타입 사용 방법 비교. seongjoon 2012.06.30 54549
163 Java iBATIS framework with CUBRID file 웁쓰 2009.05.19 54546
162 Linux HP-UX에서 CUBRID 사용을 위한 kernel 설정값 조정 1 admin 2009.04.01 54135
161 Java Spring framework 사용 예제 admin 2009.04.01 53119
160 기타 [주의사항] CUBRID에서의 BLOB/CLOB 사용시 백업 및 복구에 대한 주의 점 admin 2011.07.14 52555
» 기타 connect by 구현 방법 admin 2009.04.01 52371
158 PHP windows 상에서 PHP 설치하기 file admin 2008.11.21 51804
157 기타 오라클 개발자를 위한 CUBRID 개발 안내 1 admin 2009.04.01 49817
156 Java DBCP 사용법 1 admin 2008.11.21 48362
155 Windows CUBRID 2008 사용법 admin 2008.11.21 47933
154 Java jsp용 jdbc 샘플입니다. file 시난 2009.05.25 47606
153 기타 CUBRID 활용 - 질의 튜닝 사례 중심 file cubrid 2010.12.02 45960
152 Java CUBRID에서 log4sql을 사용하자 정병주 2009.06.12 44302
151 Install Windows 에서 CUBRID 설치하기 file 남재우 2008.11.22 43641
150 PHP LINUX에서 PHP 사용하기 file admin 2008.11.21 42495
149 Install CUBRID Manager 사용시 로그인에 대하여 1 file admin 2009.04.01 42038
148 PHP Apache, PHP 설치 방법 file 이용미 2013.05.16 41246
147 기타 CUBRID 개발 로드맵 admin 2008.12.27 40321
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