When using JdbcTemplate, how do I access the connection?

[jayschm]

In an EJB app, I'm using JdbcTemplate to simplify some of the SQL coding
I must do. Basically, I've got a session bean which uses JdbcTemplate, and
the datasource that the session bean uses is an XA datasource which
defaults to auto-commit off.

For various reasons, I need to turn auto-commit on in the connection that
JdbcTemplate ends up using, and possibly invoke commit or rollback on
the connection as well, presumably prior to closing the connection.

How can I gain access to the connection that JdbcTemplate uses?

Thanks!

: jay

[trisberg]

Short answer - you should not mess with the connection. Use some kind of transaction management that is available for your application server. You can use Bean Managed transactions if you want and you can also use Spring's transaction framework to demarcate your transactions. I can't see any good reasons for manipulating the connection in an EJB environment.

Here is an example of a transaction setup where the methods in the target class are all executed within a new transaction.

<!--  transaction manager -->
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
  <property name="transactionManagerName">
    <value>javax.transaction.TransactionManager</value>
  </property>
</bean>

<!-- factory bean with all methods executed in new transaction. -->
<bean id="senderNewTx" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
  <property name="transactionManager">
  </property>
  <property name="target">
    <bean class="org.buggybean.MyTargetClass"/>
  </property>
  <property name="transactionAttributes">
    <props>
      <prop key="*">PROPAGATION_REQUIRES_NEW</prop>
    </props>
  </property>
</bean>

Having said that, if you really need the Connection you can use

jdbcTemplate.getDataSource().getConnection()

to get hold of the COnnection object.

[irbouho]

jdbcTemplate.getDataSource().getConnection()

I have a small question with regards to this: Does jdbcTemplate use the same connection?
I mean, if I call getDataSource().getConnection() twice in my jdbcTemplate, am I garanteed to get the same connecition?

[jayschm]

We are using WebLogic 8.1 as our appserver. We are using distributed
transactions as our app uses both Informix and Oracle. We are runnning
into a situation where we receive the following exceptionr:

java.sql.SQLException: SQL operations are not allowed with no global
transaction by default for XA drivers. If the XA driver supports performing
SQL operations with no global transaction, explicitly allow it by setting
"SupportsLocalTransaction" JDBC connection pool property to true. In this
case, also remember to complete the local transaction before using the
connection again for global transaction, else a XAER_OUTSIDE
XAException may result. To complete a local transaction, you can either
set auto commit to true or call Connection.commit() or Connection.rollback().

The basic scenario is client makes a remote method call on a session bean
A. Bean A then makes a local method call on session bean B. B makes use
of JdbcTemplate to do some read-only JDBC things and returns the results
to A. A then goes on to retrieve some entity beans and possibly update
them.

It seemed plausible to me that perhaps the local transaction was being
started because of the read-only JDBC operations, and so acquiring the
connection from JdbcTemplate and invoking commit or rollback on it
might solve the problem. Hence the original question.

However jdbcTemplate.getDataSource().getConnection() will give me a
brand new connection, won't it? I'll need the connection used to by
JdbcTemplate to perform the query in order to do a commit or rollback
on it.

: jay

[trisberg]

Sounds like you are trying to operate outside of a global transaction. Have you tried setting B's transaction property to NotSupported and then use a non-XA JDBC driver?

If you want to be sure to use the same connection for some processing then you can create a SingleConnectionDataSource like this:

		Connection singleConn = dataSource.getConnection();
		singleConn.setAutoCommit(true);
		SingleConnectionDataSource singleDs = new SingleConnectionDataSource(singleConn, true);
		JdbcTemplate jt2 = new JdbcTemplate(singleDs);
		Number n = (Number) jt2.queryForObject("select count(*) from booking", Number.class); 
		singleDs.destroy();

You need to call destroy() to properly return the connection to the pool.

[ryan.tyer]

Also, silly question but you didn't mention it....you are using the JTA transactionManager in your spring configuration, correct?

[Colin Sampaleanu]

jdbcTemplate.getDataSource().getConnection()

I have a small question with regards to this: Does jdbcTemplate use the same connection?
I mean, if I call getDataSource().getConnection() twice in my jdbcTemplate, am I garanteed to get the same connecition?

That's a good question. No, you would actually get another connection, unless the DataSource itself is some sort of special DS (like the test one) which always returns the same connection. And you would in fact be responsible for properly closing that connection. If you want to just reuse the connection that may already be bound to the thread, and ensure that if there is none already bound there a new one is created and bound, you should actually use:

Connection con = DataSourceUtils.getConnection(myJdbcTemplate.getDa taSource());

That's basically what JdbcTemplate does itself, internally. Any connection you get like this will also properly be closed by the template itself (if you are using it inside the template in an execute callback) or the enclosing transaction, depending on where the connection is actually synchronized.

Regards,

[jayschm]

Also, silly question but you didn't mention it....you are using the JTA transactionManager in your spring configuration, correct?

No, that's not a silly question. I have no spring configuration other than its
default. I started using Spring because I had started going down the path
of developing something very much like JdbcTemplate, and then stumbled
across Spring and began using its JDBC support because it did almost
everything I needed, and it seemed to work "out-of-the-box".

Should I be using the JTA transaction manager? Judging by its name, I
think so. How do I configure it? To this point, I've really made use of very
little of Spring other than JdbcTemplate.

: jay

[cstamas]

<!--  transaction manager -->
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
  <property name="transactionManagerName">
    <value>javax.transaction.TransactionManager</value>
  </property>
</bean>

I think there is some typo here... transactionManagerName property expects the JNDI name of the TM of the given container... no?

<!--  transaction manager -->
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
  <property name="transactionManagerName">
    <value>java&#58;/TransactionManager</value>
  </property>
</bean>

on JBoss, and see Spring JavaDoc JtaTransactionManager for other containers.

Bye,
cstamas