How to write a transaction using jdbc driver?

Question

I want to write a transaction using jdbc in java.

I have tried this simple transaction

"BEGIN TRANSACTION"+NL+"GO"+NL+"UPDATE table SET col='test' where id=1010"+NL+"GO"+NL+"COMMIT"

I have tried with

NL= "\n" and NL="\r\n" and NL="\r"

but I get always the following error:

java.sql.SQLException: Incorrect syntax near 'GO'.

In sql server management studio the transaction works

Answer 1

Get your Connection object. Turn off auto commit.

connection.setAutoCommit(false);

Wrap your entire transaction in a try-catch block. When you finish processing your inserts/updates, call:

connection.commit();

If you get an exception, call:

connection.rollback();

Don't put the transaction statements in your JDBC's SQL at that point. I suggest looking at wrappers, such as Hibernate and JPA. Transactions in JDBC can get pretty long winded.