Web Hosting Java, JSP, Tomcat 6, J2EE, Servlets, Struts, Jboss

JavaSercer Pages 17.3.1 The and Actions The and actions both need access to the action body to read the SQL statement. Hence, the corresponding tag handlers extend the BodyTagSupport class described in Chapter 16. They also implement an interface called com.ora.jsp.sql.ValueParent, which is used by the nested value actions to find the correct parent, following the pattern described for cooperating actions in Chapter 16. These two actions share the same set of attributes and have almost the same behavior, so a common superclass called com.ora.jsp.tags.sql.DBTag implements most of the tag handler functionality for both actions. Example 17.14 shows the top part of the DBTag class, with the class declaration and all property setter methods. Example 17.14. The DBTag Declaration and Properties package com.ora.jsp.tags.sql; import java.util.*; import java.sql.*; import javax.sql.*; import javax.servlet.jsp.*; import javax.servlet.jsp.tagext.*; import com.ora.jsp.sql.*; import com.ora.jsp.sql.value.*; public abstract class DBTag extends BodyTagSupport implements ValueTagParent { private SQLCommandBean sqlCommandBean = new SQLCommandBean( ); private String dataSourceName; private String id; private int scope = PageContext.PAGE_SCOPE; private String sqlValue; private Vector values; private boolean isExceptionThrown = false; private boolean isPartOfTransaction = false; public void setDataSource(String dataSourceName) { this.dataSourceName = dataSourceName; } public void setId(String id) { this.id = id; } public void setScope(String scopeName) { if (”page”.equals(scopeName)) { scope = PageContext.PAGE_SCOPE; } else if (”request”.equals(scopeName)) { scope = PageContext.REQUEST_SCOPE; } else if (”session”.equals(scopeName)) { scope = PageContext.SESSION_SCOPE; } else if (”application”.equals(scopeName)) { scope = PageContext.APPLICATION_SCOPE; } } An instance of the SQLCommandBean is kept as a private instance variable. The bean is used to perform all database operations; the tag handler just provides an easy-to-use interface to the bean for page authors. The setter methods for the dataSource and id properties set the corresponding instance variables. The setter method for the scope property converts the String value to the corresponding scope int value defined by the PageContext class before saving the value, since the int value is later needed in the doEndTag( ) method. The page author specifies the SQL statement to execute in the action element’s body. Because the tag handler implements the BodyTag interface (by extending the BodyTagSupport class), it can read the SQL statement in the doAfterBody( ) method as shown in Example 17.15. page 250

Hint: This post is supported by Gama web hosting php mysql provider

JavaSercer Pages The Column class is an abstract class, very similar to the Value class shown in Example 17.4. It contains access methods for all datatypes, with default implementations that throw an UnsupportedConversionException . Each subclass provides a real implementation of the access method corresponding to its type, plus the getString( ) method. Example 17.13 shows the IntColumn class. Example 17.13. The IntColumn Class package com.ora.jsp.sql.column; import com.ora.jsp.sql.Column; public class IntColumn extends Column { private int value; public IntColumn(String name, int value) { super(name); this.value = value; } public int getInt( ) { return value; } public String getString( ) { return String.valueOf(value); } } The constructor takes the column name and value as arguments. The name is used to initialize the Column superclass and is returned by its getName( ) method. 17.3 Developing Generic Database Custom Actions The database custom actions introduced in Chapter 9 can be used like this in a JSP page: UPDATE Account SET Balance = Balance - ? WHERE AccountNumber = ? UPDATE Account SET Balance = Balance + ? WHERE AccountNumber = ? The database custom actions use all of the classes described previously in this chapter. A DataSource available in the application scope is used to get a Connection, and an SQLCommandBean is used to execute the SQL statement specified in the database action element body. The nested value actions create Value subclass instances and add them to a list held by the parent action tag handler. The action saves the result as a Vector of Row objects in the scope specified by the page author. In this section, we first look at how the tag handlers for the , , and actions are implemented. All value actions follow the same pattern as , so they are not described here. At the end of this section, we also look at the tag handler for the action to see how it provides a transaction scope for the database actions nested in its body. page 249
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

JavaSercer Pages Example 17.12. The Row’s Column Value Access Methods public BigDecimal getBigDecimal(int columnIndex) throws NoSuchColumnException, UnsupportedConversionException { Column col = null; try { col = columns[columnIndex - 1]; } catch (ArrayIndexOutOfBoundsException e) { throw new NoSuchColumnException(String.valueOf(columnIndex)); } return col.getBigDecimal( ); } public BigDecimal getBigDecimal(String columnName) throws NoSuchColumnException, UnsupportedConversionException { return getBigDecimal(getIndex(columnName)); } public boolean getBoolean(int columnIndex) throws NoSuchColumnException, UnsupportedConversionException { Column col = null; try { col = columns[columnIndex - 1]; } catch (ArrayIndexOutOfBoundsException e) { throw new NoSuchColumnException(String.valueOf(columnIndex)); } return col.getBoolean( ); } public boolean getBoolean(String columnName) throws NoSuchColumnException, UnsupportedConversionException { return getBoolean(getIndex(columnName)); } … public String getString(int columnIndex) throws NoSuchColumnException { Column col = null; try { col = columns[columnIndex - 1]; } catch (ArrayIndexOutOfBoundsException e) { throw new NoSuchColumnException(String.valueOf(columnIndex)); } return col.getString( ); } public String getString(String columnName) throws NoSuchColumnException { return getString(getIndex(columnName)); } All these methods locate the Column subclass instance specified by the argument and call the corresponding method on the instance. Except for the getString( ) method, this call results in an UnsupportedConversionException if the column is not of the requested type. All types can be converted to a String, however, so a getString( ) call is successful provided that the requested column exists. page 248
Note: If you are looking for cheapest and affordable webspace to host and run your servlet application check Astra j2ee hosting services

JavaSercer Pages 17.2.2 The Row and Column Classes Let’s now look at the Row and Column classes. Example 17.10 shows a part of the Row class constructor. Example 17.10. The Row Class Constructor package com.ora.jsp.sql; import java.util.*; import java.sql.*; import java.sql.Date; import java.math.*; import com.ora.jsp.sql.column.*; public class Row { private Column[] columns; public Row(ResultSet rs) throws SQLException, UnsupportedTypeException { ResultSetMetaData rsmd = rs.getMetaData( ); int cols = rsmd.getColumnCount( ); columns = new Column[cols]; // Note! Columns are numbered from 1 in the ResultSet for (int i = 1; i <= cols; i++) { int type = rsmd.getColumnType(i); switch (type) { case Types.DATE: columns[i - 1] = new DateColumn(rsmd.getColumnName(i), rs.getDate(i)); break; case Types.TIME: columns[i - 1] = new TimeColumn(rsmd.getColumnName(i), rs.getTime(i)); break; ... default: throw new UnsupportedTypeException("Unsupported SQL " + "data type: " + type); } } } The Row class keeps all column values as an array of Column objects. The constructor is called with a ResultSet that has been positioned at a new row by the caller using the next( ) method. It loops through all columns in the row and creates a Column subclass instance for each. The column's datatype, retrieved from the ResultSetMetaData object, is used to decide which Column subclass to create. Similarly to the Value class structure, the Column class structure contains subclasses corresponding to JDBC column datatypes, as shown in Figure 17.3. Two methods provide access to the number of columns and the array of Column objects, shown in Example 17.11. Example 17.11. The Row's getColumnCount( ) and getColumns( ) Methods public int getColumnCount( ) { return columns.length; } public Column[] getColumns( ) { return columns; } Another set of methods can be used to retrieve the value of an individual column, given its name or index. This set of methods contains one pair per supported datatype. Example 17.12 shows the methods for the BigDecimal, boolean, and String types. page 247
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

JavaSercer Pages The executeUpdate( ) method, shown in Example 17.9, is very similar to the executeQuery( ) method. Example 17.9. The SQLCommandBean’s executeUpdate( ) Method public int executeUpdate( ) throws SQLException, UnsupportedTypeException { int noOfRows = 0; ResultSet rs = null; PreparedStatement pstmt = null; Statement stmt = null; try { if (values != null && values.size( ) > 0) { // Use a PreparedStatement and set all values pstmt = conn.prepareStatement(sqlValue); setValues(pstmt, values); noOfRows = pstmt.executeUpdate( ); } else { // Use a regular Statement stmt = conn.createStatement( ); noOfRows = stmt.executeUpdate(sqlValue); } } finally { try { if (rs != null) { rs.close( ); } if (stmt != null) { stmt.close( ); } if (pstmt != null) { pstmt.close( ); } } catch (SQLException e) { // Ignore. Probably caused by a previous // SQLException thrown by the outer try block. } } return noOfRows; } The main difference is that the executeUpdate( ) method is used to execute SQL statements that do not return rows, only the number of rows affected by the statement. Examples of such statements are UPDATE, INSERT, and DELETE. In the same way as the executeQuery( ) method, a PreparedStatement is created and initialized with the values defined by the values property, if set. Otherwise a regular Statement is used. The statement is executed and the number of affected rows is returned to the caller. page 246

Hint: If you are looking for very good and affordable webspace to host and run your j2ee hosting application check Sandzak.com j2ee web hosting services

JavaSercer Pages The code for creating the PreparedStatement or Statement object and executing the statement is enclosed in a try/finally block. This is important, because if something fails (due to an invalid SQL statement, for instance), the JDBC methods throw an SQLException . You want the exception to be handled by the application using the SQLCommandBean, but first you must make sure that all JDBC resources are released and the Connection object is returned to the pool. Using a try block with a finally clause but no catch clause gives this behavior. If an exception is thrown, the finally clause is executed, and then the exception is automatically thrown to the object that called the executeQuery( ) method. In the finally clause, the ResultSet object and either the PreparedStatement or Statement object are closed. It should be enough to close the statement object according to the JDBC specification (closing the statement should also close the ResultSet associated with the statement), but doing it explicitly doesn’t hurt and makes the code work even with a buggy JDBC driver. Example 17.7 shows a part of the setValues( ) method. Example 17.7. The SQLCommandBean’s setValues( ) Method private void setValues(PreparedStatement pstmt, Vector values) throws SQLException { for (int i = 0; i < values.size( ); i++) { try { Value v = (Value) values.elementAt(i); // Set the value using the method corresponding to // the type. // Note! Set methods are indexed from 1, so we add // 1 to i if (v instanceof BigDecimalValue) { pstmt.setBigDecimal(i + 1, v.getBigDecimal( )); } else if (v instanceof BooleanValue) { pstmt.setBoolean(i + 1, v.getBoolean( )); } ... } catch (UnsupportedConversionException e) { // Can not happen here since we test the type first } } } The setValue( ) method loops through all elements in the Vector with values. For each element, it tests which Value subclass it is and uses the corresponding JDBC method to set the value for the PreparedStatement object. You may wonder why a PreparedStatement is used here, since it's used only once. It's true that a PreparedStatement is intended to be reused over and over again to execute the same SQL statement with new values. But it offers a convenient solution to the problem of different syntax for values of type date/time and numbers when represented by a string literal. When a PreparedStatement is used, the variable values in the SQL statement can be represented by Java variables of the appropriate types without worrying about what literal representation a certain JDBC driver supports. So even though it's used only once, a PreparedStatement still has an advantage over a regular Statement. The toVector( ) method is shown in Example 17.8. Example 17.8. The SQLCommandBean's toVector( ) Method private Vector toVector(ResultSet rs) throws SQLException, UnsupportedTypeException { Vector rows = new Vector( ); while (rs.next( )) { Row row = new Row(rs); rows.addElement(row); } return rows; } This method simply walks through the ResultSet and adds a new Row object for each row to a Vector that it then returns. As you will see later, the Row constructor reads all column values and creates a Column object for each. page 245
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check professional tomcat hosting services

JavaSercer Pages public int getInt( ) { return value; } public String getString( ) { return String.valueOf(value); } } An application that uses the SQLCommandBean can create Value objects and set the bean’s properties like this: SQLCommandBean sqlBean = new SQLCommandBean( ); sqlBean.setConnection(ds.getConnection( )); String sqlValue = “SELECT * FROM MyTable WHERE IntCol = ? AND TextCol = ?”; sqlBean.setSqlValue(sqlValue); Vector values = new Vector( ); values.addElement(new IntValue(10)); values.addElement(new StringValue(”Hello!”)); sqlBean.setValues(values); One of two methods in the SQLCommandBean is used to execute the SQL statement: the executeQuery( ) method for a SELECT statement, and the executeUpdate( ) method for all other types of statements. Example 17.6 shows the executeQuery( ) method. Example 17.6. The SQLCommandBean’s executeQuery( ) Method public Vector executeQuery( ) throws SQLException, UnsupportedTypeException { Vector rows = null; ResultSet rs = null; PreparedStatement pstmt = null; Statement stmt = null; try { if (values != null && values.size( ) > 0) { // Use a PreparedStatement and set all values pstmt = conn.prepareStatement(sqlValue); setValues(pstmt, values); rs = pstmt.executeQuery( ); } else { // Use a regular Statement stmt = conn.createStatement( ); rs = stmt.executeQuery(sqlValue); } // Save the result in a Vector of Row object rows = toVector(rs); } finally { try { if (rs != null) { rs.close( ); } if (stmt != null) { stmt.close( ); } if (pstmt != null) { pstmt.close( ); } } catch (SQLException e) { // Ignore. Probably caused by a previous // SQLException thrown by the outer try block } } return rows; } If the values property is set, a JDBC PreparedStatement is needed to associate the values with the question mark placeholders in the SQL statement. A method named setValues( ) takes care of setting all values, using the appropriate JDBC method for the datatype represented by each Value object. If the values property is not set, a regular JDBC Statement is created instead. In both cases, the JDBC driver is asked to execute the statement, and the resulting ResultSet is turned into a Vector with Row objects by the toVector( ) method. The Vector is then returned to the caller. You may wonder why the ResultSet is not returned directly instead of creating a Vector with Row objects. The reason is that a ResultSet is tied to the Connection that was used to generate it. When the Connection is closed or used to execute a new SQL statement, all open ResultSet objects for the Connection are released. You must therefore make sure to save the information from the ResultSet in a new data structure before reusing the Connection or returning it to the pool. page 244

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Sandzak.com tomcat web hosting provider

JavaSercer Pages 17.2.1 The SQLCommandBean and Value Classes The SQLCommandBean has three write-only properties. Example 17.3 shows the beginning of the class file with the setter methods. Example 17.3. SQLCommandBean Property Setter Methods package com.ora.jsp.sql; import java.util.*; import java.sql.*; import com.ora.jsp.sql.value.*; public class SQLCommandBean { private Connection conn; private String sqlValue; private Vector values; private boolean isExceptionThrown = false; public void setConnection(Connection conn) { this.conn = conn; } public void setSqlValue(String sqlValue) { this.sqlValue = sqlValue; } public void setValues(Vector values) { this.values = values; } … The connection property holds the Connection to use, and the sqlValue property is set to the SQL statement to execute, with question marks as placeholders for variable values, if any. The placeholders are then replaced with the values defined by the values property, a Vector with one com.ora.jsp.sql.Value object per placeholder. Before we look at the other SQLCommandBean methods, let’s look at the Value class. The Value class is an abstract class used as a superclass for classes representing specific Java types, as shown in Figure 17.3. It contains default implementations of methods for getting the specific type of value a subclass represents. Example 17.4 shows two of the methods. Example 17.4. Two Value Class Methods public abstract class Value { public BigDecimal getBigDecimal( ) throws UnsupportedConversionException { throw new UnsupportedConversionException( “No conversion to BigDecimal”); } public boolean getBoolean( ) throws UnsupportedConversionException { throw new UnsupportedConversionException( “No conversion to boolean”); } … The default implementation for each method simply throws a com.ora.jsp.sql.UnsupportedConversionException . Each subclass implements the method that returns the value of the type it represents, as well as the getString( ) method. The getString( ) method returns the value converted to a String. Example 17.5 shows the com.ora.jsp.sql.value.IntValue subclass. Example 17.5. The IntValue Class package com.ora.jsp.sql.value; import com.ora.jsp.sql.Value; public class IntValue extends Value { private int value; public IntValue(int value) { this.value = value; } page 243

Hint: If you are looking for very good and affordable webspace to host and run your java hosting application check Sandzak.com java web hosting provider

JavaSercer Pages The initialization servlet should also clean up when the application is shut down. The web container calls the destroy( ) method: public void destroy( ) { getServletContext( ).removeAttribute(”exampleDS”); } Most connection pools used in production provide a method that should be called at shutdown to let it close all connections. If you use such a pool, you need to call this method in the servlet’s destroy( ) method as well. The example pool used here doesn’t provide a shutdown method. 17.2 Using a Generic Database Bean All the database custom action tag handler classes described later in this chapter are based on a generic database bean named com.ora.jsp.sql.SQLCommandBean . This bean uses a number of other classes. Figure 17.3 shows the relationship between all these classes. Figure 17.3. The SQLCommandBean and related classes The SQLCommandBean takes care of setting all values in a JDBC java.sql.PreparedStatement and executing the statement. For SELECT statements, it also processes the result by creating com.ora.jsp.sql.Row objects containing a com.ora.jsp.sql.Column object for each column in the result. The rows returned by the SELECT statement are returned to the caller as a java.util.Vector with Row objects. The EmployeeRegistryBean described in Chapter 15 is one example of how to use this bean, and other examples follow in this chapter. Let’s look at each class in detail, starting with the SQLCommandBean itself. page 242
Quick Hint: If you are looking for cheap and reliable provider to host and run your servlet application check Vision servlet hosting plans

JavaSercer Pages The servlet class, defined by the element, is given a name through the element. A number of elements, with nested
and
elements, are used to define the following initialization parameters: jdbcDriverClassName, jdbcURL, dbUserName, and dbUserPassword. If you use a JDBC 2.0 connection pool, you need to define the URL used to get a reference from JNDI to it instead of all these parameters. The last servlet element, , tells the container that this servlet should be initialized when the web application is started. The container initializes servlets in the relative order specified by this element, from the lowest number to the highest. If two servlets have the same value, their relative start order is undefined. The servlet reads all the initialization parameters in its init( ) method, creates a DataSourceWrapper instance, and sets it as a ServletContext attribute named exampleDS: public void init( ) throws ServletException { ServletConfig config = getServletConfig( ); String jdbcDriverClassName = config.getInitParameter(”jdbcDriverClassName”); String jdbcURL = config.getInitParameter(”jdbcURL”); String dbUserName = config.getInitParameter(”dbUserName”); String dbUserPassword = config.getInitParameter(”dbUserPassword”); // Make sure a driver class and JDBC URL is specified if (jdbcDriverClassName == null || jdbcURL == null) { throw new UnavailableException(”Init params missing”); } DataSource ds = null; try { ds = new DataSourceWrapper(jdbcDriverClassName, jdbcURL, dbUserName, dbUserPassword); } catch (Exception e) { throw new UnavailableException(”Cannot create connection pool” + “: ” + e.getMessage( )); } getServletContext( ).setAttribute(”exampleDS”, ds); } All servlets and JSP pages in the application can now obtain a reference to the DataSource. Servlets use the ServletContext getAttribute( ) method to accomplish this. For JSP pages, the DataSource appears as an application scope object. All the database custom actions introduced in Chapter 9 look for a DataSource in the application scope, so all you have to do to use the one created by the initialization servlet is to provide the name: <%@ page language="java" contentType="text/html" %> <%@ taglib uri="/orataglib" prefix="ora" %> SELECT * FROM Employee WHERE FirstName LIKE ? AND LastName LIKE ? AND Dept LIKE ? ORDER BY LastName Note how the dataSource attribute value matches the name of the ServletContext attribute holding the reference to the DataSource, set by the initialization servlet. It’s much better to let an initialization servlet create the DataSource, as described here, than to use the custom action described in Chapter 9. With a servlet, all information about the JDBC driver class, URL, user and password is in one place (the WEB-INF/web.xml file), as opposed to being repeated in every JSP page that uses the database custom actions. This makes it easier to change the information when needed. Also, if you decide at some point to use another connection pool implementation, such as a true JDBC 2.0 connection pool available from your JDBC driver or database vendor, you can easily change the servlet’s init( ) method. So even for a pure JSP application, I recommend that you use an application initialization servlet like the one described here. page 241
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check professional tomcat hosting services