How to obtain column names from SQL query (Jooq,Java)

Question

Hello I have a problem with obtain values from SQL query (in Java with using library of jooq)?

create table `filetest`(`id` int not null auto_increment, `Meno` varchar(21) null, `Priezvisko` varchar(24) null, `Vek` int null, constraint `pk_filetest` primary key (`id`))

or

insert into `filetest` (`Meno`, `Priezvisko`, `Vek`) values ('Jack', 'Daniels', '21')

What I need to obtain (parse/get) are values: Meno, Priezvisko, Vek. Is it possible somehow get it from sql query name of columns of table (with some jooq method)?

Answer 1

From your question, I'm assuming you'd like to use the jOOQ parser API to parse your SQL string and then extract the column names from jOOQ's meta model.

Currently (as of jOOQ 3.11), the meta model is not available through a public API. You can access it only by means of using a VisitListener, which is an SPI that is called on every QueryPart (i.e. expression tree element) that is contained in the meta model. This example implementation can give you an idea:

import org.jooq.*;
import org.jooq.impl.*;

public class Columns {
    public static void main(String[] args) {
        var parser =
        DSL.using(new DefaultConfiguration().set(new DefaultVisitListener() {
            @Override
            public void visitStart(VisitContext ctx) {
                if (ctx.queryPart() instanceof Field
                        && !(ctx.queryPart() instanceof Param))
                    System.out.println(((Named) ctx.queryPart()).getQualifiedName());
            }
        })).parser();

        System.out.println("Query 1");
        System.out.println("-------");
        parser.parseQuery("create table `filetest`(`id` int not null auto_increment, `Meno` varchar(21) null, `Priezvisko` varchar(24) null, `Vek` int null, constraint `pk_filetest` primary key (`id`))").getSQL();

        System.out.println();
        System.out.println("Query 2");
        System.out.println("-------");
        parser.parseQuery("insert into `filetest` (`Meno`, `Priezvisko`, `Vek`) values ('Jack', 'Daniels', '21')").getSQL();
    }
}

It will print:

Query 1
-------
"id"
"Meno"
"Priezvisko"
"Vek"
"id" -- Field is referenced again from the constraint

Query 2
-------
"Meno"
"Priezvisko"
"Vek"

Another option is, of course, to use reflection to access jOOQ's internals.