IBMDataAccessTest.java sample

// *******************************************************************
// * IBMDataAccessTest.java - test the data access JavaBeans         *
// *******************************************************************
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.math.BigDecimal;
// ********
// STEP 1 *
// ********
// Import JDBC packages and IBM implemented extensions, plus naming 
// service packages, plus DataAccess JavaBeans packages.
import java.sql.*;
import javax.sql.*;
import com.ibm.ejs.dbm.jdbcext.*;
import javax.naming.*; 
import com.ibm.db.*;

public class IBMDataAccessTest extends HttpServlet 
{
   // **************************************************************** 
   // * Variables                                                    *
   // ****************************************************************
   private static DataSource ds   = null;
   private static String user     = null; 
   private static String password = null;
   private static String owner    = null;
   private static String source   = null;
   private static String title    = "Data Access JavaBean Test";

   // Name of property file used to complete the user, password,
   // and table owner information at runtime, plus other information.
   // The ".properties" extension is assumed.
   private static final String CONFIG_BUNDLE_NAME = "DataAccessTestStrings";

   // Single metaData object, used by all user requests, will be
   // fully defined in the init() method when the servlet is loaded.
   private static StatementMetaData metaData = null;

   // ****************************************************************
   // * Initialize servlet when it is first loaded                   *
   // ****************************************************************
   public void init(ServletConfig config)
   throws ServletException
   {
      super.init(config);
      try
      {
         // Get information at runtime (from an external property file
         // identified by CONFIG_BUNDLE_NAME) about the user, password,  
         // and owner information.  This information could be provided 
         // in other ways - perhaps in a somewhat more secure compiled
         // resource bundle, or hardcoded within this application.
         PropertyResourceBundle configBundle = 
         (PropertyResourceBundle)PropertyResourceBundle.
         getBundle(CONFIG_BUNDLE_NAME);
         user     = configBundle.getString("accessServlet.user");
         password = configBundle.getString("accessServlet.password");
         owner    = configBundle.getString("accessServlet.owner");

         //If user and password are empty, set to null so that
         //the implicit database login will be used
         if (user != null && (user.equals("null") || user.equals(""))) user = null;
         if (password != null && (password.equals("null") || password.equals(""))) password = null;

         // Get context and logical name information to use below in a
         // naming service lookup to get a DataSource object.
         source   = configBundle.getString("accessServlet.source");

         // Get string to be used in the HTML response.  Translate the 
         // string in the property file to another national language
         // as needed.
         title    = configBundle.getString("accessServlet.title");
      }
      catch (Exception e)
      {
         System.out.println("Properties file exception: " + e.getMessage());
      }

      try
      {
         // **********
         // * STEP 2 *
         // **********
         // Create the initial naming context. 
         Hashtable parms = new Hashtable();
         parms.put(Context.INITIAL_CONTEXT_FACTORY, 
                 "com.ibm.ejs.ns.jndi.CNInitialContextFactory");
         Context ctx = new InitialContext(parms);

         // **********
         // * STEP 3 *
         // **********
         // Perform a naming service lookup to get a DataSource object.
         // The single DataSource object is a "factory" used by all
         // requests to get an individual connection for each request.
         // The Web administrator can provide the lookup parameters.
         // The code below uses a value read in from an external property
         // file.  The text string source is typically something like
         // "jdbc/sample" where "jdbc" is the context for the
         // lookup and "sample" is the logical name of the DataSource
         // object to be retrieved.
         ds = (DataSource)ctx.lookup(source);
      }
      catch (Exception e)
      {
         System.out.println("Naming service exception: " + e.getMessage());
      }

      // Add data access JavaBeans code.
      // Query string, with :idParm and :deptParm parameters.
      String sqlQuery = "SELECT ID, NAME, DEPT, COMM " + 
                        "FROM " + owner.toUpperCase() + ".STAFF " + 
                        "WHERE ID >= ? " + 
                        "AND DEPT =  ? " + 
                        "ORDER BY ID ASC";
      // Start defining the metaData object based on the query string.
      metaData = new StatementMetaData();
      metaData.setSQL(sqlQuery);
      try
      {
         // Add some more information to the metaData to make Java
         // programming more convenient.  The addParameter() method allows
         // us to supply an input parameter for the query using a  
         // convenient Java datatype, doing a conversion to the datatype
         // actually needed by SQL.  The addColumn() method makes things 
         // convenient in the other direction, retrieving data in a 
         // datatype convenient for Java programming, doing a conversion
         // from the underlying SQL datatype.  The addTable() method
         // identifies the relational table and makes it possible for
         // result cache changes to be folded back onto the table.
         metaData.addParameter("idParm",   Integer.class,    Types.SMALLINT);
         metaData.addParameter("deptParm", String.class,     Types.SMALLINT);
         metaData.addColumn("ID",          String.class,     Types.SMALLINT);
         metaData.addColumn("NAME",        String.class,     Types.VARCHAR);
         metaData.addColumn("DEPT",        Integer.class,    Types.SMALLINT);
         metaData.addColumn("COMM",        BigDecimal.class, Types.DECIMAL);
         metaData.addTable(owner.toUpperCase() + ".STAFF");
      }
      catch (DataException e)
      {
         System.out.println("Set metadata exception: " + e.getMessage());
      }
   }

   // ****************************************************************
   // * Respond to user GET request                                  *
   // ****************************************************************
   public void doGet(HttpServletRequest req, HttpServletResponse res)
   {
      Connection conn  = null;
      SelectResult result  = null;
      try
      {
         // **********
         // * STEP 4 *
         // **********
         // Get a Connection object conn using the DataSource factory.
         conn = ds.getConnection(user, password);

         // **********
         // * STEP 5 *
         // **********
         // Make use of the externally managed connection conn gotten
         // through the DataSource object.  Our dataAccessConn object 
         // should be local (a new object for each request), since there
         // may be multiple concurrent requests.
         DatabaseConnection dataAccessConn = 
         new DatabaseConnection(conn);
         // Begin building our SQL select statement - it also needs to be
         // local because of concurrent user requests.
         SelectStatement selectStatement = new SelectStatement();
         selectStatement.setConnection(dataAccessConn);
         // Attach a metadata object (which includes the actual SQL
         // select in the variable sqlQuery) to our select statement.
         selectStatement.setMetaData(metaData);
         // Make use of the facilities provided through the metadata
         // object to set the values of our parameters, and then execute
         // the query.  Values for dept and id are usually not hardcoded,
         // but are provided through the user request.
         String  wantThisDept  = "42";
         Integer wantThisId    = new Integer(100);
         selectStatement.setParameter("deptParm", wantThisDept);
         selectStatement.setParameter("idParm",   wantThisId);
         selectStatement.execute();
         // The result object is our cache of results.
         result = selectStatement.getResult();
         // Try an update on the first result row.  Add 12.34 to the 
         // existing commission, checking first for a null commission.
         BigDecimal comm = (BigDecimal)result.getColumnValue("COMM");
         if (comm == null)
         {
            comm = new BigDecimal("0.00");
         }
         comm = comm.add(new BigDecimal("12.34"));
         result.setColumnValue("COMM", comm);
         result.updateRow();
         // Close the result object - no more links to the relational
         // data, but we can still access the result cache for local
         // operations, as shown in STEP 7 below.
         result.close();
         dataAccessConn.disconnect();       // release connection
      }
      catch (Exception e)
      {
         System.out.println("get connection, process statement: " + 
                            e.getMessage());
         try
         {
            if (result != null)
            {
               result.close();              // Handles cleanup when updateRow failed
            }
         }
         catch (Exception resultClose)
         {
            System.out.println("close result set: " + resultClose.getMessage());
         }
      }

      // **********
      // * STEP 6 *
      // **********
      // Close the connection, which does not close and actual connection, 
      // but releases it back to the pool.
      finally
      {
         if (conn != null)
         {
            try
            {
               conn.close();
            }
            catch (Exception e)
            {
               System.out.println("Close connection exception: " + e.getMessage());
            }
         }
      }
      // **********
      // * STEP 7 *
      // **********
      // Prepare and return HTML response
      res.setContentType("text/html");
      // Next three lines prevent dynamic content from being cached
      // on browsers.
      res.setHeader("Pragma", "no-cache");
      res.setHeader("Cache-Control", "no-cache");
      res.setDateHeader("Expires", 0);
      try
      {
         ServletOutputStream out = res.getOutputStream();
         out.println("<HTML>");
         out.println("<HEAD><TITLE>" + title + "</TITLE><HEAD>");
         out.println("<BODY>");
         out.println("<TABLE BORDER>");
         // Note the use of the result cache below.  We can jump to 
         // different rows.  We also take advantage of metadata 
         // information to retrieve data in the Java datatypes that 
         // we want.
         result.nextRow();
         out.println("<TR>");
         out.println("<TD>" + (String)result.getColumnValue("ID") + "</TD>"); 
         out.println("<TD>" + (String)result.getColumnValue("NAME") + "</TD>");
         out.println("<TD>" + (Integer)result.getColumnValue("DEPT") + "</TD>");
         out.println("<TD>" + (BigDecimal)result.getColumnValue("COMM") + "</TD>");
         out.println("</TR>");
         result.previousRow();
         out.println("<TR>");
         out.println("<TD>" + (String)result.getColumnValue("ID") + "</TD>"); 
         out.println("<TD>" + (String)result.getColumnValue("NAME") + "</TD>");
         out.println("<TD>" + (Integer)result.getColumnValue("DEPT") + "</TD>");
         out.println("<TD>" + (BigDecimal)result.getColumnValue("COMM") + "</TD>");
         out.println("</TR>");
         out.println("</TABLE>");
         out.println("</BODY></HTML>");
         out.close();
      }
      catch (Exception e)
      {
         System.out.println("HTML response: " + e.getMessage());
      }
   }
}