 | |
 |
|
| Jim Mason | |
| |
IBM's WebSphere Development Studio Client for iSeries (WDSC) tool set offers some handy wizards that can generate several basic types of Web applications. One of the easiest wizards to use is the Web database pages wizard. It generates a simple Web database application that allows custom search, select and view using your own custom SQL statement.
You can also use the IBM DBBeans.jar file directly in your own applications productively. It's easier than building your own database components and easier than using JDBC directly.
Below is a simple example that uses the IBM DB Beans to print a simple report to the System console.
Advantages for using DB Beans:
- Well-engineered for a variety of flexible application uses
- Not tied to any specific DB2 driver or platform
- Easy to use in Java applications
- Redistribute the DB Beans jar with your own applications
- Supports all key SQL operations: SELECT, INSERT, UPDATE, DELETE and CALL
- Create generic beans that can perform SQL for ANY table on your system
One Bean to report on any file in DB2? That's the kind of power Java offers compared to RPG. Could this have been done in RPG? Yes. Would it have taken less than an hour to learn how to do? No.
To do the example below, do the following:
- Create a Java bean with the sample code below
- Set up DBBeans.jar on your classpath
- Compile your Java bean
- Test your Java bean
package ebt.test2;
import com.ibm.db.*;
import com.ibm.db.beans.*;
import java.util.*;
/**
* @author Cape Cod Bay Systems all rights reserved, copyright 2002 (www.ebt-now.com) jem
*
*/
public class DbbeansTest1 {
/**
* Constructor for DbbeansTest1.
*/
public DbbeansTest1() {
super();
}
public static void main(String[] args) {
(new DbbeansTest1()).runQuery();
}
public void runQuery() {
/**
* Tip - using IBM DBBeans.jar for data access in a custom program
*
* NOTE:
* the simple example below could be extended to add:
* - custom selection parameters
* - point to any table in DB2 on your iSeries
* - incorporate this logic in a custom bean for use on web pages
*
* (See the IBM WDSC for more..)
* WDSC already provides a web database pages wizard that
* - generates a search, select, view web app on any DB2 table
* - includes WebSphere v4.0 IN WDSC for easy testing of the applications
*
* Jim Mason
* www.ebt-now.com
* jemason@ebt-now.com
*
* #1 setup variables for data access
*/
// ConnectionSpec
DBConnectionSpec connectionSpec = null;
//Variables
DBSelect selectBean = new DBSelect();
String _user = "jem";
String _password = "jem";
String _driver = "COM.ibm.db2.jdbc.app.DB2Driver";
String _url = "jdbc:db2:SAMPLE";
String _parm1_value;
String _parm2_value;
String _sqlString;
String _whereClause = " WHERE (LASTNAME >= 'D' ) ";
String _columns = " LASTNAME, FIRSTNME, WORKDEPT, PHONENO, EMPNO ";
String _tableName = " JEM.EMPLOYEE ";
String _orderBy = " ORDER BY LASTNAME ";
/**
* #2 setup a try catch block to test data access to sample database
* any exceptions result in a stack trace print to System output device
*
* logic for data access
* 1 define database connection
* 2 setup SQL SELECT statement for the selectBean
* 3 get the metadata for the selectBean describing the columns in the query
* 4 execute the SQL statement in the selectBean
* 5 print a report heading
* 6 iterate over all the rows in the result set to print the detail
* 7 print report to the System.console
* 8 close the database connection
*/
try {
// setup connection spec to database
connectionSpec = new DBConnectionSpec();
connectionSpec.setUsername(_user);
connectionSpec.setPassword(_password);
connectionSpec.setDriverName(_driver);
connectionSpec.setUrl(_url);
// create a StringBuffer to build a report string
StringBuffer sb = new StringBuffer();
// set a connection spec on the selectBean
selectBean.setConnectionSpec(connectionSpec);
selectBean.setOptimizeForJsp(true);
// define the SQL statement
_sqlString = "SELECT " + _columns + " FROM " + _tableName + _whereClause + _orderBy;
selectBean.setCommand(_sqlString);
// get result metadata
DBSelectMetaData resultMetaData = selectBean.getMetaData();
// execute statement returning result
selectBean.execute();
// print a report - heading - sql statement - result table
sb.append("n Report on table: " + _tableName + "/t/t on: " + new java.util.Date());
sb.append("nn SQL statement = ");
sb.append("nttt" + _sqlString);
// print column headings
for (int i = 0; i < resultMetaData.getColumnCount(); i++) {
sb.append(resultMetaData.getColumnLabel(i + 1) + " ");
};
sb.append("n");
// print row data for each row in the selectBean resultSet
while (selectBean.next()) {
for (int i = 0; i < resultMetaData.getColumnCount(); i++) {
sb.append(selectBean.getColumnAsString(i + 1) + " ");
};
sb.append("n");
};
// print report to console
System.out.println(sb.toString());
// close statement
selectBean.close();
selectBean = null;
} catch (Exception e) {
e.printStackTrace();
};
}
}
Output report on SQL query using DBBeans.jar
The output below is not formatted at all, but it DOES show how easy it is to quickly generate standard reports using a single Java bean that takes String parameters to define an SQL query dynamically.
Report on table: JEM.EMPLOYEE /t/t on: Fri Dec 20 11:51:24 EST 2002 SQL statement = SELECT LASTNAME, FIRSTNME, WORKDEPT, PHONENO, EMPNO FROM JEM.EMPLOYEE WHERE (LASTNAME >= 'D' ) ORDER BY LASTNAME LASTNAME FIRSTNME WORKDEPT PHONENO EMPNO GOUNOT JASON E21 5698 000340 HAAS CHRISTINE A00 3978 000010 HENDERSON EILEEN E11 5498 000090 JEFFERSON JAMES D21 2094 000230 JOHNSON SYBIL D21 8953 000260 JONES WILLIAM D11 0942 000210 KWAN SALLY C01 4738 000030 LEE WING E21 2103 000330 LUCCHESSI VINCENZO A00 3490 000110 LUTZ JENNIFER D11 0672 000220 MARINO SALVATORE D21 3780 000240 MEHTA RAMLAL E21 9990 000320 NICHOLLS HEATHER C01 1793 000140 O'CONNELL SEAN A00 2167 000120 PARKER JOHN E11 4502 000290 PEREZ MARIA D21 9001 000270 PIANKA ELIZABETH D11 3782 000160 PULASKI EVA D21 7831 000070 QUINTANA DOLORES C01 4578 000130 SCHNEIDER ETHEL E11 8997 000280 SCOUTTEN MARILYN D11 1682 000180 SETRIGHT MAUDE E11 3332 000310 SMITH DANIEL D21 0961 000250 SMITH PHILIP E11 2095 000300 SPENSER THEODORE E21 0972 000100 STERN IRVING D11 6423 000060 THOMPSON MICHAEL B01 3476 000020 WALKER JAMES D11 2986 000190 YOSHIMURA MASATOSHI D11 2890 000170
Your next steps
- Visit IBM's Web site for WebSphere version 5 information: http://www-3.ibm.com/software/info1/websphere/index.jsp?tab=products/appserv&S_TACT=102BBW01&S_CMP=campaign.
- Visit IBM's iSeries WebSphere site to keep abreast of announcements as they come out: http://www-1.ibm.com/servers/eserver/iseries/software/websphere/wsappserver/. (IBM Rochester doesn't provide much advanced planning information usually on coming products.)
- Stay tuned to Search400.com, which provides very good "planning lead time" on coming iSeries WebSphere news.
- Visit the QuickWebSupport site (http://groups.yahoo.com/group/QuickWebSupport/), which will provide free tutorials on WebSphere Express usage when it becomes available.
--------------------------
About the author: Jim Mason is president of ebt-now.com, and he writes, consults, teaches, designs and develops iSeries Web applications using Java, WebSphere, DB2, Lotus Domino and the WebSphere Development Tools for iSeries.
==================================
MORE INFORMATION
==================================
- Start right with the new WDSC workbench
Web development experts Jim Mason and David Slater say WebSphere Development Studio Client (WDSC) for iSeries Eclipse workbench -- with its easy-to-use wizards and full support for Java, J2EE, XML and Web services -- builds integrated, iSeries e-business applications and data for the Web easier, faster, cheaper and better than any other tool they've seen. Read their overview of the basic features in the workbench and follow their instructions for building your own Java application. (It's easier than you may think.) iSeries developers new to both Java and Eclipse tools will benefit. - Web-enabling apps easier with new WebSphere Studio Development Client
If you have not ventured into the world of Java and WebSphere, IBM has removed any and all reasons that may have prevented you from doing so. WebSphere Studio Development Client, announced June 4, greatly eases and speeds up the process for Web-enabling applications. One of the significant enhancements in this package is the support for WebFacing and PCML. Wizards and tutorials make it easier than ever to use those features. Bob Cancilla, one of the beta testers for the product, shows you just how easy. - Get started building Java Web apps in WDSC
IBM's WebSphere Development Studio Client (WDSC) for iSeries is a powerful, easy-to-use tool for building every type of Web application you can think of. Web development expert Jim Mason offers a step-by-step guide that shows you how to quickly build simple Java Web applications that access iSeries data and call RPG programs with a browser interface.
This was first published in December 2002