database connection in servlet

[Yi Meng]

Is a good practice to maintain a statement attribute in the servlet(setup in init()) so that whenever there is anything to do with the database i just use the stmt.
What about a connection attribute? Then every time i have to first Statement stmt = conn.createStatement() and then goon with stmt
And what about a DataSource attribute? Then every time i have to Connection conn = ds.getConnection(); and .....

what is the common practice of doing such things?

[Sri Basavanahally]

Typical tasks in the init() method of a servlet do include some of the activities you mention. It does make sense to get these references once and use them in the servlet.
-Sri

[Mike Curwen]

I'd say the last thing is the best.

Retrieving a JNDI Datasource and 'caching' it locally saves you some time.

And I've heard of people obtaining connections and whatnot in init(), but I've never thought this was a great idea. This is obtaining a resource and holding on to it, when it's not needed. Using a pool is a much better idea.
Statements for sure not. If you have two people using your servlet (two threads) and one person is half-way through setting some preparedstatement to his values, is interupted by the second person (who finishes his job successfully), and the first person is allowed to continue... his database updates will contain 1/2 of the second user's data (the first half), and half of his own.

[Yi Meng]

just to clarify with myself:
1. it's not thread safe, it's also not worthy to do sth else to make it threadsafe, so should never use
2. it's all right, but the servlet is always holding the database connection, i.e. not efficient in terms of resource utilization
3. the best way, (btw, only one copy of ds (DataSource) is needed, so let ds be static), but may lead to lengthy(not much) code because of things like below have to be there everytime there is something to do with database

 
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
.
.
.
.
if(stmt != null){stmt.close();stmt = null;}
if(conn != null){conn.close();conn = null;} 

am i all right?
[ November 21, 2003: Message edited by: Yi Meng ]