cachedresultset.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

                    parameters[i].markerPos += offset;
                }
            }
            cursorSQL.append(sql);
            cursorTds.executeSQL(cursorSQL.toString(), null, parameters,
                    false, statement.getQueryTimeout(), statement.getMaxRows(),
                    statement.getMaxFieldSize(), true);
            cursorTds.clearResponseQueue();
            cursorTds.getMessages().checkErrors();
            //
            // OK now open cursor and fetch the first set (fetchSize) rows
            //
            cursorSQL.setLength(0);
            cursorSQL.append("\r\nOPEN ").append(cursorName);
            if (fetchSize > 1 && isSybase) {
                cursorSQL.append("\r\nSET CURSOR ROWS ").append(fetchSize);
                cursorSQL.append(" FOR ").append(cursorName);
            }
            cursorSQL.append("\r\nFETCH ").append(cursorName);
            cursorTds.executeSQL(cursorSQL.toString(), null, null, false,
                    statement.getQueryTimeout(), statement.getMaxRows(),
                    statement.getMaxFieldSize(), true);
            //
            // Check we have a result set
            //
            while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse());

            if (!cursorTds.isResultSet()) {
                // Throw exception but queue up any others
                SQLException ex = new SQLException(
                        Messages.get("error.statement.noresult"), "24000");
                ex.setNextException(statement.getMessages().exceptions);
                throw ex;
            }
            columns = cursorTds.getColumns();
            if (connection.getServerType() == Driver.SQLSERVER) {
                // Last column will be rowstat but will not be marked as hidden
                // as we do not have the Column meta data returned by the API
                // cursor.
                // Hide it now to avoid confusion (also should not be updated).
                if (columns.length > 0) {
                    columns[columns.length - 1].isHidden = true;
                }
            }
            columnCount = getColumnCount(columns);
            rowsInResult = cursorTds.isDataInResultSet() ? 1 : 0;
        } else {
            //
            // Open a memory cached scrollable or forward only possibly updateable cursor
            //
            if (isSelect && (concurrency != ResultSet.CONCUR_READ_ONLY
                    || resultSetType >= ResultSet.TYPE_SCROLL_SENSITIVE)) {
                // Need to execute SELECT .. FOR BROWSE to get
                // the MetaData we require for updates etc
                // OK Should have an SQL select statement
                // append " FOR BROWSE" to obtain table names
                // NB. We can't use any jTDS temporary stored proc
                    cursorTds.executeSQL(sql + " FOR BROWSE", null, procedureParams,
                            false, statement.getQueryTimeout(),
                            statement.getMaxRows(), statement.getMaxFieldSize(),
                            true);
                while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse());
                if (!cursorTds.isResultSet()) {
                    // Throw exception but queue up any others
                    SQLException ex = new SQLException(
                            Messages.get("error.statement.noresult"), "24000");
                    ex.setNextException(statement.getMessages().exceptions);
                    throw ex;
                }
                columns = cursorTds.getColumns();
                columnCount = getColumnCount(columns);
                rowData = new ArrayList(INITIAL_ROW_COUNT);
                //
                // Load result set into buffer
                //
                cacheResultSetRows();
                rowsInResult  = rowData.size();
                initialRowCnt = rowsInResult;
                pos = POS_BEFORE_FIRST;
                //
                // If cursor is built over one table and the table has
                // key columns then the result set is updateable and / or
                // can be used as a scroll sensitive result set.
                //
                if (!isCursorUpdateable()) {
                    // No so downgrade
                    concurrency = ResultSet.CONCUR_READ_ONLY;
                    if (resultSetType != ResultSet.TYPE_FORWARD_ONLY) {
                        resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;
                    }
                }
            } else {
                //
                // Create a read only cursor using direct SQL
                //
                cursorTds.executeSQL(sql, procName, procedureParams, false,
                        statement.getQueryTimeout(), statement.getMaxRows(),
                        statement.getMaxFieldSize(), true);
                while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse());

                if (!cursorTds.isResultSet()) {
                    // Throw exception but queue up any others
                    SQLException ex = new SQLException(
                            Messages.get("error.statement.noresult"), "24000");
                    ex.setNextException(statement.getMessages().exceptions);
                    throw ex;
                }
                columns = cursorTds.getColumns();
                columnCount = getColumnCount(columns);
                rowData = new ArrayList(INITIAL_ROW_COUNT);
                //
                // Load result set into buffer
                //
                cacheResultSetRows();
                rowsInResult  = rowData.size();
                initialRowCnt = rowsInResult;
                pos = POS_BEFORE_FIRST;
            }
        }
        //
        // Report any cursor downgrade warnings
        //
        if (concurrency < requestedConcurrency) {
            statement.addWarning(new SQLWarning(
                    Messages.get("warning.cursordowngraded",
                            "CONCUR_READ_ONLY"), "01000"));
        }
        if (resultSetType < requestedType) {
            statement.addWarning(new SQLWarning(
                    Messages.get("warning.cursordowngraded",
                            "TYPE_SCROLL_INSENSITIVE"), "01000"));
        }
        //
        // Report any SQLExceptions
        //
        statement.getMessages().checkErrors();
    }

    /**
     * Analyses the tables in the result set and determines if the primary key
     * columns needed to make it updateable exist.
     * <p/>
     * 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.
     * <p/>
     * If the table is unkeyed but there is an identity column then this is
     * promoted to a key.
     * <p/>
     * Alternatively we can update, provided all the columns in the table row
     * have been selected, by regarding all of them as keys.
     * <p/>
     * 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.
     *
     * @return <code>true<code> if there is one table and it is keyed
     */
    boolean isCursorUpdateable() throws SQLException {
        //
        // Get fully qualified table names and check keys
        //
        isKeyed = false;
        HashSet tableSet = new HashSet();
        for (int i = 0; i < columns.length; i++) {
            ColInfo ci = columns[i];
            if (ci.isKey) {
                // If a table lacks a key Sybase flags all columns except timestamps as keys.
                // This does not make much sense in the case of text or image fields!
                if (ci.sqlType.equals("text") || ci.sqlType.equals("image")) {
                    ci.isKey = false;
                } else {
                    isKeyed = true;
                }
            } else
            if (ci.isIdentity) {
                // This is a good choice for a row identifier!
                ci.isKey = true;
                isKeyed = true;
            }
            StringBuffer key = new StringBuffer();
            if (ci.tableName != null && ci.tableName.length() > 0) {
                key.setLength(0);
                if (ci.catalog != null) {
                    key.append(ci.catalog).append('.');
                    if (ci.schema == null) {
                        key.append('.');
                    }
                }
                if (ci.schema != null) {
                    key.append(ci.schema).append('.');
                }
                key.append(ci.tableName);
                tableName = key.toString();
                tableSet.add(tableName);
            }
        }
        //
        // MJH - SQL Server 7/2000 does not return key information for temporary tables.
        // I regard this as a bug!
        // See if we can find up to the first 8 index columns for ourselves.
        //
        if (tableName.startsWith("#") && cursorTds.getTdsVersion() >= Driver.TDS70) {
            StringBuffer sql = new StringBuffer(1024);
            sql.append("SELECT ");
            for (int i = 1; i <= 8; i++) {
                if (i > 1) {
                    sql.append(',');
                }
                sql.append("index_col('tempdb..").append(tableName);
                sql.append("', indid, ").append(i).append(')');
            }
            sql.append(" FROM tempdb..sysindexes WHERE id = object_id('tempdb..");
            sql.append(tableName).append("') AND indid > 0 AND ");
            sql.append("(status & 2048) = 2048");
            cursorTds.executeSQL(sql.toString(), null, null, false, 0,
                    statement.getMaxRows(), statement.getMaxFieldSize(), true);
            while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse());

            if (cursorTds.isResultSet() && cursorTds.getNextRow())
            {
                Object row[] = cursorTds.getRowData();
                for (int i =0 ; i < row.length; i++) {
                    String name = (String)row[i];
                    if (name != null) {
                        for (int c = 0; c < columns.length; c++) {
                            if (columns[c].realName != null &&
                                columns[c].realName.equalsIgnoreCase(name)) {
                                columns[c].isKey = true;
                                isKeyed = true;
                                break;
                            }
                        }
                    }
                }
            }
            // Report any errors found
            statement.getMessages().checkErrors();
        }
        //
        // Final fall back make all columns pseudo keys!
        // Sybase seems to do this automatically.
        //
        if (!isKeyed) {
            for (int i = 0; i < columns.length; i++) {
                String type = columns[i].sqlType;
                if (!type.equals("ntext") &&
                        !type.equals("text") &&
                        !type.equals("image") &&
                        !type.equals("timestamp") &&
                        columns[i].tableName != null) {
                    columns[i].isKey = true;
                    isKeyed = true;
                }
            }
        }

        return (tableSet.size() == 1 && isKeyed);
    }

    /**
     * Fetches the next result row from the internal row array.
     *
     * @param rowNum the row number to fetch
     * @return <code>true</code> if a result set row is returned
     * @throws SQLException if an error occurs
     */
    private boolean cursorFetch(int rowNum)
            throws SQLException {
        rowUpdated = false;
        //
        if (cursorName != null) {
            //
            // Using a conventional forward only server cursor
            //
            if (!cursorTds.getNextRow()) {
                // Need to fetch more rows from server
                StringBuffer sql = new StringBuffer(128);
                if (isSybase && sizeChanged) {
                    // Sybase allows us to set a fetch size
                    sql.append("SET CURSOR ROWS ").append(fetchSize);
                    sql.append(" FOR ").append(cursorName);
                    sql.append("\r\n");
                }
                sql.append("FETCH ").append(cursorName);
                // Get the next row or block of rows.
                cursorTds.executeSQL(sql.toString(), null, null, false,
                        statement.getQueryTimeout(), statement.getMaxRows(),
                        statement.getMaxFieldSize(), true);
                while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse());

                sizeChanged = false; // Indicate fetch size updated

                if (!cursorTds.isResultSet() || !cursorTds.getNextRow()) {
                    pos = POS_AFTER_LAST;
                    currentRow = null;
                    statement.getMessages().checkErrors();
                    return false;
                }
            }
            currentRow = statement.getTds().getRowData();
            pos++;
            rowsInResult = pos;
            statement.getMessages().checkErrors();

            return currentRow != null;

        }
        //
        // JDBC2 style Scrollable and/or Updateable cursor
        //
        if (rowsInResult == 0) {
            pos = POS_BEFORE_FIRST;
            currentRow = null;
            return false;
        }
        if (rowNum == pos) {
            // On current row
            //
            return true;
        }
        if (rowNum < 1) {
            currentRow = null;
            pos = POS_BEFORE_FIRST;
            return false;
        }
        if (rowNum > rowsInResult) {
            currentRow = null;
            pos = POS_AFTER_LAST;
            return false;
        }
        pos = rowNum;
        currentRow = (Object[])rowData.get(rowNum-1);
        rowDeleted = currentRow == null;

        if (resultSetType >= ResultSet.TYPE_SCROLL_SENSITIVE &&
            currentRow != null) {
            refreshRow();
        }

        return true;
    }

    /**
     * Closes the result set.
     */
    private void cursorClose() throws SQLException {
        if (cursorName != null) {
            statement.clearWarnings();
            String sql;
            if (isSybase) {
                sql = "CLOSE " + cursorName +
                      "\r\nDEALLOCATE CURSOR " + cursorName;
            } else {
                sql = "CLOSE " + cursorName +
                      "\r\nDEALLOCATE " + cursorName;
            }
            cursorTds.submitSQL(sql);
        }
        rowData = null;