Commit a JDBC Transaction

[tdev]

If I do not use autocommit on or a transaction manager but simple using only a JDBC Template there is no way to commit a operation in a fashion manner. I must use getConnetion to commit or rollback but with the burden of SQLException. Why couldnt it be handled as the other operations in the template where sqlexeptions are behind the spring-dao-exception hierarchy? Or is there a way to commit this case in a spring-dao-conform manner? E.g. looking on DataSourceUtils has this feature not. And its not the question why I need it, I need it. thanks.

[karldmoore]

Surely you've got to implicitly have some form of commit. If you aren't using programatic or declarative transcations, then what happens when you execute an insert statement?

[wpoitras]

I'm not sure what you mean by an "operation". Are you talking a single call to JdbcTemplate? Data sources are automatically configured autocommit to true, so in this case each operation would be committed.

[tdev]

With operations I meant inserts and updates.
Clear I have to commit. Thats not the question.
My question is how to achieve it in spring-fashion-manner when using ONLY a jdbc-template configured with autocommit OFF and NO tx-mgr, say e.g. SingleConnectionDataSource ALONE. All I found is a programmatic way to get a connection of the datasource and do a commit with the BURDEN of having to act again myself with SQLExceptions, what is not the spring idea. The same with close(), rollback(). Could that not be done better for that case?

[wpoitras]

You can't manage transactions in Spring easily without a transaction manager. To perform a transaction programatically is you would configure:
- JdbcTemplate with a datasource
- DataSourceTransactionManager using the same datasource
- TransactionTemplate using the DataSourceTransactionManager.

You would then do something like:

        transactionTemplate.execute(new TransactionCallback() {
            public Object doInTransaction(TransactionStatus status) {
                return new Integer(jdbcTemplate.update("Insert Update SQL here"));
            }
        });

[tdev]

That's than not a "fine" solution. Dont really understand why commit,rollback,close cant go into JDBCTemplate as all the other operations or at least into DataSoureUtil so I have not to deal with SQLExceptions when using JDBC-Transaction alone, if I want so --> not to believe but Spring forces me here!. I asked because I want keep configuration simple where no explicit TransactionManager is necessarily needed and where I explicit want use autocommit off.
Thanks.

[wpoitras]

You can use explicit autocommit. But you complicate your actual code instead of complicating your configuration, and you have to handle exceptions yourself.

The code you want is in there. Its just not in as few classes as you want. JdbcTemplate is for performing JDBC operations, DataSource implementations for getting connections and PlatformTransactionManager implementations for managing transactions. You can use TransactionInterceptor, TransactionProxyFactoryBean or TransactionTemplate to apply transactions. Its just the way Spring works.

If you create JdbcTemplate based code to not manage the transactions itself, but use other objects to handle it for you, you can easily use different other transaction implementations. I understand that isn't what you want to do, but that is why this flexibility is there. I have used the flexibility and was glad it was there.

I understand your frustration. I remember converting legacy code to use JdbcTemplate and there were a lot of issues because Spring worked differently enough from standard JDBC.

I would suggest a simpler JDBC framework for your needs. You can still use the Spring container, but instead of JdbcTemplate, you could use something like SQLExecutor

[tdev]

That's why I asked! Must that so complicated in that case. And I am glad about flexibility too, no question! But why does Spring not provide me with flexibility/choice in that case ?
So SQLExecutor is worth a try. Also I get a little stomach pain using something what is already excessive provided inside spring but simply not exposed to the user . Nevertheless, beside the try, I think I switch to autocommit ON and/or using springs transaction support, although I think this does it make more complicated than it is. A somewhat overhead so to say too. Thanks again.
Or has anyone else found another way to commit,close only using Spring but no tx as I like doing so when using update,inserts,query methods of the jdbc-template without to have to deal with exception handling ?