Package net.sourceforge.jtds.jdbc
Class CachedResultSet
- 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:- 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
Fields Modifier and Type Field Description protected ConnectionJDBC2
connection
The parent connection objectprotected 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 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
-
-
Constructor Summary
Constructors Constructor 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, 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.CachedResultSet(JtdsStatement statement, ColInfo[] columns, java.lang.Object[] data)
Creates a cached result set containing one row.
-
Method Summary
All Methods Static Methods Instance Methods Concrete Methods Modifier and Type Method Description boolean
absolute(int row)
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.StringBuffer 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
-
-
-
-
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.
-
initialRowCnt
protected int initialRowCnt
The row count of the initial result set.
-
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 ConnectionJDBC2 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 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:
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 objectcolName
- array of column namescolType
- 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 copyload
- 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 objectcolumns
- the column descriptor arraydata
- the row data- Throws:
java.sql.SQLException
- if an error occurs
-
-
Method Detail
-
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
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 SQLinfo
- theColInfo
column descriptorvalue
- 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 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:
java.sql.SQLException
-
buildWhereClause
ParamInfo[] buildWhereClause(java.lang.StringBuffer 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 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:
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 interfacejava.sql.ResultSet
- Overrides:
setFetchSize
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
afterLast
public void afterLast() throws java.sql.SQLException
- Specified by:
afterLast
in interfacejava.sql.ResultSet
- Overrides:
afterLast
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
beforeFirst
public void beforeFirst() throws java.sql.SQLException
- Specified by:
beforeFirst
in interfacejava.sql.ResultSet
- Overrides:
beforeFirst
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
cancelRowUpdates
public void cancelRowUpdates() throws java.sql.SQLException
- Specified by:
cancelRowUpdates
in interfacejava.sql.ResultSet
- Overrides:
cancelRowUpdates
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
close
public void close() throws java.sql.SQLException
- Specified by:
close
in interfacejava.lang.AutoCloseable
- Specified by:
close
in interfacejava.sql.ResultSet
- Overrides:
close
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
deleteRow
public void deleteRow() throws java.sql.SQLException
- Specified by:
deleteRow
in interfacejava.sql.ResultSet
- Overrides:
deleteRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
insertRow
public void insertRow() throws java.sql.SQLException
- Specified by:
insertRow
in interfacejava.sql.ResultSet
- Overrides:
insertRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
moveToCurrentRow
public void moveToCurrentRow() throws java.sql.SQLException
- Specified by:
moveToCurrentRow
in interfacejava.sql.ResultSet
- Overrides:
moveToCurrentRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
moveToInsertRow
public void moveToInsertRow() throws java.sql.SQLException
- Specified by:
moveToInsertRow
in interfacejava.sql.ResultSet
- Overrides:
moveToInsertRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
refreshRow
public void refreshRow() throws java.sql.SQLException
- Specified by:
refreshRow
in interfacejava.sql.ResultSet
- Overrides:
refreshRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
updateRow
public void updateRow() throws java.sql.SQLException
- Specified by:
updateRow
in interfacejava.sql.ResultSet
- Overrides:
updateRow
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
first
public boolean first() throws java.sql.SQLException
- Specified by:
first
in interfacejava.sql.ResultSet
- Overrides:
first
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
isLast
public boolean isLast() throws java.sql.SQLException
- Specified by:
isLast
in interfacejava.sql.ResultSet
- Overrides:
isLast
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
last
public boolean last() throws java.sql.SQLException
- Specified by:
last
in interfacejava.sql.ResultSet
- Overrides:
last
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
next
public boolean next() throws java.sql.SQLException
- Specified by:
next
in interfacejava.sql.ResultSet
- Overrides:
next
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
previous
public boolean previous() throws java.sql.SQLException
- Specified by:
previous
in interfacejava.sql.ResultSet
- Overrides:
previous
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
rowDeleted
public boolean rowDeleted() throws java.sql.SQLException
- Specified by:
rowDeleted
in interfacejava.sql.ResultSet
- Overrides:
rowDeleted
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
rowInserted
public boolean rowInserted() throws java.sql.SQLException
- Specified by:
rowInserted
in interfacejava.sql.ResultSet
- Overrides:
rowInserted
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
rowUpdated
public boolean rowUpdated() throws java.sql.SQLException
- Specified by:
rowUpdated
in interfacejava.sql.ResultSet
- Overrides:
rowUpdated
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
absolute
public boolean absolute(int row) throws java.sql.SQLException
- Specified by:
absolute
in interfacejava.sql.ResultSet
- Overrides:
absolute
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
relative
public boolean relative(int row) throws java.sql.SQLException
- Specified by:
relative
in interfacejava.sql.ResultSet
- Overrides:
relative
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
getCursorName
public java.lang.String getCursorName() throws java.sql.SQLException
- Specified by:
getCursorName
in interfacejava.sql.ResultSet
- Overrides:
getCursorName
in classJtdsResultSet
- Throws:
java.sql.SQLException
-
-