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.

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.*;

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

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

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

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));
}

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

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

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.sql.Types.VARCHAR

java.lang.String

java.sql.Types.LONGVARCHAR

java.lang.String

java.sql.Types.NUMERIC

java.math.BigDecimal

java.sql.Types.DECIMAL

java.math.BigDecimal

java.sql.Types.BIT

boolean

java.sql.Types.TINYINT

byte

java.sql.Types.SMALLINT

short

java.sql.Types.INTEGER

int

java.sql.Types.BIGINT

long

java.sql.Types.REAL

float

java.sql.Types.FLOAT

double

java.sql.Types.DOUBLE

double

java.sql.Types.BINARY

byte[]

java.sql.Types.VARBINARY

byte[]

java.sql.Types.LONGVARBINARY

byte[]

java.sql.Types.DATE

java.sql.Date

java.sql.Types.TIME

java.sql.Time

java.sql.Types.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

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)

PreviousJDBC Standards NextDataSource Objects and Database URLs

Last updated 6 months ago