Useful guide & FAQ for Application+SQL
I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there.

As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session.

For web apps, you want to open a connection at the last possible moment, and close it as early as possible. You might read from 30 tables in a page request though, so use the same connection in that time.

Database connections are "expensive" in terms of memory and startup time, so one creates a pool of them. You don't really close your db connection, you just release it back to the pool.