Fetching a row before execute update/delete

Question

It is possible to fetch deleted or updated rows before changes were made?

Let's say I have the following statement

        dslContext.deleteFrom(Tables.USERS)
                .where(Tables.USERS.NAME.eq("xy"))
                .execute();

I'd like to print all rows that match the where statement. I known there is a DefaultExecuteListener class that I can use, but I have no idea how to do that.

Answer 1

Unfortunately, MySQL does not yet support DELETE .. RETURNING style statements (see below).

A solution that works on all RDBMS with multiple round trips

A thorough solution that would work for all kinds of sophisticated DELETE and UPDATE statements would use a VisitListener to transform the statements into equivalent SELECT statements. I can think of a ton of edge cases that would have to be taken into account if you want this to always work.

If an "80/20" solution is good enough, then you could use this simple regex based ExecuteListener. Here's an example:

create table t (i int primary key, j int);
insert into t values (1, 1), (2, 2), (3, 3);

And then:

try (Connection c = getConnection()) {
    DSLContext ctx = DSL.using(c);

    ctx.configuration().set(new DefaultExecuteListener() {
        @Override
        public void executeStart(ExecuteContext c) {
            if (c.query() instanceof Delete)
                System.out.println(ctx.fetch(c.sql().replace("delete from", "select * from")));
        }

    });
    System.out.println(ctx.delete(table("t")).where("i > 1").execute());
}

The output of the above program is:

+----+----+
|   i|   j|
+----+----+
|   2|   2|
|   3|   3|
+----+----+
2

This approach has drawbacks which may or may not matter to you:

• It does not work with plain SQL queries. If that's an issue, replace the instanceof Delete check with a more expensive c.sql().startsWith("delete") check.
• It does not take into account case sensitivity (e.g. plain SQL DELETE statements) or formatted SQL. That's fixable, of course.
• It does not work with some MySQL specific DELETE clauses, like PARTITION or IGNORE, which you could fix as well.
• The approach assumes that it's OK to run a second statement on the same Connection. This may not be the case depending on your DataSource / transaction model.

A solution that works for some non-MySQL RDBMS

For completeness' sake and future readers of this answer, I will also offer a solution that will work on DB2, Firebird, Oracle, PostgreSQL, and SQL Server, which all have a form of DELETE .. RETURNING, or equivalent statement. On those RDBMS, you can write:

dslContext.deleteFrom(Tables.USERS)
          .where(Tables.USERS.NAME.eq("xy"))
          .returning()
          .fetch();

This will delete the records and return all the affected records in one single statement, instead of creating two round trips.