JDBC之关于数据库操作回滚研究（JDBC之四）

查询数据库是否支持游标回滚操作

try {

        DatabaseMetaData dmd = connection.getMetaData();

        if (dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)) {

            // Insensitive scrollable result sets are supported

        }

        if (dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {

            // Sensitive scrollable result sets are supported

        }

        if (!dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)

            && !dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {

            // Updatable result sets are not supported

        }

    } catch (SQLException e) {

    }

创建一个回滚得数据结果集

try {

        // Create an insensitive scrollable result set

        Statement stmt = connection.createStatement(

            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        // Create a sensitive scrollable result set

        stmt = connection.createStatement(

            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

    } catch (SQLException e) {

    }

查询一个数据结果集的回滚类型

try {

        // Get type of the result set

        int type = resultSet.getType();

        if (type == ResultSet.TYPE_SCROLL_INSENSITIVE

              || type == ResultSet.TYPE_SCROLL_SENSITIVE) {

            // Result set is scrollable

        } else {

            // Result set is not scrollable

        }

    } catch (SQLException e) {

    }

移动游标到指定的位置

try {

        // Create a scrollable result set

        Statement stmt = connection.createStatement(

            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

        // Move cursor forward

        while (resultSet.next()) {

            // Get data at cursor

            String s = resultSet.getString(1);

        }

        // Move cursor backward

        while (resultSet.previous()) {

            // Get data at cursor

            String s = resultSet.getString(1);

        }

        // Move cursor to the first row

        resultSet.first();

        // Move cursor to the last row

        resultSet.last();

        // Move cursor to the end, after the last row

        resultSet.afterLast();

        // Move cursor to the beginning, before the first row.

        // cursor position is 0.

        resultSet.beforeFirst();

        // Move cursor to the second row

        resultSet.absolute(2);

        // Move cursor to the last row

        resultSet.absolute(-1);

        // Move cursor to the second last row

        resultSet.absolute(-2);

        // Move cursor down 5 rows from the current row.  If this moves

        // cursor beyond the last row, cursor is put after the last row

        resultSet.relative(5);

        // Move cursor up 3 rows from the current row.  If this moves

        // cursor beyond the first row, cursor is put before the first row

        resultSet.relative(-3);

    } catch (SQLException e) {

    }

获取游标的位置

try {

        // Create a scrollable result set

        Statement stmt = connection.createStatement(

            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

        // Get cursor position

        int pos = resultSet.getRow();            // 0

        boolean b = resultSet.isBeforeFirst();   // true

        // Move cursor to the first row

        resultSet.next();

        // Get cursor position

        pos = resultSet.getRow();                // 1

        b = resultSet.isFirst();                 // true

        // Move cursor to the last row

        resultSet.last();

        // Get cursor position

        pos = resultSet.getRow();                // If table has 10 rows, value would be 10

        b = resultSet.isLast();                  // true

        // Move cursor past last row

        resultSet.afterLast();

        // Get cursor position

        pos = resultSet.getRow();                // If table has 10 rows, value would be 11

        b = resultSet.isAfterLast();             // true

    } catch (SQLException e) {

    }

获取数据库表中全部的行数

try {

        // Create a scrollable result set

        Statement stmt = connection.createStatement(

            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

        // Move to the end of the result set

        resultSet.last();

        // Get the row number of the last row which is also the row count

        int rowCount = resultSet.getRow();

    } catch (SQLException e) {

}