Table of Contents
This chapter describes how to use tbJDBC, its features, and relevant programming.
Tibero provides the tbJDBC (Tibero Java database connectivity) interface to enable Java programs to run SQL statements and access the database.
tbJDBC operates in JDK 1.4 (or later version) environment. To fully use all the functions of JDBC 4.0, JDK 6 or later needs to be used. When JDK 6 or later is used, the driver file is provided as tibero6-jdbc.jar and for JDK 1.4 as tibero6-jdbc-14.jar.
This guide briefly explains JDBC programming. For more information about tbJDBC, refer to Tibero JDBC Developer's Guide.
To use tbJDBC, JDK 1.4 or later must be installed.
JDK can be downloaded from the following website:
http://www.oracle.com/technetwork/java/javase/downloads/index.html
If Oracle's JDK cannot be used, install the appropriate JDK. For example, the JDK for HP-UX and AIX can be downloaded from HP and IBM website respectively.
Instructions for installing JDK for each operating system can be found here:
http://www.oracle.com/technetwork/java/index.html
For vendor-specific JDK installation instructions, refer to each vendor's documentation.
tbJDBC complies with the JDBC 4.0 standard that includes the following JDBC standards. The standard specifies mandatory features and optional features that need to be implemented by each vendor. Therefore, some optional features may not be supported.
JDBC 3.0 API
JDBC 2.0 API (JDBC 2.0 Optional Package API and JDBC 2.1 core API)
JDBC 1.2 API
JDBC 1.0 API
This section describes the standard features of tbJDBC according to JDBC version. For more information, refer to Sun Microsystems's JDBC 4.0 Specification.
The following describes the supported and unsupported features of tbJDBC for JDBC 1.0 and JDBC 2.0:
SQL-99 data types
Of the newly added data types in SQL-99, tbJDBC supports only BLOB and CLOB. When using the BLOB and CLOB data types in Tibero, the following rules apply:
BLOB and CLOB interfaces are available while a related transaction is being processed.
BLOB and CLOB interfaces are implemented through a locator.
The locator is a logical pointer to data that exists in a database server.
Interface methods
Supported interface methods are:
java.sql.Array
java.sql.Blob
java.sql.CallableStatement
java.sql.Clob
java.sql.Connection
java.sql.DatabaseMetaData
java.sql.Driver
java.sql.PreparedStatement
java.sql.ResultSet
java.sql.ResultSetMetaData
java.sql.Statement
java.sql.Struct
Driver that implements static initialization
The Driver class that implements the java.sql.Driver interface method can register a newly created driver instance with a driver manager by calling the Drivermanager.registerDriver method in a static initialization block.
Extended feature of SQL-92 entry level
DROP TABLE and ESCAPE statements can be used.
All scalar functions supported in a database server can be used. Scalar functions can be searched with the DatabaseMetaData class.
Multiple threads can simultaneously process all operations for objects that exist in the java.sql and javax.sql packages.
Scrollable ResultSet
A ResultSet can be searched backwards and forwards. A ResultSet supports relative and absolute positioning.
Updatable ResultSet
A ResultSet can be updated.
Positioned updates and deletions
A record in the current position set by a cursor in a ResultSet can be updated and deleted.
Sensitive ResultSet
Data generated after a ResultSet cursor is opened can be committed.
Batch updates
Multiple updates can be processed at the same time.
RowSet feature
Data can be easily transferred and handled.
Distributed transactions
Enables transactions to be distributed to multiple database servers.
SQL-99 data types
Of the SQL-99 data types, tbJDBC does not support Array, Ref, or Struct.
Interface methods
Unsupported interface methods are:
java.sql.Ref
java.sql.SQLData
java.sql.SQLInput
java.sql.SQLOutput
Exception for a truncated data
If data is unexpectedly truncated by the JDBC driver, the invocation of the Data Truncation warning for reading processes and the Data Truncation exception for writing processes are not supported.
The following describes the JDBC 3.0 standard features. JDBC 3.0 API can be used in the J2SE platform and version 1.4 or later environment.
Interface
Supported interfaces are:
java.sql.ParameterMetaData
java.sql.Savepoints
javax.sql.ConnectionEventListener
javax.sql.ConnectionPoolDataSource
javax.sql.DataSource
javax.sql.PooledConnection
javax.sql.RowSet
javax.sql.RowSetInternal
javax.sql.RowSetListener
javax.sql.RowSetMetaData
javax.sql.RowSetReader
javax.sql.RowSetWriter
javax.sql.XAConnection
javax.sql.XADataSource
Connection pooling configuration
Supports the API for setting various parameters, used for connection pooling, such as the maximum pool size, the minimum pool size, and the initial pool size.
Supports statement pooling related to connection pooling.
ParameterMetaData
The JDBC class implementing the ParameterMetaData interface provides the number of parameters used in prepared statements. However, it does not provide metadata describing data type or property.
Savepoint
By implementing the Savepoint interface, the savepoint's setting, commitment, and rollback features are provided. However, the Connection.releaseSavepoint java.sql method, that releases the savepoint, is not supported.
Automatically generated key
By using the getGeneratedKeys function of
a ResultSet of a SQL statement, the key or an automatically
generated column value of the result row can be obtained.
ResultSet Holdability
Whether to maintain or close a ResultSet, when a commitment occurs while a ResultSet is opened, can be set. tbJDBC only supports maintaining a ResultSet.
Returning Multiple ResultSets
The opening of multiple ResultSets by a single SQL statement is supported.
Modifying data in BLOB and CLOB object
Data modification included in BLOB and CLOB objects is supported with an updateXXX API.
Added data types
| Type | Description |
|---|---|
| java.sql.Types.DATALINK | Ability to access external resources, such as URLs, is not supported. |
| java.sql.Types.BOOLEAN | This data type, which is logically identical to the bit type, is not supported. |
Searching and modifying an object referred by a REF object
The searching and modifying of an object referred by a REF object are not supported.
User defined data types (UDTs) and java classes can be mapped with the JDBC API. However, Tibero does not support UDT.
The following describes the standard features of JDBC 4.0. JDBC 4.0 API can be used in the Java SE platform and version 6 or later environment.
Interface
Supported interfaces are:
java.sql.NClob
java.sql.RowId
java.sql.SQLXML
java.sql.Wrapper
javax.sql.StatementEventListener
XML data types added in SQL:2003
XML data types are supported with the SQLXML interface.
Auto java.sql.Driver discovery
The usage of a driver object, without loading the java.sql.Driver class by using Class.forName method, is supported.
National character set
The API for supporting national character set, specified separately by each database, has been added.
Improved SQLException
Consecutively chained exceptions can be invoked to output a more detailed information. In addition, new exception types have been added.
Improved BLOB/CLOB feature
The API for creating and releasing a BLOB or CLOB object is supported.
SQL ROWID data type
The usage of the SQL ROWID type using the rowid interface is supported.
Accessing an actual JDBC object
Accessing an actual JDBC object with the wrapper interface is supported in an application server or in the connection pooling environment.
Notifying the actual connection status in the connection pooling environment
When a connection is closed or not available anymore in the connection pooling environment, the status is notified to the objects of pooled statements.
Searching a user defined type and hierarchical structure
The usage of the API for searching for a user defined type's property and hierarchical structure is not supported.
This section describes how to write a basic java program using interface methods provided in tbJDBC.
To see the complete source code of the basic program explained in this section, refer to “Appendix A. tbJDBC Example”.
The following is a section of the JdbcTest file that includes the public class:
[Example 2.1] Basic Programming Using tbJDBC
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class JdbcTest { Connection conn; public static void main(String[] args) throws Exception { JdbcTest test = new JdbcTest(); test.connect(); ... <A> ... test.executeStatement(); ... <B> ... test.executePreparedStatement(); ... <C> ... test.executeCallableStatement(); ... <D> ... test.disconnect(); ... <E> ... } /* ... Implementation of memeber functions by feature */ }
The line of code labeled <A> connects to the database through the JdbcTest class.
The lines of code labeled <B> through <D> execute several member functions.
The line of code labeled <E> releases the connection to the database.
The following describes the implementation of the member functions (called by lines labeled <A> through <E> in the previous example).
The connect member function connects to the database using the default driver manager. The driver manager function can be used through the java.sql.DriverManager class, which is provided in Java by default.
[Example 2.2] tbJDBC - Connection
private void connect() throws ClassNotFoundException { Class.forName("com.tmax.tibero.jdbc.TbDriver"); try { conn = DriverManager.getConnection( "jdbc:tibero:thin:@localhost:8629:tibero", "tibero", "tmax"); } catch (SQLException e) { System.out.println("connection failure!"); System.exit(-1); } System.out.println("Connection success!"); }
Description of the previous example is as follows:
The Class.forName method is called by specifying the class that corresponds to the driver that will be used.
The driver is registered before the driver manager is used. To make a Java virtual machine load the driver class, specify the class name that corresponds to the driver.
A database connection is created through the driver manager by calling the DriverManager.getConnection method.
To connect to a database by using the DriverManager.getConnection method, a user enters the URL, DB user name, and its password as arguments. The DriverManager class searches for a driver appropriate for the URL. If the connection is successful, the DriverManager returns the database connection using the java.sql.Connection interface object. The connection is allocated to the field variable 'conn'.
If the driver manager fails to find a driver appropriate for the URL or the driver fails to access a database, SQLException is thrown.
tbJDBC uses the concept of statements similar to those used in clients of other databases. The executeStatement member function executes statements.
[Example 2.3] tbJDBC - Execution
private void executeStatement() throws SQLException
{
String dropTable = "drop table emp";
String createTable = "create table emp (id number, "+
" name varchar(20), salary number)";
String InsertTable = "insert into emp values(1000, 'Park', 5000)";
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate(dropTable);
} catch(SQLException e) {
// if there is not the table
}
stmt.executeUpdate(createTable);
stmt.executeUpdate(InsertTable);
stmt.close();
}
Description of the previous example is as follows:
dropTable, createTable, and InsertTable are converted to string object reference variables. SQL statements that will be executed are created in these object reference variables.
A new statement is created by calling the
createStatement method from the connection. A java.sql.Statement
object is returned. The object enables the execution of SQL
statements.
Specified SQL statements are executed using the
executeUpdate(str) method.
Using a prepared statement, a desired task can also be executed by binding a parameter. The executePreparedStatement member function executes a query to which the parameter is bound to.
[Example 2.4] tbJDBC - Prepared Statements Declaration and Parameter Binding
private void executePreparedStatement() throws SQLException { PreparedStatement pstmt = conn.prepareStatement("select name from emp where id = ?"); ... (1) ... pstmt.setString(1, "1000"); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1)); } pstmt.close(); }
Description of the previous example is as follows:
A prepared statement is created by calling the
prepareStatement method from the
connection.
A java.sql.PreparedStatement interface object is returned. The statement can be executed by calling the execute(), executeUpdate(), and executeQuery() methods.
The order and the values of the parameters to be bound are set
using the setString(bind_no, bind_value)
method.
pstmt.setString(1, "1000") binds "1000" to the first parameter of the prepared statement (labeled (1) in [Example 2.4]).
After the parameter is bound, ResultSet can be obtained by
executing the executeQuery method.
ResultSet is conceptually the same as obtaining a cursor for
tbCLI or tbESQL. Execution results can be checked
in string format by using the
getString(column_no) method.
In a program written with tbJDBC, PL/SQL statements can be called. These statements are called Callable statement. The executeCallableStatement member function executes callable statements.
[Example 2.5] tbJDBC - Calling
private void executeCallableStatement() throws SQLException
{
String callSQL =
" CREATE PROCEDURE testProc "+
" (ID_VAL IN NUMBER, SAL_VAL IN OUT NUMBER) as " +
" BEGIN" +
" update emp" +
" set salary = SAL_VAL" +
" where id = ID_VAL;" +
" select salary into SAL_VAL" +
" from emp" +
" where id = ID_VAl;" +
" END;";
String dropProc = "DROP PROCEDURE testProc";
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate(dropProc);
} catch(SQLException e) {
// if there is not the procedure
}
stmt.executeUpdate(callSQL);
CallableStatement cstmt = conn.prepareCall("{call testProc(?, ?)}");
cstmt.setInt(1, 1000);
cstmt.setInt(2, 7000);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.executeUpdate();
int salary = cstmt.getInt(2);
System.out.println(salary);
stmt.close();
cstmt.close();
}
Description of the previous example is as follows:
The procedure is created. The java.sql.Statement object is used to execute the SQL statement that creates the procedure.
A callable statement is created by calling
prepareCall(str) method from the connection. The created callable
statement is returned as a
java.sql.CallableStatement interface
object.
Input and output parameters are bound.
Input parameters can be bound using a binding method, such as
setInt(bind_no, bind_value), in the same way as
in a prepared statement.
Output parameters are registered using the
registerOutParameter(bind_no, type) method. The output parameter to be registered is the
second parameter (SAL_VAL IN OUT NUMBER) of
the procedure created in the first step. This parameter can be
registered as an output parameter, because it is declared as an
input and an output parameter.
After executing the statement by calling the
executeUpdate method, an execution result is checked by using an
appropriate method for the output parameter. Since the output
parameter is an integer, the result is obtained using the
getInt(bind_no) method of the CallableStatement class.
A transaction consists of one or more SQL statements. When SQL statements are executed, all SQL statements are committed or rolled back. tbJDBCcan set a transaction-related value in a connection object. The default mode is auto-commit.
To change a transaction handling mode, add the following lines of source code:
conn.setAutoCommit(false); conn.rollback(); conn.commit(); conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITED)