I would guess that most decent JDBC drivers would, indeed, have the Connections and othe objects close() themselves in finalize(). But you can't depend on garbage collection to clean up after you, because you don't know when it will happen. All operating systems place limits on the number of simultaneous network connections you can have open, and on the numbers of other system resources you can consume. You could easily open enough Connections, for example, to exhaust all the available network sockets (i.e., database connections) without filling up memory -- so you'd not be able to connect, but the garbage collector wouldn't fire, so none of the old Connections would be closed.
Well, we'll have to regard Oracle 9i JDBC drivers as not decent! They do not have finalizer methods; instead, they perform cleanup routines by using the close() method of the ResultSet and Statement classes.
This is my understanding of what happens. A ResultSet represents a database cursor which indexes a particular row and knows how to get the next one. What is this cursor? It's an area of memory in the database in which the executed SQL string is parsed and the result held. (I'm not sure whether all the data is necessarily held here or maybe pointers to where the data is.) When you close a ResultSet or Statement, this releases the corresponding cursor in the database. So, failure to close can cause two problems: you can suffer serious memory leaks and run out of cursors in the database.
As for Connections: you need to close them or you will probably exhaust the connection pool. If you are not using connection pooling, then closing the Connection will (probably) cause the JDBC driver to close any open Statement objects associated with it, thus releasing the cursor objects on the server side.