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를 사용하면, AUTO COMMIT을 TRUE로 설정하였더라도 AUTO COMMIT이 되지 않는다. 따라서 항상 open한 연결에 대해 명시적으로 커밋을 해야 한다. CUBRID 확장 API는 OID, 컬렉션, GLO 등을 다루는 메소드이다.
OID를 사용할 때 다음의 규칙을 지켜야 한다.
import java.sql.*;
import cubrid.sql.*; //a
import cubrid.jdbc.driver.*;
/*
CREATE TABLE oid_test(
id INTEGER,
name VARCHAR(10),
age INTEGER
);
INSERT INTO oid_test VALUES(1, 'Laura', 32);
INSERT INTO oid_test VALUES(2, 'Daniel', 39);
INSERT INTO oid_test VALUES(3, 'Stephen', 38);
*/
class OID_Sample
{
public static void main (String args [])
{
// Making a connection
String url= "jdbc:cubrid:localhost:33000:demodb:::";
String user = "dba";
String passwd = "";
// SQL statement to get OID values
String sql = "SELECT oid_test from oid_test"; //b
// columns of the table
String[] attr = { "id", "name", "age" } ;
// Declaring variables for Connection and Statement
Connection con = null;
Statement stmt = null;
CUBRIDResultSet rs = null;
ResultSetMetaData rsmd = null;
try {
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
} catch (ClassNotFoundException e) {
throw new IllegalStateException("Unable to load Cubrid driver", e);
}
try {
con = DriverManager.getConnection(url, user, passwd);
stmt = con.createStatement();
rs = (CUBRIDResultSet)stmt.executeQuery(sql); //c
rsmd = rs.getMetaData();
// Printing columns
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");
// Printing rows
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");
// New values of the first row
Object[] value = { 4, "Yu-ri", 19 };
if (k == 1) oid.setValues(attr, value); //f
k = 0;
}
con.commit(); //g
} catch(CUBRIDException e) {
e.printStackTrace();
} catch(SQLException ex) {
ex.printStackTrace();
} finally {
if(rs != null) try { rs.close(); } catch(SQLException e) {}
if(stmt != null) try { stmt.close(); } catch(SQLException e) {}
if(con != null) try { con.close(); } catch(SQLException e) {}
}
}
}
아래 예제 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를 사용하려면 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();
}
}
}