08
Fri, Nov
10 New Articles

Just Browsing with JDBC

APIs
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: