CachedResultSet (jTDS API)

java.lang.Object
- net.sourceforge.jtds.jdbc.JtdsResultSet
- - net.sourceforge.jtds.jdbc.CachedResultSet

All Implemented Interfaces:

java.lang.AutoCloseable, java.sql.ResultSet, java.sql.Wrapper
```
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

Fields
Modifier and Type	Field and Description
`protected JtdsConnection`	`connection` The parent connection object
`protected TdsCore`	`cursorTds` Cursor TdsCore object.
`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 ParamInfo[]`	`procedureParams` Original parameters.
`protected java.lang.String`	`procName` Original procedure name.
`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 java.lang.String`	`sql` Original SQL statement.
`protected java.lang.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
cancelled, CLOSE_CURSORS_AT_COMMIT, closed, columnCount, columns, concurrency, currentRow, cursorName, direction, fetchDirection, fetchSize, HOLD_CURSORS_OVER_COMMIT, INITIAL_ROW_COUNT, pos, POS_AFTER_LAST, POS_BEFORE_FIRST, resultSetType, rowData, rowPtr, rowsInResult, statement, wasNull

Fields inherited from interface java.sql.ResultSet
CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE

Constructor Summary

Constructors
Constructor and Description
`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, ColInfo[] columns, java.lang.Object[] data)` Creates a cached result set containing one row.
`CachedResultSet(JtdsStatement statement, java.lang.String[] colName, int[] colType)` Constructs a cached result set based on locally generated data.
`CachedResultSet(JtdsStatement statement, java.lang.String sql, java.lang.String procName, ParamInfo[] procedureParams, int resultSetType, int concurrency)` Constructs a new cached result set.

Method Summary

Methods
Modifier and Type	Method and Description
`boolean`	`absolute(int row)`
`(package private) void`	`addRow(java.lang.Object[] data)` Warning!
`void`	`afterLast()`
`void`	`beforeFirst()`
`protected static ParamInfo`	`buildParameter(int pos, ColInfo info, java.lang.Object value, boolean isUnicode)` Creates a parameter object for an UPDATE, DELETE or INSERT statement.
`(package private) ParamInfo[]`	`buildWhereClause(java.lang.StringBuilder sql, java.util.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()`
`java.lang.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 java.lang.Object`	`setColValue(int colIndex, int jdbcType, java.lang.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
cacheResultSetRows, checkOpen, checkScrollable, checkUpdateable, clearWarnings, copyInfo, copyRow, findColumn, 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, getCurrentRow, getDate, getDate, getDate, getDate, getDouble, getDouble, getFetchDirection, getFetchSize, getFloat, getFloat, getHoldability, getInt, getInt, getLong, getLong, getMetaData, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getObject, getObject, getObject, getObject, getRef, getRef, getRow, getRowId, getRowId, getShort, getShort, getSQLXML, getSQLXML, getStatement, getString, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, isAfterLast, isBeforeFirst, isClosed, isFirst, isWrapperFor, newRow, notImplemented, setColLabel, setColName, setColType, setColumnCount, setFetchDirection, unwrap, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRowId, updateRowId, updateShort, updateShort, updateSQLXML, updateSQLXML, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp, wasNull

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - onInsertRow
```
protected boolean onInsertRow
```
    Indicates currently inserting.
  - insertRow
```
protected ParamInfo[] insertRow
```
    Buffer row used for inserts.
  - updateRow
```
protected ParamInfo[] updateRow
```
    The "update" row.
  - rowUpdated
```
protected boolean rowUpdated
```
    Indicates that row has been updated.
  - rowDeleted
```
protected boolean rowDeleted
```
    Indicates that row has been deleted.
  - tempResultSet
```
protected final boolean tempResultSet
```
    True if this is a local temporary result set.
  - cursorTds
```
protected final TdsCore cursorTds
```
    Cursor TdsCore object.
  - updateTds
```
protected final TdsCore updateTds
```
    Updates TdsCore object used for positioned updates.
  - isSybase
```
protected boolean isSybase
```
    Flag to indicate Sybase.
  - sizeChanged
```
protected boolean sizeChanged
```
    Fetch size has been changed.
  - sql
```
protected java.lang.String sql
```
    Original SQL statement.
  - procName
```
protected final java.lang.String procName
```
    Original procedure name.
  - procedureParams
```
protected final ParamInfo[] procedureParams
```
    Original parameters.
  - isKeyed
```
protected boolean isKeyed
```
    Table is keyed.
  - tableName
```
protected java.lang.String tableName
```
    First table name in select.
  - connection
```
protected JtdsConnection connection
```
    The parent connection object
- Constructor Detail
  - CachedResultSet
```
CachedResultSet(JtdsStatement statement,
               java.lang.String sql,
               java.lang.String procName,
               ParamInfo[] procedureParams,
               int resultSetType,
               int concurrency)
          throws java.sql.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
    
    Throws:
    
    java.sql.SQLException - if an error occurs
  - CachedResultSet
```
CachedResultSet(JtdsStatement statement,
               java.lang.String[] colName,
               int[] colType)
          throws java.sql.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
    
    Throws:
    
    java.sql.SQLException - if an error occurs
  - CachedResultSet
```
CachedResultSet(JtdsResultSet rs,
               boolean load)
          throws java.sql.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
    
    Throws:
    
    java.sql.SQLException - if an error occurs
  - CachedResultSet
```
CachedResultSet(JtdsStatement statement,
               ColInfo[] columns,
               java.lang.Object[] data)
          throws java.sql.SQLException
```
    Creates a cached result set containing one row.
    
    Parameters:
    statement - the parent statement object
    columns - the column descriptor array
    data - the row data
    
    Throws:
    
    java.sql.SQLException - if an error occurs
- Method Detail
  - addRow
```
void addRow(java.lang.Object[] data)
```
    Warning! Ensure the provided data matches the column layout of this ResultSet. All kind of weird behavior and errors could be expected otherwise.
    
    Parameters:
    data - data of the row to add
  - setConcurrency
```
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
  - cursorCreate
```
private void cursorCreate()
                   throws java.sql.SQLException
```
    Creates a new scrollable result set in memory or a named server cursor.
    
    Throws:
    
    java.sql.SQLException - if an error occurs
  - isCursorUpdateable
```
boolean isCursorUpdateable()
                     throws java.sql.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
    Throws: java.sql.SQLException
  cursorFetch private boolean cursorFetch(int rowNum) throws java.sql.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 Throws: java.sql.SQLException - if an error occurs cursorClose private void cursorClose() throws java.sql.SQLException Closes the result set. Throws: java.sql.SQLException buildParameter protected static ParamInfo buildParameter(int pos, ColInfo info, java.lang.Object value, boolean isUnicode) throws java.sql.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 Throws: java.sql.SQLException setColValue protected java.lang.Object setColValue(int colIndex, int jdbcType, java.lang.Object value, int length) throws java.sql.SQLException Sets the specified column's data value. Overrides: setColValue in class JtdsResultSet Parameters: colIndex - index of the column value - new column value length - the length of a stream parameter Returns: the value, possibly converted to an internal type Throws: java.sql.SQLException buildWhereClause ParamInfo[] buildWhereClause(java.lang.StringBuilder sql, java.util.ArrayList params, boolean select) throws java.sql.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[] Throws: java.sql.SQLException - if an error occurs refreshKeyedRows protected void refreshKeyedRows() throws java.sql.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. Throws: java.sql.SQLException - if an error occurs refreshReRead protected void refreshReRead() throws java.sql.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. Throws: java.sql.SQLException setFetchSize public void setFetchSize(int size) throws java.sql.SQLException Specified by: setFetchSize in interface java.sql.ResultSet Overrides: setFetchSize in class JtdsResultSet Throws: java.sql.SQLException afterLast public void afterLast() throws java.sql.SQLException Specified by: afterLast in interface java.sql.ResultSet Overrides: afterLast in class JtdsResultSet Throws: java.sql.SQLException beforeFirst public void beforeFirst() throws java.sql.SQLException Specified by: beforeFirst in interface java.sql.ResultSet Overrides: beforeFirst in class JtdsResultSet Throws: java.sql.SQLException cancelRowUpdates public void cancelRowUpdates() throws java.sql.SQLException Specified by: cancelRowUpdates in interface java.sql.ResultSet Overrides: cancelRowUpdates in class JtdsResultSet Throws: java.sql.SQLException close public void close() throws java.sql.SQLException Specified by: close in interface java.lang.AutoCloseable Specified by: close in interface java.sql.ResultSet Overrides: close in class JtdsResultSet Throws: java.sql.SQLException deleteRow public void deleteRow() throws java.sql.SQLException Specified by: deleteRow in interface java.sql.ResultSet Overrides: deleteRow in class JtdsResultSet Throws: java.sql.SQLException insertRow public void insertRow() throws java.sql.SQLException Specified by: insertRow in interface java.sql.ResultSet Overrides: insertRow in class JtdsResultSet Throws: java.sql.SQLException moveToCurrentRow public void moveToCurrentRow() throws java.sql.SQLException Specified by: moveToCurrentRow in interface java.sql.ResultSet Overrides: moveToCurrentRow in class JtdsResultSet Throws: java.sql.SQLException moveToInsertRow public void moveToInsertRow() throws java.sql.SQLException Specified by: moveToInsertRow in interface java.sql.ResultSet Overrides: moveToInsertRow in class JtdsResultSet Throws: java.sql.SQLException refreshRow public void refreshRow() throws java.sql.SQLException Specified by: refreshRow in interface java.sql.ResultSet Overrides: refreshRow in class JtdsResultSet Throws: java.sql.SQLException updateRow public void updateRow() throws java.sql.SQLException Specified by: updateRow in interface java.sql.ResultSet Overrides: updateRow in class JtdsResultSet Throws: java.sql.SQLException first public boolean first() throws java.sql.SQLException Specified by: first in interface java.sql.ResultSet Overrides: first in class JtdsResultSet Throws: java.sql.SQLException isLast public boolean isLast() throws java.sql.SQLException Specified by: isLast in interface java.sql.ResultSet Overrides: isLast in class JtdsResultSet Throws: java.sql.SQLException last public boolean last() throws java.sql.SQLException Specified by: last in interface java.sql.ResultSet Overrides: last in class JtdsResultSet Throws: java.sql.SQLException next public boolean next() throws java.sql.SQLException Specified by: next in interface java.sql.ResultSet Overrides: next in class JtdsResultSet Throws: java.sql.SQLException previous public boolean previous() throws java.sql.SQLException Specified by: previous in interface java.sql.ResultSet Overrides: previous in class JtdsResultSet Throws: java.sql.SQLException rowDeleted public boolean rowDeleted() throws java.sql.SQLException Specified by: rowDeleted in interface java.sql.ResultSet Overrides: rowDeleted in class JtdsResultSet Throws: java.sql.SQLException rowInserted public boolean rowInserted() throws java.sql.SQLException Specified by: rowInserted in interface java.sql.ResultSet Overrides: rowInserted in class JtdsResultSet Throws: java.sql.SQLException rowUpdated public boolean rowUpdated() throws java.sql.SQLException Specified by: rowUpdated in interface java.sql.ResultSet Overrides: rowUpdated in class JtdsResultSet Throws: java.sql.SQLException absolute public boolean absolute(int row) throws java.sql.SQLException Specified by: absolute in interface java.sql.ResultSet Overrides: absolute in class JtdsResultSet Throws: java.sql.SQLException relative public boolean relative(int row) throws java.sql.SQLException Specified by: relative in interface java.sql.ResultSet Overrides: relative in class JtdsResultSet Throws: java.sql.SQLException getCursorName public java.lang.String getCursorName() throws java.sql.SQLException Specified by: getCursorName in interface java.sql.ResultSet Overrides: getCursorName in class JtdsResultSet Throws: java.sql.SQLException

Class CachedResultSet

Field Summary

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

Fields inherited from interface java.sql.ResultSet

Constructor Summary

Method Summary

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

Methods inherited from class java.lang.Object

Field Detail

onInsertRow

insertRow

updateRow

rowUpdated

rowDeleted

tempResultSet

cursorTds

updateTds

isSybase

sizeChanged

sql

procName

procedureParams

isKeyed

tableName

connection

Constructor Detail

CachedResultSet

CachedResultSet

CachedResultSet

CachedResultSet

Method Detail

addRow

setConcurrency

cursorCreate

isCursorUpdateable

cursorFetch

cursorClose

buildParameter

setColValue

buildWhereClause

refreshKeyedRows

refreshReRead

setFetchSize

afterLast

beforeFirst

cancelRowUpdates

close

deleteRow

insertRow

moveToCurrentRow

moveToInsertRow

refreshRow

updateRow

first

isLast

last

next

previous

rowDeleted

rowInserted

rowUpdated

absolute

relative

getCursorName