Home / server / Questions / 381302
Accepted
Yeti
Asked: 2012-04-20 12:41:27 +0800 CST

How to use JDBC Connection Pooling without JNDI DataSource? (in Apache Tomcat)

  • 772

I have an Apache Tomcat 7 server. I have a quite complex Java Servlet application running on it which needs some MySQL queries per POST. Thus I need a MySQL Database connection to do the query. Currently I use the following Java code to create the DataSource:

//db is for example: jdbc:mysql://127.0.0.1:3306/databasename
DataSource ds = new DataSource(initPoolProperties(db, user, pass));

private PoolProperties initPoolProperties(String db, String user, String pass)
{
    PoolProperties defaultPoolProperties = new PoolProperties();
    defaultPoolProperties.setUrl(db);
    defaultPoolProperties.setUsername(user);
    defaultPoolProperties.setPassword(pass);
    defaultPoolProperties.setDriverClassName("com.mysql.jdbc.Driver");
    // And a long list of properties here ...

    return defaultPoolProperties;
}

In my whole hierarchical Java Servlet application I only create one DataSource per Database connection per POST (by HTML Form to browse through the website). After this I use a few times ds.getConnection() to get the connection needed for executing some MySQL queries.

Everybody seems to use a JNDI DataSource. Which means they define a tag in the context.xml of their application, and use a JNDI lookup to get the DataSource, and use the connections the way I do it as well.

The question is wether I am actually doing the same thing here. Are the connections I use, pooled and shared between multiple visitors/clients? Because my online website application is going to be used a lot, and some pages contain quite heavy SQL queries. And I do not want to risk a person to be waiting for longer than 0.01 seconds loading the page.

I read about the way I do it (as shown above in the small simplified code example) on this website: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html Scroll down a bit and you will find the Good ol' Java section, I think it is the same as using the , but I'm not sure. How can I be sure of my application using connection pooling and sharing those between clients anyways?

PS I prefer using the PoolProperties instead of putting something in the context.xml since I use multiple databases which are defined in a configuration database. The database which is selected, depends on the user. Thus I can more easily maintain the databases in a MySQL administration program this way, than putting as well lots of code in the context.xml.

tomcat mysql jdbc connection-pool

1 Answers

  1. Best Answer

    If you want to have pooling without using the built-in feature Tomcat 7 provides, you have to manage it yourself (no recommended) or introduce a third party library like c3p0, DBCP is no more recommended: see here.

    In my opinion I would define all the possible datasource in the context.xml (to benefits from the Tomcat 7 JDBC pooling) and I will implement the logic in a service layer to choose which data source have to be used according to your context. The Spring framework can helps a lot defining such services and DAO's.

    • 0