during a single transaction I wanted to store a cuple of
rows in a database. If the key already exists the program
says "failed" otherwise it says "ok". At the end the transaction
is commited.
I expected that if there would by a single "failed" then none
of the "ok"'s would have been stored in the database. But that
was not the case!
In Sun's Java Tutorial it is said that you should use "rollback"
but this behavior isn't right, isn't it?
From the programmers view the transaction is only partly commited.
When I was trying this from the SQL console the database behaves
like expected. Is this an error in the JDBC driver? Does it start
a new transaction after an exception?
In the example I use Postgresql with one simple table in which
onw row already exists:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
public class dbtest {
public static void main(String[] args) {
Connection con;
Statement stm;
// suppose we have this:
// create table a (a int primary key);
// insert into a values (1);
try {
Class.forName("org.postgresql.Driver");
con=DriverManager.getConnection("jdbc:postgresql://host/db", "user", "password");
con.setAutoCommit(false);
System.out.println("autocommit: "+con.getAutoCommit());
stm = con.createStatement();
for (int i=1; i<=2; i++) {
try {
System.out.print("insert "+i+"...");
stm.executeUpdate("insert into a values("+i+")");
System.out.println("ok");
} catch (SQLException e) {
System.out.println( "failed" );
System.out.println( e.getMessage() );
}
}
System.out.print("commit...");
con.commit();
System.out.println("ok");
}
catch (SQLException e) {
System.out.println("failed");
System.out.println( e.getMessage() );
}
catch (ClassNotFoundException e) {
System.out.println( e.getMessage() );
}
}
Quote:}