Loop over the same update Statement #2654
Unanswered
RezaLabudeBSM
asked this question in
Q&A
Replies: 1 comment 1 reply
-
I improved my solution a little bit, with using another try - with - ressouce block around the update:
` And here is my next question: does it makes sense to have both in a try-with-ressource block or is it enought to have the one outside. And strangely again: It is working with both versions correctly. Can please someone help me here understand JDBI better, so that i use it correctly. |
Beta Was this translation helpful? Give feedback.
1 reply
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hello,
I'm new to JDBI and therefore perhaps my code or idea or useage of JDBI may be wrong, so i thought to better ask. I have the following problem: I need to run the same SQL Statement "delete from XYZ_TABLE x where x.ABC= 123 and ROWNUM <= 100" over and over again (thereby deleting always 100 rows) until the returned update count of deleted rows is less than 100, meaning that the tabel is cleaned. My initial version was pretty straight forward and looked like this:
`
`
I know that the real expensive operation here is obviously like always the database itself, but nevertheless during this operation:
'
transactionHandle.createUpdate(sqlDeleteStatement).bindMap(deleteBindMap)
'
the sqlDeleteStatement must be parsed and during bindMap the Map must be processed and all of during a loop and for each iteration as neither the sqlString nor the bindMap will change so essentially the same object will be created over and over again.
Therefore i came up with this solution where i do this "expensive creation" before the loop by creating an updateOperation with NEW and reusing it during the do-while-loop like this:
`
`
This optimized code also works, but i do not know if i used JDBI here correctly. Inside of the transactionHandle i do not use the update that was created for this transactionHandle, but for the surrounding handle ( The method transactionHandle.createUpdate( uses new Update(this, sql) where I can only hope that this is my surrounding handle that i used in the try-with-rescource block).
But as far as i can tell the transactionHandle is still working correctly also in my second example (I verified this by doing randomly transactionHandle.rollback() in between and checking in the Database the amount of rows in the table).
So my question is: Are you as JDBI Experts fine with this solution? what is you suggestion?
This topic is somewhat releted to #2447 but i think i have here a different scenario.
Beta Was this translation helpful? Give feedback.
All reactions