Is statement.executeBacth() a transaction?

[George]

I need to reset a database into blank by delete several tables. I
would like to put all those "delete ..." query into one transaction.
My understanding is that addBatch() for preparedstatement is a
transaction and I do not need a rollback. How about executeBatch() in
the statement? The javadoc about this method says that it will return
an array about each one and it might be failure, which seems to
indicate it is not executed as one transaction. Is that the case?
Here is my code now. Is the rollback in the catch necessary?

try {
boolean commit = conn.getAutoCommit();

conn.setAutoCommit(false);
Statement deleteState = conn.createStatement();
for (String table : tableNames) {

deleteState.addBatch("DELETE FROM " + table);

}
deleteState.executeBatch();
conn.commit();
conn.setAutoCommit(commit);
} catch (SQLException e) {
log.error(e, e);
conn.rollback();
throw new DAOException();
}


BTW: Can the preparedStatement use table names as parameters?

conn.setAutoCommit(false);
PreparedStatement st=new PreparedStatement("Delete From ?");
for (String table:tableNames){
st.setString(1, table);
st.addBatch();
}
conn.commint();

 

[Lew]

My understanding is that addBatch() for preparedstatement is a
transaction and I do not need a rollback.

Not as I read the Javadocs. 'PreparedStatement#addBatch()'

Adds a set of parameters to this PreparedStatement object's batch of commands.

I admit I do not understand from the Javadocs just what this method
does. I guess that you call it after a series of
'PreparedStatement#setXxx()' calls in order to set parameters for
subsequent commands in the batch.

Regardless, it isn't (in) a transaction because it isn't interacting
with the DBMS yet.

How about executeBatch() in the statement?  The javadoc about this method says that it will return
an array about each one and it might be failure, which seems to
indicate it is not executed as one transaction.  Is that the case?

That depends entirely on where you set your transaction boundaries. I
do not know if it acts as one transaction if you have set auto-commit
'true', but it might. Transactions can, and quite often do comprise
many statements each, any one of which can fail. If one part of a
transaction fails, the whole thing usually does, and must be rolled
back.

Here is my code now.  Is the rollback in the catch necessary?

I would think so.

                          try {

You need to seriously tone back your indentation. Four spaces per
indent level is about the maximum for readability on Usenet.

 

[Donkey Hottie]

I need to reset a database into blank by delete several tables. I
would like to put all those "delete ..." query into one transaction.
My understanding is that addBatch() for preparedstatement is a
transaction and I do not need a rollback. How about executeBatch() in
the statement? The javadoc about this method says that it will return
an array about each one and it might be failure, which seems to
indicate it is not executed as one transaction. Is that the case?
Here is my code now. Is the rollback in the catch necessary?

try {
boolean commit = conn.getAutoCommit();

conn.setAutoCommit(false);
Statement deleteState = conn.createStatement();
for (String table : tableNames) {

deleteState.addBatch("DELETE FROM " + table);

}
deleteState.executeBatch();
conn.commit();
conn.setAutoCommit(commit);
} catch (SQLException e) {
log.error(e, e);
conn.rollback();
throw new DAOException();
}


BTW: Can the preparedStatement use table names as parameters?

conn.setAutoCommit(false);
PreparedStatement st=new PreparedStatement("Delete From ?");
for (String table:tableNames){
st.setString(1, table);
st.addBatch();
}
conn.commint();

I think the transaction begins with 'setAutoCommit(false)' and ends in
the next commit() or rollback(), when a new transaction begins.

You do not need batch for such deletes.

Just begin a transaction, do whatever you want with multiple executes and
finally commit or rollback it. A batch is not a requirement for a
successful transaction.

 

[George]

Thank you very much.

Lew#
"Transaction" for me is just either all statements executes or none
run. I got the impression about the preparedstatement#addbatch() as a
transaction from sun's document here
http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html.
They pointed out that rollback is not necessary in their example
program toward the end. So I figure that is executed as one
transaction.

Donkey#
That is a really nice point. I have never thought about that.

 

[Lew]

"Transaction" for me is just either all statements executes or none
run. I got the impression about the preparedstatement#addbatch() as a
transaction from sun's document here
http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html.
They pointed out that rollback is not necessary in their example
program toward the end. So I figure that is executed as one
transaction.

That example makes no mention whatsoever of executeBatch(). The reason they
say rollback isn't necessary is because no further use is made of the data
after a SQLException. The tutorial even goes on to say:

If the application continued and used the results of the transaction,
however, it would be necessary to include a call to rollback in the
catch block in order to protect against using possibly incorrect data.

The paired statements in their example are, in fact, executed as a single
transaction, but that is not why rollback isn't needed.

 

[Lew]

I think the transaction begins with 'setAutoCommit(false)' and ends in
the next commit() or rollback(), when a new transaction begins.

From the tutorial cited by the OP:

 

[George]

Thank you. As always, I got a bit more confused on the other by
clearing up one part. I originally thought all the statements in one
transaction are all executed or non executed in jdbc automatically. Am
I right? However, the second last paragraph in the article I linked
seems to say the opposite. It says one needs to rollback when
SQLExcption is thrown, which seems to indicate that one transaction
(one conn.commit()) might leave some statement executed and some
not.

 

[Lew]

Thank you. As always, I got a bit more confused on the other by
clearing up one part. I originally thought all the statements in one
transaction are all executed or non executed in jdbc automatically. Am
I right? However, the second last paragraph in the article I linked
seems to say the opposite. It says one needs to rollback when
SQLExcption is thrown, which seems to indicate that one transaction
(one conn.commit()) might leave some statement executed and some
not.

Please do not top-post. (nor strictly bottom-post - correct is to post
inline, trimming no-longer relevant content.)

That same article points out that all statements in a transaction are
committed or rolled back together. That is, after all, the entire point of
transactions.

"Seems to indicate" is a different proposition from "does indicate".

 

[Martin Gregorie]

Thank you. As always, I got a bit more confused on the other by
clearing up one part. I originally thought all the statements in one
transaction are all executed or non executed in jdbc automatically. Am I
right? However, the second last paragraph in the article I linked seems
to say the opposite. It says one needs to rollback when SQLExcption is
thrown, which seems to indicate that one transaction (one conn.commit())
might leave some statement executed and some not.

If you use the default (auto-commit) each SQL statement is treated as a
separate commitment unit, IOW it does:

start commitment unit
execute SQL statement
if (statement failed)
rollback
else
commit

This is OK for most single-table updating, but as soon as you start to
work with multiple tables, e.g. storing a new payment detail, crediting
the recipient and debiting the sender, then you have several SQL
statements that must all complete successfully if the database is to be
consistent. Under these conditions you need to turn auto-commit off and
your program must explicitly commit or rollback depending on whether the
statement set completed or failed.

JDBC's batch execution has nothing to do with commitment apart from
telling you that all statements executed OK or that at least one failed.
This looks to me as if you'd need to disable auto-commit before using
batch execution, so you'd still need to commit or rollback. The javadoc
says that not all JDBC drivers support batch execution, so IMO if there's
any change that your code needs to run against different RDBMS then its a
good thing to avoid.

Batch execution isn't something I'd do myself. I prefer more control over
the transaction. In any case its only suitable for running an inline set
of SQL statements while many cases the update actions depend on the
database contents (e.g. do we update an existing row or must it be
inserted).

 

[George]

That same article points out that all statements in a transaction are
committed or rolled back together.  That is, after all, the entire point of
transactions.

So let me rephrase my question. If I do something like this

try{
conn.setAutoCommit(false);
Statement state1=conn.createStatement();
state1.addBatch("update XXXX");
state1.addBatch("update YYYY");
state1.executeBatch();
conn.commit();
}
catch( SQLException e){
if (conn!=null)
try{ conn.rollback();}
catch(SQLException e){
}
}

Is it necessary to put the rollback() there to guarantee the integrity
of the database or is the transaction (one commit) already guarantee
that? In another word, is the rollback function of the transaction
built-in in the jdbc's commit or do I need to implement it manually?

Sincerely
Zhu, Guojun

 

[Lew]

Is it necessary to put the rollback() there to guarantee the integrity
of the database or is the transaction (one commit) already guarantee
that?  In another word, is the rollback function of the transaction
built-in in the jdbc's commit or do I need to implement it manually?

You have to explicitly call either 'commit()' or 'rollback()' to
terminate the transaction.

 

[George]

You have to explicitly call either 'commit()' or 'rollback()' to
terminate the transaction.

So if I have the commint() as in the above example, do I still need to
EXPLICITLY have rollback() in the catch{}, or the library will call
roll back in case of any problems automatically?

 

[Lew]

So if I have the commint() [sic] as in the above example, do I still need to

It's 'commit()', not 'commint()'.

EXPLICITLY have rollback() in the catch{}, or the library will call
roll back in case of any problems automatically?

Did you read my answer that you quoted?

If you set auto-commit to 'false', nothing happens automatically.
That is the whole point of setting auto-commit to 'false'.

Did you read the tutorial that you cited?

 

[George]

Did you read my answer that you quoted?

Well, it seems that either I did not get your explanation or you did
not make it clear enough. I appreciate your time and effort in the
usenet, but it does not help by sentences as this.

If you set auto-commit to 'false', nothing happens automatically.
That is the whole point of setting auto-commit to 'false'.

In my example above, I did set autocommit to false and use the
commit() explicitly. The question is that, in case first update runs
fine and SQLException is thrown for the second "update", will the jdbc
automatically rollback to the situation before the transaction,
(meaning rollback/wipe-out the first update), or should I do it
manually in the catch by calling rollback()? I do not think either
your post or the article gives a clear answer for this or maybe I just
missed the point somewhere.

 

[Lew]

Well, it seems that either I did not get your explanation or you did
not make it clear enough. I appreciate your time and effort in the
usenet, but it does not help by sentences as this.

Well, I'm sorry it doesn't help you, but repeating my answer as a
question leaves me with the conclusion that you didn't read the
answer.

Lew:

George:
In my example above, I did set autocommit to false and use the

Then you must EXPLICITLY (to quote your shouting) call either
'commit()' or 'rollback()' to end the transaction.

commit() explicitly.  The question is that, in case first update runs
fine and SQLException is thrown for the second "update", will the jdbc
automatically rollback to the situation before the transaction,

Question answered upthread.

(meaning rollback/wipe-out the first update), or should I do it
manually in the catch by calling rollback()?  I do not think either
your post or the article gives a clear answer for this or maybe I just
missed the point somewhere.

I said:
You asked:

do I still need to EXPLICITLY have rollback() in the catch{} ...?

You repeated the answer almost word for word only with a question
mark.

You have to explicitly call either 'commit()' or 'rollback()' to end
the transaction.

I do not know how to make that clearer. I do not comprehend repeating
the answer with a question mark as a legitimate question. If you turn
off auto-commit, then you have to use either 'commit()' or
'rollback()' explicitly. It doesn't necessarily have to be in the
'catch' block, but it has to happen somewhere.

 

[George]

Well. English is not my native tongue. So maybe I did miss some
point and did not make my question clearer enough. My question is what
happened when a commit() call failed and SQLException throwed? In the
example, let us assume that after conn.commit(), driver is able to
make the first update (XXXX), but fails on the second (YYYY). Will
the jdbc driver automatically roll the database back to the situation
before update XXXX or leave the database in the situation that XXXX
updated and yyyy not? In the first case, I do not need to put the
rollback() in the catch since the driver already does that, but in the
second case, I need to take care of it myself by calling rollback
explicitly.

The reason I am confused about it is that the "transaction" nature
indicates the all or nothing approach. But I am not sure whether this
is part of the jdbc driver's responsibility or mine?

try{
conn.setAutoCommit(false);
Statement state1=conn.createStatement();
state1.addBatch("update XXXX");
state1.addBatch("update YYYY");
state1.executeBatch();
conn.commit();
}

catch( SQLException e){
if (conn!=null)
try{ conn.rollback();}
catch(SQLException e){
}

}

 

[Lew]

Well. English is not my native tongue. So maybe I did miss some
point and did not make my question clearer enough. My question is what
happened when a commit() call failed and SQLException throwed? In the
example, let us assume that after conn.commit(), driver is able to
make the first update (XXXX), but fails on the second (YYYY). Will
the jdbc driver automatically roll the database back to the situation
before update XXXX or leave the database in the situation that XXXX
updated and yyyy not? In the first case, I do not need to put the
rollback() in the catch since the driver already does that, but in the
second case, I need to take care of it myself by calling rollback
explicitly.

The reason I am confused about it is that the "transaction" nature
indicates the all or nothing approach. But I am not sure whether this
is part of the jdbc driver's responsibility or mine?

If auto-commit is set 'false', the code must explicitly close the transaction
either via 'commit()' or via 'rollback()'.

 

[Donkey Hottie]

Well. English is not my native tongue. So maybe I did miss some
point and did not make my question clearer enough. My question is what
happened when a commit() call failed and SQLException throwed? In the
example, let us assume that after conn.commit(), driver is able to
make the first update (XXXX), but fails on the second (YYYY). Will
the jdbc driver automatically roll the database back to the situation
before update XXXX or leave the database in the situation that XXXX
updated and yyyy not? In the first case, I do not need to put the
rollback() in the catch since the driver already does that, but in the
second case, I need to take care of it myself by calling rollback
explicitly.

The reason I am confused about it is that the "transaction" nature
indicates the all or nothing approach. But I am not sure whether this
is part of the jdbc driver's responsibility or mine?

try{
conn.setAutoCommit(false);
Statement state1=conn.createStatement();
state1.addBatch("update XXXX");
state1.addBatch("update YYYY");
state1.executeBatch();
conn.commit();
}

catch( SQLException e){
if (conn!=null)
try{ conn.rollback();}
catch(SQLException e){
}

}

addBatch is not more powerful than any execute or such statement. If you
get a rollback, everything that was done after the setAutoCommit(false)
will be rolled back.

addBatch or executeBatch will not make any "partial" transactions inside
your actual transaction.

A commit is a commit, a rollback is a rollback. It's that easy.

 

[George]

A commit is a commit, a rollback is a rollback. It's that easy.

Got you.


Thank you all very much.