How to use hibernate-spatial with Oracle and c3p0

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How to use hibernate-spatial with Oracle and c3p0

shane
It took me most of the day to figure this out, so I thought I would share to the list to help out frantic Googlers from the future.

If you're seeing the error "Couldn't get at the OracleSpatial Connection object from the PreparedStatement" and you're using c3p0, this is for you.

First, make sure you're using hibernate-spatial 1.0+ (if you're seeing the above error then you already are).

Create a class in your project that implements the ConnectionFinder like so:

package your.package;

import java.lang.reflect.Method;
import java.sql.Connection;

import oracle.jdbc.OracleConnection;

import org.hibernatespatial.helper.FinderException;
import org.hibernatespatial.oracle.ConnectionFinder;

import com.mchange.v2.c3p0.C3P0ProxyConnection;

/**
 * Code taken from Spring's C3P0NativeJdbcExtractor
 */
public class OracleC3P0ConnectionFinder implements ConnectionFinder {
public static Connection getRawConnection(Connection con) {
return con;
}

public OracleConnection find( Connection con ) throws FinderException {
if (con instanceof OracleConnection) {
return (OracleConnection) con;
}
if (con instanceof C3P0ProxyConnection) {
C3P0ProxyConnection cpCon = (C3P0ProxyConnection) con;
Connection unwrappedCon = null;
try {
Method rawConnectionMethod = getClass().getMethod("getRawConnection",
new Class[] {Connection.class});
unwrappedCon = (Connection) cpCon.rawConnectionOperation(
rawConnectionMethod, null, new Object[] {C3P0ProxyConnection.RAW_CONNECTION});
}
catch (Exception ex) {
throw new FinderException( ex.getMessage() );
}
if( unwrappedCon != null && unwrappedCon instanceof OracleConnection ){
return (OracleConnection) unwrappedCon;
}
}
throw new FinderException("Couldn't get Oracle Connection in OracleConnectionFinder");
}
}


Then add a file to the classpath (in src/main/resources if you're using Maven) called org.hibernatespatial.oracle.OracleSpatial10gDialect.properties (see http://www.hibernatespatial.org/hibernate-spatial-oracle/usage.html). NOTE: if you're using a custom dialect, name it according to your dialect's classname instead.

Then put this in org.hibernatespatial.oracle.OracleSpatial10gDialect.properties:
CONNECTION-FINDER = your.package.OracleC3P0ConnectionFinder

That should do it. Good luck!

--
Shane StClair
Software Engineer
Axiom Consulting & Design
http://www.axiomalaska.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use hibernate-spatial with Oracle and c3p0

fgdrf
Shane,

two things :

It depends from hibernate spatial version how to name the properties
file, in case of
* 3.6.x use org.hibernatespatial.oracle.OracleSpatial10gDialect.properties
* 4.0 use org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect.properties

or more generalized : the name of the properties file is the full
qualified name of the referenced Dialect-class.

Second:
In my case I have spring jdbc in classpath and use the following class
to get the native oracle connection from the proxy c3p0 connection:

org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor

and here is the example :

                final C3P0NativeJdbcExtractor extractore = new C3P0NativeJdbcExtractor();

                try {
                        return (OracleConnection) extractore.getNativeConnection(subject);
                catch (final SQLException e) {
                        throw new FinderException("unable top resolve OracleConnection [" +
e.getMessage() + "]");
                }


Hope this helps a bit

Frank

2012/10/11 Shane StClair <[hidden email]>:

> It took me most of the day to figure this out, so I thought I would share to
> the list to help out frantic Googlers from the future.
>
> If you're seeing the error "Couldn't get at the OracleSpatial Connection
> object from the PreparedStatement" and you're using c3p0, this is for you.
>
> First, make sure you're using hibernate-spatial 1.0+ (if you're seeing the
> above error then you already are).
>
> Create a class in your project that implements the ConnectionFinder like so:
>
> package your.package;
>
> import java.lang.reflect.Method;
> import java.sql.Connection;
>
> import oracle.jdbc.OracleConnection;
>
> import org.hibernatespatial.helper.FinderException;
> import org.hibernatespatial.oracle.ConnectionFinder;
>
> import com.mchange.v2.c3p0.C3P0ProxyConnection;
>
> /**
>  * Code taken from Spring's C3P0NativeJdbcExtractor
>  * see
> https://github.com/SpringSource/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/support/nativejdbc/C3P0NativeJdbcExtractor.java
>  */
> public class OracleC3P0ConnectionFinder implements ConnectionFinder {
> public static Connection getRawConnection(Connection con) {
> return con;
> }
>
> public OracleConnection find( Connection con ) throws FinderException {
> if (con instanceof OracleConnection) {
> return (OracleConnection) con;
> }
> if (con instanceof C3P0ProxyConnection) {
> C3P0ProxyConnection cpCon = (C3P0ProxyConnection) con;
> Connection unwrappedCon = null;
> try {
> Method rawConnectionMethod = getClass().getMethod("getRawConnection",
> new Class[] {Connection.class});
> unwrappedCon = (Connection) cpCon.rawConnectionOperation(
> rawConnectionMethod, null, new Object[]
> {C3P0ProxyConnection.RAW_CONNECTION});
> }
> catch (Exception ex) {
> throw new FinderException( ex.getMessage() );
> }
> if( unwrappedCon != null && unwrappedCon instanceof OracleConnection ){
> return (OracleConnection) unwrappedCon;
> }
> }
> throw new FinderException("Couldn't get Oracle Connection in
> OracleConnectionFinder");
> }
> }
>
>
> Then add a file to the classpath (in src/main/resources if you're using
> Maven) called org.hibernatespatial.oracle.OracleSpatial10gDialect.properties
> (see http://www.hibernatespatial.org/hibernate-spatial-oracle/usage.html).
> NOTE: if you're using a custom dialect, name it according to your dialect's
> classname instead.
>
> Then put this in
> org.hibernatespatial.oracle.OracleSpatial10gDialect.properties:
> CONNECTION-FINDER = your.package.OracleC3P0ConnectionFinder
>
> That should do it. Good luck!
>
> --
> Shane StClair
> Software Engineer
> Axiom Consulting & Design
> http://www.axiomalaska.com
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
>
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use hibernate-spatial with Oracle and c3p0

shane
Excellent points Frank. In my case I didn't have a dependency on Spring jdbc and didn't want to add the whole library just for access to one method, but if you're already using Spring you can just use the existing method as you described. Thanks for chiming in.

Best,
Shane

On Wed, Oct 10, 2012 at 11:53 PM, Frank Gasdorf <[hidden email]> wrote:
Shane,

two things :

It depends from hibernate spatial version how to name the properties
file, in case of
* 3.6.x use org.hibernatespatial.oracle.OracleSpatial10gDialect.properties
* 4.0 use org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect.properties

or more generalized : the name of the properties file is the full
qualified name of the referenced Dialect-class.

Second:
In my case I have spring jdbc in classpath and use the following class
to get the native oracle connection from the proxy c3p0 connection:

org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor

and here is the example :

                final C3P0NativeJdbcExtractor extractore = new C3P0NativeJdbcExtractor();

                try {
                        return (OracleConnection) extractore.getNativeConnection(subject);
                catch (final SQLException e) {
                        throw new FinderException("unable top resolve OracleConnection [" +
e.getMessage() + "]");
                }


Hope this helps a bit

Frank

2012/10/11 Shane StClair <[hidden email]>:
> It took me most of the day to figure this out, so I thought I would share to
> the list to help out frantic Googlers from the future.
>
> If you're seeing the error "Couldn't get at the OracleSpatial Connection
> object from the PreparedStatement" and you're using c3p0, this is for you.
>
> First, make sure you're using hibernate-spatial 1.0+ (if you're seeing the
> above error then you already are).
>
> Create a class in your project that implements the ConnectionFinder like so:
>
> package your.package;
>
> import java.lang.reflect.Method;
> import java.sql.Connection;
>
> import oracle.jdbc.OracleConnection;
>
> import org.hibernatespatial.helper.FinderException;
> import org.hibernatespatial.oracle.ConnectionFinder;
>
> import com.mchange.v2.c3p0.C3P0ProxyConnection;
>
> /**
>  * Code taken from Spring's C3P0NativeJdbcExtractor
>  * see
> https://github.com/SpringSource/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/support/nativejdbc/C3P0NativeJdbcExtractor.java
>  */
> public class OracleC3P0ConnectionFinder implements ConnectionFinder {
> public static Connection getRawConnection(Connection con) {
> return con;
> }
>
> public OracleConnection find( Connection con ) throws FinderException {
> if (con instanceof OracleConnection) {
> return (OracleConnection) con;
> }
> if (con instanceof C3P0ProxyConnection) {
> C3P0ProxyConnection cpCon = (C3P0ProxyConnection) con;
> Connection unwrappedCon = null;
> try {
> Method rawConnectionMethod = getClass().getMethod("getRawConnection",
> new Class[] {Connection.class});
> unwrappedCon = (Connection) cpCon.rawConnectionOperation(
> rawConnectionMethod, null, new Object[]
> {C3P0ProxyConnection.RAW_CONNECTION});
> }
> catch (Exception ex) {
> throw new FinderException( ex.getMessage() );
> }
> if( unwrappedCon != null && unwrappedCon instanceof OracleConnection ){
> return (OracleConnection) unwrappedCon;
> }
> }
> throw new FinderException("Couldn't get Oracle Connection in
> OracleConnectionFinder");
> }
> }
>
>
> Then add a file to the classpath (in src/main/resources if you're using
> Maven) called org.hibernatespatial.oracle.OracleSpatial10gDialect.properties
> (see http://www.hibernatespatial.org/hibernate-spatial-oracle/usage.html).
> NOTE: if you're using a custom dialect, name it according to your dialect's
> classname instead.
>
> Then put this in
> org.hibernatespatial.oracle.OracleSpatial10gDialect.properties:
> CONNECTION-FINDER = your.package.OracleC3P0ConnectionFinder
>
> That should do it. Good luck!
>
> --
> Shane StClair
> Software Engineer
> Axiom Consulting & Design
> http://www.axiomalaska.com
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
>
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users



--
Shane StClair
Software Engineer
Axiom Consulting & Design
http://www.axiomalaska.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users