com.boylesoftware.cb2.db.tsql
Class NoCursorFetchExecutor

java.lang.Object
  com.boylesoftware.cb2.GenericFetchExecutor
      com.boylesoftware.cb2.db.tsql.NoCursorFetchExecutor

All Implemented Interfaces:

DAOPlugin, FetchExecutor

public class NoCursorFetchExecutor

extends GenericFetchExecutor

Fetch executor implementation for Sybase and MS SQL Server that does not use scrollable result sets for paginated fetches.

Some JDBC driver implementations have issues with using scrollable result sets, for example Sybase's jConnect reads the whole result set into the client's memory when the ResultSet.last() method, used by the GenericFetchExecutor to determine the total rows count, is executed, which slows down the performance and may cause out of memory errors. This fetch executor implementation avoids using scrollable result sets by performing a SELECT INTO based on the original request and copying data into a temporary table first, and then selecting the requested page from that temporary table.

Please note, that since this executor uses SELECT INTO the DAO's fetch methods cannot be called from within a transaction.

Also note, that the field name separator for the DAO, which uses this fetch executor implementation, HAS TO BE a dollar sign ($).

This fetch executor allows paginated result sets containing no more than a million rows.

Version:

$Id: NoCursorFetchExecutor.java,v 1.4 2004/04/20 16:00:58 levahim Exp $

Author:

Lev Himmelfarb

Field Summary

protected static java.lang.String	DROP_TEMP_TABLE_QUERY SQL query used to drop the temp table.
protected static java.util.regex.Pattern	REP_PATTERN Regex pattern used to parse and modify the query.
protected static java.lang.String	REP_STRING Replacement string for the query modification.
protected static java.lang.String	SELECT_PAGE_QUERY_1 SQL query used to select the final result from the temporary table.
protected static java.lang.String	SELECT_PAGE_QUERY_2 SQL query used to select the final result from the temporary table.
protected static java.lang.String	TMP_TABLE_NAME Name of the temporary table to use.

Fields inherited from class com.boylesoftware.cb2.GenericFetchExecutor

appCtx, con, daoConfig, fetchResultDescriptor, page, pageSize, query, queryParams, rs, stmt, usePreparedStatement

Constructor Summary

NoCursorFetchExecutor()

Method Summary

java.sql.Statement	execute() Executes the query and returns the statement actually used for getting the result set.
java.sql.Statement	prepareStatement() Prepares a statement object before the execute method call.
boolean	setToTheFirstRow() Positions the result set cursor to the first row of the requested data.

Methods inherited from class com.boylesoftware.cb2.GenericFetchExecutor

destroy, finish, getResultSet, init, setApplicationContext

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

TMP_TABLE_NAME

protected static final java.lang.String TMP_TABLE_NAME

Name of the temporary table to use.

See Also:

Constant Field Values

REP_PATTERN

protected static final java.util.regex.Pattern REP_PATTERN

Regex pattern used to parse and modify the query.

REP_STRING

protected static final java.lang.String REP_STRING

Replacement string for the query modification.

See Also:

Constant Field Values

DROP_TEMP_TABLE_QUERY

protected static final java.lang.String DROP_TEMP_TABLE_QUERY

SQL query used to drop the temp table.

See Also:

Constant Field Values

SELECT_PAGE_QUERY_1

protected static final java.lang.String SELECT_PAGE_QUERY_1

SQL query used to select the final result from the temporary table. Part 1.

See Also:

Constant Field Values

SELECT_PAGE_QUERY_2

protected static final java.lang.String SELECT_PAGE_QUERY_2

SQL query used to select the final result from the temporary table. Part 2.

See Also:

Constant Field Values

Constructor Detail

NoCursorFetchExecutor

public NoCursorFetchExecutor()

Method Detail

prepareStatement

public java.sql.Statement prepareStatement()
                                    throws java.sql.SQLException

Prepares a statement object before the execute method call. If a paginated result set is requested, this implementation actually modifies the original query by adding a INTO clause to it.

Specified by:

prepareStatement in interface FetchExecutor

Overrides:

prepareStatement in class GenericFetchExecutor

Returns:

the statement object, which may be an instance of PreparedStatement if queryParams is specified.

Throws:

java.sql.SQLException - if a database error happened.

execute

public java.sql.Statement execute()
                           throws java.sql.SQLException

Executes the query and returns the statement actually used for getting the result set. The getResultSet method should be called to get the result set.

Specified by:

execute in interface FetchExecutor

Overrides:

execute in class GenericFetchExecutor

Returns:

the statement owning the result set.

Throws:

java.sql.SQLException - if a database error happened.

setToTheFirstRow

public boolean setToTheFirstRow()
                         throws java.sql.SQLException

Positions the result set cursor to the first row of the requested data.

Specified by:

setToTheFirstRow in interface FetchExecutor

Overrides:

setToTheFirstRow in class GenericFetchExecutor

Returns:

true if the cursor is set or false if there is not data (for example the result set is empty or the requested page does not exist).

Throws:

java.sql.SQLException - if a database error happened.