Re: [Springframework-developer] Why does SingleConnectionDataSource always set autoCommit=true?

[Colin S. <col...@ex...>]

I am anything but an expert in this area unfortunately, but after 
thinking about it, from my point of view, it's somewhat of a wash as to 
which approach is 'cleaner'.

There is a very simple requirement and use case here. You already have a 
connection object, and at the same location are creating another object 
which under ideal circumstances uses the very same connection, and 
should get it with as little work as possible.  So in your ideal use 
scenario of using the real datasource, you can rely on the fact that 
asking your container (somwhat more indirectly) for a connection from 
that datasource, will get you the same connection. But it's actually out 
of your control and there's not necessarily much you can do about it if 
the container doesn't do this, in which case your are looking at a 
potentially much less efficient result. I've found two references to the 
possiblity that maybe OC4J and the Advanced container in WebSphere don't 
do the local transaction optimization:
(search for 6.12)
  http://otn.oracle.com/tech/java/oc4j/doc_library/902/servicesjun02/jca.htm
  
http://www-3.ibm.com/software/webservers/appserv/doc/v40/ae/infocenter/was/040901.html

So I think in this particular case the use of the 'real' DataSource just 
complicates things without really adding any advtantages. It's not 
making testing easier, it's not providing better separation of concerns 
(the contrary, I would say, actually), etc.

In any case, this is somewhat orthogonal to the original post which was 
about the need to allow the autoComit flag to go either way on the 
connection obtained from SingleConnectionDataSource, whether or not it 
was given an existing connection or gets it through DriverManager. 
Hopefully we are both in agreement that the option is needed in this class.

Regards,
Colin

jürgen höller [werk3AT] wrote:

>>Since in this case the DataSource is not already bound to the thread, in this case, my JDBC code ends up calling getConnection on the real DataSource (from JNDI). Then that datasource ends up allocating a new connection from its pool, which is returned to me. There is no issue in terms of working together with the existing connection, JTA takes care of that, but the whole process is a lot more heavyweight and takes more resources (two pooled connections instead of one), plus the coordination among them.
>>    
>>
> 
>To the best of my knowledge, any decent JTA implementation will return the same Connection instance if asked for a Connection for the same DataSource again within an active transaction. This is even recommended by the JTA spec, under the name "local transaction optimization" IIRC. JTA transaction managers will typically not perform 2PC for such single DataSource transactions but just for actual distributed ones. I consider it safe to assume that all major containers implement it that way.
> 
>Juergen
>
>
>jürgen höller [werk3AT] wrote:
>
>  
>
>>Colin,
>>
>>You obviously want to perform plain JDBC access within the same transaction as the Hibernate access. This can be achieved easily via HibernateTransactionManager or JtaTransactionManager, without any specific gluing.
>>
>>If you define a respective JDBC DataSource in the application context, pointing to the same one that Hibernate uses (be it a local one specified on LocalSessionFactoryBean or a shared JNDI one), you can make HibernateTransactionManager export its transactions for this particular DataSource by passing the DataSource as bean reference into its "dataSource" property. HibernateTransactionManager will simply take the very JDBC Connection that the Hibernate Session uses and bind it to the thread via DataSourceUtils for plain JDBC access on that DataSource. The latter does not have to be aware that it is working with a Hibernate-provided Connection!
>>
>>With JtaTransactionManager, it's similar: Define the same JNDI DataSource that the Hibernate SessionFactory uses for the JdbcTemplate, and JTA will care for returning the same Connection to both the Hibernate Session and the JDBC access code if in an active transaction.
>>
>>So in any case, this "same DataSource" approach is as efficient as passing the Hibernate Session's JDBC Connection manually to a JdbcTemplate instance, as it reuses the same Connection for both the Hibernate Session and the JdbcTemplate within a transaction. I strongly recommend such a solution over manual gluing.
>>
>>Juergen
>>
>>
>>
>>      -----Ursprüngliche Nachricht-----
>>      Von: Colin Sampaleanu [mailto:col...@ex...]
>>      Gesendet: Mo 25.08.2003 23:14
>>      An: jürgen höller [werk3AT]
>>      Cc: spr...@li...
>>      Betreff: Re: [Springframework-developer] Why does SingleConnectionDataSource always set autoCommit=true?
>>     
>>     
>>
>>      W/regards to auto commit in the default case (when the class gets the
>>      connection itself), I would say, exactly, the JDBC specs specifically
>>      say the getConnection called on the datasource will return a connection
>>      with autoCommit on by default. So why is SingleConnectionDataSource then
>>      going and setting it on as well. Given that SingleConnectionDatasource
>>      doesn't have any clue about the intended use of the connection, the most
>>      correct behaviour is probably to not have it call setAutoCommit with
>>      either state, by default, which would mirror normal
>>      datasource/connection usage, and also allow a mechanism to specify that
>>      it should be called one way or another. However, this may be more work
>>      than is worth it, and a default call with the option to on, along with a
>>      mechanism (another constructor with a value for the call) to override
>>      it, would be fine. So to be clear, I do think even when
>>      SingleConnectionDataSource gets the connection there is a need for an
>>      override.
>>     
>>      As for my use case, I don't think I was clear enough.  I have some code
>>      in a Hibernate specific DAO/Mapper object. I am already executing inside
>>      a transaction introduced via Spring's AOP interceptor. So I do have a
>>      SessionFactory, etc. bound to the thread. Now inside a method in this
>>      mapper I need to do an operation on a BLOB/CLOB, where there are a
>>      number of db platform specific issues, and Hibernate's support of
>>      BLOBs/CLOBs is not good enough. Given that I have a Hibernate Session
>>      which already has a connection available, using it is the best mechanism
>>      (as opposed to trying to get a real DataSource in Spring's context to
>>      use). Now I am using JDBCTemplate and JDBCHelper as convenience classes,
>>      to wrap JDBC exceptions, and for the methods they provide. Now I could call
>>        DataSourceUtils.getThreadObjectManager().bindThreadObject(...)
>>      to bind the SingleConnectionDatasource to the thread, before using the
>>      JDBCTemplate, but I'm not sure that's buying me much over the case of
>>      not binding it. In either case, JDBCTemplate is simply going to end up
>>      getting the same Connection. Because I am using the
>>      SingleConnectionDatasource, it's not like in this case there is a real
>>      DataSource object, and you only want to call getConnection on it once...
>>     
>>      Hopefully my exaplanation is clear now...
>>     
>>     
>>      jürgen höller [werk3AT] wrote:
>>     
>>      >I agree that in case of an existing connection, SingleConnectionDataSource should not override the commit mode. For the default case, I consider auto commit mode appropriate though. Any J2EE DataSource will return an auto commit connection outside a transaction, so why shouldn't SingleConnectionDataSource do too?
>>      >
>>      >BTW, what you are trying to do seems a bit awkward. Why manually feed the Connection of a Hibernate Session via SingleConnectionDataSource to JdbcTemplate? JdbcTemplate is supposed to be a threadsafe, reusable object, fetching connections on demand. Wouldn't it be easier to use Spring's thread-binding mechanism (DataSourceUtils.getThreadObjectManager) to make JdbcTemplate use a specific connection, be it implicitly via HibernateTransactionManager or explicitly in some custom way?
>>      >
>>      >Juergen
>>      >
>>      >
>>      >
>>      >       -----Ursprüngliche Nachricht-----
>>      >       Von: Colin Sampaleanu [mailto:col...@ex...]
>>      >       Gesendet: Mo 25.08.2003 20:02
>>      >       An: spr...@li...
>>      >       Cc:
>>      >       Betreff: [Springframework-developer] Why does SingleConnectionDataSource always set autoCommit=true?
>>      >     
>>      >     
>>      >
>>      >       I want to use SingleConnectionDataSource with Hibernate, so I can wrap a
>>      >       connection I get from a Hibernate Session, and feed it as a DataSource
>>      >       to JDBCTemplate/JDBCHelper.
>>      >     
>>      >       However, right now I've had to make my own variant since the current
>>      >       code always calls
>>      >         source.setAutoCommit(true);
>>      >       in the init() method.  This seems very arbitrary to me. At least in the
>>      >       case when the class is given an existing connection, it should just
>>      >       assume the commit option has been set appropriately on the connection.
>>      >     
>>      >       Even in the case when the class is opening the connection itself from an
>>      >       existing DataSource, there needs to be an option to set autoCommit
>>      >       either way.
>>      >     
>>      >       Regards,
>>    
>>