Re: C3P0 Connection pool support

[Saso Musevic]

I've little knowledge of how C3P0 works, so I'm glad you're
investigating this issue. I agree that the current strategy (using
findOracleConnection) can't work with the C3P0 proxy. But I'm not
sure if I entirely understand your proposed solution. Is the
following an accurate description of what you propose?

1. in SDOGeometryType: retrieve the Connection from the
PreparedStatement

2. wrap the connection in a dynamic proxy that implements
OracleConnection

Dynamic proxy is nice and flexible, but not really necessary. Following code would do the trick:

public class OracleC3P0ConnectionAdapter implements OracleConnection {
    private C3P0ProxyConnection wrapped;
   
    public OracleC3P0ConnectionAdapter(C3P0ProxyConnection wrapped){
        this.wrapped = wrapped;
    }

    public Statement createStatement() throws SQLException {
      return wrapped.createStatement();
    }

    public Statement createStatement(int resultSetType, int resultSetConcurrency)
            throws SQLException {
       return wrapped.createStatement(resultSetType, resultSetConcurrency);
    }
.
.
.
//All other java.sql.Connection (non Oracle specific) methods  just delegated to wrapped connection.
.
.
.
    public void oracleReleaseSavepoint(OracleSavepoint arg0)
            throws SQLException {
        try {
            Method thisMethod = OracleConnection.class.getMethod("oracleReleaseSavepoint", new Class[]{OracleSavepoint.class});
            Object toReturn = wrapped.rawConnectionOperation(thisMethod, C3P0ProxyConnection.RAW_CONNECTION, new Object[]{arg0});
            //cast and return if necessary
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }

    }
.
.
.
//all other Oracle specific methods delegated as above
}

The same procedure might also be needed for PreparedStatement - OraclePreparedStatement, ResultSet - OracleResultSet...pairs.
Of course, we are creating dependencies on C3P0 libraries, but it looks like it is unavoidable.

For performance issues, it would maybe be better to simply decide, that connection pools MUST supply an OracleConnection, rather than java.sql.Connection  instance. For example: C3P0 includes complex code generation logic, which could be easily modified to generate proxies that implement OracleConnection and extend C3P0ProxyConnection. The result would probably look much like the example above. The point is, connection pools are already quite sophisticated pieces of software and would easily solve our problem with no performance issues. It is also questionable, if complex connection pools like C3P0 really help in spatial context. I'm guessing, that most of the time spent in spatial apps is dealing with geometries, so statement caching probably shows no real performance improvement, so avoiding C3P0 might be a good idea. There are other connection pools that do not cache statements (btw: as stated on c3p0 dev page, statement caching is the reason, c3p0  connection pool disallows direct access to underlying connection).

Regards,
   Saso


_______________________________________________
hibernatespatial-users mailing list
hibernatespatial-users@...
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

[Karel Maesen]

Hi Saso,

I finally got around to answering you on this issue.

You are right that it is not really possible to avoid introducing  
dependencies to C3P0 when

I'm not really willing to introduce dependencies on the C3P0 jars for  
Hibernate Spatial.

On 30 Jan 2008, at 13:49, Saso Musevic wrote:

> Dynamic proxy is nice and flexible, but not really necessary.  
> Following code would do the trick:

<deleted>

> The same procedure might also be needed for PreparedStatement -  
> OraclePreparedStatement, ResultSet - OracleResultSet...pairs.

See also this discussion from GeoTools where they had the same  
problem: http://docs.codehaus.org/display/GEOTOOLS/J2EE+and+Connection 
+Pools

>
> Of course, we are creating dependencies on C3P0 libraries, but it  
> looks like it is unavoidable.

> For performance issues, it would maybe be better to simply decide,  
> that connection pools MUST supply an OracleConnection, rather than  
> java.sql.Connection  instance. For example: C3P0 includes complex  
> code generation logic, which could be easily modified to generate  
> proxies that implement OracleConnection and extend  
> C3P0ProxyConnection. The result would probably look much like the  
> example above. The point is, connection pools are already quite  
> sophisticated pieces of software and would easily solve our problem  
> with no performance issues.

I think the best way is to provide a "ConnectionFinder" interface  
that specifies a finder method that returns an OracleConnection. A  
default implementation uses the current strategies for retrieving an  
OracleConnection. Users of C3P0 or other connectionpools/
containers/... can then create their own implementation that  
retrieves the native OracleConnection by whatever means possible.  
This is very close to the GeoTools proposal I referred to above.

I'm going to add this interface in the next release (1.0-RC2) of  
Hibernate Spatial.


> It is also questionable, if complex connection pools like C3P0  
> really help in spatial context. I'm guessing, that most of the time  
> spent in spatial apps is dealing with geometries, so statement  
> caching  probably shows no real performance improvement, so  
> avoiding C3P0 might be a good idea. There are other connection  
> pools that do not cache statements (btw: as stated on c3p0 dev  
> page, statement caching is the reason, c3p0  connection pool  
> disallows direct access to underlying connection).

I don't expect C3P0 to increase performance for spatial data acces  
either. However, Hibernate Spatial is especially useful for  
traditional enterprise-type applications with traditional patterns of  
data access and storage but where some of the data contain  
geometries. In those cases the arguments for using C3P0 remain valid.


In any case, thank you for the information and insights regarding C3P0!

Regards,

Karel Maesen


_______________________________________________
hibernatespatial-users mailing list
hibernatespatial-users@...
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users