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

Question

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?

Lukas Eder · Accepted Answer · 2018-01-13 13:03:25Z

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/

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related