JDBC Programming

• Configuration Connection
• Checking Foreign Key Information
• Using Object Identifiers (OIDs) and Collections
• Getting Auto Increment Column Values
• Using BLOB/CLOB

Configuration Connection

The DriverManager is an interface for managing the JDBC driver. It is used to select a driver and create new database connection. If CUBRID JDBC driver is registered, you can connect a database by calling the DriverManager.getConnection(db-url, user-id, password) method. The getConnection method returns the Connection object and it is used to execute queries and commands, and commit and roll back transactions. The syntax below shows the db-url argument for configuring connection.

jdbc:cubrid:< host> :< port> :< db-name> :[user-id]:[password]:[?< property> [& < property> ]]

 

< host> ::=

hostname | ip_address

 

< property> ::=

altHosts=< alternative_hosts> | rcTime=< second> | connectTimeout=< second> | queryTimeout=< second> | charSet=< character_set> | zeroDateTimeBehavior=< behavior_type> | logFile=< file_name> | logOnException=< bool_type> | logSlowQueries=< bool_type> & slowQueryThresholdMillis=< millisecond>

< alternative_hosts> ::=

< standby_broker1_host> :< port> [,< standby_broker2_host> :< port> ]

< behavior_type> ::= exception | round | convertToNull

< bool_type> ::= true | false

• < host> : IP address or host name where the CUBRID broker is running
• < port> : The CUBRID broker port number (default value: 33,000)
• < db-name> : The name of the database to connect
• [user-id]: The user ID which is connected to a database. There are two types of users in a database by default: DBA and PUBLIC. If an empty string (" " ) is entered, a database is connected by the PUBLIC user.
• [password]: The password of a user who is to be connected to a database. If no password is set, enter an empty string (" " ).
• altHosts: The host IP addresses and connection ports of one or more stand by brokers which will perform failover in the HA environment.
• rcTime: Interval time (in seconds) to try to connect active brokers during failover in the HA environment. For more information, see the example in " Administrator's Guide > CUBRID HA > Environment Configuration > JDBC Configuration."
• connectTimeout: Timeout value (in seconds) for database connection (default value: 0). The DriverManger.setLoginTimeout() method can be used to configure it however, value configured in this method will be ignored if a value is configured in the connection URL.
• queryTimeout: Timeout value (in seconds) for query execution (default value: 0, infinite). This value can be changed by the DriverManger.setQueryTimeout() method.
• charSet: The character set of a database to be connected
• zeroDateTimeBehavior: The property used to determine the way to handle an output value because JDBC does not allow a value having zero for both date and time regardless of date and time in the object with the java.sql.Date type. The default operation is exception. For information about the value having zero for both date and date, see " CUBRID SQL Guide > Data Type > Date/Time Data Type > Definition and Characteristics". The operation for each configuration is as follows:
  • exception: Default operation. It is handled as an SQLException exception.
  • round: Converts to the minimum value allowed for a type to be returned.
  • convertToNull: Converts to NULL.

• logFile: The name of a log file for debugging (default value: cubrid_jdbc.log). If a path is not configured, it is stored the location  where applications are running.
• logOnException: Whether to log exception handling for debugging (default value: false)
• logSlowQueries: Whether to log slow queries for debugging (default value: false)
• slowQueryThresholdMillis: Timeout value (in milliseconds) of slow queries (default value: 60,000).

Example 1

--connection URL string when user name and password omitted

 

URL=jdbc:CUBRID:192.168.0.1:33000:db1:::

 

--connection URL string when zeroDateTimeBehavior property specified

URL=jdbc:CUBRID:127.0.0.1:31000:db1:::?zeroDateTimeBehavior=convertToNull

 

--connection URL string when charSet property specified

 

URL=jdbc:CUBRID:192.168.0.1:33000:db1:::?charSet=utf-8

 

--connection URL string when queryTimeout and charSet property specified

 

URL=jdbc:CUBRID:127.0.0.1:31000:db1:::?queryTimeout=1& charSet=utf-8

 

--connection URL string when a property(altHosts) specified for HA

URL=jdbc:CUBRID:192.168.0.1:33000:db1:::?altHosts=192.168.0.2:33000,192.168.0.3:33000

 

--connection URL string when properties(altHosts,rcTime, connectTimeout) specified for HA

URL=jdbc:CUBRID:192.168.0.1:33000:db1:::?altHosts=192.168.0.2:33000,192.168.0.3:33000& rcTime=600& connectTimeout=5

 

--connection URL string when properties(altHosts,rcTime, charSet) specified for HA

URL=jdbc:CUBRID:192.168.0.1:33000:db1:::?altHosts=192.168.0.2:33000,192.168.0.3:33000& rcTime=600& charSet=utf-8

Example 2

String url = " jdbc:cubrid:192.168.0.1:33000:demodb:::"

String userid = " "

String password = " "

 

try {

      Connection conn =

                      DriverManager.getConnection(url,userid,password)

      // Do something with the Connection

 

      ...

 

      } catch (SQLException e) {

              System.out.println(" SQLException:" + e.getMessage())

              System.out.println(" SQLState: " + e.getSQLState())

      }

      ...

Caution

• Because a colon (:) and a question mark are used as a separator in the URL string, it is not allowed to use them as parts of a password. To use them in a password, you must specify a user name (user-id) and a password (password) as a separate argument in the getConnection method.

Note The rollback method requesting transaction rollback will exit after a server complete the rollback job.

Checking Foreign Key Information

Description

You can check foreign key information by using getImportedKeys, getExportedKeys, and getCrossReference methods of the DatabaseMetaData interface. The usage and example of each method are as follows:

Syntax

getImportedKeys(String catalog, String schema, String table)

getExportedKeys(String catalog, String  schema, String  table)

getCrossReference(String parentCatalog, String  parentSchema, String  parentTable, String  foreignCatalog, String  foreignSchema, String  foreignTable)

• getImportedKeys method: Retrieves information of primary key columns which are referred by foreign key columns in a given table. The results are sorted by PKTABLE_NAME and KEY_SEQ.
• getExportedKeys method: Retrieves information of all foreign key columns which refer to primary key columns in a given table. The results are sorted by FKTABLE_NAME and KEY_SEQ.
• getCrossReference method: Retrieves information of primary key columns which are referred by foreign key columns in a given table. The results are sorted by PKTABLE_NAME and KEY_SEQ.

Return Value

When the methods above are called, the ResultSet consisting of 14 columns listed in the table below is returned.

Name	Type	Note
PKTABLE_CAT	String	null without exception
PKTABLE_SCHEM	String	null without exception
PKTABLE_NAME	String	The name of a primary key table
PKCOLUMN_NAME	String	The name of a primary key column
FKTABLE_CAT	String	null without exception
FKTABLE_SCHEM	String	null without exception
FKTABLE_NAME	String	The name of a foreign key table
FKCOLUMN_NAME	String	The name of a foreign key column
KEY_SEQ	short	Sequence of columns of foreign keys or primary keys (starting from 1)
UPDATE_RULE	short	The corresponding values to referring actions defined as to foreign keys when primary keys are updated. Cascade=0, Restrict=2, No action=3, Set null=4
DELETE_RULE	short	The corresponding value to referring actions defined as to foreign keys when primary keys are deleted. Cascade=0, Restrict=2, No action=3, Set null=4
FK_NAME	String	Foreign key name
PK_NAME	String	Primary key name
DEFERRABILITY	short	6 without exception (DatabaseMetaData.importedKeyInitiallyImmediate)

Example

ResultSet rs = null

 

                                DatabaseMetaData dbmd = conn.getMetaData()

 

                                System.out.println(" ===== Test getImportedKeys" )

                                System.out.println(" =====" )

                                rs = dbmd.getImportedKeys(null, null, " pk_table" )

                                Test.printFkInfo(rs)

                                rs.close()

 

                                System.out.println(" ===== Test getExportedKeys" )

                                System.out.println(" =====" )

                                rs = dbmd.getExportedKeys(null, null, " fk_table" )

                                Test.printFkInfo(rs)

                                rs.close()

 

                                System.out.println(" ===== Test getCrossReference" )

                                System.out.println(" =====" )

                                rs = dbmd.getCrossReference(null, null, " pk_table" , null, null, " fk_table" )

                                Test.printFkInfo(rs)

                                rs.close()

Using Object Identifiers (OIDs) and Collections

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

To use these methods, you must import cubrid.sql.* as well as the CUBRID JDBC driver classes which are imported by default. Furthermore, you should convert to not the  ResultSet class, which is provided by the standard JDBC API) but the CUBRIDResultSet class to get result.

import cubrid.jdbc.driver.*

import cubrid.sql.*

...

CUBRIDResultSet urs = (CUBRIDResultSet) stmt.executeQuery(

" SELECT city FROM location" )

Warning If extended API is used, transactions won't be automatically committed even though AUTOCOMMIT is set to TRUE. Therefore, you must explicitly commit transactions for open connections. The extended API of CUBRID is method that handle OIDs, collections, etc.

Using OIDs

You must follow the rules below when using OIDs.

• To use CUBRIDOID, you must import cubrid.sql.*. (a)
• You can get OIDs by specifying the class name in the SELECT statement. It can also be used together with other properties. (b)
• ResultSet for queries must be received by CUBRIDResultSet. (c)
• The method to get OIDs from CUBRIDResultSet is getOID(). (d)
• You can get the value from OIDs by using the getValues() method and the result will be ResultSet. (e)
• You can substitute OID with a value by using the setValues() method. (f)
• When you use extended API, you must always execute commit() for connection. (g)

Example

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 " a" in the example 1 is where data of collection types (SET, MULTISET, and LIST) is fetched from CUBRIDResultSet. The results are returned as array format. Note that this can be used only when data types of all elements defined in the collection types 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()

              }

      }

}

Getting Auto Increment Column Value

Auto Increment

Auto increment (AUTO_INCREMENT) is a column-related feature that increments the numeric value of each row. For more information, see " CUBRID SQL Guide > Table Definition > CREATE TABLE > Column Definition." It can be defined only for numeric domains (SMALLINT, INTEGER, DECIMAL(p, 0), and NUMERIC(p, 0)).

Auto increment is recognized as automatically created keys in the JDBC programs. To retrieve the key, you need to specify the time to insert a row from which the automatically created key value is to be retrieved. To perform it, you must set the flag by calling Connection.prepareStatement and Statement.execute methods. In this case, the command executed should be the INSERT statement or INSERT within SELECT statement. For other commands, the JDBC driver ignores the flag-setting parameter. 

Steps

1. Use one of the followings to indicate whether or not to return keys created automatically. The following method forms are used for tables of the database server that supports the auto increment columns. Each method form can be applied only to a single-row INSERT statement.
   • Write the PreparedStatement object as shown below.
   • Connection.prepareStatement(sql statement, Statement.RETURN_GENERATED_KEYS)
   • To insert a row by using the Statement.execute method, use the Statement.execute method as shown below.
   • Statement.execute(sql statement, Statement.RETURN_GENERATED_KEYS)
2. Get the ResultSet object containing automatically created key values by calling the PreparedStatement.getGeneratedKeys or Statement.getGeneratedKeys method. Note that the data type of the automatically created keys in ResultSet is DECIMAL regardless of the data type of the given domain.

Example

The following example shows how to create a table with auto increment, enter data into the table so that automatically created key values are entered into auto increment columns, and check whether the key values are successfully retrieved by using the Statement.getGeneratedKeys() method. Each step is explained in the comments for commands that correspond to the steps above.

import java.sql.*

import java.math.*

import cubrid.jdbc.driver.*

 

Connection con

Statement stmt

ResultSet rs

java.math.BigDecimal iDColVar

...

stmt = con.createStatement()         // Create a Statement object

 

stmt.executeUpdate(

" CREATE TABLE EMP_PHONE (EMPNO CHAR(6), PHONENO CHAR(4), "

+     " IDENTCOL INTEGER AUTO_INCREMENT)" ) // Create table with identity column

 

stmt.execute(

" INSERT INTO EMP_PHONE (EMPNO, PHONENO) "

+     " VALUES ('000010', '5555')" ,                  // Insert a row  < Step 1>

Statement.RETURN_GENERATED_KEYS)                       // Indicate you want automatically

 

rs = stmt.getGeneratedKeys()       // generated keys

                                                                            // Retrieve the automatically  < Step 2>

                                                                            // generated key value in a ResultSet.

                                                                            // Only one row is returned.

                                                                            // Create ResultSet for query

while (rs.next()) {

  java.math.BigDecimal idColVar = rs.getBigDecimal(1)        

                                                                            // Get automatically generated key

                                                                            // value

  System.out.println(" automatically generated key value = " + idColVar)

}

rs.close()                                                   // Close ResultSet

stmt.close()                                               // Close Statement

Using BLOB/CLOB

The interface that handles LOB data in JDBC is implemented based on JDBC 4.0 specification. The constraints of the interface are as follows:

• Only sequential write is supported when creating BLOB or CLOB object. Writing to arbitrary locations is not supported.
• You cannot change BLOB or CLOB data by calling methods of BLOB or CLOB object fetched from ResultSet. 
• Blob.truncate, Clob.truncate, Blob.position, and Clob.position methods are supported.
• You cannot bind the LOB data by calling PreparedStatement.setAsciiStream, PreparedStatement.setBinaryStream, and PreparedStatement.setCharacterStream methods for BLOB/CLOB type columns.
• To use BLOB/CLOB type in the environment where JDBC 4.0 is not supported such as JDK versions 1.5 or earlier, you must do explicit type conversion for the  conn object to CUBRIDConnection. See example below.

// JDK 1.6 or later

import java.sql.*

Connection conn = DriverManager.getConnection(url, id, passwd)

Blob blob = conn.createBlob()

…

// JDK 1.6 or earlier

import java.sql.*

import cubrid.jdbc.driver.*

Connection conn = DriverManager.getConnection(url, id, passwd)

Blob blob = ((CUBRIDConnection)conn).createBlob()

…

Storing LOB Data

You can bind the LOB type data in the following ways.

• Create java.sql.Blob or java.sql.Clob object, store file content in the object, use setBlob() or setClob() of PreparedStatement (example 1).
• Execute a query, get java.sql.Blob or java.sql.Clob object from the ResultSet object, and bind the object in PreparedStatement (example 2).

Example 1

Class.forName(" cubrid.jdbc.driver.CUBRIDDriver" )

Connection conn = DriverManager.getConnection (" jdbc:cubrid:localhost:33000:image_db:::" , " " , " " )

PreparedStatement pstmt1 = conn.prepareStatement(" INSERT INTO doc(image_id, doc_id, image) VALUES (?,?,?)" )

pstmt1.setString(1, " image-21" )

pstmt1.setString(2, " doc-21" )

 

//Creating an empty file in the file system

Blob bImage = conn.createBlob()

byte[] bArray = new byte[256]

…

 

//Inserting data into the external file. Position is start with 1.

bImage.setBytes(1, bArray)

//Appending data into the external file

bImage.setBytes(257, bArray)

…

pstmt1.setBlob(3, bImage)

pstmt1.executeUpdate()

…

Example 2

Class.forName(" cubrid.jdbc.driver.CUBRIDDriver" )

Connection conn = DriverManager.getConnection (" jdbc:cubrid:localhost:33000:image_db:::" , " " , " " )

conn.setAutoCommit(false)

PreparedStatement pstmt1 = conn.prepareStatement(" SELECT image FROM doc WHERE image_id = ? " )

pstmt1.setString(1, " image-21" )

ResultSet rs = pstmt1.executeQuery()

 

while (rs.next())

{

Blob bImage = rs.getBlob(1)

PreparedStatement pstmt2 = conn.prepareStatement(" INSERT INTO doc(image_id, doc_id, image) VALUES (?,?,?)" )

pstmt2.setString(1, " image-22" )

pstmt2.setString(2, " doc-22" )

pstmt2.setBlob(3, bImage)

pstmt2.executeUpdate()

pstmt2.close()

}

pstmt1.close()

conn.commit()

conn.setAutoCommit(true)

conn.close()

…

Getting LOB Data

You can get the LOB type data in the following ways.

• Get data directly from ResultSet by using getBytes() or getString() method (example 1).
• Get the java.sql.Blob or java.sql.Clob object from ResultSet by calling getBlob() or getClob() method and get data for this object by using the getBytes() or getSubString() method (example 2).

Example 1

Connection conn = DriverManager.getConnection (" jdbc:cubrid:localhost:33000:image_db:::" , " " , " " )

 

// Getting data directly from ResetSet

PrepareStatement pstmt1 = conn.prepareStatement(" SELECT content FROM doc_t WHERE doc_id = ? " )

pstmt2.setString(1, " doc-10" )

ResultSet rs = pstmt1.executeQuery()

while (rs.next())

{

String sContent = rs.getString(1)

System.out.println(" doc.content= " +sContent.)

}

Example 2

Connection conn = DriverManager.getConnection (" jdbc:cubrid:localhost:33000:image_db:::" , " " , " " )

 

// Getting BLOB data from ResultSet and getting data from the BLOB object

PrepareStatement pstmt2 = conn.prepareStatement(“SELECT image FROM image_t WHERE image_id = ?”)

pstmt2.setString(1,”image-20”)

ResultSet rs = pstmt2.executeQuery()

while (rs.next())

{

Blob bImage = rs.getBlob(1)

Bytes[] bArray = bImange.getBytes(1, (int)bImage.length())

}

Note If a string longer than defined max length is inserted (INSERT) or updated (UPDATE), the string will be truncated.

Note To get the latest information about JDBC driver, click http://www.cubrid.org/wiki_apis/entry/cubrid-jdbc-driver.