Quick question.

I have a dao interface, and one implementation of it is to use direct jdbc.
Its deployed in jboss (stateless session bean calling dao).
What i was wondering is the prefered way of getting and using the connection for that dao consider the session bean will
be returned to pool after each method call to it.

Options I consider.
On session bean creation. It does:
1)SLSB does jndi lookup of datasource. Gets one connection.
2)creates dao passing in a connection. keeps one dao as member variable.
3)dao exists with that one connection, reusing it.
4)dao method calls, only close statements + results sets, never bothers with closing that one connection.
5) connection.close() only called when dao finallize method called. (due to bean being GC 'd)

Other option.
1)SLSB jndi lookup of datasource. holds on to datasource as member variable
  I believe this is ok, because SLSB never goes in to passive state, so no need to worry about making sure datasource is closed and set to null.
2)creates dao, NOT passing a connection to the constructor.
3)before every dao method call, get a new connection from datasource, call setconnection(conn) on dao.
  call dao method. Then close connection after method call (done in SLSB), thus returnig connection to connectionpool, or closing connection.


They were the two main options, personally i thought the second one better, as you never know how long a SLSB might exist for
and if a connection died, there would have to be some funky stuff to get a new connection, for dao again.

Question I have with the second option is whether its safe enough holding on to datasource reference in the SLSB as a member variable.

Which option is the better, or is there a more preferred way of doing getting connections for the dao.

Thanks for help.

There's a third option as well: don't hold either as a member variable -- do the JNDI lookup and getting the connection whenever you call a dao moethod. Preferably, this would be done as part of your persistence logic, not as part of your business logic. Ideally, your SLSB shouldn't even know it's using a database.

Hmm, ya what i described was just a rough outline. SLSB doesnt know about datasource.

doing a jndi lookup every time seems costly. At the momnent the jdbc-sql imple of the dao, holds on to datasource as member variable and gets connection from it, and closes connection for every dao method call. It seems good enough solution.