There are several alternatives to consider:
- #Use Commons ObjectPool Lifecycle - Use life cycle methods as notification for cleaning up the connection
- #Swap out Connection for Datasource - not recommended
- #Use ConnectionPolicy Strategy Object - acceptable compromise
And one related design consequence raised by switching to DataSource:
Shortcomings of the existing Design
- It violates the expected Java EE contract
- In a high use enviornment it would effectivly starve the rest of the Java EE application for connections
- The connection handling policy cannot be configured (it is fixed at 20 mins)
Advantages of the Existing Design
The ability to cache prepared statements is vital to good performance (it is estimaed that this optimization resulted in a 2 to 3 times speed improvement). This ability is especially important when doing a find operation (where thousands of CoordinateReferenceSystems are created in order to do a check against their ReferencedIdentifier).
We can only "keep" the PreparedStatements for as long as we hold onto a connection. This is the purpose of the "20 min timeout".
This performance must be maintained by the GeoTools change request we submit.
Use Commons ObjectPool Lifecycle
Modify the FactoryUsingOracleSQL class to create a connection as needed. Impose a lifecycle on the object so it can close a connection (and cached prepaired statements) when not in use.
The tricky part is imposing a sensible lifecycle. By using an off the shelf compoment (the commons ObjectPool) to handle the lifecycle of FactoryUsingOracleSQL instances, we get a tested real world solutions - with enough controls for object eviction, timeout delays and so on ... that we can reproduce the existing GeoTools preformance and set things up appropriatly for Java EE.
Please note that the number of instances allowed in the ObjectPool should be less than the DataSource pools size; we are using the ObjectPool basically to cache the PreparedStatements - connections are left as the responsibility of the DataSource.
Maintains expected performance, replace complex code in GeoTools that lacks configuration options with a plastic-wrapped solution.
This is a design change, there is a large impact on the shared cache.
Swap out Connection for Datasource
This would bring us in line with expected Java EE best practices, connections would be made just as needed. The solution does not allow us to keep PreparedStatements around - and as such will perform differently (this may be offset by having access to more worker threads?). This approach forces us to use either the "Fire and Forget" or "Threadsafe" concurency solution.
Simple and easy to understand, expected Java EE best practice
A fair amount of code change needed, loss of performance, careful attention needed for concurrency.
Use ConnectionPolicy Strategy Object
Since we know what we want we could very well "Just Do It"; the problem is that a compromise must be reached on the performance gain represented by PreparedStatements. This is a classic place to apply the GOF Strategy Pattern - allowing us to explicitly represent (and optimize) these two policies.
This is straight forward object oriented practice; we would need to define a new GeoTools hint to allow Brunswick to specify the preferred ConnectionPolicyFactory.
Obvious solution, gives us the ability to Compromise with GeoTools about connection handling policy (20 mins vs close immediately)
Obvious solution is not always the best, significant amount of code combined with the joy of Hints as the only configuration option
The use of Hints for configuration is not so bad - their default value can be set using a system property (either on the command line in simple applications, or most webservices have a configuration screen for such things).
What to do if you need an OracleConnection
By not making direct use of Oracle DataSource we can no longer be sure that the resulting Connection is an OracleConnection (indeed we are sure it is not when working with Tomcat). A specific chalange is to "unwrap" the Connection in order to get access to the OracleConnection for code that needs it (such as code working directly with SDO types such as SDO_Geometry).
For more details on the approach recommended please review this page:
This is a concern holding back DataSource adoption in the GeoTools library.