• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Definitive Best practices with JDBC and oracle

 
John Lindo
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working on a web app (environment is apache-tomcat, Oracle 9i, all servlets and jsps). I have an inconsistent problem in my servlets where sometimes the sql executes(executeQuery, executeUpdate, execute, or using resultSet to execute the update or insert). Most of my servlets will need to process 4-5 different sql statements. So my question is what are the best practices when dealing with db connections, Statements, PreparedStatements, closing out connections, reusing connections with different statements, can you even reuse a statement or resultSet (or should you close these as soon as the query is finished and never think about reusing) etc..

Any assistance is greatly appreciated.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you obtaining your connections? In my experience, relying on container-managed connection pooling (I use Tomcat) works best for me.

I obtain such a connection from the pool at the beginning of the request for use thorughout that one thread. It is released before the response is sent back to the browser.

If I am performing multiple operations within the request, I clear the connection's auto-commit flag (and restore it to its prior state before releasing) so that the operations occur as a single transaction (that can be committed or rolled back as necessary).

I am very careful to make sure that no resources (statement, result sets, and the connections themselves) are leaked by making use of "finally" blocks.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic