0

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

Noro96
  • 123
  • 11
  • Possible duplicate of [How can I get column names from a table in SQL Server?](https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server) – J.A.P Dec 31 '18 at 19:39
  • I don't think so . My article is about jooq and java... :) (it is about parsing sql in Jooq) – Noro96 Dec 31 '18 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 Jan 02 '19 at 04:56

1 Answers1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 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 Jan 17 '19 at 20:21
  • 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 ? – Noro96 Jan 20 '19 at 10:52
  • @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. – Lukas Eder Jan 21 '19 at 08:39