Best place to look up JNDI resource, init db connection, in servlet.

[jason.cipriani]

I'm new to Java servlet development. I'm accessing a database via a
JNDI DataSource resource that I've defined in my servlet's context. I
have gotten it working using some code like this:

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/
mssql");
Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(stmt);
ResultSet rs = pstmt.executeQuery();

My question is, where is the most appropriate place to:

1. Construct a new InitialContext,
2. Look up the DataSource resource (call ctx.lookup),
3. Initialize db connection (call ds.getConnection).

And also I'm wondering about the most appropriate place to store the
InitialContext, DataSource, and Connection. My options, for each of
the above 3, are:

A. Init in servlet class constructor. Store item as class member.
B. Init when creating a new HTTP session. Store item as session
attribute.
C. Init as needed in servlet class methods. Store item as local
variable.
D. Maybe something else I'm not thinking of.

Can somebody help me match up my 1,2,3's with my A,B,C's?

Also when should I close the database connection? Do I even need to
worry about it, or will it automagically happen for me? I am using
connection pooling.

Thanks a lot,
Jason

 

[jason.cipriani]

Lew, thanks a lot for the detailed and informative explanation. That
clears a lot of stuff up.

I would only store the DataSource.  Abandon the InitialContext after it's
yielded the DataSource to you.  Use the connection only in the moment of
connection; don't keep it around.

All right, so, what if the web application also consists of a bunch of
JSP pages? I can look up the DataSource in the servlet's init() (I
forgot about init), for the servlet, but for the JSP pages should I
just look it up in an InitialContext every time a page loads? Or does
it make sense to "cache" the looked-up DataSource in a session
attribute or something?

Is the DataSource going to differ between sessions?

I don't think so...? I don't really know how it works. The DataSource
will represent the same source of data for every session, if that's
what you mean -- I'm using Tomcat 6 and this DataSource is a JNDI
resource that I've defined in the web application's context.xml. Does
InitialContext.lookup() return the same instance every time (does that
even matter with DataSources)?

Thanks again,
Jason

 

[jason.cipriani]

Lew, thanks a lot for your insight below. You've cleared up all my
issues with this.

Jason