Re: Servlets with JDBC connectivity

[Todd O'Bryan]

On Dec 3, 2003, at 5:40 AM, Todd O'Bryan wrote:

On Dec 3, 2003, at 2:59 AM, Nikola Milutinovic wrote:

Peter Harrison wrote:
On Wed, 03 Dec 2003 16:18, Todd O'Bryan wrote:
How do people handle this elegantly? The requirements are: a single,
globally visible (within a webapp) database interface and the 
ability
to access multiple databases easily.
The first point is to use a singleton to set up the database 
connection - or more correctly the connection pool. This way you can 
request a connection and return it to the pool easily. Of course 
every time you use one you will have to use try-catch blocks. Sorry 
no way around that.
Both Tomcat and J2EE specification support javax.sql.DataSource 
objects over JNDI. That is how we handle it elegantly.

A DataSource object is specified by the administrator and created by 
the container. Container then deploys it under specified JNDI name. A 
servlet (or EJB) can then lookup this object and use it, something 
like this:

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup( 
"java:comp/env/jdbc/MyDataSource" );
Connection conn = ds.getConnection();

But this means I still have to get a connection, create a statement, 
and execute a query or update on the statement in every servlet where 
I want to use the connection. Yes, it locates the connection details 
(i.e., the JDBC connection method, the database name, user and 
password) somewhere centrally so that I don't have to keep coding it, 
but all of the connection overhead still has to be dealt with in every 
servlet in a webapp.

At least, I think that's what it's doing. Am I missing something?

Here's what I want. In every servlet in my webapp, I'm only going to 
be using one of a very few database connections. I'd like to be able 
to do something like:

Test.executeQuery("SQL")

without doing any setup because the Test class handles all the setup, 
initializes the connection the first time it's called, etc.

Basically, it's a singleton class that never gets an instance created 
because if I create an instance, I'd have to pass it around to all the 
servlets in the webapp, which would kill the convenience of having it 
be available with minimal overhead.

Oo, oo, oo...

As I've been sitting here writing this, I had a brainstorm. What if I 
create an abstract class that has all the database connectivity built 
into it as static methods, but is just waiting for a connection 
method, a username, and a password. Then creating a new JDBC 
connection which is visible to all my servlets is just a matter of 
making a concrete subclass that defines those three variables and 
inherits all the functionality from its abstract parent class.

Any reason this is a horrible idea?

Thanks for being patient,
Todd
Never mind. The compiler just reminded me that abstract and static 
together are nonsensical. It must be too early in the morning.

I suppose I could write a constructor that initializes the database, 
user, and password settings, and then use the instance. Instead of

ProductionDB.executeUpdate("a SQL statement");

I'd have

ProductionDB prod = new ProductionDB();
prod.executeUpdate("a SQL statement");
slightly more complicated than what I envisioned, but I could create 
any connection I need in each servlet's constructor so it would only 
have to be done once.

Once more, any obvious (or, better for my self-esteem, non-obvious) 
reason this is a bad idea?

Thanks,
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]