2

I have a query, represented by a string:

final String q = "select 1 union select 2 union select 3";

This string comes from an external source (configuration), hence it is a string. In the real scenario, the query is ofcourse more meaningful.

I would like to execute this query as a subquery within a jOOQ type-safe query. The following works, but it is not really what I want:

    System.out.println(<context>.select().from(DSL.table("person")).where(DSL.field("identifier").in(
            <context>.fetch(q).intoArray(0)
    )).fetch());

The problem here is that I am essentially executing two queries. This introduces overhead.

Is it possible to execute the string-query as a real subquery? I somehow have to convert the string-query to a Select<Record1> instance (I guess), but I cannot find how to do that.

Jef Jedrison
  • 283
  • 4
  • 11
  • That won't make a difference; even if this were pure jooq, a subquery would be executed. – fge Feb 02 '15 at 14:49
  • True, but the intermediate results from the subquery will not be sent to the client and back to the server (for the execution of the outer query). – Jef Jedrison Feb 02 '15 at 14:56

1 Answers1

2

There are a variety of places where you can inject a Select type as plain SQL. For instance:

As a plain SQL WHERE clause:

<context>.select()
         .from(DSL.table("person"))
         .where(
             "identifier in ({0})", DSL.resultQuery(q)
         )
         .fetch();

As a plain SQL Table:

<context>.select()
         .from(DSL.table("person"))
         .where(DSL.field("identifier").in(
             DSL.select().from("(" + q + ")")
         ))
         .fetch();

There are others. The important thing to notice is that by using plain SQL, you have the possibility to embed your own SQL strings in templates that have enumerated placeholders

... {0} ... {1} ...
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks, very useful. Isn't there a case for adding the `in(String, QueryPart...)` function? I am not found of the first solution, since we are not using the `in()` function. I am also not found of the second solution, since we are `selecting from a select statement`. This is being pedantic though. – Jef Jedrison Feb 02 '15 at 16:25
  • Or even better, something like this: `.in(DSL.query("..."))` – Jef Jedrison Feb 02 '15 at 16:29
  • @JefJedrison: `in(String, QueryPart...)` is not possible, because it would conflict with `in(T...)`, especially in your case where you bind `` to `Object`. *"I am not found of the first solution, since we are not using the in() function"* - Well, I can only answer the question that you asked, not the one you had in mind :). *"I am also not found of the second solution, since we are selecting from a select statement"* - I agree that's not optimal. *"Or even better, something like this: .in(DSL.query("..."))"* There is no lean way to create a `Select>` this way – Lukas Eder Feb 02 '15 at 16:41
  • 1
    @JefJedrison: You're welcome. I agree that there is room for improvement for your use-case, but Stack Overflow comments are a bit tricky to discuss these things. Maybe, we continue discussing this on the [jOOQ User Group](https://groups.google.com/d/forum/jooq-user)? – Lukas Eder Feb 02 '15 at 17:09
  • I agree we should'nt discuss on SO. I will register myself to the mailing list later this week. Thank you for your endless support! – Jef Jedrison Feb 02 '15 at 18:33