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.