This week's book giveaway is in the Agile and Other Processes forum. We're giving away four copies of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java and have Dr. Raoul-Gabriel Urma & Richard Warburton on-line! See this thread for details.
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 rs.next() to see whether it contains any rows.
Q. How do I INSERT or UPDATE a text value with a ' in it
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. 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.
Remember to always leap before you look. But always take the time to smell the tiny ads: