CODEWITHSUNDEEP
javadatabasejdbc
aviad
aviad

Reputation: 8278

Is the order of the rows in the ResultSet constant for the same SQL query via JDBC on the same state of DB data?

Trying to write a job that executes SQL query in Java using JDBC drivers (the DB vendors can be either Oracle, DB2 or Postgres). The query does not really matter. Let’s say it filters on certain values in few columns in 1 DB table and the result is few thousand rows.

For each row in the ResultSet I need to do some logic and sometimes that can fail.

I have a cursor position so, I “remember” last successfully processed row position.

Now I want to implement a “Resume” functionality in case of failure in order not to process again the entire ResultSet.

I went to JDBC spec of Java 8 and found nothing about the order of the rows (is it the same for the same query on the same data or not)? Also failed to find anything in DB vendors specs.

Anyone who could hint where to look for the answer about row order predictability?

Upvotes: 0

Views: 887

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

You can guarantee the order of rows by including an ORDER BY clause that includes all of the columns required to uniquely identify a row. In fact, that's the only way to guarantee the order from repeated invocations of a SELECT statement, even if nothing has changed in the database. Without an unambiguous ORDER BY clause the database engine is free to return the rows in whatever order is most convenient for it at that particular moment.

Consider a simple example:

You are the only user of the database. The database engine has a row cache in memory that can hold the last 1000 rows retrieved. The database server has just been restarted, so the cache is empty. You SELECT * FROM tablename and the database engine retrieves 2000 rows, the last 1000 of which remain in the cache. Then you do SELECT * FROM tablename again. The database engine checks the cache and finds the 1000 rows from the previous query, so it immediately returns them because in doing so it won't have to hit the disk again. Then it proceeds to go find other 1000 rows. The net result is that the 1000 rows that were returned last for the initial SELECT are actually returned first for the subsequent SELECT.

Upvotes: 1

Related Questions