Swimming In Connection Pools
- Tom Coppeto
Summary
Building a bunch of individual database-oriented OSID Providers lego-style will eventually lead to the question of how best to balance OSID interoperability with database connection performance.
It Started Like…
And Now The Database Jocks Are Giving Me Shit For…
You may find yourself teetering on the seesaw of interoperability and simplicity vs performance and accusations of a broken architecture.
But you have to admit, you did go a little nuts with this modularity thing.
The Shared Pool
Common Database Connection Pool
Each OSID Provider implementation gets its JDBC connection from a common place.
Implementing a Common Class
Think globally, act locally. Instead of wiring all your impls to a framework (which will no doubt need to change), create a local class which will encapsulate it. I tend to look at the OsidManagers as a place to do this.
public final class MappingManager
implements org.osid.mapping.MappingManager {
private DatabaseController dc;
@OSID @Override
public void initialize(org.osid.RuntimeManager runtime)
throws org.osid.ConfigurationErrorException,
org.osid.OperationFailedException {
this.dc = new DatabaseController.newInstance();
return;
}
@OSID @Override
public org.osid.mapping.LocationLookupSession getLocationLookupSession()
throws org.osid.OperationFailedException {
return (new LocationLookupsession(this));
}
...
/* only used by my OsidSessions */
java.sql.Connection getConnection()
throws org.osid.OperationFailedException {
return (this.dc.getConnection());
}
}When I need a database connection from an OsidSession, I ask my local OsidManager impl.
public final class LocationLookupSession
implements org.osid.mapping.LocationLookupSession {
private final LocationManager mgr;
LocationLookupSession(LocationManager mgr) {
this.mgr = mgr;
return;
}
@OSID @Override
public getLocation(org.osid.id.Id locationId)
throws org.osid.NotFoundException,
org.osid.OperationFailedException,
org.osid.PermissionDeniedException {
try (java.sql.Connection conn = this.mgr.getConnection()) {
// make query
// return results
}
}
...
}A local class to manage the connection pool. I’m using a singleton pattern so that the same pool is available to one, two, or as many OSID Providers which may be using it.
public final class DatabaseController {
private static DatabaseController DC;
private static final String CONFIG_FILE = "dbconfig.properties";
private final com.zaxxer.hikari.HikariDataSource dataSource;
private DatabaseController()
throws org.osid.ConfigurationErrorException,
org.osid.OperationFailedException {
try (java.io.InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(CONFIG_FILE)) {
if (is == null) {
throw new org.osid.ConfigurationErrorException("cannot find " + CONFIG_FILE + " for database");
}
java.util.Properties properties = new java.util.Properties();
properties.load(is);
com.zaxxer.hikari.HikariConfig config = new com.zaxxer.hikari.HikariConfig();
config.setJdbcUrl(properites.get("db_url"));
config.setUsername(properties.get("db_user"));
config.setPassword(properties.get("db_password"));
this.dataSource = new com.zaxxer.hikari.HikariDataSource(this.config)
} catch (java.io.IOException ioe) {
throw new org.osid.OperationFailedException(ioe);
}
}
public static DatabaseController newInstance()
throws org.osid.ConfigurationErrorException,
org.osid.OperationFailedException {
if (DatabaseController.DC == null) {
DatabaseController.DC = new DatabaseController();
}
return (DatabaseController.dc);
}
public java.sql.Connection getConnection()
throws org.osid.OperationFailedException {
try {
return (this.dataSource.getConnection());
} catch (java.sql.SQLException sqe) {
throw new org.osid.OperationFailedException(sqe);
}
}
}Ungluing From Common Infrastructure
This pattern works for a set of OSID Providers talking to the same database. It is possible that we have some talking to one and some talking to another. Possible solutions:
Retrieve a database configuration key from the OSID configuration in OsidManager.initialize() and pass it to DatabaseController.newInstance(). In DatabaseController, maintain a table of its own objects indexed by the configuration key. Create a new instance if none already found and use that key as part of the properties filename.
Or, create database-specific DatabaseController classes (abstracting out common things) which drive different configuration files. Use the OSID configuration in OsidManager.initialize() to select which controller class to use.
Typical Flow
The implementations of the various OSID interfaces lend themselves to performing certain jobs.
When Configuration Varies
Sometimes, the way in which the OsidSession performs its job of assembling SQL statements is dependent on the context of the situation. The OsidSession identifies context with an OsidCatalog.
The OsidCatalog can be used to stash information on how to form an SQL statement, such as a varying table name or the injection of a WHERE clause. The OsidCatalog can get this information because it is coded into its own class, supplied from the OsidManager, reads its own configuration, or a little of everything.
What About JNDI?
The use of JDNI to find a common resource is very infrastructure dependent. Instead of having each and every OSID Provider implementation be coded to a specific infrastructure, I would use the same topology to encapsulate named lookups (or injections) to Tomcat, Spring, WebSphere, or any other framework. This is a concern of the DatabaseController class. It also makes it easy to roll our own lightweight mechanism.
Â