So it's best to close the objects as soon as you're done, as in:
Statement s = conn.CreateStatement();
ResultSet rs = s.executeQuery(...);
use the data ...
Unfortunately, once you start involving exceptions in the code, you want to try to ensure that you close your objects in all exit paths from your function, so you start writing try blocks and finally blocks, and putting your close calls in your finally blocks.
And then it becomes more of a mess because the close calls themselves can throw exceptions, so you have to put the close calls in try blocks, and pretty soon your finally-try-close-catch logic seems like it's overwhelming your actual database execution logic in your program.
It's the bane of all JDBC programmers, and it's been with us for a decade or more, ever since JDBC was invented.
But hope springs eternal! One of the features that is still alive in the ever-delayed, volatile, unpredictable release that JDK 1.7 has become is known as "try-with-resources", and it promises to greatly reduce this JDBC PITA.
If you want to learn more about try-with-resources, you'll need to know that it's described under Project Coin: Automatic Resource Management, or you can go read the original spec by Joshua Bloch.
Or, if you just want to see how much better this makes the life of the average JDBC programmer, here's a nice short essay by Arul Dheslaseelan with code blocks to illustrate.