"Pagination" refers to the technique commonly used by web applications to display a section of a larger query result, and let the user jump backwards and forwards to particular sections. Google, for example, uses this technique to display search results.
This article assumes that your queries routinely return more row data than can fit into a single 1 MB response message sent from the Teradata Database to the Teradata JDBC Driver.
"Stateless" refers to the model in which the JDBC Connection is leased from the connection pool by the servlet only for the duration of processing the servlet request.
With the stateless model, the JDBC Connection is retrieved from the connection pool each time the servlet is invoked, is used only for the duration of composing the page, and is immediately returned to the pool.
The first time the result set display servlet is invoked, the servlet should execute a select count(*) query and save the row count as an HttpSession attribute.
select count(*) from DBC.ErrorMsgs
Then the servlet should use a PreparedStatement with the query structured to use the row_number function to select a particular set of rows. The servlet should store the current row number as an HttpSession attribute.
PreparedStatement ps = con.prepareStatement("select row_number() over (order by ErrorCode) as RowNum, ErrorCode, ErrorText from DBC.ErrorMsgs qualify RowNum between ? and ?");
ps.setInt(1, 1); // first 10 rows
ResultSet rs = ps.executeQuery();
The servlet can calculate the total number of pages from the count(*) query, and render the web page with "Page X of Y" verbiage.
As the user clicks Previous or Next links to page though the data, and invokes the servlet again, the servlet can refer to the current row number from a ServletRequest attribute, calculate the delta for another set of rows, prepare the same query, bind the parameter values, execute the query, and render the page.
Characteristics of the stateless model
This article is focused on paging with a JDBC ResultSet, directly. Some of the stateless model's drawbacks can be avoided with a more sophisticated scheme, such as materializing your query results into a volatile table. Take care when using volatile tables in a pooled connection; they must be cleaned up or the connection may run out of spool space.
"Stateful" refers to the model in which the JDBC Connection is leased from the connection pool by the servlet for a relatively long time, spanning the processing of multiple servlet requests.
The first time the result set display servlet is invoked, the servlet leases a JDBC Connection from the connection pool and saves it as an HttpSession attribute.
With the stateful model, the servlet avoids the need for a select count(*) query.
The servlet can use a Statement or a PreparedStatement to execute the actual query. The servlet should specify a Scrollable ResultSet (TYPE_SCROLL_INSENSITIVE), to enable jumping back and forward to specific rows in the ResultSet. Scrollable Result Sets have been supported since TTU 8.0 Teradata JDBC Driver + Teradata Database V2R5.1.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(10); // fetch 10 rows at a time
ResultSet rs = stmt.executeQuery("select ErrorCode, ErrorText from DBC.ErrorMsgs order by ErrorCode");
PreparedStatement ps = con.prepareStatement("select ErrorCode, ErrorText from DBC.ErrorMsgs order by ErrorCode", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps.executeQuery();
rs.setFetchSize(10); // fetch 10 rows at a time
The fetch size can be specified for a regular Statement before executing the query. With a PreparedStatement, we must set the fetch size using the ResultSet, after the query was executed. The application-specified fetch size is respected beginning with Teradata JDBC Driver 13.10.00.25 and Teradata Database 13.10. (Fetch size is ignored/unused in older versions.)
The servlet saves the PreparedStatement and the ResultSet as HttpSession attributes; meaning the Connection, the PreparedStatement, the ResultSet, and the total row count all must be saved as HttpSession attributes.
Because the stateful approach uses a Scrollable result set, the servlet can calculate the total number of pages by jumping to the last row of the result set and obtaining the row number.
int nRowCount = rs.getRow();
If the result set is not already positioned near the last row, this operation will require a round-trip to the Teradata Database; however, it is a much less expensive operation than executing a select count(*) query. The servlet saves the row count as an HttpSession attribute, and renders the web page with "Page X of Y" verbiage.
As the user clicks Previous or Next links to page though the data, and invokes the servlet again, the servlet can refer to the current row number from a ServletRequest attribute, calculate the delta for another set of rows, retrieve the ResultSet as an HttpSession attribute, use the ResultSet.absolute method to jump to the desired ResultSet row, fetch the row data, and render the page. The query is not executed again.
The Connection will remain leased from the pool, and the PreparedStatement and ResultSet will remain open, for as long as the user is paging through the row data. The web application must be designed to provide a mechanism to close the ResultSet, PreparedStatement, and Connection when the user is done viewing the row data. (Closing the Connection will return it to the pool.) This is the most challenging and complicated part of the stateful model.
Characteristics of the stateful model
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.