CODEWITHSUNDEEP
javajooq
Eugene Goldberg
Eugene Goldberg

Reputation: 15564

Is there a way to dynamically generate JOOQ sql statement without knowing column names in advance?

I'm trying to put together a SQL generator, so I could dynamically build SQL statements, and pass them onto Spark Job Server. When the table columns are known in advance, it seems easy (using JOOQ):

String sql = DSL.select(field("field-1"), field("field-2"), field("field-3"))
        .from(table("myTable"))

However, the goal here is to be able to parse an incoming HTTP POST, get the JSON schema out of it, and generate a SQL select statement based on the table structure described in the JSON.

...

List<String> fieldNames = new ArrayList<>();
fieldNames.add("field-1");
fieldNames.add("field-2");
fieldNames.add("field-3");

...

JOOQ "field" appears to be a strongly typed object.

Is there a way to build such JOOQ select statement, using such dynamically constructed list of columns?

Upvotes: 3

Views: 2309

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

Every query that you build with jOOQ is a dynamic SQL query. For instance, in your first example:

DSL.select(field("field-1"), field("field-2"), field("field-3"))
   .from(table("myTable"))

What did you really do? You called DSL.select(SelectField, SelectField, SelectField), which is a convenience overload of DSL.select(SelectField...), which you could have called like this just the same:

SelectField[] fields = {
    field("field-1"), 
    field("field-2"), 
    field("field-3")
};
DSL.select(fields)
   .from(table("myTable"))

Or like this, calling DSL.select(Collection<? extends SelectField<?>>):

DSL.select(Arrays.asList(fields))
   .from(table("myTable"));

Always remember, even if ordinary jOOQ queries read, write, and feel like static SQL, they're really dynamic SQL queries, and you can always assign your different jOOQ queryparts to local variables, or retrieve them from methods.

This is particularly useful when used in a more functional programming paradigm:

https://blog.jooq.org/2017/01/16/a-functional-programming-approach-to-dynamic-sql-with-jooq/

Upvotes: 5

Related Questions