Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you're working with a database where the tables have a lot of data, there will be many times when you need to retrieve only a certain amount of data from the table. You can limit the number of rows in a couple of different ways. One approach is to use a method of the statement interface called setMaxRows. There are some complications in trying to use this, especially with Java 7, where you're using to try-with-resources block to instantiate your objects. I'm working in a project called LimitingRows, and I'm starting off with the ResultSet that contains all of the data from the Tours table.
I retrieve the data, and then use this method of the Tours class which I have created in a previous video. The displayData method receives the ResultSet object as an argument, loops through its data, and displays its data in the console. I'll run the code and show that I'm retrieving all of the data from the Tours table. I'll also switch from MySQL where I started to HSQLDB, and then I'll run the code again and show that it works there as well. Now, I'm going to limit the number of rows that I'm retrieving. If you want to do this using pure JDBC and not SQL, you do it with a setMaxRows method.
This is a method of the Statement object, and you might be tempted to try this, stmt.setMaxRows, and I'll pass in a value of five. Meaning I only want to work with five rows, but when I save that change, I'll see all kinds of errors appear. The problem is that you can't modify an object within a try-with-resources block. This is a Java 7-specific issue. If you're working with Java 6 or prior versions, you would have declared your Statement object outside the try block, then instantiated within it, and then it would have worked fine.
But if you're working with Java 7, and you want to take advantage of try-with-resources, this is the complication. You can only setMaxRows within the try block, not in the try-with-resources section. So I'll move that code down a few rows. I did that on Mac by holding the Option key down and pressing the Down Arrow, and if you're on Windows, you can do the same thing with Alt and the Down Arrow. Now I'll save my changes again, but now I'm calling executeQuery before I call the setMaxRows method. And when I run the code, I still get back all of the data.
So I'll make another adjustment. I'll take the code that's creating the ResultSet, and move that down so it's below setMaxRows. But now I also have to close the ResultSet, so I'll add a finally block, and I'll call rs.close, and to make that possible, I'll need to declare the ResultSet outside the try block so that it's visible in the finally block. I'll move the cursor above the try, I'll declare the object, and then I'll remove the data type from the line of code that's instantiating the object.
I'll test my code, and now it runs correctly. I'll switch back to my other database, MySQL, and test it there as well. And I'll see that it works there as well. So if you want to use the setMaxRows method, you can. But using it in combination with Java 7's try-with-resources syntax is complicated. There is one other issue though with setMaxRows. Let's say you're dealing with a database table that has 500 rows, and you say you only want 5. When you execute the SQL statement, you'll actually be retrieving all 500 rows from the database into your Java client, and then when you say setMaxRows with the statement, you're saying discard the unused rows.
You're not saving any memory or any network bandwidth if you're dealing with the database server. So I'm going to get rid of all the code that I just generated. So instead of using setMaxRows, I'm going to use pure SQL to limit the number of rows that I want to retrieve. I'm going to put all my code back the way it was and just comment out the stuff that I'm not using. I'll comment out my declaration outside the try block, I'll take my two lines of code that are executing the query, and I'll move them back to within try-with-resources. I'll add the data type back to the executeQuery method, and I'll comment out setMaxRows.
Now here's how you can limit your data using Pure SQL. I will use something called the LIMIT clause. The LIMIT clause can be placed in a couple of different places in your select statement, depending on what database you're using. But if you want to use the LIMIT clause in a way that's broadly compatible across many database platforms, place it after the FROM clause. I'll place the cursor inside the SQL string before the closing quote, and I'll add a space then I'll append to that another bit of code LIMIT 5.
You can use the LIMIT clause with either one or two numbers. If you only put a single number in, you're saying this is how many of rows I want, and I want the first set of rows that are available from the database. So here I'll get the first five rows. I'll clean up my warning at the end because I'm now declaring the ResultSet within the try-with-resources. I don't need this finally code anymore so I'll delete it. And now I'll run the code, and I once again get back five rows of data. Now let's say that instead of the first five rows you wanted five rows from an arbitrary segment of the ResultSet.
You can do this, too, by adding another numeric value to the LIMIT clause. You separate the two numbers with a comma. I'll start by using 0, 5. That means starting with row zero, the first row of the ResultSet give me five rows. I'll save the change and run the code, and it looks exactly the same as it did before when I was using setMaxRows, but now I'm only retrieving those rows from the database. And particularly when working with a network database server this is clearly a better approach because I'm not chewing up network bandwidth, and I'm not using memory in my client that I just don't need to use.
I'll once again switch databases, and I'll test it in HSQLDB, and I'll see that it works exactly the same there. One last note about the LIMIT clause, the first value is the starting row. You might remember I said that with ResultSet columns you start numbering at one, so if you want to refer to the first column of the ResultSet, it's column one and not column zero. But in SQL, when you're using the Select statement with the LIMIT clause, you always start numbering at zero for the number of rows, so you start numbering at one for columns in a Java ResultSet and zero for rows in SQL. And just keep those two things straight, and you'll be in great shape.
Now I'll test this a little bit further. I'll change the numeric value representing the first row I want to retrieve from zero to five, and now I get back rows six through ten. And if you want to develop a paging interface, that is a client that pages through data one set of rows at a time, this is clearly the best way to do it. By putting your request directly into your SQL statement, you limit the amount of memory you're using in your client, you limit the amount of network bandwidth you're using, and in general you limit the amount of data that you're retrieving from the database.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101553 Viewers
61 Video lessons · 88310 Viewers
71 Video lessons · 72180 Viewers
56 Video lessons · 103932 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.