Package net.sourceforge.jtds.jdbc
Class CachedResultSet
java.lang.Object
net.sourceforge.jtds.jdbc.JtdsResultSet
net.sourceforge.jtds.jdbc.CachedResultSet
- All Implemented Interfaces:
AutoCloseable
,ResultSet
,Wrapper
A memory cached scrollable/updateable result set.
Notes:
- For maximum performance use the scroll insensitive result set type.
- As the result set is cached in memory this implementation is limited to small result sets.
- Updateable or scroll sensitive result sets are limited to selects which reference one table only.
- Scroll sensitive result sets must have primary keys.
- Updates are optimistic. To guard against lost updates it is recommended that the table includes a timestamp column.
- 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.
- Updateable result sets cannot be built from the output of stored procedures.
- This implementation uses 'select ... for browse' to obtain the column meta data needed to generate update statements etc.
- Named forward updateable cursors are also supported in which case positioned updates and deletes are used referencing a server side declared cursor.
- 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
FieldsModifier and TypeFieldDescriptionprotected JtdsConnection
The parent connection objectprotected final TdsCore
Cursor TdsCore object.protected ParamInfo[]
Buffer row used for inserts.protected boolean
Table is keyed.protected boolean
Flag to indicate Sybase.protected boolean
Indicates currently inserting.protected final ParamInfo[]
Original parameters.protected final String
Original procedure name.protected boolean
Indicates that row has been deleted.protected boolean
Indicates that row has been updated.protected boolean
Fetch size has been changed.protected String
Original SQL statement.protected String
First table name in select.protected final boolean
True if this is a local temporary result set.protected ParamInfo[]
The "update" row.protected final TdsCore
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
ConstructorsConstructorDescriptionCachedResultSet
(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[] colName, int[] colType) Constructs a cached result set based on locally generated data.CachedResultSet
(JtdsStatement statement, String sql, String procName, ParamInfo[] procedureParams, int resultSetType, int concurrency) Constructs a new cached result set.CachedResultSet
(JtdsStatement statement, ColInfo[] columns, Object[] data) Creates a cached result set containing one row. -
Method Summary
Modifier and TypeMethodDescriptionboolean
absolute
(int row) (package private) void
Warning! Ensure the provided data matches the column layout of thisResultSet
.void
void
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
(StringBuilder sql, ArrayList params, boolean select) Builds a WHERE clause for UPDATE or DELETE statements.void
void
close()
private void
Closes the result set.private void
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
boolean
first()
void
(package private) boolean
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
void
boolean
next()
boolean
previous()
protected void
Refreshes a result set row from keyed tables.protected void
Refreshes the row by rereading the result set.void
boolean
relative
(int row) boolean
boolean
boolean
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
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
Methods inherited from interface java.sql.ResultSet
updateObject, updateObject, updateObject, updateObject
-
Field Details
-
onInsertRow
protected boolean onInsertRowIndicates currently inserting. -
insertRow
Buffer row used for inserts. -
updateRow
The "update" row. -
rowUpdated
protected boolean rowUpdatedIndicates that row has been updated. -
rowDeleted
protected boolean rowDeletedIndicates that row has been deleted. -
tempResultSet
protected final boolean tempResultSetTrue if this is a local temporary result set. -
cursorTds
Cursor TdsCore object. -
updateTds
Updates TdsCore object used for positioned updates. -
isSybase
protected boolean isSybaseFlag to indicate Sybase. -
sizeChanged
protected boolean sizeChangedFetch size has been changed. -
sql
Original SQL statement. -
procName
Original procedure name. -
procedureParams
Original parameters. -
isKeyed
protected boolean isKeyedTable is keyed. -
tableName
First table name in select. -
connection
The parent connection object
-
-
Constructor Details
-
CachedResultSet
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 objectsql
- the SQL statement used to build the result setprocName
- an optional stored procedure nameprocedureParams
- parameters for prepared statementsresultSetType
- the result set type eg scrollableconcurrency
- the result set concurrency eg updateable- Throws:
SQLException
- if an error occurs
-
CachedResultSet
CachedResultSet(JtdsStatement statement, String[] colName, int[] colType) throws SQLException Constructs a cached result set based on locally generated data.- Parameters:
statement
- the parent statement objectcolName
- array of column namescolType
- array of corresponding data types- Throws:
SQLException
- if an error occurs
-
CachedResultSet
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 copyload
- load data from the supplied result set- Throws:
SQLException
- if an error occurs
-
CachedResultSet
CachedResultSet(JtdsStatement statement, ColInfo[] columns, Object[] data) throws SQLException Creates a cached result set containing one row.- Parameters:
statement
- the parent statement objectcolumns
- the column descriptor arraydata
- the row data- Throws:
SQLException
- if an error occurs
-
-
Method Details
-
addRow
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 egResultSet.CONCUR_READ_ONLY
-
cursorCreate
Creates a new scrollable result set in memory or a named server cursor.- Throws:
SQLException
- if an error occurs
-
isCursorUpdateable
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:
SQLException
-
cursorFetch
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:
SQLException
- if an error occurs
-
cursorClose
Closes the result set.- Throws:
SQLException
-
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 SQLinfo
- theColInfo
column descriptorvalue
- the column data item- Returns:
- the new parameter as a
ParamInfo
object - Throws:
SQLException
-
setColValue
protected Object setColValue(int colIndex, int jdbcType, Object value, int length) throws SQLException Sets the specified column's data value.- Overrides:
setColValue
in classJtdsResultSet
- Parameters:
colIndex
- index of the columnvalue
- new column valuelength
- the length of a stream parameter- Returns:
- the value, possibly converted to an internal type
- Throws:
SQLException
-
buildWhereClause
ParamInfo[] buildWhereClause(StringBuilder 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 toparams
- the parameter descriptor array for this statementselect
- true if this WHERE clause will be used in a select statement- Returns:
- the parameter list as a
ParamInfo[]
- Throws:
SQLException
- if an error occurs
-
refreshKeyedRows
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:
SQLException
- if an error occurs
-
refreshReRead
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:
SQLException
-
setFetchSize
- Specified by:
setFetchSize
in interfaceResultSet
- Overrides:
setFetchSize
in classJtdsResultSet
- Throws:
SQLException
-
afterLast
- Specified by:
afterLast
in interfaceResultSet
- Overrides:
afterLast
in classJtdsResultSet
- Throws:
SQLException
-
beforeFirst
- Specified by:
beforeFirst
in interfaceResultSet
- Overrides:
beforeFirst
in classJtdsResultSet
- Throws:
SQLException
-
cancelRowUpdates
- Specified by:
cancelRowUpdates
in interfaceResultSet
- Overrides:
cancelRowUpdates
in classJtdsResultSet
- Throws:
SQLException
-
close
- Specified by:
close
in interfaceAutoCloseable
- Specified by:
close
in interfaceResultSet
- Overrides:
close
in classJtdsResultSet
- Throws:
SQLException
-
deleteRow
- Specified by:
deleteRow
in interfaceResultSet
- Overrides:
deleteRow
in classJtdsResultSet
- Throws:
SQLException
-
insertRow
- Specified by:
insertRow
in interfaceResultSet
- Overrides:
insertRow
in classJtdsResultSet
- Throws:
SQLException
-
moveToCurrentRow
- Specified by:
moveToCurrentRow
in interfaceResultSet
- Overrides:
moveToCurrentRow
in classJtdsResultSet
- Throws:
SQLException
-
moveToInsertRow
- Specified by:
moveToInsertRow
in interfaceResultSet
- Overrides:
moveToInsertRow
in classJtdsResultSet
- Throws:
SQLException
-
refreshRow
- Specified by:
refreshRow
in interfaceResultSet
- Overrides:
refreshRow
in classJtdsResultSet
- Throws:
SQLException
-
updateRow
- Specified by:
updateRow
in interfaceResultSet
- Overrides:
updateRow
in classJtdsResultSet
- Throws:
SQLException
-
first
- Specified by:
first
in interfaceResultSet
- Overrides:
first
in classJtdsResultSet
- Throws:
SQLException
-
isLast
- Specified by:
isLast
in interfaceResultSet
- Overrides:
isLast
in classJtdsResultSet
- Throws:
SQLException
-
last
- Specified by:
last
in interfaceResultSet
- Overrides:
last
in classJtdsResultSet
- Throws:
SQLException
-
next
- Specified by:
next
in interfaceResultSet
- Overrides:
next
in classJtdsResultSet
- Throws:
SQLException
-
previous
- Specified by:
previous
in interfaceResultSet
- Overrides:
previous
in classJtdsResultSet
- Throws:
SQLException
-
rowDeleted
- Specified by:
rowDeleted
in interfaceResultSet
- Overrides:
rowDeleted
in classJtdsResultSet
- Throws:
SQLException
-
rowInserted
- Specified by:
rowInserted
in interfaceResultSet
- Overrides:
rowInserted
in classJtdsResultSet
- Throws:
SQLException
-
rowUpdated
- Specified by:
rowUpdated
in interfaceResultSet
- Overrides:
rowUpdated
in classJtdsResultSet
- Throws:
SQLException
-
absolute
- Specified by:
absolute
in interfaceResultSet
- Overrides:
absolute
in classJtdsResultSet
- Throws:
SQLException
-
relative
- Specified by:
relative
in interfaceResultSet
- Overrides:
relative
in classJtdsResultSet
- Throws:
SQLException
-
getCursorName
- Specified by:
getCursorName
in interfaceResultSet
- Overrides:
getCursorName
in classJtdsResultSet
- Throws:
SQLException
-