3

I am using jOOq to write queries except I am using a vendor specific type of SQL... to be specific flexible search. The main difference between SQL and flexiSearch is that parameter values are enclosed in a curly bracket. e.g.

SELECT * FROM {Product} WHERE {code} LIKE ‘%al%’

So what I'm trying to do is to get jOOq to automatically intercept the query building procedure to include the brackets.

Looking through the docs, it seems that I should implement some kind of execute listener? But I am not sure what to do after that. Thanks

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
user1272052
  • 51
  • 3
  • Also, you need to- 1. Put curly braces around tables and their joins. 2. Apply double curly braces around subquery. to ensure the queries format match. – Farrukh Chishti Nov 13 '18 at 10:00

1 Answers1

2

You could indeed implement an ExecuteListener that replaces

  • every odd " by a { and every even " by a } using any dialect (be careful of syntactic ambiguities)
  • every odd ` by a { and every even ` by a } using MySQL dialect
  • every [ by a { and every ] by a } using SQL Server dialect

But from what I can tell, that won't be the only thing you'll be patching in generated SQL, so you might as well fork the jOOQ Open Source Edition and patch the relevant code yourself.

Beware, jOOQ doesn't really support this particular dialect. This will not be the only thing you'll run into. For example, you could try to pattern match subqueries in an ExecuteListener and wrap them in {{ ... }}, but that, too, would be much easier to achieve by patching jOOQ directly.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Hi @Lukas, thanks for your reply, it was really insightful. I had a go at overriding `DefaultVisitLister` (I need to render the sql string) but I am struggling in getting to replace the quotes with brackets using render context. This is what I tried `context.renderContext().render().replaceAll("[`]","[");` but I get NullPointer – user1272052 Nov 15 '18 at 12:46
  • @user1272052: I'll be happy to answer new questions with examples, showing what you did and more info about the problem you're getting. – Lukas Eder Nov 16 '18 at 08:08