JDBC 4.0 Version
The OC4J embedded in JDeveloper 10g does not support JDBC 4.0. When support gets added for JDBC 4.0, JDBC 4.0's features may be added to the JSP web application that we have developed in the previous section. Provision to set client info properties on the Connection object is a new feature in JDBC 4.0. Client info properties may be set using the setClientInfoProperty() method of the Connection object. Set client info properties ApplicationName, ClientUser, and ClientHostname as follows:
connection.setClientInfo("ApplicationName","DataDirectApp");
connection.setClientInfo("ClientUser","DataDirect");
connection.setClientInfo("ClientHostname","DataDirectHost");
If the database supports statement caching, we can set statement pooling to true. To find out whether the database supports statement pooling create a DatabaseMetaData object. Using the supportsStatementPooling() method test if the database supports statement pooling. If the database supports statement pooling check if the Statement is poolable using the isPoolable() method. If the Statement object is poolable, set the Statement object to poolable using the setPoolable() method:
DatabaseMetaData metaData=connection.getMetaData();
if(metaData.supportsStatementPooling())
{
if(stmt.isPoolable())
stmt.setPoolable(true);
}
We may use support for the wrapper pattern with the Wrapper interface, which is extended by the Statement interface. Oracle's extensions to the JDBC API provide an OracleStatement interface that extends the Statement interface. Using the wrapper pattern create an object of type OracleStatement type. First we need to check if the Statement object is a wrapper for the oracle.jdbc.OracleStatement using the isWrapperFor() method. Subsequently create an object of type OracleStatement type using the unwrap() method:
OracleStatement oracleStmt=null;
Class class = Class.forName("oracle.jdbc.OracleStatement");
if(stmt.isWrapperFor(class))
{
oracleStmt = (OracleStatement)stmt.unwrap(class);
}
The OracleStatement object may be used to set column types for different columns to be fetched from the database, using the defineColumnType() method. Also, the number of rows to be prefetched may be set, using the setRowPrefetch() method.
If the database supports ROWID of SQL type, we may add a column for the ROWID value of a row in the result set. A ROWID column value may be retrieved as a java.sql.RowId object using the getRowId() method. Oracle database 10g supports the ROWID data type. Therefore, modify the SELECT query to add a column for the ROWID pseudocolumn.
ResultSet resultSet= oracleStmt.executeQuery("Select ROWID, CATALOGID, JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR from Catalog");
We also have to add a column of type ROWID to the HTML table created from the result set. A ROWID column value is retrieved from a ResultSet object using the getRowId() method. The RowId object may be converted to a String value using the toString() method:
<%out.println(resultSet.getRowId("ROWID").toString());%>
Enhanced support for chained exceptions in the SQLException interface may be used in the JSP web application. We need to specify an errorPage in the input.jsp for error handling:
<%@ page errorPage="errorpage.jsp" %>
In the errorpage.jsp, the enhanced for-each loop is used to retrieve the chained exceptions and chained causes.
<%@ page isErrorPage="true" %>
<%
for(Throwable e : exception )
{
out.println("Error encountered: " + e);
}
%>
The JDBC 4.0 version of the input.jsp, which may run in JDeveloper when supports get added for JDBC 4.0 in the OC4J server, is listed below:
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page language="java" import="java.sql.*, javax.naming.*, javax.sql.*,oracle.jdbc.*" %>
<%@ page errorPage="errorpage.jsp" %>
<%
InitialContext initialContext = new InitialContext();
DataSource ds = (DataSource)
initialContext.lookup("java:comp/env/jdbc/OracleDS");
java.sql.Connection connection = ds.getConnection();
connection.setClientInfo("ApplicationName","DataDirectApp");
connection.setClientInfo("ClientUser","DataDirect");
connection.setClientInfo("ClientHostname","DataDirectHost");
Statement stmt=connection.createStatement();
DatabaseMetaData metaData=connection.getMetaData();
if(metaData.supportsStatementPooling())
{
if(stmt.isPoolable())
stmt.setPoolable(true);
}
OracleStatement oracleStmt=null;
Class class = Class.forName("oracle.jdbc.OracleStatement");
if(stmt.isWrapperFor(class))
{
oracleStmt = (OracleStatement)stmt.unwrap(class);
oracleStmt.defineColumnType(1, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(2, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(3, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(4, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(5, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(6, OracleTypes.VARCHAR);
oracleStmt.defineColumnType(7, OracleTypes.VARCHAR);
oracleStmt.setRowPrefetch(2);
}
ResultSet resultSet=oracleStmt.executeQuery("Select ROWID, CATALOGID, JOURNAL, PUBLISHER, EDITION, TITLE, AUTHOR from Catalog");
%>
<table border="1" cellspacing="0">
<tr>
<th>Row Id</th>
<th>Catalog Id</th>
<th>Journal</th>
<th>Publisher</th>
<th>Edition</th>
<th>Title</th>
<th>Author</th>
</tr>
<%
while (resultSet.next())
{
%>
<tr>
<td><%out.println(resultSet.getRowId("ROWID").toString());%></td>
<td><%out.println(resultSet.getString(1));%></td>
<td><%out.println(resultSet.getString(2));%></td>
<td><%out.println(resultSet.getString(3));%></td>
<td><%out.println(resultSet.getString(4));%></td>
<td><%out.println(resultSet.getString(5));%></td>
<td><%out.println(resultSet.getString(6));%></td>
</tr>
<%
}
%>
</table>
<%
resultSet.close();
oracleStmt.close();
if(!connection.isClosed())
connection.close();
%>