IBM: DB Tools dbbeans.jar

DBBeans.jar

IBM DBBeans.jar comes with elegant APIs for JDBC and simplifies JDBC. Approx +/- 25 JAVA files implemented to do this. This jar is shipped as part of WSAD/RAD in datatools plugin. This jar is DB acqnostic and ready to use directly in application

In JDBC implementation, we have to establish DB connection and then create statement from connection. However, in this jar, we can tag a connection in a statement bean object.

DBStatement is the super class which helps to define statements. DBStatement offers API to release the resource in various levels by calling close(...) API. DBStatement comes with three different flavours

DBSelect

created and used to select rows from DB, which gives facility to retrieve number of rows fetched.

DBModify

makes insert or update or delete operation in DB

DBProcedureCall

calls stored procedure in DB. This class is subclass of DBSelect

DBSelectBeanInfo, DBModifyBeanInfo, DBProcedureCallBeanInfo are the beans stores information for the above listed statements.

DB Events

This implementation facilitate to triggers events before/after calling/executing the DBStatement. We can achive this by registering listener(DBBeforeListener/DBAfterListener) implemented classes in DBStatement.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.ibm.db.beans.DBModify;
import com.ibm.db.beans.DBProcedureCall;
import com.ibm.db.beans.DBSelect;
import com.ibm.db.beans.DBStatement;

public class DBStatementFactory {

 public static DBSelect getDBSelect() {
  DBSelect dbSelect = new DBSelect();
  dbSelect = (DBSelect) initializeDBStatement(dbSelect);
  return dbSelect;
 }

 public static DBModify getDBModify() {
  DBModify dbModify = new DBModify();
  dbModify = (DBModify) initializeDBStatement(dbModify);
  return dbModify;
 }

 public static DBProcedureCall getDBProcedureCall() {
  DBProcedureCall dbProcedureCall = new DBProcedureCall();
  dbProcedureCall = (DBProcedureCall) initializeDBStatement(dbProcedureCall);
  return dbProcedureCall;
 }

 public static DataSource getDataSource() throws ServiceLocatorException {
  //write a logic to get DataSource
 }

 private static DBStatement initializeDBStatement(DBStatement dbStatement) {
  try {
   Connection conn = getDataSource().getConnection();
   dbStatement.setConnection(conn);
   dbStatement.setOptimizeForNoListeners(true);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return dbStatement;
 } 
}

DBConnectionSpec

If we want to establish connection without any of connection pooling mechanism, this class helps to do that.

private static DBStatement initializeDBStatement(DBStatement dbStatement) {
 try {
 DBConnectionSpec connectionSpec = new DBConnectionSpec();
 connectionSpec.setUsername(user);
 connectionSpec.setPassword(password);
 connectionSpec.setDriverName(driver);
 connectionSpec.setUrl(url);
 
 dbStatement.setConnectionSpec(connectionSpec);
 } catch (Exception e) {
    e.printStackTrace();
 }
}

DBSelectMetaData

DBSelectMetaData stores meta data of DBSelect which will be resulted out after execution

DBSelectMetaData dm= dbSelect.getMetaData();
for(int i=0;i<dm.getColumnCount();i++)
{
System.out.println(dm.getColumnName(i)+" Type:"+dm.getColumnType());
}

DBSelect and ResultSet

Create DBSelect statement and tag with DB connection using DBStatementFactory class and then set SQL command using setCommand(). Once all set to go then execute() has to be called to retrieve data from DB.

String SELECT_ALL_SHIRTS_CATALOGS = "SELECT SHIRTNAME, SHIRTDESC FROM catalogs";
public void displayAllShirtsCatalogs() throws Exception { 
  DBSelect dbSelect = null;
  try {
   DBSelect dbSelect = DBStatementFactory.getDBSelect();
   dbSelect.setCommand(SELECT_ALL_SHIRTS_CATALOGS);

   dbSelect.execute();

   if (dbSelect.onRow()) {
    int size = dbSelect.getRowCount();
    do {
  System.out.println(dbSelect.getColumn(1) 
  + " " + dbSelect.getColumn(2)); 
    } while (dbSelect.next());
   }

  } finally {
   dbSelect.close(DBStatement.LEVEL_CONNECTION);
  } 
 }