JavaRanch Home    
 
This page:         last edited 19 March 2012         What's Changed?         Edit

JDBC Usage Questions   

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:
int count = 0;
while (rs.next()) {
  count++;
  // do other stuff with 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 rs.next() 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

while (rs.next()){
  rs.getString(1);
  rs.getString(3);
  rs.getString(3);// error happens here
  rs.getString(2);// error would also happen here
}

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. http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html describes this option in more detail. Alternatively, you can use the open-source projects http://www.jamonapi.com or http://www.p6spy.com.

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 http://en.wikipedia.org/wiki/SQL In the main article there are links to the various statements for a generic look at the purpose and syntax of these statements.


JdbcFaq

JavaRanchContact us — Copyright © 1998-2014 Paul Wheaton