How can I access the jdbc Connection in a JPA Converter?

Question

I'm using Oracle Spatial, and I have a table with an SDO_GEOMETRY field. The table is mapped to a JPA entity. I want to have the SDO_GEOMETRY field mapped to a java oracle.spatial.geometry.JGeometry type.

I figured I should use a JPA Converter and to convert to and from java.sql.Struct (or maybe oracle.sql.STRUCT).

The problem is the JGeometry method that converts to Struct, JGeometry.storeJS(Connection conn, JGeometry geom), wants the jdbc connection as a parameter.

The spring EntityManagerFactory is configured with the persistence unit name, the persistence unit contains the data source jndi name, and the data source is defined in tomcat, as a connection pool.

Any idea on how I can get the Connection in the converter ?

This what I want to achieve:

@Converter(autoApply = true)
public class GeometryConverter implements AttributeConverter<JGeometry, Struct> {

  @Override
  public Struct convertToDatabaseColumn(JGeometry geometry) {
    // How to get this connection ?
    return JGeometry.storeJS(connection, geometry);
  }

  @Override
  public JGeometry convertToEntityAttribute(Struct struct) {
    try {
      return JGeometry.loadJS(struct);
    } catch (SQLException e) {
      throw new RuntimeException("Failed to convert geometry", e);
    }
  }
}

I am using Spring 4, spring-data-jpa 1.6, Hibernate 4, Tomcat 8, Oracle 12c.

Updated with more info:

Spring configuration:

@Configuration
@EnableJpaRepositories("com.package.repository")
@EnableTransactionManagement
@ComponentScan("com.package")
public class SpringConfig {

  @Bean(name = "entityManagerFactory", destroyMethod = "destroy")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
    emf.setPersistenceUnitName("persistence-unit");
    return emf;
  }

  @Bean(name = "transactionManager")
  public JpaTransactionManager getTransactionManager() {
    return new JpaTransactionManager();
  }
}

Answer 1

If you use spring, and you need to use both JPA and JDBC, you should :

• construct a datasource bean and make connection pooling there (or get if from jndi(*))
• inject that datasource in one on the spring helpers for building the EntityManagerFactory (such as LocalContainerEntityManagerFactoryBean)
• inject that datasource in any bean where you want to do direct JDBC

That way you can use JPA for your normal DAO, and still have access to JDBC in special parts - without a too strong dependance of the internals of your JPA provider.

EDIT:

(*) If your datasource is defined by a jndi name, all is fine. Expose it as a bean (ref)

If using Spring's XML schema based configuration, setup in the Spring context like this:

<xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="http://www.springframework.org/schema/jee 
                    http://www.springframework.org/schema/jee/spring-jee-3.2.xsd">
...
<jee:jndi-lookup id="dbDataSource"
   jndi-name="jdbc/DatabaseName"
   expected-type="javax.sql.DataSource" />

Alternatively, setup using simple bean configuration like this:

<bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/DatabaseName"/>
</bean>

As you are using a JpaTransactionManager, there will not be any problem because as specified in spring javadoc This transaction manager also supports direct DataSource access within a transaction (i.e. plain JDBC code working with the same DataSource). This allows for mixing services which access JPA and services which use plain JDBC (without being aware of JPA)! provided you get your Connection through DataSourceUtils.getConnection(javax.sql.DataSource)

EDIT2 :

Ok now the only problem is how to access a singleton bean from a non bean object. A simple way to solve it is to create a holder singleton bean with a static method.

@Bean
public class DataSourceHolder implements InitializingBean {
    private DataSource dataSource;

    private static DataSourceHolder instance;

    public static DataSource getDataSource() {
        return instance.dataSource;
    }

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    @Override
    public void afterPropertiesSet() throws Exception {
        DataSourceHolder.instance = this;
    }
}

Then in any object, be it a bean or not, you can use

DataSource ds = DataSourceHolder.getDataSource();
Connection con = DataSourceUtils.getConnection(ds);

Answer 2

That would be tricky. Purely from the jpa api. You will have to dig into the specific provider implementation and get hold of the DataSource object or the PersistenceUnitInfo object.

From here you can get hold of the Connection object.

Now depending on which environment you are working. If you are on an JavaEE environment, and you inject EntityManager or its EntityManagerFactory, there is no guarantee that the return instance is an instance of the provider own implementation as this may just be a proxy that implements the interface, and hence no relation to the provider's own implementation.

On JSE environment, since you are the one creating the EntityManagerFactory from Persistence.createEntityManagerFactory(), you could tweak the provider in order to get the Connection.