Of Java and DB2: Using Java Database Connectivity and SQLJ

IBM has provided the OS/390 Java programmer with two access mechanisms to access DB2 data: Java Database Connectivity (JDBC) and SQLJ. Learn what will work best for you. Receive an overview of Java with DB2 in the OS/390 environment, view code snippets illustrating each access mechanism, and compare the strengths and weaknesses of JDBC versus SQLJ.

As part of IBM's "Java Everywhere" strategy, IBM has provided the OS/390 Java programmer with two access mechanisms to access DB2 data. This article discusses these two mechanisms: using Java Database Connectivity (JDBC) and using SQLJ. In addition, the Java programmer can use either mechanism to access DB2 data by calling DB2 stored procedures.

The article provides an overview of Java with DB2 in the OS/390 environment. Next, the article covers how the OS/390 Java programmer can access DB2 data by using JDBC, SQLJ and stored procedures. The reader will see code snippets illustrating each access mechanism. A comparison of the strengths and weaknesses of JDBC versus SQLJ follows.

An Overview

The programmer can access DB2 data by including static DB2 in their Java source (SQLJ), dynamic DB2 (JDBC) or both. Also, the programmer can access DB2 data by having their Java programs access DB2 stored procedures.

If the Java programmer uses either SQLJ or stored procedures, they must call a DB2 preprocessor called the SQLJ translator. This translator provides the same function as the DB2 preprocessor for translating static SQL embedded in COBOL, PL/I or C programs. After a successful SQLJ translation, it's business as usual with the production of a DBRM, and a plan or package bind. The Java programmer using JDBC for dynamic SQL skips these DBRM/plan or package bind steps.

The OS/390 Java DB2 programmer can use a mixture of static (including stored procedures) and dynamic SQL statements like the COBOL DB2 programmer. Whatever the combination of SQL statement types used, the code gets to the Java compiler where the compiler produces industry-standard Java Bytecode. From here, the Bytecode can get passed to the Java Virtual Machine for program execution or to IBM's High Performance Java (HPJ) compiler.

The HPJ compiler accepts Bytecode as input and produces OS/390 native code as output. Java's cross-platform execution by using standard Bytecode formats comes at a price: Bytecode execution is interpretive and, therefore, slow when compared to traditional compiled and linked languages like COBOL or C.

To address the needs of customers who want the advantages of Java application development (cross-platform source code, use of object-oriented techniques, for example) without the execution performance degradation common with interpretive languages, IBM offers the HPJ compiler.

The same source code that gets compiled and sent to the Java Virtual Machine (JVM) can be sent to the HPJ.

Java Database Connectivity (JDBC)

JDBC is an industry-standard SQL API that allows the Java programmer to access most relational databases, including DB2 (see sidebar "Using JDBC"). The programmer should include the java.sql package to use JDBC. This package includes the following class and abstract interfaces:

• DriverManager. This class is used to load the DB2 driver code needed to create database connections.

• Connection. This interface allows a programmer to connect and disconnect to a named data source.

• Statement. This interface is used to execute SQL statements. Included with the statement interface are: PreparedStatement (Executes SQL statements containing input parameters), and CallableStatement (Invokes a DB2 stored procedure).

• ResultSet. This interface is used to retrieve the data in the results set of a previously executed SQL statement.

SQLJ

SQLJ is a standard way of embedding static SQL statements in Java programs. The overall methodology is similar to embedding SQL in COBOL or PL/I programs. A Java program can use both SQLJ and JDBC to access DB2 data (see sidebar "Using SQLJ").

The SQLJ standard includes three parts: the embedded SQL, the SQLJ translator and a SQLJ runtime environment. The programmer embeds SQL in SQLJ programs by preceding the SQL statement with the #sql token. SQLJ programs can contain any data manipulation SQL, DB2 table DDL, COMMIT/ROLLBACK, searched and positioned UPDATE and DELETE, CALL to access stored procedures, and SET for host variables. SQLJ files containing Java and embedded SQLJ must have a .sqlj extension.

The SQLJ translator produces Java source containing embedded SQL into Java source files. Put another way, the SQLJ translator is similar to the DB2 preprocessor used by the COBOL, PL/I or C programmer accessing DB2 data. The SQLJ translator also produces profiles that provide the runtime with various details on the database schema. These profiles are used to create DBRMS that are bound to packages or plans.

The SQLJ runtime environment executes SQL using, in part, the information found in the previously generated profile(s). Typically, the runtime SQL implementation is done through JDBC.

Comparing JDBC to SQLJ

Java programs that access DB2 data may use both JDBC and SQLJ. However, if a Java program needs to issue dynamic SQL, that program must use JDBC only. The dynamic nature of JDBC has an impact on security. DBAs can grant users access to tables; a user can either change a table or not. In contrast, a program that uses SQLJ uses a previously bound package or plan. The DBA can grant users execution authority to the package or plans.

Of course, the old argument of execution speed still applies. Programs that use statically bound SQL statements execute faster than programs that use dynamically bound SQL statements. In addition, the access paths contained in the DBRMs can be analyzed and tweaked, yielding further performance gains.

The JDBC program cannot check that the SQL data types match the Java primitive types or classes until runtime. In programs that use SQLJ, type checking is done at the translator (precompiler) stage.

On the flip side, using SQLJ limits the portability of the application. Each vendor could provide their SQLJ translator, which would be different for different products. Also, the SQLJ development process is more complex owing to the need for translation (precompilation) and binding.

If Java/DB2 development parallels DB2 development in COBOL, PL/I or C, it's a safe bet to assume that most Java/DB2 programs would use static SQL more than dynamic.

About the Author:

Lou Marco is a writer, technical instructor and a consultant with over 20 years of computing experience. He has authored ISPF/REXX Development for Experienced Programmers, and is writing a book titled Developing Java Mainframe Applications for John Wiley & Sons. He can be reached at loumarco@hotmail.com.

Using JDBC

Here are the steps a Java programmer takes to access DB2 data with JDBC (with code snippets):

1. Import the JDBC package java.sql by coding

import java.sql.* ;

2. Load the DB2 driver using DriverManager.

String ibmDriver = "ibm.sql.DB2Driver" ;

try { Class.forName( ibmDriver ) ; }

catch ( ClassNotFoundException exception) {_

exception.printStackTrace() ;

}

The method Class.forName automatically creates an instance of a driver object.

3. Declare and connect to a named data source (database):

String db2URL = "jdbc:db2os390:mydb2db" ;

Connection myCon ;_try { myCon = DriverManager.getConnection( myURL ) ; }

catch ( SQLException exception ) {

exception.PrintStackTrace() ;

}

For OS/390, JDBC identifies a data source for connection by accepting a database URL in the following format: jdbc:db2os390:, where is the DB2 LOCATION found in the DB2 catalog table SYSIBM.LOCATIONS.

4. Execute one or more SQL statements by creating a Statement object, associating the statement(s) with an active connection, and execute the statement using the appropriate method. For SQL SELECT statements, the appropriate method is executeQuery(); for SQL statements that modify or create tables, the appropriate method is executeUpdate().

Statement deleteStmt = myCon.createStatement() ; //myCon is the active connection

String deleteSQL = "DELETE FROM EMP WHERE UNIT = 'A001'" ;

deleteStmt.executeUpdate( deleteSQL ) ; //Execute Statement

5. Retrieve the data returned by a SELECT statement by creating a ResultSet object and _accessing the rows of this ResultSet, usually with a loop construct.

Statement selectStmt = myCon.createStatement() ;

ResultSet selectResults =

selectStmt.executeQuery( SELECT NAME, SALARY, UNIT FROM EMP) ;

//Loop through selectResults to access each row of the ResultSet object_while( selectResults.next() ) {

String javaName= selectResults.getString( 1 ) ;

float javaSalary= selectResults.getBigDecimal( 2,2 ).floatValue() ;

String javaUnit= selectResults.getString("UNIT") ;

}

The next() method of an instance of ResultSet advances the cursor to the next row. The cursor is initially above the first row; hence, the first call to next() advances to the first row in the ResultSet. Next() returns false when no following row exists in the ResultSet.

Class ResultSet contains get???? methods, where ???? is a Java primitive data type or class that corresponds to a SQL data type. A program could use a method to access the DB2 data but could get unexpected results. For example, a program accessing a DECIMAL type as FLOAT could result in a loss of precision. The recommended method to access a DB2 FLOAT data type is getDouble(), not getFloat(). The generic getObject() method could be used to access data of any type but the returned results could be unpredictable.

JDBC allows two ways to identify a column in the results set: by position and by name. Note the first and second calls to getString() uses position; the third uses name. The author is not aware of any advantages one method of identification has over the other.

6. Clean up by closing the statement, and closing the connection from the database.

selectStmt.close() ; //Close the statement

myCon.close();//Close the connection

The application should have code that closes the statement(s) and the active connection when the application encounters an unrecoverable error.

Using SQLJ

Here are the steps a programmer takes to create a Java/DB2 program using SQLJ.

1. Import the java.sql and sqlj.runtime.ref packages by coding:

import java.sql.* ;

import sqlj.runtime.ref.* ;

2. Declare and establish a connection context. A connection context is the SQLJ equivalent of a database connection. In JavaSpeak, a connection context is an instance of a connection context class. Here's how an SQLJ program declares a connection context:

#sqlcontextmyCtx ;

Once the connection context is known to the program, the programmer has a choice of two DB2 connection methods: The first is to invoke the constructor for the class created by the above declaration using the location of the DB2 data source as an argument. For example:

Stringdb2URL = "jdbc:db2os390sqlj:mydb2db" ;//Note difference in second qualifier

//from JDBC location name

myCtxmyCon = new myCtx( db2URL ) ;

The second method is to invoke the constructor for the context connection class using the JDBC connection returned by DriverManager.getConnection(). For example,

Connection myJDBCCon = DriverManager.getConnection( db2URL ) ;

myCtxmyCon= new mtCtx( myJDBCCon ) ;

3. Load a DB2 driver, as shown in the section Using JDBC.

4. Declare result set iterators. A result set iterator is similar to the ResultSet object discussed above. In the language of static SQL, a result set iterator is like a cursor. Like a cursor, the iterator declaration identifies the columns of a DB2 table or join to be accessed. One interesting feature of iterators not shared by their cousins, the cursors, is that iterators can be passed to methods as arguments (like other Java objects) whereas cursors cannot.

SQLJ iterators come in two flavors: positioned iterators and named iterators. A positioned iterator associates the columns in the results table with the columns referenced in the iterator in left-to-right order. A named iterator associates these columns by the name of the column in the DB2 table.

The programmer declares the iterator classes corresponding to those SQL statements that produce result set tables having more than one row.

The programmer declares objects of the iterator classes. Here are some examples:

#sqliteratorByposUnitIter ( String ) ; //By Position iterator showing data type of

//result set column.

#sqliteratorBynameNameIter( String unit ) ;//By Name iterator showing data type

//and name of result set column

It is important to remember that the above #sql statements declare Java classes, and that the DB2 program works with objects created from these classes.

5. Execute the SQL statement(s). If the SQL statements access more than one row, the SQL statement may refer to a previously declared iterator. For example:

ByposUnitIteraniterByPos ;//Declare an object of Class ByposUnitIter

//Construct the iterator object and execute statement

#sql (myCon)aniterByPos = {SELECT UNIT FROM EMP WHERE EMPID=23790 } ;

//Do the FETCH, loop down the iterator

#sql (myCon){ FETCH :aniterByPos INTO:javaUnit } ;

while (!aniterByPos.endFetch() ) {

/* Do Stuff With The Retrieved Data*/

#sql (myCon){ FETCH :aniterByPos INTO:javaUnit } ;

}

Like embedding SQL in a COBOL or PL/I program, the Java programmer passes data between the Java program and DB2 by using host expressions.

A Java host expression could be a Java identifier, like the above FETCH statement shows, or a Java expression that evaluates to a value, preceded by a colon. Java identifiers, therefore host expressions, are case sensitive.

Here's an example of some Java code that uses a named iterator:

BynameNameIter aniterByName;//Declare an object of Class BynameNameIter

//Construct the iterator object and execute statement

#sql (myCon) aniterByName = {SELECT NAME FROM EMP WHERE UNIT='A001'} ;

//Get the data, loop down the iterator

while (aniterByName.next() ) {

/* Do stuff with the retrieved data */

javaName = aniterByName.Unit() ;

}

Note the name of the accessor method provided to the program from the iterator class: aniterByName.Unit(). SQLJ generates an accessor method for every column named in the iterator. For those cases where the DB2 column name is not a valid Java identifier, the programmer could use the AS feature of the SELECT statement. For example,

#sqliteratoraNameIter ByName( String MyCol ) ;//Declare named iterator Class

aNameItermyIter ;//Declare iterator from Class

#sql myIter = {SELECT "GOOD DB2 BAD JAVA NAME" AS MYCOL FROM ATABLE};

6. Commit any changes by invoking the commit() method of the Connection object:

myCon.commit() ;

7. Close any iterators and close (disconnect) from the database:

myIter.close() ;

myCon.close() ;

As with a JDBC connection, the program should disconnect from the database when the program encounters an unrecoverable error.