I'm using jOOQ to rewrite a JPA based service, for the usual reasons. One of the things I'm revisiting is the way we used to catch "the user passed a resource ID that (doesn't exist/they don't have permission to access/whatever) to the resource's DELETE method, so we have to return 404".
In basic SQL, you're able to just DELETE FROM widgets WHERE id = 123, and the database server will reply with the number of rows deleted. If equal to 1, great, commit. If equal to 0, rollback and throw HTTP 404. If greater than 1, rollback and panic because we probably forgot to set up a primary key somewhere somehow. Compared to the JPA way of things, this spares me a database round trip, which probably involved way too many selected columns, as JPA likes to fetch an entity so it can be passed to a thing that will then delete it.
With jOOQ, I'm doing
int deletedRows = dsl.delete(WIDGETS).where(WIDGETS.ID.equal(123)).execute();
and then examine deletedRows and proceed from there.
However, the SELECT APIs have this neat fetchSingle method that encapsulates this behavior nicely, and is probably intended exactly for the case where you want to interact with a row identified uniquely, through the primary key or some other unique combination of columns. Great!
How can I use this with DELETE (and UPDATE, while I'm at it)? Or do I need to keep looking at my deletedRows value and maybe write a wrapper function? It would be a simple one to be sure, but I wonder if I missed something in jOOQ that does this already.
I've looked at DELETE ... RETURNING, which does implement ResultQuery so has access to fetchSingle, but unlike SELECT ... RETURNING which can be emulated for engines that don't support the keyword, DELETE ... RETURNING and UPDATE ... RETURNING are indicated as not having support for most engines, so I'm wary of using that just for access to fetchSingle, as I don't actually need a returned column value from that query.
Indeed, only a select few jOOQ dialects have native support for RETURNING or OUTPUT or FINAL TABLE or something similar, and even if they do support the syntax for INSERT, they might not support it for UPDATE or DELETE. As per the doc of DELETE .. RETURNING (at jOOQ 3.21), only these support it natively:
If you're sure that you won't make many mistakes and have to roll back tons of rows all the time, then checking the update count will be the simplest / safest route. If, however, you don't really have a unique key on your ID column and this situation happens all the time, I'd consider using SELECT .. FOR UPDATE (pessimistic locking) prior to your DELETE statement in order to avoid too big of a transactional footprint. Rollbacks aren't cheap, especially in MVCC locking models, where frequent rollbacks can lead to quite a bit of contention.
A simple utility should be enough here:
public static int executeSingle(Query query) {
if (query.execute() != 1)
throw new MyException();
}