mysql 用小的结果集驱动大的结果集 数据库结果集是什么

ResultSet (结果集)

RSType 和 RSConcurrency

当对数据库进行查询操作的时候, 数据库返回的数据通过 ResultSet 接口获取. ResultSet 内部管理了一个 cursor(游标), cursor 指向当前要读的数据, ResultSet 提供了以下三类接口:

• 游标移动接口, 用来操作移动游标.
• 获取数据接口, 用来从当前游标指向位置的数据
• 更新数据接口, 用来更新当前游标指向位置的数据, 并可以更改对应数据库中的数据.

ResultSet 的类型:

在创建 Statement 的时候, 可以添加两个参数.

• createStatement(int RSType, int RSConcurrency);
• prepareStatement(String SQL, int RSType, int RSConcurrency);
• prepareCall(String sql, int RSType, int RSConcurrency);

其中第一个参数RSType主要描述两件事:

• Cusor 是否可以前后移动
• 已经取出的结果集对数据库的改动是否需要更新

RSType 的值可以取下面三个:

• ResultSet.TYPE_FORWARD_ONLY: Cursor 只能往前移动, 默认值
• ResultSet.TYPE_SCROLL_INSENSITIVE: Cursor 可以前后移动, 但是对于数据库的改动不关心.
• ResultSet.TYPE_SCROLL_SENSITIVE: Cursor 可以前后移动, 并且当数据库发生改动的时候, ResultSet也会随之更新.

RSConcurrency 用来描述我们是否可以更新结果集中的数据到数据库. 有两个值可以用:

• ResultSet.CONCUR_READ_ONLY: 结果集是只读的
• ResultSet.CONCUR_UPDATABLE: 结果集是可以更新的.

注意, 并不是所有的数据库都支持这些类型, 可以查看数据库驱动的支持情况:

public static void printResultSetSupport(Connection conn) {
        DatabaseMetaData md = null;
        try {
            md = conn.getMetaData();
            // Verify ResultSet's type
            System.out.println("Supports TYPE_FORWARD_ONLY: " + md.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
            System.out.println("Supports TYPE_SCROLL_INSENSITIVE: " + md.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
            System.out.println("Supports TYPE_SCROLL_SENSITIVE: " + md.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
            // Verify ResultSet's concurrency
            System.out.println("Supports CONCUR_READ_ONLY for TYPE_FORWARD_ONLY: " + md.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
            System.out.println("Supports CONCUR_UPDATABLE for TYPE_FORWARD_ONLY: " + md.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE));

            System.out.println("Supports CONCUR_READ_ONLY for TYPE_SCROLL_INSENSITIVE: " + md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
            System.out.println("Supports CONCUR_UPDATABLE for TYPE_SCROLL_INSENSITIVE: " + md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));

            System.out.println("Supports CONCUR_READ_ONLY for TYPE_SCROLL_SENSITIVE: " + md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
            System.out.println("Supports CONCUR_UPDATABLE for TYPE_SCROLL_SENSITIVE: " + md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));
        } catch(SQLException e) {
            printSQLException(e);
        }
    }

MySQL 的执行结果如下:

Supports TYPE_FORWARD_ONLY: false
Supports TYPE_SCROLL_INSENSITIVE: true
Supports TYPE_SCROLL_SENSITIVE: false
Supports CONCUR_READ_ONLY for TYPE_FORWARD_ONLY: true
Supports CONCUR_UPDATABLE for TYPE_FORWARD_ONLY: true
Supports CONCUR_READ_ONLY for TYPE_SCROLL_INSENSITIVE: true
Supports CONCUR_UPDATABLE for TYPE_SCROLL_INSENSITIVE: true
Supports CONCUR_READ_ONLY for TYPE_SCROLL_SENSITIVE: false
Supports CONCUR_UPDATABLE for TYPE_SCROLL_SENSITIVE: false

__ 我在测是的时候, 发现 MySQL 默认的 Cursor 竟然也可以往前往后移动! 难道 MySQL 默认的是 TYPE_SCROLL_INSENSITIVE?

其实, 不是的, MYSQL 会缓存结果集, 导致默认类型的 TYPE_FORWARD_ONLY 升级成了可以前后移动的. 只要我们调整一下将结果集的缓存设为最小, cursor 的行为就正常了:

tatement s = dbConnection.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, 
        ResultSet.CONCUR_READ_ONLY);
s.setFetchSize(Integer.MIN_VALUE);

JavaDB 的执行结果如下:

Supports TYPE_FORWARD_ONLY: true
Supports TYPE_SCROLL_INSENSITIVE: true
Supports TYPE_SCROLL_SENSITIVE: false
Supports CONCUR_READ_ONLY for TYPE_FORWARD_ONLY: true
Supports CONCUR_UPDATABLE for TYPE_FORWARD_ONLY: true
Supports CONCUR_READ_ONLY for TYPE_SCROLL_INSENSITIVE: true
Supports CONCUR_UPDATABLE for TYPE_SCROLL_INSENSITIVE: true
Supports CONCUR_READ_ONLY for TYPE_SCROLL_SENSITIVE: false
Supports CONCUR_UPDATABLE for TYPE_SCROLL_SENSITIVE: false

Cursor Holdability

关于 Cursor 其实还有一个属性, 是关于事务提交后, 是否需要关闭结果集的问题. 相关 API 为:

conn.setHoldability();

来改变这种行为, 可以传入的参数有:

• HOLD_CURSORS_OVER_COMMIT: 当调用 Connection.commit()后, ResultSet 将不会关闭, 一般用于只读的结果集中.
• CLOSE_CURSORS_AT_COMMIT : 当调用 Connection.commit()后, ResultSet 将会关闭.

JDBC 没有规定默认的值, 但是有相应的 API 来获取默认值以及支持情况:

public static void printCursorHoldability(Connection conn) throws SQLException {
        DatabaseMetaData md = conn.getMetaData();
        System.out.println("-- ResultSet Cursor Holdability --");
        int holdability = md.getResultSetHoldability();
        if (holdability == ResultSet.CLOSE_CURSORS_AT_COMMIT) {
            System.out.println("Default: CLOSE_CURSORS_AT_COMMIT");
        } else if (holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT) {
            System.out.println("Default: ResultSet.HOLD_CURSORS_OVER_COMMIT");
        }
        System.out.println("Supports CLOSE_CURSORS_AT_COMMIT: "
                + md.supportsResultSetHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT));
        System.out.println("Supports HOLD_CURSORS_OVER_COMMIT: "
                + md.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT));
    }

MySQL 结果是:

-- ResultSet Cursor Holdability --
Default: ResultSet.HOLD_CURSORS_OVER_COMMIT
Supports CLOSE_CURSORS_AT_COMMIT: false
Supports HOLD_CURSORS_OVER_COMMIT: true

而 JavaDB 的结果是:

-- ResultSet Cursor Holdability --
Default: ResultSet.HOLD_CURSORS_OVER_COMMIT
Supports CLOSE_CURSORS_AT_COMMIT: true
Supports HOLD_CURSORS_OVER_COMMIT: true