WDSC -- Using DBBeans.jar for data access

Here's a simple example that uses the IBM DB Beans to print a simple report to the System console.


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:

  1. Create a Java bean with the sample code below
  2. Set up DBBeans.jar on your classpath
  3. Compile your Java bean
  4. 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

  1. 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.
  2. 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.)
  3. Stay tuned to Search400.com, which provides very good "planning lead time" on coming iSeries WebSphere news.
  4. 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

Dig deeper on Web Tools

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close