The result set contains zero or more rows which are retrieved and examined individually using something called a cursor. Just as a GUI cursor marks your position on the screen, a database cursor indicates the row of the result set that you are currently looking at. A cursor is usually implemented as an unsigned integer that holds the offset into the file containing your result set. It has enough knowledge to move forward row by row through the result set.

Database management systems typically provide a cursor to the SQL programmer automatically. The programmer can use it to iterate through the result set. JDBC 2 upgrades the features of a cursor available to Java. Now you can move the cursor backward as well as forward, providing the underlying database supports that. You can also move the cursor to an absolute position (e.g., the fifth row in the result set) or to a position relative to where it is now (e.g., go to the immediate previous record).

We can ask for our result set to come to us sorted by some column or columns. We achieve this by using the “ORDER BY” clause in the query. When you do that, it makes sense to use the cursor to ask for the record before the one we are currently looking at. For example, if you order by “billing price” you can go backward until you reach orders under $10. That way, you can process your most valuable orders first, and stop invoicing when the amount is smaller than the cost of processing.

Here are some common pitfalls encountered when using the SELECT statement. When you hit an error in your programs in the next chapter, check if it is one of these!