Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Limiting the number of fetched rows

From: Java Database Integration with JDBC

Video: Limiting the number of fetched rows

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.

Limiting the number of fetched rows

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.

Show transcript

This video is part of

Image for Java Database Integration with JDBC
Java Database Integration with JDBC

32 video lessons · 6358 viewers

David Gassner
Author

 
Expand all | Collapse all
  1. 5m 23s
    1. Welcome
      53s
    2. What you should know before starting this course
      1m 20s
    3. Using the exercise files
      3m 10s
  2. 12m 2s
    1. Testing your Java development environment
      5m 14s
    2. Importing a MySQL database
      5m 1s
    3. Creating a database user in MySQL
      1m 47s
  3. 32m 47s
    1. What is JDBC?
      4m 26s
    2. Choosing a JDBC driver
      6m 46s
    3. Connecting to a MySQL database server
      8m 7s
    4. Connecting to a HyperSQL database file
      6m 23s
    5. Executing a static SQL statement
      7m 5s
  4. 17m 42s
    1. Connecting to multiple databases
      6m 24s
    2. Handling JDBC exceptions
      7m 7s
    3. Closing database resources in Java 7
      4m 11s
  5. 47m 25s
    1. Looping through result sets
      8m 23s
    2. Moving the cursor in scrollable result sets
      5m 51s
    3. Limiting the number of fetched rows
      6m 57s
    4. Filtering data with prepared statements
      6m 58s
    5. Calling stored procedures
      5m 48s
    6. Handling multiple values from stored procedures
      5m 54s
    7. Using generic getter methods in Java SE 7
      7m 34s
  6. 45m 23s
    1. Managing data entities with JavaBean classes
      5m 0s
    2. Retrieving a single row as a JavaBean object
      6m 5s
    3. Inserting rows with prepared statements
      8m 2s
    4. Updating rows with prepared statements
      5m 4s
    5. Deleting rows with prepared statements
      4m 9s
    6. Managing data with updatable result sets
      6m 6s
    7. Using a persistent database connection
      6m 43s
    8. Committing and rolling back transactions
      4m 14s
  7. 9m 35s
    1. Getting the DatabaseMetaData object
      3m 40s
    2. Getting column and data type information
      5m 55s
  8. 50s
    1. Next steps
      50s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Java Database Integration with JDBC.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.