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)?

Possible duplicate of How can I get column names from a table in SQL Server? — J.A.P
– J.A.P, Commented Dec 31, 2018 at 19:39
I don't think so . My article is about jooq and java... :) (it is about parsing sql in Jooq) — Noro96
– Noro96, Commented Dec 31, 2018 at 20:57
@Noro98 SQL is a language in it self and it is the same for all implementations. Your question is not dependant on JOOQ nor Java. You will therefor find your answer in that duplicate answer. — J.A.P
– J.A.P, Commented Jan 2, 2019 at 4:56

Lukas Eder · Accepted Answer · 2022-02-08 17:10:50Z

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.

Using the jOOQ 3.16 query object model API

jOOQ 3.16 introduced a new, experimental (as of 3.16) query object model API, which can be traversed, which is going to be enhanced in the next few minor releases. Starting from jOOQ 3.17, when you parse your SQL:

Query query = ctx.parser().parseQuery(sql);

// The QOM.CreateTable type is available in jOOQ 3.17+
if (query instanceof QOM.CreateTable ct) {
    for (TableElement te : ct.$tableElements()) {
        if (te instanceof Field<?> f) {
            System.out.println(f.getName());
        }
    }
}

Alternative approaches in earlier jOOQ versions

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.

edited Feb 8, 2022 at 17:10

answered Jan 2, 2019 at 12:47

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Noro96 Over a year ago

Can you please give me an example how it all works ? I am not able to understand it from your page or documentation... and there is not so much examples on internet with using of visitListener to get values from Query

Noro96 Over a year ago

I have last question, I want to ignore id columns, it means probably ignore all columns in table that contains classic id with incement + constraint id. I don't know if there is already method for that, but I think about: getting somehow values one by one as here below: id` int not null auto_increment Meno varchar(21) null Priezvisko varchar(24) null Vek int null constraint pk_filetest primary key (id) And then ask if values contains keywod contraint or primary key or auto_incement to ignore those values. Is there some way how to get those values in those full format ?

Lukas Eder Over a year ago

@Noro96: I've shown you a tool that can do anything you want with it. I suggest using a debugger to step through it to see how it works. Do note that the tool (VisitListener) is much more powerful than what you immediately need, so you will probably have quite a few additional follow up questions, unless you try to understand how it works, yourself. Or perhaps, it's not the right tool for your task, given its power. A future version of jOOQ will allow you to access the jOOQ expression tree directly. The current one doesn't.

Collectives™ on Stack Overflow

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

1 Answer 1

Using the jOOQ 3.16 query object model API

Alternative approaches in earlier jOOQ versions

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using the jOOQ 3.16 query object model API

Alternative approaches in earlier jOOQ versions

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related