net.sourceforge.jtds.jdbc
Class CachedResultSet

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

All Implemented Interfaces:

java.sql.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 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

(package private)	CachedResultSet(JtdsResultSet rs, boolean load) Creates a cached result set with the same columns (and optionally data) as an existing result set.
(package private)	CachedResultSet(JtdsStatement statement, ColInfo[] columns, java.lang.Object[] data) Creates a cached result set containing one row.
(package private)	CachedResultSet(JtdsStatement statement, java.lang.String[] colName, int[] colType) Constructs a cached result set based on locally generated data.
(package private)	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

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.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, 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, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, isAfterLast, isBeforeFirst, isClosed, isFirst, newRow, notImplemented, setColLabel, setColName, setColType, setColumnCount, setFetchDirection, 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, updateShort, updateShort, 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 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 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.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 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.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