Bookmark Topic Watch Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
  • Mikalai Zaikin
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Q. Must I actually close all my ResultSets, Statements and Connections?

Yes, yes and yes. When you are using JDBC in your application you are allocating resources not only in your program but in a database server as well. Failure to properly close ResultSets, Statements (and PreparedStatements and CallableStatements) and Connections can cause all of the following problems

- You will run out of connections that can be opened or used
- You will not be able to create any new result sets
- You will not be able to execute any queries of any kind
- Your program will get very slow
- The database server get very slow
- Your program will crash
- The database server will crash

It is critical that you always close all the JDBC resources you obtain in the reverse order that you obtained them to avoid these serious problems.

Q. Can I set ResultSets, Statements and Connections to null instead of closing them?

No. Setting references to null does not have the same result as calling close on the objects. Code that does this will be leaking resources on the database server with possibly dire consequences. Further code like this will often have major problems in using connection pools.

Q. How do I know how many rows there are in a Result Set

  • If you need to know how many rows there are without knowing the data, you can do a query along the lines of "select count(*) from ... "
  • If you need all the data, count the rows as you loop through the data:

  • If you have a JDBC 3 driver, you can call rs.afterLast() to move to the end and then rs.getRow() to get the row number. This Result Set MUST have a scrollable cursor. Either ScrollSensitive or Insensitive but it won't work with a Forward Only cursor

  • Q. Can I get a null ResultSet

    No. Drivers will always return a result set from a select query. You can call to see whether it contains any rows.

    Q. How do I INSERT or UPDATE a text value with a ' in it

    Use a PreparedStatement rather than a Statement. A PreparedStatement will handle this formatting issue for you and it has other benefits as well.

    Q. How do I fix the Out of Memory Error when I have a Result Set with 100,000 rows

    The best way to fix this problem is to not SELECT so many rows at one time. Use WHERE and/or HAVING and/or LIMIT type clauses to restrict the number of rows you return at one time. For a robust JDBC driver, Statement.setMaxRows(int max) can limit it too.

    If you need to page data then you should also do that with the use of better SQL rather than seeking a Java only solution.

    A further solution is often to change the scrolling type of your ResultSet cursor to be type forward. Due to to the way many drivers implement scrolling in ResultSets these types of Cursors can be very expensive.

    Q. Why do I get a fetch out of sequence error and how do I fix it

    This type of error happens because your JDBC driver only supports fetching values for columns once AND in the order that they appear in your ResultSet. For example

    The way to avoid this is to call the getXXX methods on each row in the order they appear in your result set and to only call them each once. If you need to look at these values again you should use a Collection (e.g. ArrayList) to hold the values from your row.

    Q. How do I view the actual SQL that a PreparedStatement is sending to the database?

    You can use a DebuggableStatement. describes this option in more detail. Alternatively, you can use the open-source projects or

    Q. How do I limit the number of rows returned from the database?

    This technique is often referred to as PaginationOrPaging

    Q. Where can I get more information on SQL (the language)?

    There are a vast number of SQL references, primers and tutorials available on the internet. It is recommended that you use Google to find an appropriate syntax guide for your database because databases do tend to have their own specific SQL dialects and what works on one system may not work on another.

    For general SQL guidance the Wikipedia SQL reference is useful and may be found at In the main article there are links to the various statements for a generic look at the purpose and syntax of these statements.

      Bookmark Topic Watch Topic
    • New Topic