Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
By default, result sets are forward only, that is, the cursor starts before the first row of data and can only move forward once. But a scrollable ResultSet gives you the ability to move back and forth in the result set as needed. You can make the ResultSet scrollable with most database management systems and different database management systems have different rules. For example, MySQL using the Connector/J returns a result set that scrollable by default. But HSQLDB can do a scrollable ResultSet, but you have to say you want it.
You select the ability to scroll the data set when you create the Statement object. In this code I'm creating the Statement with the CreateStatement method, and I'm passing in a type of TYPE_SCROLL_INSENSITIVE. The ResultSet type can be scrollable or forward only, it can also be READ_ONLY or updatable, and it is possible to have a scrollable ResultSet that also updatable if you combine these properties in the right way. Once you have gotten a scrollable ResultSet into memory you can move the cursor that is the pointer to the current row using these methods, beforeFirst(), and first(), move to the position before the first row, and the first row itself, last(), and afterLast(), do the same thing, but for the end of the table and the absolute method receives an integer argument and moves to that particular row.
As I previously mentioned, your number ResultSet rows starting with 1, not with 0. So if your ResultSet has say 50 rows, they are numbered from 1 to 50, not from 0 to 49. There are also methods that return boolean values that you can use to check the current cursor position. isBeforeFirst(), isFirst(), isLast(), and isAfterLast(), and the ResultSet object has other available methods to manage inserting new data and moving the cursor to particular rows after insertions. Take a look at the documentation for more details about that.
But let's go to the code. I'm working in a project called scrollable. In this version of my application, I'm executing a query that's retrieving data from the state's table. Instead of using an asterisk for the columns, I'm explicitly naming the columns I want retrieved, which is typically a better practice than using the asterisk wildcard. I'll get back to result set with those two columns, and I have created a new class called states that in the db table's package, which has a displayData method, the displayData method is looping through the ResultSet, outputting first the stateId, the abbreviation, and then the stateName.
I'll run the code in its existing state, and I get back the list of States. Now I'm going to demonstrate moving the cursor from row to row, notice that when I created my statement, I set the type as TYPE_SCROLL_INSENSITIVE. So I should get back a scrollable result set, regardless of whether I'm working with HSQLDB or MySQL. I'll place the cursor after the call to the displayData method, and first I'll move the cursor to the last row of the ResultSet, I'll call rs.Last, then I'll do some System.output, and I'll output (Number of rows), and I'll append to that the current row number using the ResultSet getRow method.
I'll save and run that code, and at the end of the display, I see that the number of rows is 50, and that's correct. Now I'll add some code to move back to the first row, rs.First, and then once again I'll use some System.output, and I'll output the string, The first state is, and I'll append to that the value of the state name column using rs.getString, and I'll pass in the name of the column stateName. I'll save and run that code, and I see that the first state is Alaska, this table has the states in alphabetical order, now we'll add some code to move the cursor to the last row, rs.Last, and I'll make a copy of this output, and I'll paste it here, and I'll change from the first state to the last state, and I'll run that code, and I see that the last state alphabetically is Wyoming.
Finally, I'll move the cursor to a specific row using rs.absolute, and I'll pass the value of 10, and once again I'll pasted my output code, and I'll change from first to 10th, and I'll run that code. And I'll see that I can successfully move the cursor as much as I need to. In this code, I'm scrolling first forward through all the data, then I'm moving to the end to get the number of rows, back to the beginning, back to the end again, and then to a specific row by its integer value. So that's a look at how you can use scrollable result sets.
Now let's take a look at what happens if you don't specify that you want a scrollable ResultSet. I'm going to make a copy of this line of code that creates the Statement, and I am going to comment those new lines out, those are my backup, because I'm going to make some changes to the original code. Now I'll go to my CreateStatement method call, and I'll get rid of the options that I was passing in. Now, notice I'm working with HSQLDB, and I'll run the code, and this time I'm able to look forward through the data, but then I get an exception SQLFeaturedNonSupportedException, and that's because with HSQLDB the ResultSet by default isn't scrollable.
It's a forward-only ResultSet. Now I'll change my databaseType from HSQLDB to MySQL, and I'll run the code again, and this time it works, and that's because with MySQL, you get a scrollable ResultSet automatically. To fix this and make sure that this code will work fine with either database, I'll always explicitly say that I want a scrollable ResultSet. So I'll go back to the code again, I'll delete these two lines, and I'll uncomment these, I'll save and run the code, and I see that it works fine with MySQL, and I'll switch back to HSQLDB, and I'll save and run again, and I'll see it works there as well.
So for maximum portability, be explicit about the type of the ResultSet that you want, and you set those options when you create the Statement object.
Get unlimited access to all courses for just $25/month.Become a member