JOOQ to get Table Names from MySQL sql file

[tuk]

We are using JOOQ with MySQL 8 for generating Java code.

Can someone let me know if it is possible to also use JOOQ as a parser to extract all table names referenced in a MySQL .sql file? The .sql file can contain both DDL, DML statements. 

[Lukas Eder]

Hello,

You can use jOOQ's query object model API to extract all tables from your parsed queries:

https://www.jooq.org/doc/latest/manual/sql-building/model-api/

It will be easiest to achieve using Traversers:

https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-traversal/

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Traversers.html

... but it's also possible to do this manually

I hope this helps,

Lukas

On Thu, Aug 21, 2025 at 3:26 PM tuk <subhara...@gmail.com> wrote:

We are using JOOQ with MySQL 8 for generating Java code.

Can someone let me know if it is possible to also use JOOQ as a parser to extract all table names referenced in a MySQL .sql file? The .sql file can contain both DDL, DML statements. 

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/295226fc-b029-46aa-a446-9924c1dca87fn%40googlegroups.com.

[tuk]

Thanks for replying. 

I am using Jooq 3.19.25 Open source edition. I tried something like below

class TableExtractorTest {

    @Test
    @SneakyThrows
    void test() {
        String sql = new String(Files.readAllBytes(Paths.get("/tmp/create_db.sql")));

        Parser parser = DSL.using(SQLDialect.MYSQL).parser();
        Queries queries = parser.parse(sql);

        Set<String> tableNames = new HashSet<>();

        for (Query query : queries) {
            Configuration configuration = DSL.using(SQLDialect.MYSQL)
                    .configuration()
                    .derive(() -> new DefaultVisitListener() {
                        @Override
                        public void visitStart(VisitContext context) {
                            if (context.queryPart() instanceof Table) {
                                Table<?> table = (Table<?>) context.queryPart();
                                tableNames.add(table.getName());
                            }
                        }
                    });
            DSL.using(configuration).render(query);
        }

        System.out.println("Tables found: " + tableNames);
    }
}

But it is failing for queries like below

CREATE TABLE IF NOT EXISTS `audit_control`
(
   `id`            BIGINT auto_increment NOT NULL,
   `message_id`    VARCHAR(100) DEFAULT NULL,
    `resource_type` VARCHAR(30) NOT NULL,
    `event_type`    VARCHAR(30) NOT NULL,
    `resource_id`   INT NOT NULL,
    `origin`        VARCHAR(100) NOT NULL,
    `created_at`    TIMESTAMP NOT NULL,
    `body`          mediumtext NOT NULL,
    `audit_ts`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, audit_ts),
    KEY `audit_control_resource_type_resource_id_IDX` (`resource_type`,`resource_id`) USING BTREE,
    KEY `audit_control_created_at_IDX` (`created_at`) USING BTREE
    ) PARTITION BY RANGE ( UNIX_TIMESTAMP(audit_ts)) (
    PARTITION p2401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
    PARTITION p2402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00')),
    PARTITION p2403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01 00:00:00')),
    PARTITION p2404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01 00:00:00')),
    PARTITION p2405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01 00:00:00')),
    PARTITION p2406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01 00:00:00')),
    PARTITION p2407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01 00:00:00')),
    PARTITION p2408 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01 00:00:00')),
    PARTITION p2409 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01 00:00:00')),
    PARTITION p2410 VALUES LESS THAN (UNIX_TIMESTAMP('2024-11-01 00:00:00')),
    PARTITION p2411 VALUES LESS THAN (UNIX_TIMESTAMP('2024-12-01 00:00:00')),
    PARTITION p2412 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')),
    PARTITION pN VALUES LESS THAN MAXVALUE
);

I am getting the below error

org.jooq.impl.ParserException: Unsupported query type: [18:7] ..._created_at_IDX` (`created_at`) USING BTREE
    ) [*]PARTITION BY RANGE ( UNIX_TIMESTAMP(audit_ts)) (
                               ...

at org.jooq.impl.AbstractParseContext.exception(AbstractParseContext.java:173)

[Lukas Eder]

You can wrap syntax that jOOQ's parser doesn't understand with a special comment syntax explained here:

https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-parser/

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/6fcb4db9-e167-4cf7-a99e-e5c412eaa4b4n%40googlegroups.com.