I got a requirement where I have to rollback a stored procedure based on a condition.
First i call the stored procedure and later check a condition and if condition fails, have to rollback. Below is the code I have tried.
public static void main(String[] args) {
Student student=new Student();
student.setName("AAAA");
student.setAge("20");
student.setDob("14/08/1988");
student.setPhone("98841");
student.setSslc("1111");
student.setHsc("222");
student.setCollege("333");
System.out.println(student);
try {
Connection conn=ConnectDB.getConnection();
conn.setAutoCommit(false);
CallableStatement callableStatement = null;
String proc = "{call STUDENT_OP(?,?,?,?,?,?,?,?)}";
callableStatement = conn.prepareCall(proc);
Savepoint savepoint1 = conn.setSavepoint("ROLLBACK_SP");
int age=Integer.parseInt(student.getAge());
callableStatement.setString(1, student.getName());
callableStatement.setInt(2, age);
callableStatement.setString(3, student.getDob());
callableStatement.setString(4, student.getPhone());
callableStatement.setString(5, student.getSslc());
callableStatement.setString(6, student.getHsc());
callableStatement.setString(7, student.getCollege());
callableStatement.registerOutParameter(8, java.sql.Types.NUMERIC);
callableStatement.executeUpdate();
int returnCode=callableStatement.getInt(8);
getStudents();
if(SOME CONDITION){
conn.rollback(savepoint1);
}
getStudents();
} catch (SQLException e) {
e.printStackTrace();
}
}
In the above code, getStudents() method prints the list of name in student table. Am running this getStudents() method before rollback and after rollback. I have set the Savepoint as Savepoint savepoint1 = conn.setSavepoint("ROLLBACK_SP"); and am rolling back using this savepoint.
But rollback is not happening. Am I missing something? please help.
