Does jOOQ support putting names on SQL statements?

user_303914 0
Viewed 23

Is there a way to "tag" or put names on SQL statements in jOOQ so when I look at the Performance Insights of AWS RDS, I can see something more meaningful than the first 500 chars of the statement?

For example, Performance Insights shows that this query is taking a toll in my DB:

select "my_schema"."custs"."id", "my_schema"."custs"."other_id", "my_schema"."custs"."cid_id", "my_schema"."custs"."valid_since", "my_schema"."custs"."valid_until", "my_schema"."custs"."address", "my_schema"."custs"."address_id_1", "my_schema"."pets"."id", "my_schema"."pets"."cst_id", "my_schema"."pets"."tag", "my_schema"."pets"."name", "my_schema"."pets"."description", "my_schema"."pets"."owner", "my_schema"."pets"."created_on", "my_schema"."pets"."created_by", "my_schema"."pets"."modified_on",

But as it comes chopped, it's not straight-forward to know which jOOQ code generated this.

I would prefer to see something like this:

Customer - Pet Lookup

or:

(Customer - Pet Lookup) select "my_schema"."custs"."id", "my_schema"."custs"."other_id", "my_schema"."custs"."cid_id", "my_schema"."custs"."valid_since", "my_schema"."custs"."valid_until", "my_schema"."custs"."address", "my_schema"."custs"."address_id_1", "my_schema"."pets"."id", "my_schema"."pets"."cst_id", "my_schema"."pets"."tag", "my_schema"."pets"."name", "my_schema"."pets"."description", "my_schema"."pets"."owner", "my_schema"."pets"."created_on", "my_schema"."pets"."created_by", "my_schema"."pets"."modified_on",

jooq amazon-rds
1 Answers

There are at least two out of the box approaches to what you want to achieve, both completely vendor agnostic:

1. Use "hints"

jOOQ supports Oracle style hints using the hint() method, at least for SELECT statements. Write something like:

ctx.select(T.A, T.B)
   .hint("/* my tag */")
   .from(T)
   .where(...)

The limitation here is the location of the hint, which is going to be right after the SELECT keyword. Not sure if this will work for your RDBMS.

2. Use an ExecuteListener

You can supply your Configuration with an ExecuteListener, which patches your generated SQL strings with whatever you need to be added:

class MyListener extends DefaultExecuteListener {

    // renderEnd() is called after the SQL string is generated, but
    // before the prepared statement is created, let alone executed
    @Override
    public void renderEnd​(ExecuteContext ctx) {
        if (mechanismToDetermineIfTaggingIsNeeded())
            ctx.sql("/* My tag */ " + ctx.sql());
    }
}

Using regular expressions, you can place that tag at any specific location within your SQL string.

user_5217990
Related