Question: What is the best way to manage a database connection in a Java servlet?

Currently, I simply open a connection in the `init()` function, and then close it in `destroy()`. 

However, I am concerned that "permanently" holding onto a database connection could be a bad thing. 

Is this the correct way to handle this? If not, what are some better options?

edit: to give a bit more clarification: I have tried simply opening/closing a new connection for each request, but with testing I've seen performance issues due to creating too many connections.

Is there any value in sharing a connection over multiple requests? The requests for this application are almost all "read-only" and come fairly rapidly (although the data requested is fairly small).

Answer: 
The best way, and I'm currently looking through Google for a better reference sheet, is to use pools. 

On initialization, you create a pool that contains X number of SQL connection objects to your database. Store these objects in some kind of List, such as ArrayList. Each of these objects has a private boolean for 'isLeased', a long for the time it was last used and a Connection. Whenever you need a connection, you request one from the pool. The pool will either give you the first available connection, checking on the isLeased variable, or it will create a new one and add it to the pool. Make sure to set the timestamp. Once you are done with the connection, simply return it to the pool, which will set isLeased to false.

To keep from constantly having connections tie up the database, you can create a worker thread that will occasionally go through the pool and see when the last time a connection was used. If it has been long enough, it can close that connection and remove it from the pool.

The benefits of using this, is that you don't have long wait times waiting for a Connection object to connect to the database. Your already established connections can be reused as much as you like. And you'll be able to set the number of connections based on how busy you think your application will be.
Pool it.

Also, if you are doing raw JDBC, you could look into something that helps you manage the Connection, PreparedStatement, etc. Unless you have very tight "lightweightness" requirements, using Spring's JDBC support, for instance, is going to simplify your code a lot- and you are not forced to use any other part of Spring.

See some examples here:

<http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html>