8

I am using Spring JDBCTemplate batchUpdate to insert data in a batch. I want to verify if the data is successfully inserted. JDBCTemplate batchUpdate returns an int[][], so which is the right way to verify that the data is inserted?

This link says "All batch update methods return an int array containing the number of affected rows for each batch entry. This count is reported by the JDBC driver and it's not always available in which case the JDBC driver simply returns a -2 value". I am unable to understand the significance of returning -2 value here. Does it means the insert was unsuccessful?

Ayushi
  • 405
  • 1
  • 9
  • 19

1 Answers1

16

-2 does not necessarily mean error, it might be as mentioned, the case of count of affected rows is not available.

EDIT

-2 is the value of Statement.SUCCESS_NO_INFO (while EXECUTE_FAILED is -3). So unless the driver does not comply with the JDBC specification, -2 unequivocally means success

END OF EDIT

The errors are reported via BatchUpdateException

Normally, if you run N queries in your batch script you will get the count of updates per query i in the result:

int result[] = jdbcTemplate.batchUpdate(sql);

so:

result[0]

will hold the update count for the first query,

result[1]

will hold the update count for the second query etc.

aviad
  • 8,229
  • 9
  • 50
  • 98
  • So does that mean that even if i get -2, the insertion might be successful for that query? – Ayushi Oct 07 '13 at 14:41
  • 4
    It might be worth mentioning that `-2` is the value of [`Statement.SUCCESS_NO_INFO`](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#SUCCESS_NO_INFO) (while `EXECUTE_FAILED` is `-3`). So unless the driver does not comply with the JDBC specification, `-2` unequivocally means success. – Mark Rotteveel Oct 07 '13 at 18:37
  • @Mark Rotteveel, Thanks for the comment. Added to the answer. – aviad Oct 07 '13 at 19:42
  • Mark,Aviad, Thanks for the clarification. I should be checking for -3 instead of -2. Do we have any scenario where execution is failed but no exception is thrown and -3 is returned? – Ayushi Oct 08 '13 at 05:35
  • 2
    I can't think of a scenario where a batch result is `-3` and no exception has been thrown. But keep in mind that a driver may decide to continue executing a batch after an exception (and throw all received exceptions when it has finished), or abandon immediately (not executing the rest of the batch). So when you encounter `-3`, it doesn't necessarily mean the next elements of the batch have failed as well. – Mark Rotteveel Oct 08 '13 at 08:07
  • @MarkRotteveel Can we stop the batch update if any one among them fails? – Dharmvir Tiwari Jun 12 '17 at 08:28
  • 1
    @DharmvirTiwari Disable auto-commit and rollback on exception. Some drivers halt on first errors, some drivers will continue. This cannot be configured (except maybe in a driver specific fashion, don't know of examples though). – Mark Rotteveel Jun 12 '17 at 08:38
  • @MarkRotteveel, how about Transactional annotation which will rollback the entire transaction where any records fail to persist or any operations fail..? – Brooklyn99 Dec 05 '19 at 23:20
  • @SantosshKumhar That essentially does the same thing. – Mark Rotteveel Dec 06 '19 at 10:00