net.sourceforge.jtds.jdbc

Class CachedResultSet

Implemented Interfaces:
ResultSet

public class CachedResultSet
extends JtdsResultSet

A memory cached scrollable/updateable result set.

Notes:

  1. For maximum performance use the scroll insensitive result set type.
  2. As the result set is cached in memory this implementation is limited to small result sets.
  3. Updateable or scroll sensitive result sets are limited to selects which reference one table only.
  4. Scroll sensitive result sets must have primary keys.
  5. Updates are optimistic. To guard against lost updates it is recommended that the table includes a timestamp column.
  6. This class is a plug-in replacement for the MSCursorResultSet class which may be advantageous in certain applications as the scroll insensitive result set implemented here is much faster than the server side cursor.
  7. Updateable result sets cannot be built from the output of stored procedures.
  8. This implementation uses 'select ... for browse' to obtain the column meta data needed to generate update statements etc.
  9. Named forward updateable cursors are also supported in which case positioned updates and deletes are used referencing a server side declared cursor.
  10. Named forward read only declared cursors can have a larger fetch size specified allowing a cursor alternative to the default direct select method.
Version:
$Id: CachedResultSet.java,v 1.26 2007/07/08 17:28:23 bheineman Exp $
Author:
Mike Hutchinson
To do:
Should add a "close statement" flag to the constructors

Field Summary

protected ConnectionJDBC2
connection
The parent connection object
protected TdsCore
cursorTds
Cursor TdsCore object.
protected int
initialRowCnt
The row count of the initial result set.
protected ParamInfo[]
insertRow
Buffer row used for inserts.
protected boolean
isKeyed
Table is keyed.
protected boolean
isSybase
Flag to indicate Sybase.
protected boolean
onInsertRow
Indicates currently inserting.
protected String
procName
Original procedure name.
protected ParamInfo[]
procedureParams
Original parameters.
protected boolean
rowDeleted
Indicates that row has been deleted.
protected boolean
rowUpdated
Indicates that row has been updated.
protected boolean
sizeChanged
Fetch size has been changed.
protected String
sql
Original SQL statement.
protected String
tableName
First table name in select.
protected boolean
tempResultSet
True if this is a local temporary result set.
protected ParamInfo[]
updateRow
The "update" row.
protected TdsCore
updateTds
Updates TdsCore object used for positioned updates.

Fields inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet

CLOSE_CURSORS_AT_COMMIT, HOLD_CURSORS_OVER_COMMIT, INITIAL_ROW_COUNT, POS_AFTER_LAST, POS_BEFORE_FIRST, cancelled, closed, columnCount, columnMap, columns, concurrency, currentRow, cursorName, direction, f, fetchDirection, fetchSize, pos, resultSetType, rowData, rowPtr, rowsInResult, statement, wasNull

Constructor Summary

CachedResultSet(JtdsResultSet rs, boolean load)
Creates a cached result set with the same columns (and optionally data) as an existing result set.
CachedResultSet(JtdsStatement statement, String sql, String procName, ParamInfo[] procedureParams, int resultSetType, int concurrency)
Constructs a new cached result set.
CachedResultSet(JtdsStatement statement, String[] colName, int[] colType)
Constructs a cached result set based on locally generated data.
CachedResultSet(JtdsStatement statement, columns[] , data[] )
Creates a cached result set containing one row.

Method Summary

boolean
absolute(int row)
void
afterLast()
void
beforeFirst()
protected static ParamInfo
buildParameter(int pos, ColInfo info, Object value, boolean isUnicode)
Creates a parameter object for an UPDATE, DELETE or INSERT statement.
(package private) ParamInfo[]
buildWhereClause(StringBuffer sql, ArrayList params, boolean select)
Builds a WHERE clause for UPDATE or DELETE statements.
void
cancelRowUpdates()
void
close()
private void
cursorClose()
Closes the result set.
private void
cursorCreate()
Creates a new scrollable result set in memory or a named server cursor.
private boolean
cursorFetch(int rowNum)
Fetches the next result row from the internal row array.
void
deleteRow()
boolean
first()
String
getCursorName()
void
insertRow()
(package private) boolean
isCursorUpdateable()
Analyses the tables in the result set and determines if the primary key columns needed to make it updateable exist.
boolean
isLast()
boolean
last()
void
moveToCurrentRow()
void
moveToInsertRow()
boolean
next()
boolean
previous()
protected void
refreshKeyedRows()
Refreshes a result set row from keyed tables.
protected void
refreshReRead()
Refreshes the row by rereading the result set.
void
refreshRow()
boolean
relative(int row)
boolean
rowDeleted()
boolean
rowInserted()
boolean
rowUpdated()
protected Object
setColValue(int colIndex, int jdbcType, Object value, int length)
Sets the specified column's data value.
(package private) void
setConcurrency(int concurrency)
Modify the concurrency of the result set.
void
setFetchSize(int size)
void
updateRow()

Methods inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet

absolute, afterLast, beforeFirst, cacheResultSetRows, cancelRowUpdates, checkOpen, checkScrollable, checkUpdateable, clearWarnings, close, copyInfo, copyRow, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getBoolean, getByte, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getColumn, getColumnCount, getColumns, getConcurrency, getConnection, getCurrentRow, getCursorName, getDate, getDate, getDate, getDate, getDouble, getDouble, getFetchDirection, getFetchSize, getFloat, getFloat, getInt, getInt, getLong, getLong, getMetaData, getObject, getObject, getObject, getObject, getRef, getRef, getRow, getShort, getShort, getStatement, getString, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getURL, getURL, getUnicodeStream, getUnicodeStream, getWarnings, insertRow, isAfterLast, isBeforeFirst, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, newRow, next, notImplemented, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setColLabel, setColName, setColType, setColValue, setColumnCount, setFetchDirection, setFetchSize, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateShort, updateShort, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp, wasNull

Field Details

connection

protected ConnectionJDBC2 connection
The parent connection object

cursorTds

protected final TdsCore cursorTds
Cursor TdsCore object.

initialRowCnt

protected int initialRowCnt
The row count of the initial result set.

insertRow

protected ParamInfo[] insertRow
Buffer row used for inserts.

isKeyed

protected boolean isKeyed
Table is keyed.

isSybase

protected boolean isSybase
Flag to indicate Sybase.

onInsertRow

protected boolean onInsertRow
Indicates currently inserting.

procName

protected final String procName
Original procedure name.

procedureParams

protected final ParamInfo[] procedureParams
Original parameters.

rowDeleted

protected boolean rowDeleted
Indicates that row has been deleted.

rowUpdated

protected boolean rowUpdated
Indicates that row has been updated.

sizeChanged

protected boolean sizeChanged
Fetch size has been changed.

sql

protected String sql
Original SQL statement.

tableName

protected String tableName
First table name in select.

tempResultSet

protected final boolean tempResultSet
True if this is a local temporary result set.

updateRow

protected ParamInfo[] updateRow
The "update" row.

updateTds

protected final TdsCore updateTds
Updates TdsCore object used for positioned updates.

Constructor Details

CachedResultSet

(package private)  CachedResultSet(JtdsResultSet rs,
                                   boolean load)
            throws SQLException
Creates a cached result set with the same columns (and optionally data) as an existing result set.
Parameters:
rs - the result set to copy
load - load data from the supplied result set

CachedResultSet

(package private)  CachedResultSet(JtdsStatement statement,
                                   String sql,
                                   String procName,
                                   ParamInfo[] procedureParams,
                                   int resultSetType,
                                   int concurrency)
            throws SQLException
Constructs a new cached result set.

This result set will either be cached in memory or, if the cursor name is set, can be a forward only server side cursor. This latter form of cursor can also support positioned updates.

Parameters:
statement - the parent statement object
sql - the SQL statement used to build the result set
procName - an optional stored procedure name
procedureParams - parameters for prepared statements
resultSetType - the result set type eg scrollable
concurrency - the result set concurrency eg updateable

CachedResultSet

(package private)  CachedResultSet(JtdsStatement statement,
                                   String[] colName,
                                   int[] colType)
            throws SQLException
Constructs a cached result set based on locally generated data.
Parameters:
statement - the parent statement object
colName - array of column names
colType - array of corresponding data types

CachedResultSet

(package private)  CachedResultSet(JtdsStatement statement,
                                   columns[] ,
                                   data[] )
            throws SQLException
Creates a cached result set containing one row.
Parameters:
statement - the parent statement object

Method Details

absolute

public boolean absolute(int row)
            throws SQLException
Overrides:
absolute in interface JtdsResultSet

afterLast

public void afterLast()
            throws SQLException
Overrides:
afterLast in interface JtdsResultSet

beforeFirst

public void beforeFirst()
            throws SQLException
Overrides:
beforeFirst in interface JtdsResultSet

buildParameter

protected static ParamInfo buildParameter(int pos,
                                          ColInfo info,
                                          Object value,
                                          boolean isUnicode)
            throws SQLException
Creates a parameter object for an UPDATE, DELETE or INSERT statement.
Parameters:
pos - the substitution position of the parameter marker in the SQL
info - the ColInfo column descriptor
value - the column data item
Returns:
the new parameter as a ParamInfo object

buildWhereClause

(package private)  ParamInfo[] buildWhereClause(StringBuffer sql,
                                                ArrayList params,
                                                boolean select)
            throws SQLException
Builds a WHERE clause for UPDATE or DELETE statements.
Parameters:
sql - the SQL Statement to append the WHERE clause to
params - the parameter descriptor array for this statement
select - true if this WHERE clause will be used in a select statement
Returns:
the parameter list as a ParamInfo[]

cancelRowUpdates

public void cancelRowUpdates()
            throws SQLException
Overrides:
cancelRowUpdates in interface JtdsResultSet

close

public void close()
            throws SQLException
Overrides:
close in interface JtdsResultSet

cursorClose

private void cursorClose()
            throws SQLException
Closes the result set.

cursorCreate

private void cursorCreate()
            throws SQLException
Creates a new scrollable result set in memory or a named server cursor.

cursorFetch

private boolean cursorFetch(int rowNum)
            throws SQLException
Fetches the next result row from the internal row array.
Parameters:
rowNum - the row number to fetch
Returns:
true if a result set row is returned

deleteRow

public void deleteRow()
            throws SQLException
Overrides:
deleteRow in interface JtdsResultSet

first

public boolean first()
            throws SQLException
Overrides:
first in interface JtdsResultSet

getCursorName

public String getCursorName()
            throws SQLException
Overrides:
getCursorName in interface JtdsResultSet

insertRow

public void insertRow()
            throws SQLException
Overrides:
insertRow in interface JtdsResultSet

isCursorUpdateable

(package private)  boolean isCursorUpdateable()
            throws SQLException
Analyses the tables in the result set and determines if the primary key columns needed to make it updateable exist.

Sybase (and SQL 6.5) will automatically include any additional key and timestamp columns as hidden fields even if the user does not reference them in the select statement.

If the table is unkeyed but there is an identity column then this is promoted to a key.

Alternatively we can update, provided all the columns in the table row have been selected, by regarding all of them as keys.

SQL Server 7+ does not return the correct primary key meta data for temporary tables so the driver has to query the catalog to locate any keys.

Returns:
true if there is one table and it is keyed

isLast

public boolean isLast()
            throws SQLException
Overrides:
isLast in interface JtdsResultSet

last

public boolean last()
            throws SQLException
Overrides:
last in interface JtdsResultSet

moveToCurrentRow

public void moveToCurrentRow()
            throws SQLException
Overrides:
moveToCurrentRow in interface JtdsResultSet

moveToInsertRow

public void moveToInsertRow()
            throws SQLException
Overrides:
moveToInsertRow in interface JtdsResultSet

next

public boolean next()
            throws SQLException
Overrides:
next in interface JtdsResultSet

previous

public boolean previous()
            throws SQLException
Overrides:
previous in interface JtdsResultSet

refreshKeyedRows

protected void refreshKeyedRows()
            throws SQLException
Refreshes a result set row from keyed tables.

If all the tables in the result set have primary keys then the result set row can be refreshed by refetching the individual table rows.


refreshReRead

protected void refreshReRead()
            throws SQLException
Refreshes the row by rereading the result set.

Obviously very slow on large result sets but may be the only option if tables do not have keys.


refreshRow

public void refreshRow()
            throws SQLException
Overrides:
refreshRow in interface JtdsResultSet

relative

public boolean relative(int row)
            throws SQLException
Overrides:
relative in interface JtdsResultSet

rowDeleted

public boolean rowDeleted()
            throws SQLException
Overrides:
rowDeleted in interface JtdsResultSet

rowInserted

public boolean rowInserted()
            throws SQLException
Overrides:
rowInserted in interface JtdsResultSet

rowUpdated

public boolean rowUpdated()
            throws SQLException
Overrides:
rowUpdated in interface JtdsResultSet

setColValue

protected Object setColValue(int colIndex,
                             int jdbcType,
                             Object value,
                             int length)
            throws SQLException
Sets the specified column's data value.
Overrides:
setColValue in interface JtdsResultSet
Parameters:
colIndex - index of the column
value - new column value
Returns:
the value, possibly converted to an internal type

setConcurrency

(package private)  void setConcurrency(int concurrency)
Modify the concurrency of the result set.

Use to make result set read only once loaded.

Parameters:
concurrency - the concurrency value eg ResultSet.CONCUR_READ_ONLY

setFetchSize

public void setFetchSize(int size)
            throws SQLException
Overrides:
setFetchSize in interface JtdsResultSet

updateRow

public void updateRow()
            throws SQLException
Overrides:
updateRow in interface JtdsResultSet

Generated on June 12 2008