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();

Resultset.updateString(index, value) and ResultSet,updateRow() may help — BigMike
– BigMike, Commented Feb 23, 2016 at 15:27
maybe i should expand on the reason i need scrollable... i just wanted to get a record count of the result set before doing my other operations. maybe there is a better way to get that without needing scrollable (though i doubt it) — da Bich
– da Bich, Commented Feb 23, 2016 at 15:29
consider if the cost of a single select count(*) issued before your processing would hurt performance, if not, go with it. If your processing is a single statement, you can get number of affected rows after the statement is executed. — BigMike
– BigMike, Commented Feb 23, 2016 at 15:32
@LanceJava, i saw that too.. but it seems my library ( java.sql.Connection.prepareStatement) only allows for a second argument for preparestatement that is something to do with autogeneratedKeys.. not sure how I can use that other method? — da Bich
– da Bich, Commented Feb 23, 2016 at 15:34

castletheperson · Accepted Answer · 2016-02-23 15:54:48Z

13

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.

edited Feb 23, 2016 at 15:54

answered Feb 23, 2016 at 15:32

castletheperson

33.7k11 gold badges74 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

castletheperson Over a year ago

You only need ResultSet.CONCUR_UPDATABLE if you plan on using the updateXXX methods on your ResultSet object. Otherwise, just put ResultSet.CONCUR_READ_ONLY

Gee Bee · Accepted Answer · 2016-02-23 15:49:50Z

1

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.

answered Feb 23, 2016 at 15:49

Gee Bee

1,78416 silver badges17 bronze badges

11 Comments

castletheperson Over a year ago

You don't have to read all the records to count them. See my answer.

Gee Bee Over a year ago

I am saying the same thing :) do select count( * ) first to count the records and then use a standard forward only result set to read the actual data. I believe the actual data is needed later no? Otherwise what is the point to scroll back to the beginning to read it all over?

castletheperson Over a year ago

The point to scrolling back to the beginning is so that you can use the data later. This is what scrolling is meant to do.

Gee Bee Over a year ago

Ok, 4castle, I might not perfectly understand your point. Can you elaborate please? I think that for counting the number of rows, no scrolling and nothing is needed, just select count( * ). Any other solutions are so resource heavy that we can call them pointless. For reading the data, we shall use a standard forward-only non- updateable read-only result set, because it is the most efficient and supported by all JDBC implementations. Scrolling back is actually not supported in many JDBC implementations, therefore I suggest not even consider using such as fragile solution.

Gee Bee Over a year ago

(Comments are restricted in length) so the final comment: "I've never seen the need for a scrollable cursor (implies WAY too much procedural code is involved)." - Tom Kyte, asktom.oracle.com/pls/apex/… And this must be correct. You shall use the power of the 4th generation language, SQL to process your data, instead of pulling all rows to the application side, and read it trough back and forth. Doing that does not scale.

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Some initial background comments

To solve your question

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Some initial background comments

To solve your question

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related