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