OID와 컬렉션 사용

JDBC 스펙에 정의된 메소드 이외에 CUBRID JDBC 드라이버에서 추가로 OID, 컬렉션(set, multiset, sequence), GLO 등을 다루는 메소드를 제공한다.

이 메소드의 사용을 위해서는 기본적으로 import하는 CUBRID JDBC 드라이버 클래스 이외에 cubrid.sql.*;를 import해야 한다. 또한 표준 JDBC API에서 제공하는 ResultSet 클래스가 아닌 CUBRIDResultSet 클래스로 변환하여 결과를 받아야 한다.

import cubrid.jdbc.driver.* ;
import cubrid.sql.* ;
...
CUBRIDResultSet urs = (CUBRIDResultSet) stmt.executeQuery(
"SELECT city FROM location");

또한 CUBRID의 확장 API를 사용하면 항상 open한 연결에 대해 커밋를 해주어야 한다. 보다 자세한 것은 표준 JDBC 확장 메소드와 CUBRIDOID 클래스의 메소드를 참고하기 바란다.

OID사용

OID를 사용할 때 다음의 규칙을 지켜야 한다.

import java.sql.*;
import java.lang.*;
import cubrid.sql.*; //a
import cubrid.jdbc.driver.*;

class OID_Sample
{
   public static void main (String args [])
   {
       String url= "jdbc:cubrid:210.216.33.250:43300:demodb:::";
       String user = "";
       String passwd = "";
       String sql = "select location from location"; //b
       String[] attr = { "lodging", "country", "city",
                       "street", "latitude", "longitude" } ;
       Object[] value = { "Korea Hotel", "Korea", "Seoul","NA",
                       new Float(36.5), new Float(55.5) };
       try {
           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
       } catch(Exception e) {
           e.printStackTrace();
       }
       try {
           Connection con = DriverManager.getConnection(url,user,passwd);
           Statement stmt = con.createStatement();
           CUBRIDResultSet rs = (CUBRIDResultSet)stmt.executeQuery(sql); //c
           ResultSetMetaData rsmd = rs.getMetaData();
           int numOfColumn = rsmd.getColumnCount();
           for (int i=1; i <= numOfColumn; i++ ) {
               String ColumnName=rsmd.getColumnName(i);
               String JdbcType=rsmd.getColumnTypeName(i);
               System.out.print(ColumnName );
               System.out.print("("+ JdbcType + ")");
               System.out.print(" | ");
           }
           System.out.print("\n");
           CUBRIDResultSet rsoid = null;
           int k=1;
           while (rs.next ()) {
               CUBRIDOID oid = rs.getOID(1); //d
               System.out.print("OID");
               System.out.print(" | ");
               rsoid=(CUBRIDResultSet)oid.getValues(attr); //e
               while (rsoid.next()) {
                   for( int j=1; j <= attr.length; j++ ) {
                       System.out.print(rsoid.getObject(j));
                       System.out.print(" | ");
                   }
               }
               System.out.print("\n");
               if (k == 1) {
                   oid.setValues(attr, value); //f
               }
               k = 0;
           }
           con.commit(); //g
           rs.close();
           stmt.close();
           con.close();
       } catch(SQLException e) {
           //System.err.println(e.getMessage());
           e.printStackTrace();
       }
   }
}

컬렉션 사용

아래 예제 1의 ‘a’에 해당하는 부분이 CUBRIDResultSet으로부터 컬렉션 타입의 데이터를 가져오는 부분으로 그 결과는 배열 형태로 반환한다.

예제 1)

import java.sql.*;
import java.lang.*;
import cubrid.sql.*;
import cubrid.jdbc.driver.*;
 
// create class collection_test(
// settest set(integer),
// multisettest multiset(integer),
// listtest list(Integer)
// );
//
 
// insert into collection_test values({1,2,3},{1,2,3},{1,2,3});
// insert into collection_test values({2,3,4},{2,3,4},{2,3,4});
// insert into collection_test values({3,4,5},{3,4,5},{3,4,5});
 
class Collection_Sample
{
   public static void main (String args [])
   {
       String url= "jdbc:cubrid:210.216.33.250:43300:demodb:::";
       String user = "";
       String passwd = "";
       String sql = "select settest,multisettest,listtest from collection_test";
       try {
           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
       } catch(Exception e){
           e.printStackTrace();
       }
       try {
           Connection con = DriverManager.getConnection(url,user,passwd);
           Statement stmt = con.createStatement();
           CUBRIDResultSet rs = (CUBRIDResultSet) stmt.executeQuery(sql);
           CUBRIDResultSetMetaData rsmd = (CUBRIDResultSetMetaData) rs.getMeta Data();
           int numbOfColumn = rsmd.getColumnCount();
           while (rs.next ()) {
               for (int j=1; j<=numbOfColumn; j++ ) {
                   Object[] reset = (Object[]) rs.getCollection(j); //a
                   for (int m=0 ; m < reset.length ; m++)
                       System.out.print(reset[m] +",");
                   System.out.print(" | ");
               }
               System.out.print("\n");
           }
           rs.close();
           stmt.close();
           con.close();
       } catch(SQLException e) {
           e.printStackTrace();
       }
   }
}

예제 2)

import java.sql.*;
import java.io.*;
import java.lang.*;
import cubrid.sql.*;
import cubrid.jdbc.driver.*;
 
// create class collection_test(
// settest set(integer),
// multisettest multiset(integer),
// listtest list(Integer)
// );
//
// insert into collection_test values({1,2,3},{1,2,3},{1,2,3});
// insert into collection_test values({2,3,4},{2,3,4},{2,3,4});
// insert into collection_test values({3,4,5},{3,4,5},{3,4,5});
 
class SetOP_Sample
{
   public static void main (String args [])
   {
       String url = "jdbc:cubrid:210.216.33.250:43300:demodb:::" ;
       String user = "";
       String passwd = "";
       String sql = "select collection_test from collection_test";
       try {
           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
       } catch(Exception e){
           e.printStackTrace();
       }
       try {
           CUBRIDConnection con =(CUBRIDConnection)
           DriverManager.getConnection(url,user,passwd);
           Statement stmt = con.createStatement();
           CUBRIDResultSet rs = (CUBRIDResultSet)stmt.executeQuery(sql);
           while (rs.next ()) {
               CUBRIDOID oid = rs.getOID(1);
               oid.addToSet("settest",new Integer(10));
               oid.addToSet("multisettest",new Integer(20));
               oid.addToSequence("listtest",1,new Integer(30));
               oid.addToSequence("listtest",100,new Integer(100));
               oid.putIntoSequence("listtest",99,new Integer(99));
               oid.removeFromSet("settest",new Integer(1));
               oid.removeFromSet("multisettest",new Integer(2));
               oid.removeFromSequence("listtest",99);
               oid.removeFromSequence("listtest",1);
           }
           con.commit();
           rs.close();
           stmt.close();
           con.close();
       } catch(SQLException e) {
           e.printStackTrace();
       }
   }
}

GLO 사용

GLO를 사용하려면 DriverManager를 통해 연결할 때 반드시 CUBRIDConnection으로 해야 한다. CUBRIDConnection으로부터 얻은 연결은 getNewGLO() 메소드를 사용해서 FileInputStream 객체를 입력할 수 있다. 그 결과로 CUBRIDOID 클래스의 인스턴스가 반환된다.

예제 1)

import java.sql.*;
import java.sql.*;
import java.io.*;
import java.lang.*;
import cubrid.jdbc.driver.*;
import cubrid.sql.*;
 
//-----------------------------------------------------------
// create class filetest under glo
//
// assumes that testin1, testin2 files are in the location
// of the execution of this program.
// result of theexecution will create in the location
// of execution,
//------------------------------------------------------------
 
class GLO_Sample
{
   public static void main (String args [])
   {
       String url = "jdbc:cubrid:210.216.33.250:43300:demodb:::" ;
       String user = "";
       String passwd = "";
       try {
           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
       } catch(Exception e){
           e.printStackTrace();
       }
       try {
           CUBRIDConnection con = (CUBRIDConnection)
           DriverManager.getConnection(url,user,passwd);
           FileInputStream inputfile = new FileInputStream("testin");
           CUBRIDOID oid = con.getNewGLO("filetest", inputfile);
           inputfile.close();
           System.out.println("getNewGLO1..");
           FileOutputStream outputfile = new FileOutputStream("testout");
           oid.loadGLO(outputfile);
           outputfile.close();
           System.out.println("loadGLO1..");
           inputfile = new FileInputStream("testin2");
           oid.saveGLO( inputfile );
           inputfile.close();
           System.out.println("saveGLO2..");
           outputfile = new FileOutputStream("testout2");
           oid.loadGLO(outputfile);
           outputfile.close();
           System.out.println("loadGLO2..");
           con.commit();
           con.close();
       } catch(SQLException e) {
           e.printStackTrace();
       } catch(Exception e) {
           e.printStackTrace();
       }
   }
}

예제 2)

import java.sql.*;
import java.lang.*;
import cubrid.jdbc.driver.*;
import cubrid.sql.*;
 
class SetOID_Sample
{
   public static void main (String args [])
   {
       String url = "jdbc:cubrid:210.216.33.250:43300:demodb:::" ;
       String user = "";
       String passwd = "";
       String usql = "update location set city='Pusan' where location=?";
       String sql = "select location, city from location" ;
 
       try {
           Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
       } catch(Exception e){
           e.printStackTrace();
       }
       try {
           CUBRIDConnection con = (CUBRIDConnection)
           DriverManager.getConnection(url,user,passwd;
           Statement stmt = con.createStatement();
           CUBRIDResultSet rs = (CUBRIDResultSet)stmt.executeQuery(sql);
           CUBRIDOID oid = null;
 
           rs.next()
           oid = rs.getOID(1);
            
           CUBRIDPreparedStatement ps = (CUBRIDPreparedStatement)
           con.prepareStatement(usql);
    
           ps.setOID(1, oid); // OID of the last tuple
           ps.executeUpdate();
           rs.close();
           stmt.close();
           con.close();
       } catch(SQLException e) {
           e.printStackTrace();
       }
    }
}