-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Richard,
On 3/25/19 14:15, Richard Huntrods wrote:
> <sigh> It's time to update my application to use "real" (i.e.
> current best practices) data connection pooling.
:)
> My application is Java Servlets, no beans, no JSP. Database is
> MySQL.
>
> System etc. details: Ubuntu live server 18.04.2, built March 6,
> 2019.
>
> MySQL - latest installed via 'apt-get install mysql-server' after
> system build.
So... MariaDB, then? Or does Ubuntu still stock MySQL binaries?
> OpenJVM - 11? - again, latest version installed via 'apt-get
> install default-jdk' at same time.
Should be pretty easy to determine this:
$ java -version
> Tomcat 8.5.39 - just updated the same day it came out.
Sounds good so far.
> This system has been running in production since the early 2001's.
> OS has changed over the years from Sun Solaris 8.x to Solaris 10.x
> and now to Ubuntu 18.04 (server). Java has been updated over the
> years as well, as has Tomcat and MySQL. Through all that the system
> works quite perfectly.
>
> Except... there are occasional hangs that implicate the 'home
> grown' data connection pool. I wrote this by hand (in Java) back
> in 2001 because there was nothing much available back then. Since
> it kept working, I didn't have the time/inclination to change over
> the years.
You may find that your home-grown connection pool is actually okay,
but it's being used incorrectly by client code (which is also your
code). IF you have problems with the client code, the "real"
connection-pool can help you tolerate them, but it won't magically fix
them.
> But the latest connector (mysql-connector-java-8.0.15.jar, a.k.a.
> "com.mysql.cj.jdbc.Driver" is giving me some hiccups. I thought
> rather than trying to debug my own connection pool, it was time to
> switch over to a proper "modern" supported connection pooling
> system.
>
> Which brings me to my question.
>
> Would the community please weigh in on the BEST tutorials /
> documents regarding creating a Tomcat/MySQL database connection
> pool for Servlets (not JSP or beans) with some good code examples
> and server.xml examples?
>
> I've already done some extensive internet searches, but when you
> are doing something for the first time it's hard to tell the
> difference between "really really good" and "blogger who has not
> really tried it".
You will want Tomcat to create the connection pool for you. Anything
else is a waste of time. Here's what happens:
1. During application startup, Tomcat creates a javax.sql.DataSource
object and squirrels it away using a path in the JNDI space that you
get to specify the tail-end of.
2. When your application needs a connection, you grab the DataSource
from JNDI (it's like a singleton in-memory Map of URLs to objects) and
ask it for a java.sql.Connection object.
3. When you are done with the connection, you call Connection.close on
it and it goes back into the pool.
So, fairly simple.
It's all fairly easy, though changing all your code to work with it
might not be. Here's basically what you need.
1. Tomcat configuration. This goes into META-INF/context.xml:
<Resource name="jdbc/myDS"
description="My database connection"
auth="Container"
type="javax.sql.DataSource"
maxActive="1"
maxIdle="1"
maxWait="10000"
url="jdbc:mysql://host/database"
username="scott"
password="tiger"
driverClassName="com.mysql.jdbc.Driver"
removeAbandoned="true"
removeAbandonedTimeout="30"
logAbandoned="true"
testOnBorrow="true"
validationQuery="/* ping */ SELECT 1"
initialSize="1"
maxTotal="1"
maxWaitMillis="10000"
removeAbandonedOnBorrow="true"
closeMethod="close"
/>
You can look-up the meanings of all of the above settings here:
https://commons.apache.org/proper/commons-dbcp/configuration.html
Note that I have the above configured for "Development Mode" which
means that there is exactly 1 connection in the pool, period. If you
have bad code which fails to return the connection (leaks) or tries to
check out more than one connection from the pool (potential deadlock),
then your application will freeze in development.
In production, feel free to crank-up the maxActive, maxIdle, and
maxTotal settings.
2. Get a connection object in your code:
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
...
Connection conn = null;
try {
try
{
Context ctx = new InitialContext();
DataSource ds =
(DataSource)ctx.lookup("java:/comp/env/jdbc/myDS"); // NOTE: Suffix
matches <Resource name="[name]"
if(null == ds)
throw new ServiceException("Cannot obtain DataSource");
conn = ds.getConnection();
}
catch (NamingException ne)
{
// Error "Cannot obtain database connection"
}
catch (SQLException sqle)
{
// Error "Cannot obtain database connection"
}
// Your code goes here
} finally {
try { conn.close(); } catch (SQLException sqle)
{ /* whatever you want */ }
}
3. Actually, the Connection.close is up there in the example above.
I would highly recommend that you create a "getConnection" method
which handles the details of fetching the DataSource and Connection
and the various Exceptions that can be thrown in there. You don't want
all that garbage to get in the way of your "real code" in your
data-access methods.
Always remember to close the connection in a finally block. And your
Statement and ResultSets as well.
Hope that helps.
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlyaTIkACgkQHPApP6U8
pFjpEw/7BeomASCkf1O86Wlx8mz0DxIB09yCQxVw7VhsR41/oB5UHomBO9fTMQRg
uOnLJjdy1SjSCPHUahB/AL+La9Vzx3+qmlfABCQmZ7SatQ7aocivUVzfI/ZYyAmO
9QcA3bdz+qHJdegD4t+jcqFM6lctjR0OE4CpO2Ik6OgizStk/oUOPDgoHvs25+Zd
zWOVMdXtomjZ/dmIYvj0NNVqksGF/RNAz0JOQN2T/AT97U677y4rfO+cdhR+urOA
aW4LmynPFdb2D+y3MCYgxerZkBQZluDtM3B2R0w1Hb0EL/7Ew8SKsBB1QA3CikeF
qQFqlSDOZzpRoua6pP+r94ZKWXvAGULQJFFw4tgyYJka2BLxKOFr1paIa/E3SEcW
io6OmLKn/m6iA7AI0G7peSdHPkc4byXWVEQAq9jN0boD3RKw6Sz1+HfM6mAxPEmh
BDbtg2/m5lwb4jO4xNF5ybxR5sU065Pc4Su5polJ/c8QuLLiCJMmpGM5UuhTpQBV
uUsl+cdsNud+ruoAu36au33YTIw3aImlEXXb3ZlntUpaGearXoV3vtJ5QmYVuKVm
dyCTAZALgNiPFYGOr7PGvZZ7jD2w0l+9trCfzZE0JwnydIi8yhX2kUWqjl6N2bou
C2iswKlBZp+wAwgSGa4+hAMLgUlpOjLiXvwLaE4idxWVjpAm7wY=
=/swK
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
