CODEWITHSUNDEEP
javasqlhsqldbjooq
Karl S.
Karl S.

Reputation: 355

Perform SQL statement after each DELETE

Is there a way to make jOOQ perform a SQL statement (in my case a CHECKPOINT) after each DELETE?

Ideally there would also be a way to specify the tables that this happens for.

The RDBMS I'm using (HSQLDB) doesn't allow this statement inside a trigger, otherwise I would have done it this way.

Upvotes: 0

Views: 70

Answers (2)

Brian
Brian

Reputation: 3713

You should be able to execute "checkpoint" from within jOOQ using the Execute statement, something like: 

//create connection

String sql = "use [MyDatabase] checkpoint ";
connection.createStatement().executeQuery(sql);

Having said that, why run a checkpoint after each deleted record? If you're worried about the data being written to disc, just run "checkpoint" you iterate through all your delete operations.

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220877

Is there a way to make jOOQ perform a SQL statement (in my case a CHECKPOINT) after each DELETE?

The easiest way forward would be to use an ExecuteListener which checks whether the current statement is a DELETE statement. You can do this:

  • Either using a regex on the generated SQL, if you also have plain SQL statements that are not generated using the jOOQ DSL
  • Or using an instanceof Delete check on ExecuteContext.query() if you're sure that you're only running jOOQ DSL statements.

This is to detect the execution of such a statement. Your follow up statement can be run in different ways, of course.

Ideally there would also be a way to specify the tables that this happens for.

This is a bit more tricky. You could implement your own VisitListener that finds all the delete statements on specific tables.

This only works if you use the DSL API, unless you're willing to run a VisitListener on a jOOQ query that you parse based on your plain SQL statements, in case of which you could also do this for arbitrary other statements. Assuming that the parser can parse your SQL.

Upvotes: 2

Related Questions