Using JNDI for Database connections

Question

This might sound like a noob question but this is the first time I'm treading into Database territory.
From here I got the info that

The most efficient way to implement communication between the server and database is to set up a database connection pool. Creating a new connection for each client request can be very time-consuming, especially for applications that continuously receive a large number of requests.

and the tutorial uses a JNDI datasource.

My application is also similar(but I won't be using Tomcat, just sockets) and my server will be getting requests from multiple clients but I don't understand why should I use a JNDI datasource, why can't the server maintain one open connection with the Database and when a client request arrives it will process the request and feed the data to the client.

In the worst case, If I should need a JNDI how can I implement it with my server app?

Answer 1

Thus, it is a client application? The application and the database usually talks with each other using a connection obtained by DriverManager#getConnection()? If so, then you don't necessarily need JNDI to get connection pooling to work. Alone the connection pooling framework in question would already suffice. For example C3P0 or Apache Commons DBCP (I would recommend C3P0; DBCP is singlethreaded). Just replace the DriverManager#getConnection() by it.

Edit: reply on your comments:

The server would be talking to the database and the clients connect to the server, so I won't know whether to call this a client application.

I actually mean, a plain vanilla Java application which doesn't run inside a Java EE container. Pascal has worded it better.

Actually I'm a bit confused about how connection pooling works, does each connection run in its own thread? is there any document/book to help me get a better understanding of these concepts vis-a-vis a non pooled connection?

To start, the connection pool opens a connection and holds it open as long as up to the configured timeout. The connection pool wraps/decorates the connection with its own implementation. The connection pool can open and hold a configured amount of connections simultaneously. When you call getConnection(), it will immediately give you an already opened connection. When you call close() on the connection, it will put the connection back in the pool for future requests. This thus means that you still have to write the JDBC code the usual way: acquire and close the Connection, Statement and ResultSet in the shortest possible scope. Close them all in the finally block. If your JDBC code is already well written, in fact only the DriverManager#getConnection() needs to be replaced. As you ought to open and close the Connection in the very same method block, it will normally run in the same thread. The connection pooling will worry about that the Connection is not acquired by another threads in the meanwhile until your code calls close() on the Connection.

You can find here a nice article to get the idea how connection pooling works under the hood (take care: don't use it for production and don't homegrow it further, it is just to get the whole idea). For real work, use an existing thoroughly developed and robust connection pooling framework.

Answer 2

My application is also similar(but I won't be using Tomcat, just sockets) and my server will be getting requests from multiple clients but I don't understand why should I use a JNDI datasource, why can't the server maintain one open connection with the Database and when a client request arrives it will process the request and feed the data to the client.

Well, you could. But what if you have multiple clients and if you have to serve concurrent requests? Of course, you could maintain one connection open per client, but this doesn't scale really well (which might not be a problem in your context). Still, the traditional way to solve this is to use a connection pool (and to benefit from extra services e.g. connection validation, connection renewal) and to use it to obtain a connection "on demand".

If you are not in a J2EE container context, use a standalone connection pool implementation, something like c3p0 (prefer c3p0 over DBCP which is considered as out of date and less robust under load) and forget JNDI (which is just the standard way to get a handle on a connection pool when you are running inside a J2EE container).

Have a look at c3p0's documentation for more details and code samples, it's pretty clear.