java.sql.Statement or java.sql.PreparedStatement - scrollable resultset with parameters

Question

In my java app, it seems to use parameters in my query to the database, I need to utilize the PreparedStatement. However at the same time, I would like to use the resultset from the statement in forward/backward mode (scrollable) PreparedStatement does not seem to offer setting the scrollable mode Statement does not seem to offer parameters.

Seems like a basic question..but nothing jumping out at me (other than using Statement and constructing the SQL without parameters). Is there really no way to supply parameters to a Statement..or have a preparedstatement scrollable? Am I missing something?

            conn = Utility.getConnection();

            tmpSQL = "SELECT * FROM " + baseTable + " WHERE " + filterCriteria
                    + " ORDER BY " + sortCriteria;

//method 1

Statement stmt = conn.createStatement(
                       ResultSet.TYPE_SCROLL_INSENSITIVE,
                       ResultSet.CONCUR_UPDATABLE);

rset = stmt.executeQuery(tmpSQL);  //not using any parameters!


//method 2

            PreparedStatement pStatement = conn.prepareStatement(tmpSQL);  //not scrollable!

            if (params != null)
                for (int i = 0; i < params.size(); i++) {

                    pStatement.setString(i + 1,
                            ((Parameter) params.get(i)).getStringValue());

                }

            rset = pStatement.executeQuery();

Answer 1

Use

PreparedStatement pStatement = conn.prepareStatement(tmpSQL,
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);

Java Doc Info

Then to get the count of records in your ResultSet, use rset.last() followed by rset.getRow(). Then use rset.beforeFirst() to put the cursor back to where it was initially.

Answer 2

Some initial background comments

Scrollability is mostly depending on the underlying database. Even though JDBC has a method to scroll back, it is not implemented e.g. in Oracle JDBC driver.

I would suggest to avoid of scrolling the result set. In fact even if it works for some databases, it is quite inefficient to implement. Also inefficient to use on the GUI, since each scrolling would then trigger a database operation, which is slow.

The usual approach is to load all rows to a container (e.g. List<...> ) and process that, if you have a moderate number of rows (say up to 1000 rows). If you have a lot more rows, then:

• think it over if you really need to read that many rows. For example, if this is a GUI list, it may not make sense loading 1 million rows, since the human user will not one-by-one scroll trough all 1 million rows. Probably a better filtering and/or pagination would make sense.
• if you really need all the rows for business side processing, then think it over. Pulling all rows from the database to the app for processing is a super-inefficient programming pattern. Use stored procedures, or packages (Oracle) to process your data on the database side.
• but if you really really need to pull like 1 millon rows to the app for processing, do the processing in a streaming-manner. I.e. instead of first fetching 1 million rows to the memory and then processing it, fetch one row, process it, fetch another row, process it. This also explains why back-scrolling is usually not supported: that would require the driver or the db to actually hold in memory one million rows of the result of your select, because you might want to scroll back.

To solve your question

To get the count of records, execute a separate statement with select count(*). Then execute another select to actually read the records and fetch them (only forward).

It is much faster than reading all records just to count them.