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

This class implements 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.15 2005/03/26 22:10:58 alin_sinpalean Exp $
      Author:
      Mike Hutchinson

      Field Summary
      protected  TdsCore cursorTds
                Cursor TdsCore object.
      protected  boolean hasKeys
                Indicates that result set is keyed.
      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, columnMap, columns, concurrency, currentRow, cursorName, direction, f, fetchDirection, fetchSize, HOLD_CURSORS_OVER_COMMIT, INITIAL_ROW_COUNT, pos, POS_AFTER_LAST, POS_BEFORE_FIRST, readAhead, 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)
                Create 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)
                Create a cached result set containing one row.
      (package private) CachedResultSet(JtdsStatement statement, java.lang.String[] colName, int[] colType)
                Construct 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)
                Construct a new cached result set.
       
      Method Summary
       boolean absolute(int row)
                 
       void afterLast()
                 
       void beforeFirst()
                 
      protected  ParamInfo buildParameter(int pos, ColInfo info, java.lang.Object value)
                Create a parameter object for an update, delete or insert statement.
      (package private)  ParamInfo[] buildWhereClause(java.lang.StringBuffer sql, java.util.ArrayList params, boolean select)
                Build a where clause for update or delete statements.
       void cancelRowUpdates()
                 
       void close()
                 
      private  void cursorClose()
                Close the result set.
      private  void cursorCreate(java.lang.String sql, java.lang.String procName, ParamInfo[] parameters)
                Create a new scrollable result set in memory or a named server cursor.
      private  boolean cursorFetch(int rowNum)
                Fetch the next result row from the internal row array.
       void deleteRow()
                 
       boolean first()
                 
       void insertRow()
                 
      (package private)  boolean isCursorUpdateable()
                Analyse the tables in the result set and determine if we have the primary key columns needed to make each updateable.
       boolean isLast()
                 
       boolean last()
                 
       void moveToCurrentRow()
                 
       void moveToInsertRow()
                 
       boolean next()
                 
       boolean previous()
                 
      protected  void refreshKeyedRows()
                Refresh a result set row from keyed tables.
      protected  void refreshReRead()
                Refresh the row by rereading the result set.
       void refreshRow()
                 
       boolean relative(int row)
                 
       boolean rowDeleted()
                 
       boolean rowInserted()
                 
       boolean rowUpdated()
                 
      protected  void setColValue(int colIndex, int jdbcType, java.lang.Object value, int length)
                Set 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, 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, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, isAfterLast, isBeforeFirst, 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, registerNatives, 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.

      hasKeys

      protected boolean hasKeys
      Indicates that result set is keyed.

      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 boolean tempResultSet
      True if this is a local temporary result set.

      cursorTds

      protected TdsCore cursorTds
      Cursor TdsCore object.

      updateTds

      protected 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 java.lang.String procName
      Original procedure name.

      procedureParams

      protected ParamInfo[] procedureParams
      Original parameters.

      isKeyed

      protected boolean isKeyed
      Table is keyed.

      tableName

      protected java.lang.String tableName
      First table name in select.
      Constructor Detail

      CachedResultSet

      CachedResultSet(JtdsStatement statement,
                      java.lang.String sql,
                      java.lang.String procName,
                      ParamInfo[] procedureParams,
                      int resultSetType,
                      int concurrency)
                throws java.sql.SQLException
      Construct 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 -  

      CachedResultSet

      CachedResultSet(JtdsStatement statement,
                      java.lang.String[] colName,
                      int[] colType)
                throws java.sql.SQLException
      Construct 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 -  

      CachedResultSet

      CachedResultSet(JtdsResultSet rs,
                      boolean load)
                throws java.sql.SQLException
      Create 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 -  

      CachedResultSet

      CachedResultSet(JtdsStatement statement,
                      ColInfo[] columns,
                      java.lang.Object[] data)
                throws java.sql.SQLException
      Create 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 -  
      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 eg ResultSet.CONCUR_READ_ONLY.

      cursorCreate

      private void cursorCreate(java.lang.String sql,
                                java.lang.String procName,
                                ParamInfo[] parameters)
                         throws java.sql.SQLException
      Create a new scrollable result set in memory or a named server cursor.
      Parameters:
      sql - The SQL SELECT statement.
      procName - Optional procedure name for cursors based on a stored procedure.
      parameters - Optional stored procedure parameters.
      Throws:
      java.sql.SQLException -  

      isCursorUpdateable

      boolean isCursorUpdateable()
                           throws java.sql.SQLException
      Analyse the tables in the result set and determine if we have the primary key columns needed to make each updateable.

      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 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:
      boolean true if there is one table and it is keyed.

      cursorFetch

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

      cursorClose

      private void cursorClose()
                        throws java.sql.SQLException
      Close the result set.

      buildParameter

      protected ParamInfo buildParameter(int pos,
                                         ColInfo info,
                                         java.lang.Object value)
                                  throws java.sql.SQLException
      Create 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.

      setColValue

      protected void setColValue(int colIndex,
                                 int jdbcType,
                                 java.lang.Object value,
                                 int length)
                          throws java.sql.SQLException
      Set the specified column's data value.
      Overrides:
      setColValue in class JtdsResultSet
      Parameters:
      colIndex - The index of the column in the row.
      value - The new column value.

      buildWhereClause

      ParamInfo[] buildWhereClause(java.lang.StringBuffer sql,
                                   java.util.ArrayList params,
                                   boolean select)
                             throws java.sql.SQLException
      Build 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 -  

      refreshKeyedRows

      protected void refreshKeyedRows()
                               throws java.sql.SQLException
      Refresh 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 -  

      refreshReRead

      protected void refreshReRead()
                            throws java.sql.SQLException
      Refresh 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.


      setFetchSize

      public void setFetchSize(int size)
                        throws java.sql.SQLException
      Overrides:
      setFetchSize in class JtdsResultSet

      afterLast

      public void afterLast()
                     throws java.sql.SQLException
      Overrides:
      afterLast in class JtdsResultSet

      beforeFirst

      public void beforeFirst()
                       throws java.sql.SQLException
      Overrides:
      beforeFirst in class JtdsResultSet

      cancelRowUpdates

      public void cancelRowUpdates()
                            throws java.sql.SQLException
      Overrides:
      cancelRowUpdates in class JtdsResultSet

      close

      public void close()
                 throws java.sql.SQLException
      Overrides:
      close in class JtdsResultSet

      deleteRow

      public void deleteRow()
                     throws java.sql.SQLException
      Overrides:
      deleteRow in class JtdsResultSet

      insertRow

      public void insertRow()
                     throws java.sql.SQLException
      Overrides:
      insertRow in class JtdsResultSet

      moveToCurrentRow

      public void moveToCurrentRow()
                            throws java.sql.SQLException
      Overrides:
      moveToCurrentRow in class JtdsResultSet

      moveToInsertRow

      public void moveToInsertRow()
                           throws java.sql.SQLException
      Overrides:
      moveToInsertRow in class JtdsResultSet

      refreshRow

      public void refreshRow()
                      throws java.sql.SQLException
      Overrides:
      refreshRow in class JtdsResultSet

      updateRow

      public void updateRow()
                     throws java.sql.SQLException
      Overrides:
      updateRow in class JtdsResultSet

      first

      public boolean first()
                    throws java.sql.SQLException
      Overrides:
      first in class JtdsResultSet

      isLast

      public boolean isLast()
                     throws java.sql.SQLException
      Overrides:
      isLast in class JtdsResultSet

      last

      public boolean last()
                   throws java.sql.SQLException
      Overrides:
      last in class JtdsResultSet

      next

      public boolean next()
                   throws java.sql.SQLException
      Overrides:
      next in class JtdsResultSet

      previous

      public boolean previous()
                       throws java.sql.SQLException
      Overrides:
      previous in class JtdsResultSet

      rowDeleted

      public boolean rowDeleted()
                         throws java.sql.SQLException
      Overrides:
      rowDeleted in class JtdsResultSet

      rowInserted

      public boolean rowInserted()
                          throws java.sql.SQLException
      Overrides:
      rowInserted in class JtdsResultSet

      rowUpdated

      public boolean rowUpdated()
                         throws java.sql.SQLException
      Overrides:
      rowUpdated in class JtdsResultSet

      absolute

      public boolean absolute(int row)
                       throws java.sql.SQLException
      Overrides:
      absolute in class JtdsResultSet

      relative

      public boolean relative(int row)
                       throws java.sql.SQLException
      Overrides:
      relative in class JtdsResultSet


      Generated on April 15 2005