Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 



Using OIDs and Collections

In addition to the methods defined in the JDBC specification, the CUBRID JDBC driver provides methods that handle OIDs and collections (set, multiset and sequence).

To use these methods, you must import cubrid.sql.*; in addition to the CUBRID JDBC driver classes which are imported by default. In addition, to get the results, you must convert ResultSet to CUBRIDResultSet first. (ResultSet is provided by the standard JDBC API, by default.)

import cubrid.jdbc.driver.* ;

import cubrid.sql.* ;

...

CUBRIDResultSet urs = (CUBRIDResultSet) stmt.executeQuery(

"SELECT city FROM location");

Caution AUTO COMMIT does not work even though it is configured to TRUE if CUBRID extended APIs are used. Therefore, you must manually commit open connections. The CUBRID extended APIs are methods that handle OIDs and collections.

Using OIDs

You must follow the following rules to use OIDs.

  • To use CUBRIDOID, you should import cubrid.sql.*;. (a)
  • You can retrieve an OID by specifying a class name in the SELECT statement. The name can be used together with other attributes. (b)
  • The ResultSet of a query must be CUBRIDResultSet. (c)
  • The method that retrieves the OID from the CUBRIDResultSet is getOID(). (d)
  • To retrieve a value from an OID, use the getValues() method. Its result is ResultSet. (e)
  • To substitute a value for an OID, use the setValues() method. (f)
  • When you use the extended APIs, you must always perform commit() to make connection. (g)

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(" ");

         // 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(" ");

 

            // 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) {}

      }

   }

}

Using Collections

The line marked by 'a' in the example 1 below is where data of a collection type (SET, MULTISET, LIST) is fetched from the CUBRIDResultSet. The results are returned as array format. Note that this function is supported only when data types of elements defined in the collection type are same.

Example 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:127.0.0.1:33000: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(" ");

           }

           rs.close();

           stmt.close();

           con.close();

       } catch(SQLException e) {

           e.printStackTrace();

       }

   }

}

Example 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:127.0.0.1:33000: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();

       }

   }

}