Обсуждение: JDBC and transactions
Hi
I'm trying to figure out how to use transactions with JDBC and PostGresql,
without much success. In particular, I can't figure out how to mark the
start of a transaction and how to do a rollback.
I'm iterating through a list of objects and I insert them into the DB one by
one. However, it is possible to get a NoSuchElement runtime exception while
going through the list. If this happens, I want to rollback all the previous
inserts.
The code/pseudo-code looks like this.
Connection conn = // get the connection
conn.setAutoCommit(false);
boolean rollBack = false;
while(myList.hasNext()) {
MyObject obj = null;
try {
obj = (MyObject)myList.next();
}
catch(NoSuchElementException e) {
rollBack = true;
break;
}
try {
persistToDB(conn, obj);
}
catch(SQLException e) {
rollBack = true;
break;
}
}
if(rollBack) {
try {
conn.rollback();
}
catch(SQLException e) {}
}
else {
try {
conn.commit();
}
catch(SQLException e) {}
}
conn.close();
I run this code and, in order to test it, deliberately throw a
NoSuchElementException. My code correctly detects this and calls the
rollback method on the connection. But when I look in the database, the rows
before the exception have been inserted, and the rollback hasn't done
anything. So how do I specify the start of the transaction? I've even tried
executing Statements and PreparedStatements with the query "begin", trying
to emulate the PostGresql "begin" syntax, but this doesn't work either. I
know the syntax of the statements I use to persist the object are good, and
I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
some of the exceptions because these haven't caused my any problems).
Has anybody successfully used the rollback method for a group of SQL
commands using the PostGresql JDBC driver?
Thanks
Ryan
Ryan,
This certainly should work as you have it. The only thing I can think of
is the persistToDb code is doing a commit? What does the postgres
backend log report?
Dave
On Tue, 2002-04-23 at 16:23, Ryan Chambers wrote:
>
> Hi
>
> I'm trying to figure out how to use transactions with JDBC and PostGresql,
> without much success. In particular, I can't figure out how to mark the
> start of a transaction and how to do a rollback.
>
> I'm iterating through a list of objects and I insert them into the DB one by
> one. However, it is possible to get a NoSuchElement runtime exception while
> going through the list. If this happens, I want to rollback all the previous
> inserts.
>
> The code/pseudo-code looks like this.
>
> Connection conn = // get the connection
> conn.setAutoCommit(false);
>
> boolean rollBack = false;
>
> while(myList.hasNext()) {
> MyObject obj = null;
> try {
> obj = (MyObject)myList.next();
> }
> catch(NoSuchElementException e) {
> rollBack = true;
> break;
> }
>
> try {
> persistToDB(conn, obj);
> }
> catch(SQLException e) {
> rollBack = true;
> break;
> }
> }
>
> if(rollBack) {
> try {
> conn.rollback();
> }
> catch(SQLException e) {}
> }
> else {
> try {
> conn.commit();
> }
> catch(SQLException e) {}
> }
>
> conn.close();
>
>
> I run this code and, in order to test it, deliberately throw a
> NoSuchElementException. My code correctly detects this and calls the
> rollback method on the connection. But when I look in the database, the rows
> before the exception have been inserted, and the rollback hasn't done
> anything. So how do I specify the start of the transaction? I've even tried
> executing Statements and PreparedStatements with the query "begin", trying
> to emulate the PostGresql "begin" syntax, but this doesn't work either. I
> know the syntax of the statements I use to persist the object are good, and
> I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
> some of the exceptions because these haven't caused my any problems).
>
> Has anybody successfully used the rollback method for a group of SQL
> commands using the PostGresql JDBC driver?
>
> Thanks
>
> Ryan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
Hi Dave
Arrghh... I _was_ doing a commit in the persistToDb() method. That's my
fault; it was some sloppy cutting-and-pasting of code. Everything is working
okay now.
Thanks for your help.
Ryan
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
Sent: Tuesday, April 23, 2002 1:48 PM
To: Ryan Chambers
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] JDBC and transactions
Ryan,
This certainly should work as you have it. The only thing I can think of
is the persistToDb code is doing a commit? What does the postgres
backend log report?
Dave
On Tue, 2002-04-23 at 16:23, Ryan Chambers wrote:
>
> Hi
>
> I'm trying to figure out how to use transactions with JDBC and PostGresql,
> without much success. In particular, I can't figure out how to mark the
> start of a transaction and how to do a rollback.
>
> I'm iterating through a list of objects and I insert them into the DB one
by
> one. However, it is possible to get a NoSuchElement runtime exception
while
> going through the list. If this happens, I want to rollback all the
previous
> inserts.
>
> The code/pseudo-code looks like this.
>
> Connection conn = // get the connection
> conn.setAutoCommit(false);
>
> boolean rollBack = false;
>
> while(myList.hasNext()) {
> MyObject obj = null;
> try {
> obj = (MyObject)myList.next();
> }
> catch(NoSuchElementException e) {
> rollBack = true;
> break;
> }
>
> try {
> persistToDB(conn, obj);
> }
> catch(SQLException e) {
> rollBack = true;
> break;
> }
> }
>
> if(rollBack) {
> try {
> conn.rollback();
> }
> catch(SQLException e) {}
> }
> else {
> try {
> conn.commit();
> }
> catch(SQLException e) {}
> }
>
> conn.close();
>
>
> I run this code and, in order to test it, deliberately throw a
> NoSuchElementException. My code correctly detects this and calls the
> rollback method on the connection. But when I look in the database, the
rows
> before the exception have been inserted, and the rollback hasn't done
> anything. So how do I specify the start of the transaction? I've even
tried
> executing Statements and PreparedStatements with the query "begin", trying
> to emulate the PostGresql "begin" syntax, but this doesn't work either. I
> know the syntax of the statements I use to persist the object are good,
and
> I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
> some of the exceptions because these haven't caused my any problems).
>
> Has anybody successfully used the rollback method for a group of SQL
> commands using the PostGresql JDBC driver?
>
> Thanks
>
> Ryan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html