To many people, e-business is a term used only by computer companies in marketing campaigns and is not yet a concept that has really been internalized and put into practice by many AS/400 shops. Most developers are quite skilled at accessing and manipulating data in AS/400 files by using RPG but have a difficult time in transferring those skills to accommodate the growing e-business trend. By now, everyone knows that IBM has Java- enabled the AS/400; it’s time to figure out how to get to that data and how to display and manipulate it using the Java Database Connectivity (JDBC) APIs.

Layout of the Land

I’d like to show you how to display information in fields called JLabels and in a tabular format using JTables. JLabels are similar to display file DDS output fields. JTables are equivalent to standard subfiles. To explain the process, I use the example of allowing customers to inquire about their orders. They do so by accessing your Web page, which has an applet that, in turn, accesses your AS/400 using JDBC.

The first thing to do is layout the data in a format that customers will find appealing and easy to understand. One way to accomplish this is to use a layout manager. A layout manager is comparable in functionality to the User Interface Manager (UIM) found on every AS/400. To use the manager, simply declare in your code the items you’d like displayed in your applet (JLabels, JTable, and JButton in Figure 1) and let the manager perform the details of the layout for you.

The example in Figure 1 uses the GridBagLayout layout manager. This layout manager gives you the simplest method of placing on the screen several fields in a form- type display. Unlike display file DDS, which allows a developer to place fields beginning at a specified row and column, Java has no such capabilities. Instead, you create the JLabels that will display both database information and its descriptive text.

Make the Connection

My Java Web application consists of three Java classes: Inquiry, which is an applet; Form, which is the panel that contains the JTable; and Detail, which constructs the JTable. The Java source for these classes can be downloaded from the Midrange Computing home page at www.midrangecomputing.com/mc/. The meat of the code is in the Form class where the SQL connection is made to the AS/400 database. (See Figure 2 for a subset of the Form

class’s Java source.) Java uses a driver manager that identifies the classes containing JDBC drivers. Loading the AS/400 JDBC driver in your applet is the first step in making the connection to your AS/400 and requires minimal code:

DriverManager.registerDriver

(new com.ibm.as400.access.AS400JDBCDriver());

The AS/400 JDBC driver is provided as one of the many features of the AS/400 Toolbox for Java. The Toolbox is simply a collection of Java classes provided by IBM and compressed into a file called jt400.jar. Later, I’ll show you how to use these files efficiently.

After the driver is registered, make the connection to the AS/400 with another bit of code:

Connection c = DriverManager.getConnection

(“jdbc:as400://mySystem”, “usrprf”, “pwd”);

The Connection object is basically a TCP/IP interface to the AS/400’s SQLServer, through which you pass SQL statements to the AS/400. Once the open connection exists, you can use it to perform all the data manipulations that you are used to doing in RPG (only now you use SQL). In addition, you can use the Connection object as you would any other object. The example passes the Connection object from the class that constructs the header of the form to the class that constructs the detail lines of the form. This, of course, saves resources by minimizing the number of times you must close and reopen connections throughout your code. You can even optimize the performance of the applet further by having the Inquiry class spawn a thread to handle the connection in the background while it draws the initial GUI components of the applet; namely, the customer number and order number fields and the lookup button.

Make a Statement

The JDBC statement is created next. This statement passes an SQL statement to the AS/400 database management system (DBMS). In Figure 2, the physical file Orders contains information about customer orders, and another file named Customer serves as the customer master file. The customer number and order number are passed as parameters to the statement, which is then sent directly to the AS/400. What is returned to your program in an SQL Select statement is an object called a Result Set. A Result Set has rows and columns of the selected data that your program will use to fill out the form. You can position the cursor of the Result Set to row one by calling its first() method, since this particular example only requires the use of one row.

Now that you have the Result Set, the labels that were created to display database information are now ready to be populated. You accomplish this by calling the Result Set’s getString() method (using the column numbers as parameters). In the example, calling the getString() method on the fourth column of the Result Set returns the date on which the order was placed; you can put the date into the JLabel you created to house such information.

In the Form.java class example, after all the SQL statements have been executed, the statement and connection are both closed (as a matter of good housekeeping) by calling their respective close() methods.

Set the Table

The detail of the order is displayed in a JTable, which is constructed using an object called a DefaultTableModel (as shown in Figure 3). The DefaultTableModel is responsible for providing the values for the cells in the JTable. DefaultTableModel gets its data from a

Vector object (essentially a dynamically sizable array) that contains row data retrieved via JDBC. In constructing the JTable, you receive the Connection object (created when you populated the JLabels of the header and footer of the form) and get a new Result Set. This time, you scroll forward through the Result Set by looping through it (and populating a Vector object in the process). Because the Result Set’s column headings are in row one, that row is skipped. Instead, another Vector object is populated with String objects to be used as the headings for the JTable. To deploy the applet, the three source files are compiled into three class files and then compressed into a single .jar file using the Java Development Kit (JDK) jar utility. The HTML file’s APPLET tag has to specify the applet’s jar file in its ARCHIVES option along with the AS/400 Toolbox for Java’s jt400.jar file, which provides the JDBC driver required by the applet.

When you’ve created your jar file and placed it in an HTML document’s applet tag, you must take care to grant appropriate permissions to the applet; namely, the permission to create a socket connection on the AS/400. In order to deploy your applet, JDBC itself does not require that it be a trusted applet. At the very least, however, the applet must be granted permission by a policy file to create the socket connection. Typically, the policy file will reside in your user home directory.

Ready, Set, Go

Applet deployment aside, you can see that it takes only two lines of code to register a JDBC driver, connect to the AS/400, and start working with your files. At first, it might feel unnatural working with result sets and SQL after working with RPG and DDS for so long. But the benefits of becoming familiar with, and skilled at, presenting AS/400 data on the Internet or your intranet through a browser will outweigh any learning curve you might encounter.

Figure 1: The Inquiry applet displays a custom JTable “subfile” after the user fills in the customer and order number.

...

DriverManager.registerDriver(

new com.ibm.as400.access.AS400JDBCDriver());

connection = DriverManager.getConnection

("jdbc:as400://sysname", "usrprf", "pwd");

Statement s = connection.createStatement();

ResultSet rs = s.executeQuery(

"SELECT * FROM Customer, Orders " +

" WHERE Customer.CustNo = Orders.CustNo

...

rs.first();

CustIddsp.setText(rs.getString(1));

...

s.close();

...

Figure 2: The Form class loads the JDBC driver, establishes a connection, and creates the GUI components of the applet.

public class Detail extends JTable {

public Detail(Connection c, int a, int b) {

try {

Statement s = c.createStatement();

ResultSet rs = s.executeQuery (
"SELECT ItemNo, ItemDsc, Qty, UnitCost, Qty * UnitCost" +
" AS Ext " FROM ORDERS WHERE CustNo = " + a +
" AND OrdNo = " + b);

columns.addElement(new String("Item No."));

//... other addElement calls omitted

while (rs.next()) {

Vector newRow = new Vector();

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

newRow.addElement(rs.getObject(i));

}

rows.addElement(newRow);

}

model.setDataVector(rows, columns);

s.close(); c.close();

} catch (Exception e) {/* code omitted */ }

setModel(model);

TableColumn desc = getColumn(columns.get(1));

desc.setPreferredWidth(200);
}

private DefaultTableModel model = new DefaultTableModel();
private Connection c;
private Vector rows = new Vector();
private Vector columns = new Vector();

}

Figure 3: The JTable “subfile” class creates a custom multicolumn view.

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?