Connect by 대체 방법
소개: Oracle의 connect by와 같은 계층구조 함수를 대체할 방법들을 소개한다..
2009-03-30 컨설팅팀 손승일, CUBRID
적용 대상: CUBRID2008, JDK1.6.0_04 이상
본 문서는 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’);
나타내고자 하는 depth 만큼 자기 참조 방식으로 조인을 늘려 union all로 질의를 생성하여 구현한다.
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'
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 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의 기본 기능인 계층 구조로 가져오는 예제이다. 좀 더 다양한 기능은 이 예제를 기본으로 하여 좀 더 구체화 시켜야 할 것이다.
