// ******************************************************************* // * 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()); } } }