There is a grid in my web application where data is obtained directly from a webservice using jQuery API. Requirement is to
- allow users to do some changes to their data
- undo if they want to
- finally commit their changes
To implement this I used JDBC Savepoints and keeping them in a stack, this stack is to allow undo operations. I am setting a savepoint before update statement, on undo I want it to rollback to last savepoint and commit is simple.
I have tested it on a desktop application first and it worked fine but here in Web based application it is not working.
Database connection class
private static Map<String, Connection> userConnections;
public static Connection getConnection(configsJson) {
... //getdata from json
if (userConnections == null) {
userConnections = new HashMap<>();
} else {
userConnection = userConnections.get(userKey);
}
if (userConnection == null) {
userConnection = DriverManager.getConnection("jdbc:oracle:thin:@" + sourceIP + ":" + sourcePort + ":" + sourceInstance, sourceUser, sourcePass);
userConnection.setAutoCommit(false);
userConnections.put(userKey, userConnection);
}
return userConnection;
}
My update function
private static Map<String, Stack<Savepoint>> userSavePoints = new HashMap<>();
@ResponseBody
@RequestMapping(value = "/webservice/updateRecord", method = RequestMethod.POST)
public int updateRecord(HttpServletRequest request, @RequestParam String configs) throws SQLException, JSONException, ParseException {
Connection con = DBService.getConnection(configJson);
...
userSavePoints.get(userKey).push(con.setSavepoint());
return statement.executeUpdate(sql);
}
If there is no savepoint statement here then after update I can see the changed records on my web page, these records are not yet saved in database since autocommit is set to false. Issue is with this savepoint statement the grid doesn't show the changed records. Which to me it seems as if the records haven't updated or they auto rolled back to this savepoint.
Is there something wrong with my logic? or these savepoints doesn't work with restful api?
