CODEWITHSUNDEEP
sqljdbctransactions
altralaser
altralaser

Reputation: 2073

Difference between JDBC transactions and sql script commands

I have a question using transactions in a JDBC connection.
I know how to use setAutoCommit(), roolback() and commit().
But now I read that it is also possible to use these commands in a sql script:

begin transaction;
create table table1 ();
create table table2 ();
commit;

What is the difference? And what happens if I set the auto-commit mode of the JDBC connection to false and then run the sql script? Does the transaction commands then only have temporary effects or are the commands ignored?

Upvotes: 1

Views: 454

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21170

If you want to use transactions in your application, you typically set autoCommitto FALSE and you explicitly issues COMMIT or ROLLBACK.

So the script in your question will create the two tables in one transaction, i.e. either both or none.

Two notes to your script:

Some database systems (e.g. Oracle) do implicitly open the transaction (there is no begin transaction)

Some database systems (e.g. Oracle, MySql) do implicitly COMMIT the create table statement, so the last COMMIT will have no effect and in those RDBMS it is possible to end with only one table created.

Upvotes: 0

Andy Guibert
Andy Guibert

Reputation: 42966

What is the difference?

Running JDBC code such as commit() or rollback() is simply a Java wrapper around raw sql (i.e. sql script). If you call commit() from a JDBC connection, ultimately the commit command gets called in SQL as a result.

And what happens if I set the auto-commit mode of the JDBC connection to false and then run the sql script?

If you set auto-commit mode of a JDBC connection to false, you are only setting auto-commit false for the scope of that particular connection. If you have a different connection and invoke an sql script, or even open another JDBC connection, the connections would have their own auto-commit modes.

Does the transaction commands then only have temporary effects or are the commands ignored?

If you are operating with auto-commit=false, then nothing permanent happens to the database until you commit the transaction. If you are operating with auto-commit=true, then a commit is essentially happening after every command.

Upvotes: 0

Related Questions