Class 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 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