-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Cris,
On 8/14/15 1:36 PM, Cris Berneburg - US wrote: > -----Original Message----- From: osagie uwaifo > [mailto:osagieuwa...@gmail.com] 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? >> >> http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html >> >> 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 course): http://blog.christopherschultz.net/index.php/2009/03/16/properly-handlin g-pooled-jdbc-connections/ 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 >> <cberneb...@caci.com> 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 1.6.0.24 >>> >>> TEST ENVIRONMENT: OS - Windows Server 2012 R2 Standard 64-Bit >>> TOMCAT - 6.0.37 JAVA - JRE 1.8.0.45 >>> >>> 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: users-unsubscr...@tomcat.apache.org >>> For additional commands, e-mail: users-h...@tomcat.apache.org >>> >>> >> >> >> -- Thanks, Osagie Uwaifo > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > -----BEGIN PGP SIGNATURE----- Comment: GPGTools - http://gpgtools.org iQIcBAEBCAAGBQJVz9oTAAoJEBzwKT+lPKRYbWAP/3cuvOcK4vuELoWi3miwmpcP OH/6SIfCou2nYy9Tmu6hBgFCeU13Kco2iZJDtrjzQqZxS9Zf54T5hWEWKnfabToq pKRJBCWyJFrV/LrI0TqTMUL6PcRy8/SJjpDNWd29WI5JK05oshTtEMI37Rv/w5ga QBsin9GKETMfGhHQRzy5544DIszHF9r4XRqWvyaWjDx08kFp+46g22Es+mFK6GyZ /874fQV001i8XYv7zL90I0IxlYtzecXRFgjqoFmqjTNNy7QE4Zs+xI/nltVVouU2 IBufOH6ki9gEQXPubOzielB+ySeNVbBGxmIrAavJ6Ar3LRi6TwEcGuZ755AkCtL4 fsVidFWQPU+xBvJwdVHlCsDnWFmP+Gti4iVrzvWqvW4ZO3IMTPxHVt4KNnTwoXXb yKGbIufuKqohb1YPG33v1ZIizKGup9wtlMPaj4LiD8U4J1AGFFS/8vBLDikY6Cf2 n9gCYXtpZ0pl7h80wlLkuLrZbPtz8c9HWh8ZaP6X8YV9JEFP87hXoQ9AFFKTeT39 IYmkegGZrKjcYc6PbWMY9SYA2RtSHA47mQqdqwxqPL1SusuyOp9C5oSAH8GIfNlY 4Np9Dt9GyxQeXRyQTQzogFu+IhKhLNfzrgZYjJu5k6BVDkSd3kSgfWBeFGhtXQaX AfuLDc+PGFouAE9OVBb6 =u3/4 -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
