Yes, my connection auto commit is set to false.
But requirement is like this, that I have to process each row, execute a set of transaction in the application,
and if any of those fails, I have to rollback all and proceed to the next row of the ResultSet.
But I guess, I can't do this with an active ResultSet open.
May be I can iterate the ResultSet first, store the values in a list of value objects and then iterate over the list.
Any ideas how can I achieve this with an active ResultSet.
That's a common solution, to iterate over the results, store them in a Java Pojo, then handle each separately. The advantage is that you don't have a single result set open for the entire length of the transaction and it's generally easier to debug.
Not a great solution for transactional-based environments where one connection might want to read the uncommitted data of the other connection. Also, it still has the issue that your maintaining a single connection for longer than you need to. Generally database resources are expensive and it's a good practice to not keep them open for longer periods of time when you don't need to.
Right. I understand your point.
But, this is a specific scenario where the process is kind of one time exercise.
And the read only connection holds the DB table which are always read only, no one is manipulating those tables.
The other connection is manipulating different set of tables.
This is just a situation based solution I would say.
I understand that your solution works but it's better in the long run to eliminate locking/concurrency issues if you can. The time you invest now may prevent problems in the future such as if you change one of the queries to a shared table.