I am trying to get Transaction working with JDBC and Oracle database. In my below example, if I try and fail the second insert by putting in an invalid table name (called
BadTableName), the rollback doesnt work because the first insert makes it into the database. Please advise how I can get this to work:
-
public class Serra
-
{
-
....
-
public PreparedStatement prep;
-
..
-
-
public int methodOne(City city)
-
{
-
int one = 0;
-
try
-
{
-
prep = connection.prepareStatement("insert into States (fieldOne,fieldTwo) values (?,?)");
-
prep.setString(1, city.getFieldOne());
-
prep.setString(2, city.getFieldTwo());
-
prep.executeUpdate();
-
}
-
catch(Exception e)
-
{
-
e.printStackTrace();
-
}
-
return one;
-
}
-
-
-
public int methodTwo(City city)
-
{
-
int two = 0;
-
try
-
{
-
prep = connection.prepareStatement("insert into BadTableName (fieldThree,fieldFour) values (?,?)");
-
prep.setString(1, city.getFieldThree());
-
prep.setString(2, city.getFieldFour());
-
prep.executeUpdate();
-
}
-
catch(Exception e)
-
{
-
e.printStackTrace();
-
}
-
return two;
-
}
-
-
...
-
-
public int mainInsert(City city)
-
{
-
try
-
{
-
connection.setAutoCommit(false);
-
methodOne(city);
-
methodTwo(city);
-
connection.commit();
-
}
-
catch (SQLException ex)
-
{
-
try
-
{
-
connection.rollback();
-
}
-
catch(Exception e)
-
{
-
e.printStackTrace();
-
}
-
ex.printStackTrace();
-
}
-
finally
-
{
-
//closing part here for the ResultSet, Statement and Connection
-
}
-
-
