tbJDBC Usage

This chapter describes how to develop an application using tbJDBC, and details the data types and streams.

Development Steps

This section describes how to develop an application using tbJDBC.

1. Importing packages Imports standard Java packages and tbJDBC.

/* Standard Java Package */ 
import java.sql.*;

/* tbJDBC Package */
import com.tmax.tibero.jdbc.*; 
import com.tmax.tibero.jdbc.ext.*;

1. Connecting to the Database

   Create a connection object to connect to the database. To create the object, load tbJDBC, and then use DriverManager.

Class.forName("com.tmax.tibero.jdbc.TbDriver");
Connection conn = DriverManager.getConnection
                 ("jdbc:tibero:thin:@localhost:8629:dbsvr", 
                  "tibero", "tmax");

Or, connection objects can be configured using the following java.util.Properties.

Class.forName("com.tmax.tibero.jdbc.TbDriver"); 
Properties prop = new Properties(); 
prop.setProperty("user", "tibero"); 
prop.setProperty("password", "tmax"); 
Connection conn =
    DriverManager.getConnection("jdbc:tibero:thin:@localhost:8629:dbsvr", prop);

Connection objects can also be created by using a DataSource object.

TbDataSource ds = new TbDataSource(); 
ds.setURL("jdbc:tibero:thin:@localhost:8629:dbsvr");
ds.setUser("tibero"); 
ds.setPassword("tmax");
Connection conn = ds.getConnection();

1. Creating a Statement Object

   After successfully connecting to the database, create a Statement object using the Connection object.

Statement             stmt = conn.createStatement();
PreparedStatement    pstmt = conn.prepareStatement("SELECT empno, name FROM emp");
CallableStatement    cstmt = conn.prepareCall("BEGIN ... END;");

1. Performing a Query and Retrieving a ResultSet Object

   Execute a query statement and retrieve a ResultSet object.

ResultSet rs = stmt.executeQuery("SELECT * FROM ALL_OBJECTS;");

1. Processing the ResultSet Object

   The ResultSet object can access all rows via the next() method. It can also retrieve any type of row data by calling the getXXX() method. If the next() method is false, there are no more results sets.

while (rs.next())
{
    System.out.println(rs.getString(1)); 
    System.out.println(rs.getInt(2));
}

1. Performing a Commit or Rollback

   By default, DML statements (INSERT, UPDATE, or DELETE) are automatically committed. This is called the auto-commit function. Users can disable this function using the following method provided by the Connection class.

void setAutoCommit(boolean flag)

If the auto-commit function is disabled using this method, the user must commit or roll back changes after using a DML statement to apply the changes to the database.

conn.commit(); 
conn.rollback();

1. Closing the ResultSet and Statement Objects

   The ResultSet object and the Statement object must be closed because tbJDBC does not contain its built-in finalizer methods. If the objects are not closed, a significant memory leak or an error may occur when the maximum amount of internal database cursors is exceeded.

rs.close();
stmt.close();

1. Disconnecting from the Database

   After all steps have been performed, the connection to the database must be closed. Otherwise, the current session remains connected, and the maximum allowable number of sessions may be exceeded and therefore the database may reject connections.

conn.close();

---

Connection Properties

The following is a list of attributes that can be configured for Connection Properties.

Properties	Type	Description
databaseName	String	Name of a database that resides in the server.
dataSourceName	String	Name of the DataSource.
description	String	Description of the DataSource.
networkProtocol	String	Name of the network protocol used to communicate with the server. (Default value: TCP)
password	String	Password to connect to the server.
user	String	User name to connect to the server.
portNumber	int	Port number of the server listener.
serverName	String	Name of the database.
login_timeout	int	Timeout period starts when a socket is first created and ends when the database connection finishes. After the database connection is complete, read_timeout is applied. If no response is received within the specified time, a timeout occurs. If set to 0, however, no timeout occurs. (Unit: millisecond, default value: 0)
read_timeout	int	Socket read timeout after creating the database connection. If no response is received within the specified time, a timeout occurs. If set to 0, however, no timeout occurs. (Unit: millisecond, default value: 0)
characterset	String	Character set used for encoding and decoding for JDBC. (Default value: character set of the server)
program_name	String	Name of the program. (Default value: JDBC Thin Client)
includeSynonyms	String	Option to include synonym objects in DatabaseMetaData.getColumn(). (Default value: false)
mapDateToTimestamp	String	Option to return the result type of the DATE column as Timestamp. (Default value: true)
defaultNChar	String	Forcibly applies the national character set configuration to the character string that is set via PreparedStatement.setString() API. (Default value: false)
self_keepalive	String	Option to enable the SELF KEEP ALIVE function. This function checks if the connection target has a network access problem based on the settings of self_keepidle, self_keepintvl, and self_keepcnt. If the entire checking process fails, the network connection is forcibly terminated. (Default value: false)
self_keepidle	int	Maximum period of time to determine whether the network status is normal if the processing time is not updated after the network was normally used to process connections and DB requests/responses. After the specified period, the process of checking the network connection starts. This is applicable only when self_keepalive is set to true. (Unit: second, Default value: 60)
self_keepintvl	int	Interval at which to check the network connection and the maximum wait time when checking. This is applicable only when self_keepalive is set to true. (Unit: second, Default value: 10)
self_keepcnt	int	Number of times to check the network connection. If the specified number of checks is reached sequentially, Tibero assumes that the connection failed. This is applicable only when self_keepalive is set to true. (Default value: 3)
failover_retry_count	int	Maximum number of retries to recover a connection when the failover function is enabled. (Default value: 3) For information about the failover function, refer to “Failover and Load balancing”.

---

Data Types

tbJDBC provides interval types as well as JDBC types.

The following table lists JDBC types and their corresponding tbJDBC.

JDBC Types (Standard)	Java Types (Standard)	tbJDBC Types
java.sql.Types.CHAR	java.lang.String	java.lang.String
java.sql.Types.VARCHAR	java.lang.String	java.lang.String
java.sql.Types.LONGVARCHAR	java.lang.String	java.lang.String
java.sql.Types.NUMERIC	java.math.BigDecimal	java.math.BigDecimal
java.sql.Types.DECIMAL	java.math.BigDecimal	java.math.BigDecimal
java.sql.Types.BIT	boolean	boolean
java.sql.Types.TINYINT	byte	byte
java.sql.Types.SMALLINT	short	short
java.sql.Types.INTEGER	int	int
java.sql.Types.BIGINT	long	long
java.sql.Types.REAL	float	float
java.sql.Types.FLOAT	double	double
java.sql.Types.DOUBLE	double	double
java.sql.Types.BINARY	byte[]	byte[]
java.sql.Types.VARBINARY	byte[]	byte[]
java.sql.Types.LONGVARBINARY	byte[]	byte[]
java.sql.Types.DATE	java.sql.Date	java.sql.Date
java.sql.Types.TIME	java.sql.Time	java.sql.Time
java.sql.Types.TIMESTAMP	java.sql.Timestamp	java.sql.Timestamp
java.sql.Types.BLOB	java.sql.Blob	com.tmax.tibero.jdbc.TbBlob
java.sql.Types.CLOB	java.sql.Clob	com.tmax.tibero.jdbc.TbClob
java.sql.Types.NCLOB	java.sql.NClob	com.tmax.tibero.jdbc.TbNClob
java.sql.Types.NCHAR	java.sql.NCHAR	java.lang.String
java.sql.Types.NVARCHAR	java.sql.NVARCHAR	java.lang.String
java.sql.Types.SQLXML	java.sql.SQLXML	com.tmax.tibero.jdbc.TbSQLXML
com.tmax.tibero.jdbc.data.DataType. VARRAY	java.sql.Array	com.tmax.tibero.jdbc.TbArray
com.tmax.tibero.jdbc.data.DataType. Struct	java.sql.Struct	com.tmax.tibero.jdbc.TbUpStruct
com.tmax.tibero.jdbc.data.DataType. CURSOR	-	com.tmax.tibero.jdbc.TbResultSet
com.tmax.tibero.jdbc.data.DataType. ITV_DTS	-	com.tmax.tibero.jdbc.TbIntervalDts
com.tmax.tibero.jdbc.data.DataType. ITV_YTM	-	com.tmax.tibero.jdbc.TbIntervalYtm
com.tmax.tibero.jdbc.data.DataType. RowId	-	com.tmax.tibero.jdbc.TbRowId
com.tmax.tibero.jdbc.data.DataType. TIMESTAMP_TZ	java.sql.Timestamp	com.tmax.tibero.jdbc.TbTimestampTZ
com.tmax.tibero.jdbc.data.DataType. TIMESTAMP_LTZ	java.sql.Timestamp	java.sql.Timestamp

Although Tibero supports the TIME data type, using the DATE type is recommended.

The TIME column cannot be configured with the PreparedStatement.setTime() method. PreparedStatement.setTime() supports columns with the DATE type. The values of the TIME column can be specified using the TO_TIME function, which converts a character string to a TIME value.

---

tbJDBC Stream

tbJDBC provides the following Stream methods for certain data types.

• Creating a Stream for columns - CHAR, VARCHAR, RAW, NCHAR, NVARCHAR - LONG, LONG RAW - LOB

• Closing a Stream

tbJDBC provides the following four types of Stream methods.

Method	Return Value	Description
getAsciiStream	Java.io.InputStream	Data stream that is represented in an ASCII format.
getBinaryStream	Java.io.InputStream	Data stream that is represented in bytes.
getUnicodeStream	Java.io.InputStream	Data stream that is represented in a Unicode format. This method has been deprecated in JDBC 4.0, so it is not recommended to use this method for compatibility reasons.
getCharacterStream	Java.io.Reader	Data reader that is represented in strings.

By using each method, data can be retrieved progressively until the allowable size is reached. Objects returned from the methods are of types InputStream and Reader, and data can be retrieved via the read() method.

Creating a Stream for Columns

If the Stream method is used for CHAR, VARCHAR, or RAW columns, the entire data is retrieved at once to create a Stream object. On the other hand, if the method is used for LONG, LONG RAW, or LOB columns, the initial part of the data is retrieved, and the Stream object is created. If the user requests additional data, the data is sequentially retrieved from the server and then the Stream object is created.

The following example shows how to retrieve data using getBinaryStream().

ResultSet rs = stmt.executeQuery("SELECT name, image FROM backgrounds");

while (rs.next()) {
    InputStream imageIS = rs.getBinaryStream(2); 
    try {
        FileOutputStream fos = new FileOutputStream(rs.getString(1)); 
        int chunk = 0;
        while ((chunk = imageIS.read()) != -1) 
            fos.write(chunk);
    }
    catch (Exception e) 
        { e.printStackTrace();
    }
    finally {
        if (fos != null) 
        fos.close();
    }
}

The following example shows how to retrieve data using getBytes() instead of getBinaryStream().

Note that since getBytes() gets the entire data stored in the column, it consumes an amount of memory equal to the size of the data.

ResultSet rs = stmt.executeQuery("SELECT name, image FROM backgrounds");
while (rs.next()) {
    bytes[] images = rs.getBytes(2); 
    try {
        FileOutputStream fos = new FileOutputStream(rs.getString(1)); 
        fos.write(images);
    }
    catch (Exception e) 
        { e.printStackTrace();
    }
    finally {
        if (fos != null) 
            fos.close();
    }
}

---

Calling Built-In Functions

Using PSM

tbJDBC can use Persistent Stored Modules (PSM), and provides the escape syntax of SQL-92 and the PSM syntax as follows:

/ SQL92 ESCAPE syntax
CallableStatement cstmt = conn.prepareCall("{call proc(?)}"); 
CallableStatement cstmt = conn.prepareCall("{? = call func(?)}");

// PSM syntax
CallableStatement cstmt = conn.prepareCall("begin proc(?); end;"); 
CallableStatement cstmt = conn.prepareCall("begin ? := func(?); end;");

The following example calls a built-in function using the PSM syntax.

create or replace function concat(x varchar2, y varchar2)
return varchar2 is 
begin
    return x || y; 
end;

CallableStatement cstmt = conn.prepareCall("begin ? := concat(?, ?); end;");

cstmt.regiterOutParameter(1, Types.VARCHAR); 
cstmt.setString(2, "Tmax"); 
cstmt.setString(3, "Tibero");
cstmt.executeUpdate();

String result = cstmt.getString(1);

Using Java Stored Procedure

Java Stored Procedures can be used in the same way as described in the previous section.

For more information about Java Stored Procedures, refer to "Tibero External Procedure Guide".

---

Handling Exceptions

tbJDBC can define a java.sql.SQLException class object to handle an exception. An exception can occur inside tbJDBC or within the database. The exception contains information about the error code and the location where the error occurred.

The following methods are provided to retrieve the information.

Method	Description
getMessage()	Cause of the error.
getSQLState()	Information about the SQL state.
getErrorCode()	Error code.
printStackTrace()	Information about stack trace from where the error occurred.

The following example handles an exception using these methods.

try {
    stmt.execute("drop table not_exist_table");
}
catch (SQLException e) {
    System.out.println("ERROR[" + e.getErrorCode() + "]" + e.getMessage()); 
    e.printStackTrace();
}

The following information is then displayed.

ERROR[-7071] Schema object'NOT_EXIST_TABLE' was not found or is invalid. 
java.sql.SQLException: Schema object 'NOT_EXIST_TABLE' was not found or is invalid.
    at com.tmax.tibero.jdbc.msg.common.TbMsgError.readErrorStackInfo(TbMsgError.java
:108)
    at com.tmax.tibero.jdbc.msg.TbMsgEreply.deserialize(TbMsgEreply.java:61)
    at com.tmax.tibero.jdbc.comm.TbStream.readMsgBody(TbStream.java:327)
    at com.tmax.tibero.jdbc.comm.TbCommType4.executeDirect(TbCommType4.java:460) 
    at com.tmax.tibero.jdbc.TbStatement.executeInternal(TbStatement.java:1051) 
    at com.tmax.tibero.jdbc.TbStatement.execute(TbStatement.java:560)
    at TestSimple.main(TestSimple.java:102)