How to update iSeries databases using JDBC, SQL

JDBC is the most commonly used method to access data in relational databases on any platform from Java applications...

of any type (web, client, EJB, host). This tip shows common JDBC examples to create, write, read and update data in normal iSeries data files using JDBC and SQL and how to efficiently update iSeries databases using JDBC (Java Database Connectivity). The sample application (TestUpdateMethods.java) demonstrates two techniques for database updates using SQL: using an UPDATE statement with a where clause, using a positioned update with SQL cursor control.

Where you can use this

These techniques will help you develop more efficient Java database applications. The data access methods shown can be added to Java beans and invoked by JSPs. The examples use both DB2 UDB for Common Servers (version 7.2) and the iSeries Java Toolbox JDBC (version 3.0) drivers for testing. The example also was tested successfully with MySQL, version 3.5.3. The Sun JDBC specification is now at 3.0. The update where statement works with JDBC 1.0 drivers. The positioned update statements work with JDBC, 2.0 drivers.

Remember, if you are developing a Web application in WebSphere Development Studio Client (WDSC), you have other choices for building data access routines. You could also use the record IO classes in the Java Toolbox. They are similar to working with iSeries physical and logical files. Generally, record IO is more expensive to program for potentially small performance improvements over JDBC. JDBC is the lowest level of SQL data access development in WDSC. It offers the most control but also requires more work than other SQL data access development options. The "data access development" layers possible in WDSC include the following:

1. JDBC coding – shown here
2. Using IBM data access beans (dbbeans.jar) to manage data access
3. Using IBM JSP data access tags in a JSP page
4. Generating a data access application using the Web database application wizard

In addition, WDSC's Data perspective offers an excellent set of database management tools to visually manage your iSeries database, import the database definitions into WDSC, create SQL statements, stored procedures (to call host programs easily) and UDFs (user defined functions).

The sample test class (TestUpdateMethods) will do the following:

• Create a table (WEBUSR) and populate it in the selected database (DB2 UDB or AS/400)
• List a short report of table rows after create completes
• Set a selection value to select rows in the database
• Run a where update (an SQL update using a where clause) to set Title to Programmer for selected rows
• List a short report of table rows after the where update completes
• Run an SQL select before the positioned update to select rows
• Run a positioned SQL update using a cursor to iterate on the result set by row and set Title to Engineer for each row
• List a short report of table rows after the where update completes
• Close the database statements and connections

Basic concepts

The sample application uses SQL statements to do the following:

• Create a table (WEBUSR) – CREATE TABLE statement
• Insert records into the table – INSERT INTO statement
• Read selected records from the table – SELECT statement
• Update records in the table using a where clause – UPDATE WHERE
• Read selected records for update from the table – SELECT … FOR UPDATE OF statement
• Update records in the table using a where clause with a cursor - UPDATE WHERE CURRENT OF {cursor}

With JDBC, the concepts are similar to using files in a traditional program:

• You connect to a database - here using a JDBC Connection or opening a data source
• You position to data in a table – here using a SELECT statement with a WHERE clause
• You can create a new table – here using CREATE TABLE (instead of CRTPF)
• You can write records to a table – here using INSERT INTO
• You can update records in a table – here using 2 routines: UPDATE WHERE, UPDATE by cursor
• You can delete records in a table – not shown here
• You can scroll backward or forward in a table – using result set scrolling (see UPDATE by cursor)

For more on JDBC, the "10-minute Tutor" covers the basics of JDBC data access as well (www.quickwebsupport.com ) if needed. The sample class TestUpdateMethods.java is available for download there.

With JDBC applications, you have ALL the control over data access you have used in traditional RPG or Cobol programs with traditional physical and logical file IO:

• Read sequential, Read by Key, Write records, Update records, Delete records
• Manage concurrent access to the file with record "lock for update" control and more
• Scrolling through the database in the application using Read Next, Read Previous
• Commitment control and rollback under program control or automated
• Access specific file members in data or source physical files
• Efficient pooling of database connections using data sources (not shown here)
• Management of errors by the application (similar to MONMSG in a CL program)

Other benefits JDBC offers:

• Less coding needed in many cases than traditional programs – you can write "generic" JDBC routines one time to talk to any table!
• Can operate on iSeries data from ANYWHERE in a TCP network using the Java toolbox JDBC drivers
• Good performance for network applications including caching options
• Opportunity to replace many expensive host programs that do database reporting or updates with simple SQL and stored procedures
• Run CL commands easily
• Execute server Java applications with RUNJVA

Beyond iSeries, the SAME JDBC routines can read data in almost any other resource (MySQL on Linux, SQL Server in Windows, Excel spreadsheets, text files and so on). There's almost no limit. In fact, I have one Java bean we've written that can read or write to ALL those data sources and more anywhere in the network! That's the power of Java.

Setup for examples

You need WDSC version 5 installed correctly on a Windows system. For the DB2 UDB Common servers local access to DB2, you should install DB2 Personal Edition or the equivalent at version 6 or higher. For the iSeries database access, you'll need a TCP connection to an iSeries at V4R4 or later that has the Java toolbox installed (57xx-JC1).

JDBC drivers are provided by database vendors to connect Java applications using JDBC to a specific database. The drivers are normally packaged as jar files that need to be added to your application classpath at runtime.

Below is a table showing the different connection information you need for different JDBC drivers.

Table of JDBC connection information for common database drivers

Unless it's already in your classpath, you'll need to find the jar file and add it to your classpath BEFORE trying to use a JDBC driver. For the iSeries Java toolbox remote connection, JT400.jar is in WDSC in a folder {WDSC home}iserieseclipsepluginscom.ibm.etools.iseries.toolbox_5.0.0runtime) where {WDSC home} represents the base directory WDSC is installed in. For DB2 UDB Common servers, the jar file Db2java.zip is usually stored in the folder: x:sqllibjava (where x is your drive identifier)

To setup a project in WDSC to create the example, you need to do the following:

• Create a new project in WDSC
• Add a class variable for your JDBC database jar file to the project build path
• Create a new class: TestUpdateMethods.java
• Copy selected methods from TestUpdateMethods.java sample here to your own class definition
• Create a scrapbook page in the project
• Copy the text to run the Database update tests to the scrapbook

Here's the scrapbook text to run the database samples:

 /**

  * TestUpdateMethods.java

  * tests jdbc update methods on a new table (WEBUSR)

  */



     com.ebt.test.TestUpdateMethods.doAS400Connection = true;

     com.ebt.test.TestUpdateMethods.user = "USER";

     com.ebt.test.TestUpdateMethods.password = "PWD";

     com.ebt.test.TestUpdateMethods.system = "SYSTEM";

     com.ebt.test.TestUpdateMethods.library = "LIBRARY";

     System.out.println("nTestUpdateMethods.main started: " + new java.util.Date());                   

     com.ebt.test.TestUpdateMethods tester = new com.ebt.test.TestUpdateMethods();

     tester.createDb();

     tester.listDb("after create database..");

     tester.setSelection();

     tester.whereUpdateDb();

     tester.listDb("after whereUpdate..");

     tester.selectDb();

     tester.positionUpdateDb();

     tester.listDb("after positionUpdate..");

     tester.closeDb();

     System.out.println("TestUpdateMethods.main endedn");                   

Run the example

To run the example using the script above in the scrapbook page:

1. In the scrabook text, set the user, password, system and library names to the correct values for your environment.
2. Highlight the selected lines
3. Right button menu > Stop evaluation (IF it is an available option)
4. Right button menu > Run Snippet

The output below is shown in the WDSC console view

Console output for the example:

 TestUpdateMethods.main started: Fri Mar 28 05:37:04 EST 2003

 connectDb connected to: dcs1/jmason3

 // the database create is done here

 createDb started

 table WEBUSR created in SAMPLE

  row: 0 inserted

  row: 1 inserted

  row: 2 inserted

 createDb for WEBUSR completed..



 //    the table is listed here

     List - table: WEBUSR    after create database..     on: Fri Mar 28 05:37:22 EST 2003

 ID    FIRSTNAME    LASTNAME    TITLE   

 1001    Jim    Mason    Architect   

 1002    John    Harkins    VP of Engineering   

 1003    Joe    Mason    President   

 //    the selection value is set

 setSelection = Mason

 //    the where update method is run

 whereUpdateDb started - update using UPDATE sql with where clause

 set update = UPDATE WEBUSR SET Title = ? WHERE (Lastname = ?)

 completed whereUpdateDb for: Mason     set Title to: Programmer

 whereUpdateDb completed

 //    the table is listed here

     List - table: WEBUSR    after whereUpdate..     on: Fri Mar 28 05:37:26 EST 2003

 ID    FIRSTNAME    LASTNAME    TITLE   

 1001    Jim    Mason    Programmer   

 1002    John    Harkins    VP of Engineering   

 1003    Joe    Mason    Programmer   

 //    the select for positional update is done

 selectDb started - select for positioned update of result set

 selectDb completed = SELECT Lastname, Title FROM WEBUSR WHERE (Lastname = ? ) FOR UPDATE OF Title

 //    the positional update is done

 positionUpdateDb started - update using a cursor to iterate through result set

 positionUpdateDb name: Mason set Title = Engineer

 positionUpdateDb name: Mason set Title = Engineer

 positionUpdateDb completed

 //    the table is listed here

     List - table: WEBUSR    after positionUpdate..     on: Fri Mar 28 05:37:30 EST 2003

 ID    FIRSTNAME    LASTNAME    TITLE   

 1001    Jim    Mason    Engineer   

 1002    John    Harkins    VP of Engineering   

 1003    Joe    Mason    Engineer   

 closeDb - closed open connection , statements

 TestUpdateMethods.main ended

Build the example

You can edit the example in WDSC using the LPEX editor and make any changes you like.

Logically, building database access for an application WOULD NOT be done in a single class as done here. This was only for demonstration purposes. You would define a valid object model for a data access framework or use an existing framework if it fits such as IBM's Dbbeans.jar file used in many of the IBM data access wizards.

Let's review some sections of the sample application.

Connecting to the database (connectDb method)

Our connectDb method handles two types of connections: iSeries database using Java toolbox for iSeries and a local DB2 database using DB2 Personal Edition for Windows.

Setting the doAS400Connection property to true causes this application example to connect to an iSeries database. If false, the application attempts to connect to a local DB2 database.

Since my test library is not journalled or an SQL collection, I set the transaction isolation level to none for the connection so the database update statements would be allowed WITHOUT commitment control in place (good for testing but not production).

 if (doAS400Connection) {

 url = "jdbc:as400://" + system + "/" + library;

 driverClass = "com.ibm.as400.access.AS400JDBCDriver";

 } else {

 url = "jdbc:db2:" + dbName;

 driverClass = "COM.ibm.db2.jdbc.app.DB2Driver";

 };           

 Class.forName(driverClass).newInstance();

 connection = DriverManager.getConnection(url, user, password);

 connection.setAutoCommit(false);

 if (! doAS400Connection) {

 connection.setCatalog(user);

 System.out.println("connectDb connected to: " + dbName);           

 } else {

 connection.setTransactionIsolation(Connection.TRANSACTION_NONE);

 System.out.println("connectDb connected to: " + system + "/" + library);

 };

Our finalize method ensures the database statements and the connection are closed even if the application aborts in an error condition.

 if (createStmt != null) { createStmt.close(); createStmt = null; };

 if (selectStmt != null) { selectStmt.close(); selectStmt = null; };

 if (updateStmt != null) { updateStmt.close(); updateStmt = null; };

 if (readStmt != null) { readStmt.close(); readStmt = null; };

 if ( connection != null ) {

     connection.commit();

     connection.close();

     connection = null;

 };

 System.out.println("closeDb - closed open connection , statements");

Create database table

The create database table method (createDb) attempts to delete the WEBUSR table if it already exists with an SQL drop statement. It then does a CREATE TABLE statement defining the ID field as the primary key. After each statement is prepared (defined), an execute causes the statement to run and the commit causes the change to be applied permanently to the database.

Note that all the sample code shown here is enclosed in try – catch blocks to handle reporting any errors that occur (much like MONMSG commands in a CL program or using the error indicators in RPG and COBOL to handle database errors).

After the table is created, we define an insert statement with positional parameters for substitution. Substitution parameters are marked with a "?". The prepared statement allows us to efficiently create the statement one time and then reuse it over and over by just substituting new parameters and executing it again. When we're finished, we close the statement releasing its resources.

The array rows hold three arrays of Strings (rowdata1 to 3). The for loop (for each row of data values in rows) below does three things: sets each variable to insert positionally with a parameter value, executes the insert statement and commits the change. The substitutions occur in order by field index. Note the rows array index begins at 0, but the JDBC field index for a row begins at 1.

 System.out.println("ncreateDb started");

 String tableName = "WEBUSR";

 rows[0] = rowdata1;

 rows[1] = rowdata2;

 rows[2] = rowdata3;

 try {

     createStmt = connection.prepareStatement("DROP TABLE " + tableName);

  createStmt.execute();

  connection.commit();

 } catch (Exception e) { System.out.println("drop table " + tableName); };

 createStmt = connection.prepareStatement("CREATE TABLE " + tableName

 + " (Id INTEGER NOT NULL PRIMARY KEY, FirstName VARCHAR(16), LastName VARCHAR(16), Company VARCHAR(30), "

 + " Department VARCHAR(20), Title VARCHAR(25), Phone1 VARCHAR(25), Phone2 VARCHAR(25), email VARCHAR(30), "

 + " Address1 VARCHAR(30), Address2 VARCHAR(30), City VARCHAR(30), State CHAR(5), MailCode VARCHAR(15), "

 + " Country CHAR(3), Status CHAR(10), Comment VARCHAR(80))");

 createStmt.executeUpdate();

 connection.commit();

 System.out.println("table " + tableName + " created in " + dbName);

 createStmt = connection.prepareStatement("INSERT INTO "

  + tableName + " (Id, FirstName, LastName, Company, "

      + " Department, Title, Phone1, email, Comment)"

  + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

 for (int i = 0; i < 3; i++) {

 createStmt.setInt(1, Integer.parseInt(rows[i][0]));

     createStmt.setString(2, rows[i][1]);

     createStmt.setString(3, rows[i][2]);

     createStmt.setString(4, rows[i][3]);

     createStmt.setString(5, rows[i][4]);

    createStmt.setString(6, rows[i][5]);

     createStmt.setString(7, rows[i][6]);

     createStmt.setString(8, rows[i][7]);

     createStmt.setString(9, rows[i][8]);

     createStmt.execute();

    connection.commit();

     System.out.println("n row: " + i + " inserted");

 };

 System.out.println("createDb for " + tableName + " completed..");

List records in a table

The listDb() method lists the all the records in the table for a selected column list (fieldList). The routine:

• Defines an SQL query
• Creates a query statement using the query definition
• Executes the query returning a result set
• Gets the column headings for each column in the result set using the result set metadata
• Lists the column data for each row in the result set
• Displays the results on the system console device

One of JDBC's big advantages over normal host applications is the dynamic access to table metadata (your DDS definitions and more). You can adapt your program to information using the metadata.

 StringBuffer sb = new StringBuffer();

 sb.append("n    List - table: " + tableName + "t" + message

     + "t on: " + new java.util.Date() + "n");       

 //    create a statement to read table

 readStmt = connection.createStatement();

 ResultSet rs = readStmt.executeQuery("SELECT "

     + fieldList + " FROM " + tableName);

 //    get resultTable and define headings

 ResultSetMetaData rsm = rs.getMetaData();

 int count = rsm.getColumnCount();

 sb.append("n");

 for (int i = 1; i <= count; i++) {

     sb.append(rsm.getColumnLabel(i) + "t");

 };

 //    read table data by row

 while (rs.next()) {                               

     sb.append("n");

     for (int i = 1; i <= count; i++) {

         sb.append(rs.getObject(i) + "t");

     };

 };               

 System.out.println(sb.toString() + "n");   

Select records for update

The selectDb() method:

• Defines a select SQL string
• Creates a select statement with the SQL string
• Executes the select statement returning a result set
• Gets the name of the SQL cursor returned from the result set.

The cursor name will be needed later when we do a positional update.

 System.out.println("nselectDb started - select for positioned update of result set");

 selectSql = "SELECT " + selectField + ", " + updateField + " FROM " + tableName

 + " WHERE (" + selectField + " = ? ) FOR UPDATE OF " + updateField;

 selectStmt = connection.prepareStatement(selectSql);

 selectStmt.setString(1, selectValue);

 resultSet = selectStmt.executeQuery();

 cursorName = resultSet.getCursorName();

 System.out.println("selectDb completed = " + selectSql);

Update records using a result set cursor

The positionalUpdateDb() method does several things:

• Defines an SQL Update string using the WHERE CURRENT OF {cursor} clause to update a result set cursor position. (We'll use the cursor name we retrieved in the selectDb() method above.)
• Prepare and update statement using the update SQL string
• Iterate on the result set with the while clause for each row in the result set retrieved in selectDb
• Set a value for the Title column in the update statement by position using setString
• Log the new value for the row to the console with System.out.println
• Execute the update statement for that row and value
• When we're finished with all rows, we update the entire set using a single commit statement.
• We end by closing all our statements used

 System.out.println("npositionUpdateDb started - update using a cursor to iterate through result set");

 //    update the rows in the result set

 updateSql = "UPDATE " + tableName

     + " SET " + updateField + " = ?"

     + " WHERE CURRENT OF " + cursorName;

 updateStmt = connection.prepareStatement(updateSql);

 while (resultSet.next()) {

     if (resultSet.getString(updateField).equals("Engineer")) {

     updateValue = "Architect";

     } else {

         updateValue = "Engineer";

     };

     updateStmt.setString(1, updateValue);

 System.out.println("n positionUpdateDb name: " + resultSet.getString(selectField)

        + " set " + updateField + " = " + updateValue);

     updateStmt.execute();

 };

 connection.commit();

 selectStmt.close();

 updateStmt.close();

 resultSet.close();

 System.out.println("positionUpdateDb completed");

Update records using a where clause

The whereUpdateDb() method is simpler to code than the positional update which required two methods: selectDb() and positionalUpdateDb(). It is useful if you are doing a single update in a program. If multiple update statements are needed for different values, the positional update is the best choice because it allows flexibility in the updates for a single row and it does the selection once.

The whereUpdateDb() method accomplishes several things:

• Defines an SQL update string with a where clause to select records and specifies SET conditions for columns
• Prepares an update statement
• Sets any positional parameter values for the SQL statement (here we have a selectValue = "Mason" and an updateValue = "Programmer")
• Executes the update statement
• Commits the changes to the database
• Closes the update statement

 System.out.println("nwhereUpdateDb started - update using UPDATE sql with where clause");

 updateValue = "Programmer";

 //    update the selected rows in the result set       

 updateSql = "UPDATE " + tableName

     + " SET " + updateField + " = ?"

     + " WHERE (" + selectField + " = ?)";

 PreparedStatement updateStmt = connection.prepareStatement(updateSql);

 System.out.println("set update = " + updateSql);

 updateStmt.setString(1, updateValue);

 updateStmt.setString(2, selectValue);

 updateStmt.execute();

 connection.commit();

 System.out.println("completed whereUpdateDb for: " + selectValue +

     "t set " + updateField + " to: " + updateValue);       

 updateStmt.close();           

 System.out.println("whereUpdateDb completed");

Key tips

----------------------------------------
About the author: Jim Mason, president of ebt-now, is an iSeries WebSphere engineer. ebt-now provides iSeries WebSphere, WebFacing engineering, development and training services. For more on JDBC, visit www.quickwebsupport.com.