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.