Scott, everybody,
FYI, instead of writing your own Hibernate ConnectionProvider, you could use a Spring-configured DataSource instance, passed to LocalSessionFactoryBean's "dataSource" property. In the case of C3P0, use C3P0's ComboPooledDataSource:
Code:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass">
<value>org.hsqldb.jdbcDriver</value></property>
<property name="jdbcUrl">
<value>jdbc:hsqldb:hsql://localhost:9001</value>
</property>
<property name="user">
<value>sa</value>
</property>
<property name="password">
<value></value>
</property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
<property name="dataSource">
<ref local="dataSource"/>
</property>
<property name="mappingResources">
<value>petclinic.hbm.xml</value>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">...</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
This is actually the typical way of setting up a connection pool reference in a Spring environment: either with a local DataSource instance (like a C3P0 ComboPooledDataSource or a Commons DBCP BasicDataSource) or with a JNDI reference (via Spring's JndiObjectFactoryBean). Of course, a Hibernate-specified ConnectionProvider will work in a Spring environment too.
In the case of a DataSource passed to LocalSessionFactoryBean, Hibernate will automatically fetch Connections from the given DataSource. Note that LocalSessionFactoryBean does
not change any settings on that Connection; so it doesn't try to modify the auto-commit flag in the first place. For custom Connection settings, I usually recommend to configure your DataSource accordingly: All connection pools out there support default auto-commit flags, default isolation levels, etc.
I don't understand why Hibernate's default ConnectionProvider implementations modify the auto-commit flag
outside of transactions. Couldn't the auto-commit flag just be left as-is, to be switched off once a transaction starts and reset to the original value after transaction completion? If someone wants auto-commit off by default, why not configure the connection pool accordingly? This is really not the job of the ConnectionProvider...
Juergen