0

after some reading of servlet architecture, tomcat mechanisms and DB pooling I wonder if I really should open a connection in the init of my servlet (and close in destroy)? My target is to archieve high performance, so I guess I should use a connection pool. For the beginning I use tomcat's built-in DB pooling mechanism.

context.xml

<Context>
    <Resource name="jdbc/mytest" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="30" maxWait="10000"
    username="userxy" password="xy" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/mytest"/>
</Context>

web xml:

<resource-ref>
    <description>MyTest</description>
    <res-ref-name>jdbc/mytest</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

I am quite sure I can replace it easilly by s.th. else if neccessary. So my question is: Should I use instead of the serlet's init()/destroy() the per-request called doGet() and trust in the pooling mechanism? What, if I use both?

Alex004
  • 785
  • 2
  • 10
  • 25

1 Answers1

2

If you open a connection in Init and close it in Destroy, you will almost certainly run into problems.

Servlets are multi-threaded. You only have one instance of the servlet processing multiple threads.

For example say you insert a record and then retrieve the last_insert_id(). If you only have one thread running it will work fine. If you have two or more threads running, some of your threads are going to get the wrong reference.

By far the best way is to get a connection from the pool on request and release it, in a finally block, when you are finished with it.

Jaydee
  • 4,138
  • 1
  • 19
  • 20
  • 2
    ...in a finally block. Better yet, move all your db logic to a separate class so you can more easily re-use it. – Christopher Schultz Jun 04 '12 at 13:47
  • @Jaydee, contrary to your above statement, I believe that a servlet init function is thread safe. The destroy() method is not necessarily as clean as the init() method. The server calls destroy either after all service calls have been completed, or after a certain number of seconds have passed, whichever comes first. This means that other threads might be running service requests at the same time as your destroy() method is called! So be sure to synchronize, and/or wait for the other requests to quit. Sun's Servlet Tutorial has an example of how to do this with reference counting. – kroiz Jul 05 '12 at 07:10
  • The init function is thread safe, it is after all only called once in the lifecycle of the servlet. However storing a database reference at this point and releasing it in destroy ignores all the multithreaded calls to service requests. Synchronizing the database requests using the single connection would cause a major bottleneck. – Jaydee Jul 05 '12 at 08:32