Java Developer’s Guide

Overview


This document covers the development of JAVA applications with Altibase. The document was written based on Altibase version 5 or later and updated based on Altibase version 6.5.

Altibase version 5 or later , JRE/JDK version 1.5 and Elicpse by developer IDE were used in the creation of this document.

The following Altibase manuals can also be used as references during development:

  1. Considerations Guide in Altibase
  2. Altibase SQL Tuning Guide

Environment Configuration


The Altibase JDBC driver is required to establish a connection between a JAVA application and Altibase. This section describes how to obtain the appropriate drivers and configure the environment prior to development.

Altibase JDBC Driver File Locations

The Altibase.jar file is the JDBC driver for Altibase. This file is located in the $ALTIBASE_HOME/lib directory.

From Altibase version 5, two JDBC drivers are provided in the $ALTIBASE_HOME/lib directory.

Altibase.jar is the generic JDBC driver, whereas Altibase5.jar is the same generic JDBC driver with different class name.

More information regarding the use of the Altibase.jar and Altibase5.jar JDBC drivers will be provided in a later section of this manual.

Verifying JDBC Driver Versions

To verify that the JDBC driver and the Altibase database are compatible, the following command can be executed to verify the JDBC driver version:

$ java –jar Altibase.jar

JDBC Driver Info :  Altibase Ver = 5.3.3.13 for JavaVM v1.4, CMP:5.6.1, $Revision: 14502 $ Jan 13 2010 14:35:28

If Altibase’s cm protocol version and the JDBC driver’s CMP value match, the database and the driver are compatible.

$ altibase -v
version 5.3.3.13 XEON_LINUX_redhat_Enterprise_AS4-64bit-5.3.3.13-release-GCC3.4.6 (xeon-redhat-linux-gnu) Jan 13 2010 14:35:30, binary db version 5.4.1, meta version 5.6.1, cm protocol version 5.6.1, replication protocol version 5.4.1

Therefore, the user should always use a JDBC driver with a version that is either the same or higher than the version of the Altibase instance.

Setting up the JDBC Driver


The chapter describes how to set up the Altibase JDBC Driver.

Setup in JRE

To utilize Altibase’s JDBC driver, one of the following methods can be used to enable the use with the installed JRE. Add the Altibase.jar file to the CLASSPATH environment variable.

case 1. CLASSPATH variable

1) On Windows

Go to My Computer -> Properties -> Advanced -> Environment Variables.

If a CLASSPATH variable already exists, simply add the path to the Altibase.jar file to the end of the existing string.

If the CLASSPATH variable does not exist, click the “New” button to register the CLASSPATH variable.

Fig1

2) On Unix

Add the Altibase.jar file to the CLASSPATH variable in ex.profile with the following commands:

export ALTIBASE_HOME=$HOME/altibase_home

export CLASSPATH=.:$ALTIBASE_HOME/lib/Altibase.jar

case 2. JRE(Java Runtime Environment) Directory

Place the Altibase JDBC driver in a JRE directory. To allow JRE to reference the Altibase.jar file automatically, the JDBC driver should be placed in one of the following directories:

$JAVA_HOME/jre/lib/ext Directory

or

$JRE_HOME/lib/ext Directory

($JAVA_HOME means JDK installation directory,

$JRE_HOME means JRE installation directory)

case 3. –classpath option

Alternatively, the –classpath option can be used during execution to manually specify the location of the JDBC driver.

$java –classpath $ALTIBASE_HOME/lib/Altibase.jar HelloApp

Setup in Eclipse

To add the Altibase JDBC driver in Eclipse, perform the following steps:

Go to ‘Project’ -> Choose ‘JRE System Library [J2SE-1.5]’ –> Go to ‘Properties’ –> Click ‘Installed JREs’ –> Check ‘jre’ from the list –> Click ‘Edit’ -> Click ‘Add External JARs’ -> add Altibase.jar.

Fig2

Loading Driver & Connection URL


This chapter describes how to load the Altibase JDBC Driver and connect to Altibase.

Loading Driver

The Altibase JDBC driver’s class name is “Altibase.jdbc.driver.AltibaseDriver.”  This class name should be used to load the Altibase driver. An example is shown below:

Class.forName(“Altibase.jdbc.driver.AltibaseDriver”);

General Connection

To obtain a connection object from JDBC, the DriverManager.getConnection method should be used. A string URL must be provided as an argument, and the connection URL must be written in the format shown below:

jdbc:Altibase://ip_address:port_no/db_name

*ip_address : Altibase DBserver ip

* port_no : Altibase DB port_no

* db_name : Altibase DB name

The following example shows how to obtain a connection object using the format outlined above:

– AltibaseConnection.java file

String db_url1 = “jdbc:Altibase://192.168.1.111:20300/mydb”;

Properties props1 = new Properties();

props1.put(“user”, “sys”);

props1.put(“password”, “manager”);

Connection altibaseConnection1 = DriverManager.getConnection(db_url1, props1);

* Server ip is 192.168.1.111, port_no is 20300, db_name is mydb

Setting Connection Properties

Several properties such as the database username and password can be supplied when a connection object is received. Each property can be configured either by using the java.util.Properties class or by supplying properties in the connection string URL. A list of properties and default values are shown in the table below:

Properties Name Description Default Value
portNumber DB port_no 20300
databaseName DB name JDBC
user DB user name SYS
password DB user name MANAGER
serverName DB server IP Localhost
CONNTYPE Connection type

1 : TCP/IP

3 : IPC

1

The following examples show how to configure connection properties using the methods described above:

  1. Configuration with the property class
Properties props1 = new Properties();

props1.put(“user”, “sys”);

props1.put(“password”, “manager”);

Connection altibaseConnection1 = DriverManager.getConnection(db_url1, props1);

  1. Configuration with the connection string URL
String db_url1 = “jdbc:Altibase://192.168.1.111:20300/mydb?user=sys&password=manager”;

Connection altibaseConnection1 = DriverManager.getConnection(db_url1);

Connection Using Altibase’s ConnectionPool

Connections can be managed with connection pools by using the ABPoolingDataSource class. After creating a ABPoolingDataSource object, the setUrl() method can be used to set connection URL information, and setXXX() methods can be used to set properties. The ABPoolingDataSource class is defined in the Altibase.jdbc.driver package.

– AltibaseConnectionPool.java file

import Altibase.jdbc.driver.*;

String db_url1 = “jdbc:Altibase://192.168.1.111:20300/mydb”;

ABPoolingDataSource pool = new ABPoolingDataSource();

pool.setUrl(db_url1);

pool.setUser(“sys”);

pool.setPassword(“manager”);

pool.setInitialPoolSize(10);

pool.setMinPoolSize(5);

pool.setMaxPoolSize(15);

pool.setMaxIdleTime(10);

..

Connection altibaseConnection1 = pool.getConnection();

If necessary, the following ConnectionPool properties can be set.

Properties Description
URL Connection string information for Altibase connection jdbc:Altibase://IP:port_no/db_name” type
user Database account
password Database password
maxPoolSize Maximum Connection number. Default value 10.
minPoolSize Minimum Connection number. Default value 0.
initialPoolSize Initial Connection number. Default value 1.
maxIdleTime Idle waiting time
propertyCycle Waiting time (millisec) when ConnectionPool is full

Connection Using XA

The ABXADataSource class can be used to manage distributed transactions. This class is defined in the Altibase.jdbc.driver package. As shown in the following example, XA can be implemented by receiving a XAConnection object from the ABXAResource object and a connection object from the XAConnection object.

– AltibaseXAConnection.java file

import Altibase.jdbc.driver.*;

ABXADataSource axds1 = new ABXADataSource();

axds1.setUrl(“jdbc:Altibase://192.168.1.131:20300/mydb”);

axds1.setUser(“SYS”);

axds1.setPassword(“MANAGER”);

ABXADataSource axds2 = new ABXADataSource();

axds2.setUrl(“jdbc:Altibase://192.168.1.35:20300/mydb”);

axds2.setUser(“SYS”);

axds2.setPassword(“MANAGER”);

// Get XA connections to the underlying data sources

XAConnection pc1  = axds1.getXAConnection();

XAConnection pc2  = axds2.getXAConnection();

// Get the physical connections

Connection conn1 = pc1.getConnection();

Connection conn2 = pc2.getConnection();

// Get the XA resources

XAResource axar1 = pc1.getXAResource();

XAResource axar2 = pc2.getXAResource();

Connection Using FailOver

Altibase version 5.3.3 or later provides support for failover.

The following example shows how to connect to Altibase using failover.

– AltibaseFailOverConnection.java file

String db_url1 = “jdbc:Altibase://192.168.6.224:21129/mydb?AlternateServers=(192.168.1.35:21129)
&ConnectionRetryCount=3&ConnectionRetryDelay=3&SessionFailOver=on
&LoadBalance=off”;

Properties props1 = new Properties();

props1.put(“user”, “sys”);

props1.put(“password”, “manager”);

Connection altibaseConnection1 = DriverManager.getConnection(db_url1,props1);

The following table shows properties related to failover:

Properties Description
AlternateServer Lists available alternative servers using the format (IP Address1:Port1, IP Address2:Port2,…)
ConnectionRetryCount The number of connection attempts made after a connection failure
ConnectionRetryDelay The amount of time in seconds to wait in between connection attempts
LoadBalance If set to “on,” the server to connect to will be randomly selected from the list of available servers. If set to “off,” the connection will be made to the primary server unless a connection failure is detected.
SessionFailOver Determines whether or not to use STF (Service Time Failover).

ON : STF, OFF : CTF

CTF (Connection Time Failover): When a connection failure is detected, an attempt is made to connect to an alternative server that is available.

STF (Service Time Failover): When a connection was initially successful but failed while providing service, a connection is made to an alternative server that is available in order to restore session properties and execute application logic. The task that was executed on the server that failed will be retried.

Concurrent connection to each different Altibase Version

From Altibase version 5, Altibase5.jar is provided.

A client application can connect two different versions of Altibase instances using Altibase.jar and Altibase5.jar.

The class name of Altibase.jar is “Altibase.jdbc.driver.AltibaseDriver” and the class name of Altibase5.jar is “Altibase5.jdbc.driver.AltibaseDriver”.

Therefore, These different class name must be used when loading corresponding driver using the Class.forName() method.

If access to two different Altibase instances with different versions is required, both JDBC drivers must be loaded. The Altibase5.jdbc.driver must be loaded first.

The following example shows how to connect to disparate versions of Altibase using the Altibase.jar and Altibase5.jar files:

– AltibaseMultiversionConnection.java file

// Driver class for Altibase version 5. Altibase5.jar of the Altibase version 5 package should be used.

Class.forName(“Altibase5.jdbc.driver.AltibaseDriver”);

// Driver class for previous version of Altibase version 5. Altibase.jar of the previous version package should be used.

Class.forName(“Altibase.jdbc.driver.AltibaseDriver”);

// URL for Altibase version 5

String db_url1 = “jdbc:Altibase://192.168.1.111:20300/mydb”;

// URL for previous version of Altibase 5

String db_url2 = “jdbc:Altibase://192.168.1.222:20300/mydb”;

IBM JAVA 1.6 Environment

When using IBM Java 1.6, the connection URL must include the cm protocol version due to the characteristics of its DriverManager.

The DriverManager typically attempts to make a connection to one of the loaded driver classes, and if an SQLException occurs, another driver class is attempted to make a connection. However, the DriverManager in IBM Java 1.6 does not make a retry attempt and simply processes the exception instead.  Therefore, the cm protocol version must be added as shown below:

– AltibaseMultiversionConnection.java file

// URL example adding cm version

String db_url1 = “jdbc:Altibase_5.6.2://192.168.1.111:20300/mydb”;

String db_url2 = “jdbc:Altibase_4.5.1://192.168.1.222:20300/mydb”;

The method of verifying a cm protocol version is explained “Verifying JDBC Driver Versions” section. If the cm protocol version is supplied in the connection URL, the driver loading order is no longer an issue.

Calling Stored Procedures/Functions

To call a database stored procedure from within an application, the callSQL statement can be used to obtain a CallableStatement object:

1.   Stored Procedure

{ call procedure_name(?,?,….) }

Note: ? is procedure parameter, corresponding variable.

2.   Stored Function

{ call ? := function_name(?,?,….) }

Note: ‘?’ before: = is parameter that shows a result value after the function called.
‘?’ in () is a corresponding parameter to the parameter of function.

Several examples of calling stored procedures and functions are shown below:

– AltibasePSMCall.java file

// Call Stored Procedurel

String sql1 = “{call sum_proc(?,?,?)}”;

CallableStatement altibaseStatement1 = altibaseConnection1.prepareCall(sql1);

altibaseStatement1.setInt(1,10);

altibaseStatement1.setInt(2,20);

altibaseStatement1.registerOutParameter(3,java.sql.Types.NUMERIC);

altibaseStatement1.execute();

System.out.println(altibaseStatement1.getDouble(3));

System.out.println();

// Call Stored Function

String sql2 = “{call ? := sum_func(?,?)}”;

CallableStatement altibaseStatement2 = altibaseConnection1.prepareCall(sql2);

altibaseStatement2.registerOutParameter(1,java.sql.Types.NUMERIC);

altibaseStatement2.setInt(2,10);

altibaseStatement2.setInt(3,20);

altibaseStatement2.execute();

System.out.println(altibaseStatement2.getDouble(1));

Development References


The chapter introduces several references useful for JAVA development.

Using PreparedStatement

The PreparedStatement object creates an SQL statement in advance and processes binding for each parameter variable. In contrast, the Statement object processes each SQL statement in a prepare-execute manner for each individual query. Therefore, the PreparedStatement object can be used to improve performance if the SQL statement can be processed through binding-execute using variable parameters.

Using executeBatch()

When processing bulk DML, the executeUpdate() method transfers data to the database server every single time it is called. In contrast, the executeBatch() method will store data in an array using array processing and transfer all the data to the database server at once.  Therefore, the use of executeBatch() may improve performance by reducing the amount of communication required between the application server and the database server. To use this function, data should be added to an array using the addBatch() method and then executed by calling executeBatch().

Using setFetchSize()

The setFetchSize() method can be used to set the number of records to fetch at a time from the database server. Increasing the number of records fetched per request can improve overall performance by reducing the amount of server communication.

If a value for setFetchSize is not provided, additional communication with the database server is necessary if the communications buffer becomes full. The communications buffer is 32K by default. Conversely, setting the value for setFetchSize to a value lower than the number of records that can fit in the communication buffer will also incur unnecessary communication costs. Therefore, it is preferably to configure setFetchSize to a value larger than the maximum number of records to fill the 32K communication buffer. However, it is important to note that setFetchSize should not be set to an arbitrarily large value as this will increase the client’s memory utilization.

Assume that there is a program that must fetch 5000 bytes worth of data. If setFetchSize(1000) is used instead of setFetchSize(10), memory utilization will increase by (5000*(1000-10)) or 4950K bytes. Therefore, care should be taken to select an appropriate value that balances communication frequency and client memory utilization.

Returning Resources

After using Connection, Statement, or ResultSet objects, the relevant resources must be returned by explicitly calling the close() method. Failure to call the close() method will result in these objects remaining in heap memory. If the Statement object remains in heap memory, the database server will use an excessive amount of memory because it must continuously save information about the preparation of that statement. Therefore, it is critical that all objects are closed appropriately after their use.

NULL Value Processing

The setObject() and setNull() methods can be used to set a NULL value in a PreparedStatement object. To set a NULL Value, setObject(parameterIndex,SQLType.NULL) or setNull(parameterIndex,null) should be used. Altibase does not support the use of setObject(parameterIndex,null).

 LOB Data Processing

The autocommit mode must be set to off in order to process LOB data in Altibase.  If autocommit mode is enabled, attempting to process LOB data may cause unexpected behavior. The user will typically receive an error message or a null value.

The user should note that Altibase’s JDBC driver runs in autocommit mode by default. Therefore, setAutoCommit(false) should be called prior to making an attempt to process LOB data.

The $ALTIBASE_HOME/sample/JDBC/CLOB directory contains example files on how to properly process LOB data.

Using REF CURSOR

REF CURSORs in stored procedures allow result-sets to be used in a client’s JAVA application. An example is provided below:

– Define TYPE

CREATE OR REPLACE TYPESET my_type

AS

TYPE my_cur IS REF CURSOR;

END;

/

– Define REF CURSOR

CREATE OR REPLACE PROCEDURE my_ref_cursor

( v_result OUT my_type.my_cur, v_sql IN VARCHAR(1000) )

AS

BEGIN

OPEN v_result FOR v_sql;

END;

/

– AltibaseRefCursor.java file

String sql = “SELECT to_char(sysdate,’YYYY-MM-DD’) FROM dual”;

altibaseStatement3= altibaseConnection1.prepareCall(” { call my_ref_cursor(?)}”);

altibaseStatement3.setString(1,sql);

altibaseStatement3.execute();

rs = altibaseStatement3.getResultSet();

while(rs.next()){

System.out.println(rs.getString(1));

}

Error Messages

The chapter introduces several errors that are commonly encountered during development.

Communication Link Failure

Users may experience this error if the database is not online, connection related properties are incorrect, the incorrect JDBC driver is used, or if the database is disconnected due to a timeout during service.

Connection related properties such as the IP address, port number, username, and password should be verified if this error occurs.

If this error occurs after a connection is successfully established, verify that a session was not terminated by a connection timeout.

No Suitable Driver

This error occurs if an incorrect JDBC driver is used. For example, attempting to use the Altibase.jar JDBC driver to connect to an Altibase version 4 instance will result in this error being thrown.

Client Unable to Establish Connection

This error occurs if the Altibase instance is currently not operational. The user should verify that the Altibase server is running if this error is encountered.

TIMEOUT Related Errors

If a timeout occurs, Altibase will return an error and will either roll back or close any related sessions.

The following timeout related error messages will be returned to the client:

Classification Error Message
QUERY_TIMEOUT Client’s query exceeded in the execution time limitation
FETCH_TIMEOUT Communication link failure.
IDLE_TIMEOUT Communication link failure.
UTRANS_TIMEOUT The session has been closed by server.

If a QUERY_TIMEOUT occurs, the related statement will be rolled back, and then the error message will be returned. The other timeout related errors will roll back the related statements and then close the related session. After this occurs, future tasks requested by the client program may fail.

If any of these errors occur, the user should investigate any timeout related errors located in $ALTIBASE_HOME/trc/Altibase_boot_log.

Classification Error Message (altibase_boot.log)
QUERY_TIMEOUT [Notify : Query Timeout] Query Canceled by Server
FETCH_TIMEOUT [Notify : Fetch Timeout] Session Closed by Server
IDLE_TIMEOUT [Notify : Idle Timeout] Session Closed by Server
UTRANS_TIMEOUT [Notify : UTrans Timeout] Session Closed by Server

Invalid Descriptor Index

This error occurs when setXXX() is called with a value larger than the bind variable of a PreparedStatement.

altibaseStatement3= altibaseConnection1.prepareStatement(“SELECT c1 FROM t2 “);

altibaseStatement3.setString(1,”1”);   //Error occur

Optional Feature Not Implemented

This error occurs when the user attempts to call a method that is currently not supported by Altibase. The attachments section outlines all currently supported JDBC methods.

Copyright ⓒ 2000~2016 Altibase Corporation. All Rights Reserved.

These documents are for informational purposes only. These information contained herein is not warranted to be error-free and is subject to change without notice. Decisions pertaining to Altibase’s product characteristics, features and development roadmap are at the sole discretion of Altibase. Altibase may own related patents, trademarks, copyright or other intellectual property rights of products and/or features discussed in this document.