tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <>
Subject Re: Need help understanding DB connection versus servlet request life cycle
Date Sun, 16 Aug 2015 00:32:20 GMT
Hash: SHA256


On 8/14/15 1:36 PM, Cris Berneburg - US wrote:
> -----Original Message----- From: osagie uwaifo
> [] Sent: Friday, August 14, 2015 10:49
> AM To: Tomcat Users List Subject: Re: Need help understanding DB
> connection versus servlet request life cycle
>> Chris,
>> Why don't you allow tomcat manage the connection for you?
>> It is a lot easier that way. Do you need examples?
> Thanks for your reply and the link.  I'll read up on that.  Yes, I
> probably need examples of hooking up the connection pool to
> myBatis.  The page does say this:
>> How to use Usage of the Tomcat connection pool has been made to
>> be as simple as possible, for those of you that are familiar with
>> commons-dbcp, the transition will be very simple. Moving from
>> other connection pools is also fairly straight forward.
> I have no experience with either commons-dbcp or using connection
> pools, so for me it does not seem "fairly straight forward".  :-)
> But I will keep reading and Google how to connect myBatis to the
> Tomcat connection pool.

You just need to code myBatis to use a JNDI DataSource. Then make sure
that the DataSource you configure in Tomcat (in your application's
META-INF/context.xml file) agrees with the DataSource's name you tell
myBatis to use. Sometimes the exact syntax on either side is tricky to
get, since certain components like to add their own prefixes without
making it abundantly clear what prefixes are actually being added from
the root of the JNDI context.

It's not a good idea to keep a database connection open for the
duration of the request. For one, not all requests are going to
require a database connection, and you'd be pulling one out of the
pool only to starve other requests that actually do need them. Second,
the request (usually) takes longer than the database transaction, so
you are also starving other threads by doing that.

I would recommend checking a connection out of the pool, performing a
small transaction, then returning the connection to the pool using
Connection.close(). If you need to perform multiple transactions, you
can grab another connection if necessary.

If you find that you have multiple separate transactions per request
that are stalling waiting for connections, you might consider creating
a utility method that handles that set of transactions and does them
all with a single connection.

Finally, you should take some time to remove all database accesses
from your JSP code and move it "back" into the servlet layer. That
will give you a clean separation between your controllers (the
servlets) and your views (the JSPs). JSPs have fewer tools to allow
you to handle errors, etc. and so it's best to know that all is well
before you try to generate a response -- because once the response has
been committed, you can't take it back and say "whoops, we actually
couldn't load that object from the database". Instead, you'll get
either a broken page or a silently-failed page where the user has no
idea a problem occurred.

It sounds like your application has out-grown the initial sloppy
design and could afford some serious refactoring. It happens. I'm
happy to hear that someone is actually taking a look at the
application and improving it.

I would also recommend all of the following (because I wrote it, of

All of that is still relevant if you are using myBatis, except that
you won't be needing to handle the JDBC calls yourself. At some point,
you may decide that myBatis is not necessary and you'll want to code
your own SQL queries and write your own transactions. The techniques
in that blog post are equally relevant in either scenario.

Hope that helps,
- -chris

>> On Fri, Aug 14, 2015 at 9:12 AM, Cris Berneburg - US
>> <> wrote:
>>> BACKGROUND:  I've been doing Java servlet coding for about 2
>>> years and need help understanding something.  I work on a
>>> legacy JSP and servlets web application project using Tomcat.
>>> Previously all the SQL was embedded right into the Java and JSP
>>> code.  I added the myBatis framework and moved all the SQL to
>>> mappers.  Also previously, every SQL statement had its own
>>> separate DB connection instantiated and opened but not closed.
>>> I changed that so each servlet request would have only one open
>>> DB connection by adding a ServletRequestListener that opens the
>>> sqlSession in requestInitialized and closes it in
>>> requestDestroyed.
>>> PROBLEM: When running in my IDE in my dev environment, various
>>> random myBatis calls in the JSP pages would fail.  After one
>>> would fail, all the rest would fail too.  There were 3 repeated
>>> phrases - NullPointerException, PersistenceException, and
>>> ExecutorException Executor was closed.
>>> DEBUGGING: It appeared the DB connection was being closed
>>> before the page was finished rendering.  That meant the MyBatis
>>>  ServletRequestListener requestDestroyed handler was being
>>> triggered before the JSP page was finished being served.  When
>>> I commented out the sqlSession.close statement the JSP page
>>> worked OK.
>>> WORK-AROUND: I added a keepConnectionOpen flag in my
>>> development configuration file and coded the
>>> ServletRequestListener requestDestroyed handler to not close
>>> the sqlSession if the flag was true.  The JSP page now loads
>>> without error.
>>> NOTE 1: While there being only one open SQL session per servlet
>>>  request open at a time is an improvement, it bothers me that
>>> the DB connection is not being explicitly closed.
>>> NOTE 2: The problem does not currently happen in our test
>>> environment.
>>> NOTE 3: Our web site has low traffic volume.
>>> QUESTION(s) 1: Why is the ServletRequestListener
>>> requestDestroyed handler triggered before the JSP page is
>>> finished?  Is it due to my IDE or Tomcat or something else?  My
>>> interpretation is that the IDE is handling the processing
>>> differently than Tomcat somehow, although I don't understand
>>> that since the IDE is invoking Tomcat.  Should I be concerned
>>> that the problem will eventually happen in my test and future
>>> production environments?
>>> QUESTION 2: Is there a better way to manage DB connections
>>> anyway?
>>> DEVELOPMENT ENVIRONMENT: OS - Windows 7 Pro SP1 64-Bit IDE -
>>> IntelliJ Idea 12.1.6 TOMCAT - 6.0.37 JAVA - JDK
>>> TEST ENVIRONMENT: OS - Windows Server 2012 R2 Standard 64-Bit
>>> TOMCAT - 6.0.37 JAVA - JRE
>>> NOTE 4: My customer uses Tomcat 6, so I must use that too.
>>> You read down this far?  Thanks for your time, help, and
>>> encouragement. :-)
>>> -- Cris Berneburg, Lead Software Engineer, CACI
>>> --------------------------------------------------------------------
- -
To unsubscribe, e-mail:
>>> For additional commands, e-mail:
>> -- Thanks, Osagie Uwaifo
> ---------------------------------------------------------------------
To unsubscribe, e-mail:
> For additional commands, e-mail:
Comment: GPGTools -


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message