CMT, JdbcTemplate and connection pools

[jayschm]

I am using JdbcTemplate in a very simple fashion, basically to do mass
updates within a session bean's method. I have no configuration for any
Spring classes defined. The session bean is using CMT.

The method is similar to

public void updateTmpQue( Finder criteria ) throws RemoteApplicationException {
    String dataSourceName = "jdbc/SessionDs";
    DataSource = (DataSource) getInitialContext().lookup( dataSourceName );
	// Insert this chunk into temporary_que
	JdbcTemplate template = new JdbcTemplate( dataSource );
	template.batchUpdate( m_insertSql, new TmpQuePreparedStatementSetter( criteria ) );
}

Now, however, I need to have another method on the session bean call
the above method, then perform a query against some of the updated
rows. The query is using the same technique with a JdbcTemplate,
except it executes a query instead of an update.

The problem is that the query doesn't see the updated rows. I'm assuming
this is because, even though it is within the same CMT transaction, the
connection that the JdbcTemplate gets from the datasource is different,
and so the query can't see the updates.

Is there a way I can have both JdbcTemplates share the same connection?

Thanks,

: jay

[wpoitras]

You would need to declare a transaction. You can either use a TransactionProxyFactoryBean to do declarative transactions or just start transactions programatically. See the Spring Reference manual under Transaction Management. This should show explain these options.

I haven't found a way to get two different operations to share the same database connection any other way. Given the way JdbcTemplate and datasources work, transactions are intimately involved.

[jayschm]

Well, here's what I'm doing: I get the datasource, get a connection from it,
then create a SingleConnectionDataSource from that, and use that as the
datasource for the JdbcTemplates for the update and the query.

This seems to be working. I'm just curious if there are any issues with
doing it this way.

: jay

[wpoitras]

Well, here's what I'm doing: I get the datasource, get a connection from it,
then create a SingleConnectionDataSource from that, and use that as the
datasource for the JdbcTemplates for the update and the query.

The JdbcTemplate (and any RdbmsOperation subclasses that might be constructed using it) would not be thread safe. This isn't that big a deal, it just changes the way you create these objects for use.