ResultSet.beforeFirst() gets ignored in Java

Question

I would like to create DefaultTableModel from ResultSet. To do that, I need Object[][].

For that, I have to specify the size of the object before I iterate through the table: I go to the rs.last(), then rs.getRow(), then rs.beforeFirst(); After that, the rs.next() does not executes in the while cycle. What am I doing wrong?

public static DefaultTableModel buildTableModel(ResultSet _resultSet) {
    ResultSetMetaData metaData;
    Object[] columnNames = null;
    Object[][] tableData = null;
    int columnCount;
    int currentRowNumber = 0;

    try {
        metaData = _resultSet.getMetaData();
        columnCount = metaData.getColumnCount();
        columnNames = new Object[columnCount];

        _resultSet.last();
        tableData = new Object[_resultSet.getRow()][columnCount];
        _resultSet.beforeFirst();

        for (int currentColumn = 0; currentColumn <= columnCount; currentColumn++) {
            columnNames[currentColumn] = metaData.getColumnName(currentColumn + 1);
        }

        while (_resultSet.next()) {
            for (int columnIndex = 0; columnIndex <= columnCount; columnIndex++) {
                tableData[currentRowNumber][columnIndex] = _resultSet.getObject(columnIndex + 1);
            }

            currentRowNumber++;
        }
    } catch (SQLException ex) {
        System.out.println("bad");
    }

    return new DefaultTableModel(tableData, columnNames);

}

does this not display "bad" after the SQLException is thrown? It is indicated to at least call `ex.printStackTrace()` in the `catch` block. The Exception has valuable information to debug your program, do not hide it! — user85421, Nov 16 '13 at 09:41
I found it: This has nothing to do with `RS` or `beforeFirst()`. The problem was the `<=` in the `for` cycle, because column numbering does not start from `0`. It starts from `1`. — Ágota Horváth, Nov 16 '13 at 09:54
it should have thrown an exception anyway... don't ignore them! — user85421, Nov 16 '13 at 18:19

score 2 · Answer 1 · answered Nov 16 '13 at 09:48

Probably your ResultSet is not scroll insensitive, that is, it can only be traversed forward.
See the documentation here:

[...] A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. [...]

To create a bi-directional one, do something like:

Connection conn = DriverManager.getConnection(...);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ...);
ResultSet rset = stmt.executeQuery(sql);

score 1 · Answer 2 · answered Nov 16 '13 at 10:09

At first you need to print column name from ResultSetMetaData. Than you apply _resultSet.last(); and _resultSet.beforeFirst();. This way it has been working my machine.

public static DefaultTableModel buildTableModel(ResultSet _resultSet) {
    ResultSetMetaData metaData;
    Object[] columnNames = null;
    Object[][] tableData = null;
    int columnCount;
    int currentRowNumber = 0;

    try {
        metaData = _resultSet.getMetaData();
        columnCount = metaData.getColumnCount();
        columnNames = new Object[columnCount];
        // Print column here.
        for (int currentColumn = 0; currentColumn <= columnCount; currentColumn++) {
            columnNames[currentColumn] = metaData.getColumnName(currentColumn + 1);
        }
        tableData = new Object[_resultSet.getRow()][columnCount];

        //Here point resultSet cursor to last and beforeFirst.
        _resultSet.last();

        _resultSet.beforeFirst();

        // After swaping the above part. Now it will enter on while loop.
        while (_resultSet.next()) {
            for (int columnIndex = 0; columnIndex <= columnCount; columnIndex++) {
                tableData[currentRowNumber][columnIndex] = _resultSet.getObject(columnIndex + 1);
            }

            currentRowNumber++;
        }
    } catch (SQLException ex) {
        System.out.println("bad");
    }

    return new DefaultTableModel(tableData, columnNames);

}

ResultSet.beforeFirst() gets ignored in Java

2 Answers2