InfoCenter Home >
4: Developing applications >
4.2: Building Web applications >
4.2.4: Accessing data >
4.2.4.2: Obtaining and using database connections >
4.2.4.2.3: Accessing relational databases with the IBM data access beans >
4.2.4.2.3.1: Example: Servlet using data access beans
4.2.4.2.3.1: Example: Servlet using data access beans
The sample servlet uses the data access beans and is based on the sample
servlet discussed in Article 4.2.4.2.1.1. The
connection pooling sample servlet uses classes such as Connection, Statement,
and ResultSet from the java.sql package to interact with the database. In contrast,
this sample servlet uses the data access beans, instead of the classes in
the java.sql package, to interact with the database. For convenience, call this
sample servlet the DA (for data access beans) and call the sample servlet
on which it is based the CP (for connection pooling).
The CP and DA sample servlets benefit from the performance and resource management
enhancements made possible by connection pooling. The programmer coding the
DA sample servlet benefits from the additional features and functions provided
by the data access beans.
The DA sample servlet differs slightly from the CP sample servlet. This discussion
covers only the changes. See Article 4.2.4.2.1.1
for the discussion of the CP sample servlet. The DA sample servlet
shows the basics of connection pooling and the data access beans, but keeps
other code to a minimum. Therefore, the servlet is not entirely realistic. You
are expected to be familiar with basic servlet and JDBC coding.
This section describes how the DA sample servlet differs from the CP sample
servlet. To view the coding in one or both of the samples while you read this
section, click these links:
Steps 1 through 6 of the CP sample servlet are mostly unchanged in the DA sample
servlet. The main changes to the DA sample servlet are:
- New package
The com.ibm.db package (containing the data access beans classes) must
be imported. The classes are in the databeans.jar file, found in the lib
directory under the Application Server root install directory. You will
need this jar file in your CLASSPATH in order to compile a servlet using
the data access beans.
- The metaData variable
This variable is declared in the Variables section at the start of the
code, outside of all methods. This allows a single instance to be used by
all incoming user requests. The full specification of the variable is completed
in the init() method.
- The init() method
New code has been appended to the init() method to do a one-time initialization
on the metaData object when the servlet is first loaded. The new code begins
by creating the base query object sqlQuery as a String object. Note the
two "?" parameter placeholders. The sqlQuery object specifies
the base query within the metaData object. Finally, the metaData object
is provided higher levels of data (metadata), in addition to the base query,
that will help with running the query and working with the results. The
code sample shows:
- The addParameter() method notes that when running the query, the parameter
idParm is supplied as a Java Integer datatype, for the convenience
of the servlet, but that idParm should be converted (through the metaData
object) to do a query on the SMALLINT relational datatype of the underlying
relational data when running the query.
A similar use of the addParameter() method for the deptParm parameter
notes that for the same underlying SMALLINT relational datatype, the
second parameter will exist as a different Java datatype within the
servlet - as a String rather than as an Integer. Thus parameters can
be Java datatypes convenient for the Java application and can automatically
be converted by the metaData object to be consistent with the required
relational datatype when the query is run.
Note that the "?" parameter placeholders in the sqlQuery
object and the addParameter() methods are related. The first addParameter()
attaches idParm to the first "?", and so on. Later, a setParameter()
will use idParm as an argument to replace the first "?" in
the sqlQuery object with an actual value.
- The addColumn() method performs a function somewhat similar to the
addParameter() method. For each column of data to be retrieved from
the relational table, the addColumn() method maps a relational datatype
to the Java datatype most convenient for use within the Java application.
The mapping is used when reading data out of the result cache and when
making changes to the cache (and then to the underlying relational table).
- The addTable() method explicitly specifies the underlying relational
table. This information is needed if changes to the result cache are to
be propagated to the underlying relational table.
- Step 5
Step 5 has been rewritten to use the data access beans to do the SQL
query instead of the classes in the java.sql package. The query is run using
the selectStatement object, which is a SelectStatement data access bean.
Step 5 is part of the process of responding to the user request. When steps
1 through 4 have run, the conn Connection object from the connection pool
is available for use. The code shows:
- The dataAccessConn object (a DatabaseConnection bean) is created
to establish the link between the data access beans and the database
connection - the conn object.
- The selectStatement object (a SelectStatement bean) is created,
pointing to the database connection through the dataAccessConn object,
and pointing to the query through the metaData object.
- The query is "completed" by specifying the parameters using the setParameter()
method. The "?" placeholders in the sqlQuery string are replaced
with the parameter values specified.
- The query is executed using the execute() method.
- The result object (a SelectResult bean) is a cache containing the
results of the query, created using the getResult() method.
- The data access beans offer a rich set of features for working with
the result cache - at this point the code shows how the first row of the
result cache (and the underlying relational table) can be updated using
standard Java coding, without the need for SQL syntax.
- The close() method on the result cache breaks the link between the result
cache and the underlying relational table, but the data in the result
cache is still available for local access within the servlet. After the
close(), the database connection is unnecessary. Step 6 (which is unchanged
from the CP sample servlet) closes the database connection (in reality,
the connection remains open but is returned to the connection pool for
use by another servlet request).
- Step 7
Step 7 has been entirely rewritten (with respect to the CP sample servlet)
to use the query result cache retrieved in Step 5 to prepare a response
to the user. The query result cache is a SelectResult data access bean.
Although the result cache is no longer linked to the underlying relational
table, the cache can still be accessed for local processing. In this step,
the response is prepared and sent back to the user. The code shows the following:
- The nextRow() and previousRow() methods are used to navigate through
the result cache. Additional navigation methods are available.
- The getColumnValue() method is used to retrieve data from the result
cache. Because of properties set earlier in creating the metaData object,
the data can be easily cast to formats convenient for the needs of the
servlet.
A possible simplification
If you do not need to update
the relational table, you can simplify the sample servlet:
- At the end of the init() method, you can drop the lines with the addColumn()
and addTable() methods, since the metaData object does not need to know as
much if there are no relational table updates.
- You will also need to drop the lines with the setColumnValue() and updateRow()
methods at the end of step 5, because you are no longer updating the relational
table.
- Finally, you can remove most of the type casts associated with the getColumnValue()
methods in step 7. You will, however, need to change the type cast to (Short)
for the "ID" and "DEPT" use of the getColumnValue() method.
|
|