Getting java.sql.Connection from EntityManager with hibernate and JPA

[maxmil]

I am using hibernate 3 and JPA configured via Springs entity manager factory (see snippet from application-context.xml below).

Unfortunately in one part of my app i need to contruct queries that jpa cannot handle, i would like to get hold of a java.sql.Connection as i think that with this object i will be able to execute the queries that i need.

I realize that this is not the desired way to work with jpa but see no other alternatives.

I am having problems retrieving the connection object.

I have tried

Connection con = ((org.hibernate.ejb.EntityManagerImpl) em).getSession().connection();

and

Connection con = ((org.hibernate.ejb.AbstractEntityManagerImpl) em).getSession().connection();

But neither will let me cast the entity manager em to the implementation object even though when i debug the code the entity manager has the class org.hibernate.ejb.EntityManagerImpl. The error that they give is

$Proxy38 cannot be cast to org.hibernate.ejb.EntityManagerImpl

This is the first time that i've worked with entity managers and JPA and i may be missing something obvious. Perhaps someone can point me in the right direction.

Heres the snippet that corresponds to my entity manager factory configuration in application-context.xml. The entity manager is correctly injected into the class where i need to get the Connection.

	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="jpaVendorAdapter">
			<bean
				class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="database" value="MYSQL" />
				<property name="showSql" value="true" />
			</bean>
		</property>
	</bean>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/mydatabase" />
		<property name="username" value="XXX" />
		<property name="password" value="XXX" />
	</bean>

Any help would be greatly appreciated

max

[Marten Deinum]

Why not simply create a JdbcTemplate in your applicationContext, inject that into your bean and use that.. Instead of messing around and retrieve the connection yourself?!

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="datasource" ref="datasource"/> 
</bean>

<bean id="yourbean" class="YourBeanClass">
  <property name="entityManager" ref="entityManagerFactory"
  <property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>

That way Spring will also manage the transaction etc.

[maxmil]

I thought that this would cause a conflict with the entity manager, in particular i want to stay in the same transaction, but if that is not the case then this is just the solution that i'm looking for.

Thanks for the quick response.

[Marten Deinum]

That shouldn't be a a problem (at least not with the HibernateTransactionManager and I expect the same for the JpaTransactionManager). Just make sure that everything executes in one transaction, the underlying implementation should operate on the same connection, because there is already a connection bound for the current thread.

[dejanp]

From JpaTransactionManager javadocs:

With a JpaDialect specified, this implementation also supports direct DataSource access within a transaction (i.e. plain JDBC code working with the same DataSource). This allows for mixing services that access JPA (including transactional caching) and services that use plain JDBC (without being aware of JPA)!