Pooling Your DataSources
June 2, 2004 Marc Logemann
[The code for this article is available for download.]
In my last article, I explained how to use the IBM Universal JDBC Driver to connect to DB2 for iSeries in the Tomcat 5 environment, using JNDI for looking up the DataSource. You should now know how to define DataSources inside Tomcat and how to get the DataSource inside your Java code. I have not yet covered the definition and using a connection pool, which is important for creating well-performing applications.
CONNECTION POOL CHOICES
In the last article you had a choice between two JDBC drivers for connecting to the iSeries. (There might be other JDBC drivers, but I focused on IBM’s). Now I will present two connection pools, which are viable solutions for optimizing performance.
Tomcat 5 already has a connection pool onboard, called Commons DBCP, a separate project hosted on Apache Jakarta that is shipped with Tomcat. Unlike its prior versions, it is not difficult to enable. Again, we will take the route of defining the DataSource in the server.xml, which makes it easy to link the DataSource to different Web applications.
A typical global resource entry in the server.xml looks like server.xml. Pay special attention to the GlobalNamingResources, because the rest is a default Tomcat server definition. Like the previous article, you define a name for our resource, and in the ResourceParams section you define the behavior of the connection pool. But, you might ask, where is the pool? All the magic is hidden in the BasicDataSourceFactory class and the underlying Commons DBCP classes.
After you specify the factory parameter with the correct DataSourceFactory, you define the characteristics of the pool:
- maxActive: How many active connections are allowed at the same time.
- maxIdle: How many connections can be in idle mode.
- maxWait: How long a connection waits to be used (in seconds)
-1 => forever. - removeAbandoned: (true/false) track down not closed connections and make
them available again. - removeAbandonedTimeout: idle time in seconds for a connecion to be in
“abandoned” state. - logAbandoned: (true/false) logs details about abandoned connections.
The “abandoned” parameters are useful in preventing connection pool leaks and should be used in a production environment.
Then you have to define the parameters that are important for the driver itself:
- username: The user name to authenticate against the database.
- password: You guessed it.
- driverClassName: The fully qualified name of the JDBC driver class.
- url: The JDBC URL to connect to the datbase.
Now that you have configured the resource as a global resource, you have to link it to your Web application. For this you only need to add the following line in your Web application context.
<ResourceLink name="jdbc/db2x" type="javax.sql.DataSource" global="jdbc/db2x"/>
The global attribute must match the name you defined in your server.xml. From now on you are able to get this resource via JNDI (Java Naming and Directory Interface), like this:
Context initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup(„java:comp/env"); DataSource myDataSource = (DataSource)envCtx.lookup(„jdbc/db2x");
Keep in mind that other servlet containers might have different ways of getting an inital context, so you can’t necessarily copy this code from other containers and paste it in.
Getting pooled connections is nothing more than calling getConnection() on the myDataSource object. And there is no coding difference if you use pooled or stand-alone connections. All the magic is hidden in the Tomcat configuraion files.
USING THE JTOPEN CONNECTION POOL
Using the JTOpen connection pool is another choice, but it has some drawbacks. I’ll explain why it’s not as straightforward an option as DBCP. The code below is suggested by the JTOpen team for creating a connection pool.
AS400JDBCConnectionPoolDataSource datasource =
(AS400JDBCConnectionPoolDataSource)context.lookup("jdbc/mydatasource");
AS400JDBCConnectionPool pool = new AS400JDBCConnectionPool(datasource);
pool.fill(10);
Connection connection = pool.getConnection();
The first line retrieves a DataSource, which is capable of pooling from the JNDI context, but you can’t directly use any methods of that DataSource; instead you have to submit the DataSource to the connection pool (line 2) and declare the behavior of the pool inside your code with method calls like poo.fill() or pool.setMaxUseTime(). Below is a list of useful methods for configuring the pool:
- setCleanupInterval(long cleanupInterval): Sets the time interval for how often the maintenance daemon is to run.
- setMaxConnections(int maxConnections): Sets the maximum number of connections.
- setMaxInactivity(long maxInactivity): Sets the maximum amount of inactive time before an available connection is to close.
- setMaxLifetime(long maxLifetime): Sets the maximum life for an available connection.
- setMaxUseCount(int maxUseCount): Sets the maximum number of times a connection can be used before it is to be replaced in the pool.
- setMaxUseTime(long maxUseTime): Sets the maximum amount of time a connection can be in use before it is to be closed and returned to the pool.
- setRunMaintenance(boolean cleanup): Sets whether the Toolbox does periodic maintenance on the connection pool to clean up expired connections.
- setThreadUsed(boolean useThreads): Sets whether threads are used in communication with the host servers.
At the end you grab the connection with pool.connection() from the pool and release it with the close() method of the connection object. The problem with this approach is that it’s not declarative; you have to define all of the pool parameters in the code without having the chance to place them in the JNDI context. Take a look at the server.xml from the previous DBCP example. Here you have all the important pool parameters in your Tomcat configuration file, which is then placed into the JNDI context. When you change a parameter, there is nothing more to do than to restart the container. The JTOpen way forces you to recompile and create a new build of your software.
Back to the JTOpen connection pool setup. As you can see with the JTOpen code snippet, you have to create an “AS400JDBCConnectionPoolDataSource.” You can do this by defining the resource, as described in server2.xml (please rename to server.xml for production). The rest can be seen from the code snippet above. Noted that you should define more pool characteristics than just the inital size. See the table above for the list of pool properties.
READY FOR SOME POOL TIME
I have used the DBCP as well as the JTOpen pool implementation in projects over the years, and it’s hard to see a difference in runtime behavior. However, I prefer the DBCP way of handling the pool, from a coding perspective, because its use is transparent inside the application.
With these two articles in mind, you can begin your own JDBC experiences with Tomcat 5. You will see it is not hard to handle.
Note: In the first article, I provided a DataSourceFactory for obtaining DataSource objects from JNDI. I recently saw that there is allready a JNDI DataSource Factory available in the JTOpen package, called AS400JDBCObjectFactory. Although the name is a little misleading, since this factory creates only variants of DataSources, it does its job. So when you just want to lookup a DataSource, define this class in the “factory” parameter of your JNDI resource within your context, as described in the first article, or take a look at the server2.xml.
Marc Logemann is founder of Logentis, a German consulting company that focuses on iSeries and Java services and development. E-mail: mlogemann@itjungle.com
Related Article:
