May be you can try the commons-dbcp BasicDataSource? There you can disable
the auto commit.
I don't know the c3po pooled data source...
Best,
Christian
On Mon, Jul 16, 2012 at 3:19 PM, jkorab <jakub.ko...@gmail.com> wrote:
> Hi,
>
> I have come across an error when using camel-mybatis with Postgres that I
> think merits a code change.
>
> I am using Camel 2.8 in Servicemix 4.4.1, though the issue is reproducible
> in Camel 2.10. I have set up the MyBatisComponent to use a mybatis-spring
> SqlSessionFactoryBean, which in turn uses an OSGi-configured DataSource:
>
> <bean id="mybatis"
> class="org.apache.camel.component.mybatis.MyBatisComponent">
> <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
> </bean>
>
> <bean id="sqlSessionFactory"
> class="org.mybatis.spring.SqlSessionFactoryBean">
> <property name="dataSource" ref="dataSource"/>
> <property name="configLocation"
> value="/META-INF/SqlMapConfig.xml"/>
> <property name="mapperLocations"
> value="classpath*:META-INF/mappers/**/*.xml"/>
> </bean>
>
> <bean id="dataSource" class="org.postgresql.ds.PGSimpleDataSource">
> <property name="serverName" value="${pg.server}"/>
> <property name="databaseName" value="${pg.database}"/>
> <property name="portNumber" value="${pg.port}"/>
> <property name="user" value="${pg.username}"/>
> <property name="password" value="${pg.password}"/>
> </bean>
>
> This setup allows me to externalise the database connection config outside
> of MyBatis' Environments construct, so it plays nicer with how ServiceMix
> apps are typically structured.
>
> The issue pops up when I attempt to insert an object into the database
> through a simple route:
>
> <from uri="direct:insert"/>
> <to uri="mybatis:myModel.insert?statementType=Insert"/>
>
> When I send a model object in to the route, I get the following exception
> (abridged):
> org.apache.camel.CamelExecutionException: Exception occurred during
> execution on the exchange: Exchange[Message: <snip>]
> <snip>
> Caused by: org.apache.ibatis.exceptions.PersistenceException:
> ### Error committing transaction. Cause:
> org.postgresql.util.PSQLException:
> Cannot commit when autoCommit is enabled.
> ### Cause: org.postgresql.util.PSQLException: Cannot commit when autoCommit
> is enabled.
> at
>
> org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
> at
>
> org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:143)
> at
>
> org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:135)
> at
>
> org.apache.camel.component.mybatis.MyBatisProducer.doInsert(MyBatisProducer.java:125)
> at
>
> org.apache.camel.component.mybatis.MyBatisProducer.process(MyBatisProducer.java:53)
> at
>
> org.apache.camel.impl.converter.AsyncProcessorTypeConverter$ProcessorToAsyncProcessorBridge.process(AsyncProcessorTypeConverter.java:50)
> at
>
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:78)
> <snip>
> ... 33 more
> Caused by: org.postgresql.util.PSQLException: Cannot commit when autoCommit
> is enabled.
> at
>
> org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:705)
> at
>
> org.mybatis.spring.transaction.SpringManagedTransaction.commit(SpringManagedTransaction.java:97)
> at
> org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:152)
> at
> org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:80)
> at
>
> org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:140)
> ... 80 more
>
> I have confirmed that it is not a mere DataSource config thing, as I get
> the
> same results using a more typical:
>
> <bean id="dataSource"
> class="com.mchange.v2.c3p0.ComboPooledDataSource">
> <property name="driverClass" value="org.postgresql.Driver"/>
> <property name="jdbcUrl"
> value="jdbc:postgresql://${pg.server}:${pg.port}/${pg.database}"/>
> <property name="user" value="${pg.username}"/>
> <property name="password" value="${pg.password}"/>
> </bean>
>
> Having taken a look at MyBatisProducer, there are SqlSession.commit() calls
> in doInsert(), doInsertList(), doUpdate() and doDelete() - rollback() is
> never used. These appear to be the cause of the behaviour I'm seeing. From
> what I have read about the autoCommit functionality, the JDBC spec doesn't
> say whether it should be turned on or off by default
> (http://www.mchange.com/projects/c3p0/index.html#autoCommitOnClose). In
> Postgres, it is, whereas in Derby (used in the unit tests) it's not. It's
> also not in H2, which I was using for my (successful) integration tests.
>
> To confirm whether the behaviour would work correctly without the commits,
> I
> wrote up a pair of MyBatis delegate classes for SqlSessionFactory and
> SqlSession, that trapped all commits. Inserts were successful both in
> Postgres and H2. I would like to propose that the commit() calls be removed
> from the MyBatisProducer (happy to provide patch), as whether the
> transaction is committed or rolled back should be up to the underlying
> layer
> in which the user might configure a transaction manager to make that
> decision. Is there anything that I have missed around the reasoning behind
> the commits?
>
> Thanks,
>
> Jakub
>
> --
> View this message in context:
> http://camel.465427.n5.nabble.com/camel-mybatis-insert-commits-failing-with-Postgres-tp5716081.html
> Sent from the Camel Development mailing list archive at Nabble.com.
>
