Loop over the same update Statement

[RezaLabudeBSM]

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:

`

public int deleteTableLimited(String sqlDeleteStatement, Map<String, Object> deleteBindMap, int parmDeleteMaxRownum)
{
	int numDelRecs = 0;
	int numDelRecsTotal = 0;
	do
	{
		numDelRecs = jdbi.inTransaction(transactionHandle -> {
			transactionHandle.begin();
			int amount = transactionHandle.createUpdate(sqlDeleteStatement).bindMap(deleteBindMap).execute();
			transactionHandle.commit();
			return amount;
		});
		numDelRecsTotal += numDelRecs;
	} while (parmDeleteMaxRownum > 0 && numDelRecs >= parmDeleteMaxRownum);

	return numDelRecsTotal;
}

`
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:
`

    public int deleteTableLimited(String sqlDeleteStatement, Map<String, Object> deleteBindMap, int parmDeleteMaxRownum)
{
	int numDelRecs = 0;
	int numDelRecsTotal = 0;
	try (Handle handle = jdbi.open())
	{
		Update updateOperation = new Update(handle, sqlDeleteStatement).bindMap(deleteBindMap);
		do
		{
			numDelRecs = handle.inTransaction(transactionHandle -> {
				transactionHandle.begin();
				int amount = updateOperation.execute();
				transactionHandle.commit();
				return amount;
			});
			numDelRecsTotal += numDelRecs;
		} while (parmDeleteMaxRownum > 0 && numDelRecs >= parmDeleteMaxRownum);
	}
	return numDelRecsTotal;
}

`
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.

[RezaLabudeBSM]

I improved my solution a little bit, with using another try - with - ressouce block around the update:
`

    try (Update updateOperation = new Update(handle, sqlDeleteStatement))
    {
    	updateOperation.bindMap(deleteBindMap);
	do
            {

`

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.

[stevenschlansker]

Yes, it usually should be OK to reuse a statement with changes to transaction status in-between.
When you close a Handle, all the resources (results, update statements, etc) will also be closed. It is still best practice to actively close resources you do not use with try-with-resources or a finally block.