mscursorresultset.java

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

// jTDS JDBC Driver for Microsoft SQL Server and Sybase
// Copyright (C) 2004 The jTDS Project
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
package net.sourceforge.jtds.jdbc;

import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Types;
import java.sql.ResultSet;

/**
 * This class extends the JtdsResultSet to support scrollable and or
 * updateable cursors on Microsoft servers.
 * <p>The undocumented Microsoft sp_cursor procedures are used.
 * <p>
 * Implementation notes:
 * <ol>
 * <li>All of Alin's cursor result set logic is incorporated here.
 * <li>This logic was originally implemented in the JtdsResultSet class but on reflection
 * it seems that Alin's original approch of having a dedicated cursor class leads to a more
 * flexible and maintainable design.
 * </ol>
 *
 * @author Alin Sinpalean
 * @author Mike Hutchinson
 * @version $Id: MSCursorResultSet.java,v 1.55 2005/06/15 14:56:58 alin_sinpalean Exp $
 */
public class MSCursorResultSet extends JtdsResultSet {
    /*
     * Constants
     */
    private static final Integer FETCH_FIRST    = new Integer(1);
    private static final Integer FETCH_NEXT     = new Integer(2);
    private static final Integer FETCH_PREVIOUS = new Integer(4);
    private static final Integer FETCH_LAST     = new Integer(8);
    private static final Integer FETCH_ABSOLUTE = new Integer(16);
    private static final Integer FETCH_RELATIVE = new Integer(32);
    private static final Integer FETCH_REPEAT   = new Integer(128);
    private static final Integer FETCH_INFO     = new Integer(256);

    private static final int CURSOR_TYPE_KEYSET = 0x01;
    private static final int CURSOR_TYPE_DYNAMIC = 0x02;
    private static final int CURSOR_TYPE_FORWARD = 0x04;
    private static final int CURSOR_TYPE_STATIC = 0x08;
    private static final int CURSOR_TYPE_FASTFORWARDONLY = 0x10;
    private static final int CURSOR_TYPE_PARAMETERIZED = 0x1000;
    private static final int CURSOR_TYPE_AUTO_FETCH = 0x2000;

    private static final int CURSOR_CONCUR_READ_ONLY = 1;
    private static final int CURSOR_CONCUR_SCROLL_LOCKS = 2;
    private static final int CURSOR_CONCUR_OPTIMISTIC = 4;
    private static final int CURSOR_CONCUR_OPTIMISTIC_VALUES = 8;

    private static final Integer CURSOR_OP_INSERT = new Integer(4);
    private static final Integer CURSOR_OP_UPDATE = new Integer(33);
    private static final Integer CURSOR_OP_DELETE = new Integer(34);

    /**
     * The row is dirty and needs to be reloaded (internal state).
     */
    private static final Integer SQL_ROW_DIRTY   = new Integer(0);

    /**
     * The row is valid.
     */
    private static final Integer SQL_ROW_SUCCESS = new Integer(1);

    /**
     * The row has been deleted.
     */
    private static final Integer SQL_ROW_DELETED = new Integer(2);

    /*
     * Instance variables.
     */
    /** Set when <code>moveToInsertRow()</code> was called. */
    private boolean onInsertRow;
    /** The "insert row". */
    private ParamInfo[] insertRow;
    /** The "update row". */
    private ParamInfo[] updateRow;
    /** The row cache used instead {@link #currentRow}. */
    private Object[][] rowCache;
    /** Actual position of the cursor. */
    private int cursorPos;

    //
    // Fixed sp_XXX parameters
    //
    /** Cursor handle parameter. */
    private final ParamInfo PARAM_CURSOR_HANDLE = new ParamInfo(Types.INTEGER, null, ParamInfo.INPUT);

    /** <code>sp_cursorfetch</code> fetchtype parameter. */
    private final ParamInfo PARAM_FETCHTYPE = new ParamInfo(Types.INTEGER, null, ParamInfo.INPUT);

    /** <code>sp_cursorfetch</code> rownum IN parameter (for actual fetches). */
    private final ParamInfo PARAM_ROWNUM_IN = new ParamInfo(Types.INTEGER, null, ParamInfo.INPUT);

    /** <code>sp_cursorfetch</code> numrows IN parameter (for actual fetches). */
    private final ParamInfo PARAM_NUMROWS_IN = new ParamInfo(Types.INTEGER, null, ParamInfo.INPUT);

    /** <code>sp_cursorfetch</code> rownum OUT parameter (for FETCH_INFO). */
    private final ParamInfo PARAM_ROWNUM_OUT = new ParamInfo(Types.INTEGER, null, ParamInfo.OUTPUT);

    /** <code>sp_cursorfetch</code> numrows OUT parameter (for FETCH_INFO). */
    private final ParamInfo PARAM_NUMROWS_OUT = new ParamInfo(Types.INTEGER, null, ParamInfo.OUTPUT);

    /** <code>sp_cursor</code> optype parameter. */
    private final ParamInfo PARAM_OPTYPE = new ParamInfo(Types.INTEGER, null, ParamInfo.INPUT);

    /** <code>sp_cursor</code> rownum parameter. */
    private final ParamInfo PARAM_ROWNUM = new ParamInfo(Types.INTEGER, new Integer(1), ParamInfo.INPUT);

    /** <code>sp_cursor</code> table parameter. */
    private final ParamInfo PARAM_TABLE = new ParamInfo(Types.VARCHAR, "", ParamInfo.UNICODE);

    /**
     * Construct a cursor result set using Microsoft sp_cursorcreate etc.
     *
     * @param statement The parent statement object or null.
     * @param resultSetType one of FORWARD_ONLY, SCROLL_INSENSITIVE, SCROLL_SENSITIVE.
     * @param concurrency One of CONCUR_READ_ONLY, CONCUR_UPDATE.
     * @throws SQLException
     */
    MSCursorResultSet(JtdsStatement statement,
                      String sql,
                      String procName,
                      ParamInfo[] procedureParams,
                      int resultSetType,
                      int concurrency)
            throws SQLException {
        super(statement, resultSetType, concurrency, null);

        PARAM_NUMROWS_IN.value = new Integer(fetchSize);
        rowCache = new Object[fetchSize][];

        cursorCreate(sql, procName, procedureParams);
    }

    /**
     * Set the specified column's data value.
     *
     * @param colIndex index of the column
     * @param value    new column value
     * @return the value, possibly converted to an internal type
     */
    protected Object setColValue(int colIndex, int jdbcType, Object value, int length)
            throws SQLException {

        value = super.setColValue(colIndex, jdbcType, value, length);

        if (!onInsertRow && getCurrentRow() == null) {
            throw new SQLException(Messages.get("error.resultset.norow"), "24000");
        }
        colIndex--;
        ParamInfo pi;
        ColInfo ci = columns[colIndex];

        if (onInsertRow) {
            pi = insertRow[colIndex];
        } else {
            if (updateRow == null) {
                updateRow = new ParamInfo[columnCount];
            }
            pi = updateRow[colIndex];
        }

        if (pi == null) {
            pi = new ParamInfo(-1, TdsData.isUnicode(ci));
            pi.name = '@'+ci.realName;
            pi.collation = ci.collation;
            pi.charsetInfo = ci.charsetInfo;
            if (onInsertRow) {
                insertRow[colIndex] = pi;
            } else {
                updateRow[colIndex] = pi;
            }
        }

        if (value == null) {
            pi.value    = null;
            pi.length   = 0;
            pi.jdbcType = ci.jdbcType;
            pi.isSet    = true;
        } else {
            pi.value     = value;
            pi.length    = length;
            pi.isSet     = true;
            pi.jdbcType  = jdbcType;
            pi.isUnicode = ci.sqlType.equals("ntext")
                    || ci.sqlType.equals("nchar")
                    || ci.sqlType.equals("nvarchar");
        }

        return value;
    }

    /**
     * Get the specified column's data item.
     *
     * @param index the column index in the row
     * @return the column value as an <code>Object</code>
     * @throws SQLException if the index is out of bounds or there is no
     *                      current row
     */
    protected Object getColumn(int index) throws SQLException {
        checkOpen();

        if (index < 1 || index > columnCount) {
            throw new SQLException(Messages.get("error.resultset.colindex",
                    Integer.toString(index)),
                    "07009");
        }

        Object[] currentRow;
        if (onInsertRow || (currentRow = getCurrentRow()) == null) {
            throw new SQLException(
                    Messages.get("error.resultset.norow"), "24000");
        }

        if (SQL_ROW_DIRTY.equals(currentRow[columns.length - 1])) {
            cursorFetch(FETCH_REPEAT, 0);
            currentRow = getCurrentRow();
        }

        Object data = currentRow[index - 1];
        wasNull = data == null;

        return data;
    }

    /**
     * Translates a JDBC result set type into SQL Server native @scrollOpt value
     * for use with stored procedures such as sp_cursoropen, sp_cursorprepare
     * or sp_cursorprepexec.
     *
     * @param resultSetType        JDBC result set type (one of the
     *                             <code>ResultSet.TYPE_<i>XXX</i></code>
     *                             values)
     * @param resultSetConcurrency JDBC result set concurrency (one of the
     *                             <code>ResultSet.CONCUR_<i>XXX</i></code>
     *                             values)
     * @return a value for the @scrollOpt parameter
     */
    static int getCursorScrollOpt(int resultSetType,
                                  int resultSetConcurrency,
                                  boolean parameterized) {
        int scrollOpt;

        switch (resultSetType) {
            case TYPE_SCROLL_INSENSITIVE:
                scrollOpt = CURSOR_TYPE_STATIC;
                break;

            case TYPE_SCROLL_SENSITIVE:
                scrollOpt = CURSOR_TYPE_KEYSET;
                break;

            case TYPE_SCROLL_SENSITIVE + 1:
                scrollOpt = CURSOR_TYPE_DYNAMIC;
                break;

            case TYPE_FORWARD_ONLY:
            default:
                scrollOpt = (resultSetConcurrency == CONCUR_READ_ONLY)
                        ? (CURSOR_TYPE_FASTFORWARDONLY | CURSOR_TYPE_AUTO_FETCH)
                        : CURSOR_TYPE_FORWARD;
                break;
        }

        // If using sp_cursoropen need to set a flag on scrollOpt.
        // The 0x1000 tells the server that there is a parameter
        // definition and user parameters present. If this flag is
        // not set the driver will ignore the additional parameters.
        if (parameterized) {
            scrollOpt |= CURSOR_TYPE_PARAMETERIZED;
        }

        return scrollOpt;
    }

    /**
     * Translates a JDBC result set concurrency into SQL Server native @ccOpt
     * value for use with stored procedures such as sp_cursoropen,
     * sp_cursorprepare or sp_cursorprepexec.
     *
     * @param resultSetConcurrency JDBC result set concurrency (one of the
     *                             <code>ResultSet.CONCUR_<i>XXX</i></code>
     *                             values)
     * @return a value for the @scrollOpt parameter
     */
    static int getCursorConcurrencyOpt(int resultSetConcurrency) {
        switch (resultSetConcurrency) {
            case CONCUR_UPDATABLE:
                return CURSOR_CONCUR_OPTIMISTIC;

            case CONCUR_UPDATABLE + 1:
                return CURSOR_CONCUR_SCROLL_LOCKS;

            case CONCUR_UPDATABLE + 2:
                return CURSOR_CONCUR_OPTIMISTIC_VALUES;

            case CONCUR_READ_ONLY:
            default:
                return CURSOR_CONCUR_READ_ONLY;
        }
    }

    /**
     * Create a new Cursor result set using the internal sp_cursoropen procedure.
     *
     * @param sql The SQL SELECT statement.
     * @param procName Optional procedure name for cursors based on a stored procedure.
     * @param parameters Optional stored procedure parameters.
     * @throws SQLException
     */
    private void cursorCreate(String sql,
                              String procName,
                              ParamInfo[] parameters)
            throws SQLException {
        TdsCore tds = statement.getTds();
        int prepareSql = statement.connection.getPrepareSql();
        Integer prepStmtHandle = null;

        //
        // If this cursor is going to be a named forward only cursor
        // force the concurrency to be updateable.
        // TODO: Cursor is updateable unless user appends FOR READ to the select
        // but we would need to parse the SQL to discover this.
        //
        if (cursorName != null
            && resultSetType == ResultSet.TYPE_FORWARD_ONLY
            && concurrency == ResultSet.CONCUR_READ_ONLY) {
            concurrency = ResultSet.CONCUR_UPDATABLE;
        }
        //
        // Simplify future tests for parameters
        //
        if (parameters != null && parameters.length == 0) {
            parameters = null;
        }
        //
        // SQL 6.5 does not support stored procs (with params) in the sp_cursor call
        // will need to substitute any parameter values into the SQL.
        //
        if (tds.getTdsVersion() == Driver.TDS42) {
            prepareSql = TdsCore.UNPREPARED;
            if (parameters != null) {
                procName = null;
            }
        }
        //
        // If we are running in unprepare mode and there are parameters
        // substitute these into the SQL statement now.
        //
        if (parameters != null && prepareSql == TdsCore.UNPREPARED) {
            sql = Support.substituteParameters(sql, parameters, tds.getTdsVersion());
            parameters = null;
        }
        //
        // For most prepare modes we need to substitute parameter
        // names for the ? markers.
        //
        if (parameters != null) {
            if (procName == null || !procName.startsWith("#jtds")) {
                sql = Support.substituteParamMarkers(sql, parameters);
            }
        }
        //
        // There are generally three situations in which procName is not null:
        // 1. Running in prepareSQL=1 and contains a temp proc name e.g. #jtds00001
        //    in which case we need to generate an SQL statement exec #jtds...
        // 2. Running in prepareSQL=4 and contains an existing statement handle.
        // 3. CallableStatement in which case the SQL string has a valid exec
        //    statement and we can ignore procName.
        //
        if (procName != null) {
            if (procName.startsWith("#jtds")) {
                StringBuffer buf = new StringBuffer(procName.length() + 16
                        + (parameters != null ? parameters.length * 5 : 0));
                buf.append("EXEC ").append(procName).append(' ');
                for (int i = 0; parameters != null && i < parameters.length; i++) {
                    if (i != 0) {
                        buf.append(',');
                    }
                    if (parameters[i].name != null) {
                        buf.append(parameters[i].name);
                    } else {
                        buf.append("@P").append(i);
                    }