How to add a semi colon ; automatically to each generated sql statement using jOOQ

Question

I'm trying to add a semi colon ; to every jOOQ generated sql statement as I'm writing multiple DDL and insert statement to an output file.

I found a similar question here suggesting using an ExecuteListener here https://jooq-user.narkive.com/6adKecpt/adding-semicolon-at-the-end-of-sql-statement.

My setup is now as follows (using Groovy):

    private DSLContext createDSLContext() {
        def configuration = new DefaultConfiguration()
        configuration.settings = new Settings()
                .withRenderFormatted(true)
                .withRenderKeywordCase(RenderKeywordCase.LOWER)
                .withRenderQuotedNames(RenderQuotedNames.ALWAYS)
                .withStatementType(StatementType.STATIC_STATEMENT)
        configuration.set(
                new DefaultExecuteListenerProvider(new DefaultExecuteListener() {
                    @Override
                    void renderEnd(ExecuteContext ctx) {
                        ctx.sql(ctx.sql() + ";")
                    }
                }),
                new DefaultExecuteListenerProvider(new DefaultExecuteListener() {
                    @Override
                    void start(ExecuteContext ctx) {
                        println "YEAH!!!"
                    }
                }))
        // return configuration.dsl();
        return DSL.using(configuration)
    }

but is not adding the semi colon, nor is it getting in the renderEnd method at all. I added another execute listener to print something at the start (as I have seen in other examples) but it is also never called..

My code looks like:

        file.withWriter { writer ->

            // Drop schema objects.

            DEFAULT_SCHEMA.tables.each {
                switch (it.type) {
                    case TABLE:
                        writer.writeLine(dsl.dropTableIfExists(it).SQL)
                        break
                    case VIEW:
                        writer.writeLine(dsl.dropViewIfExists(it).SQL)
                        break
                }
            }
            writer.writeLine("")

            // Create schema objects.

            def ddlStatements = dsl.ddl(DEFAULT_SCHEMA)
            ddlStatements.each {
                writer.writeLine(it.SQL)
                writer.writeLine("")
            }

            // Insert data.

            def insert = dsl.insertInto(Tales.CUSTOMER).columns(Tales.CUSTOMER.fields())
            customers.each {insert.values(it) }
            writer.writeLine(insert.SQL)

        }

score 0 · Accepted Answer · answered Apr 06 '21 at 06:24

The ExecuteListener lifecycle is only triggered when you execute your queries with jOOQ. You're not doing that, you're just calling Query.getSQL()

You could wrap your queries into DSLContext.queries(Query...), and jOOQ will separate the statements using ; when you call Queries.getSQL() when you call Queries.toString(). Of course, that's not reliable, the behaviour of toString() might change in the future, which is why it would make sense to offer methods like Queries.getSQL() and the likes: https://github.com/jOOQ/jOOQ/issues/11755

For the time being, why not just add the semi colon manually to the writer in your code?

Yes, that's my alternative at the moment do add the semi colons manually. As I have to do it in multiple places - and I forgot one ;-) - I looked if jOOQ could do it instead and I read about the `ExecuteListener `. I now understand that it is only used where executing queries using jOOQ. Thx! — Marcel Overdijk, Apr 06 '21 at 08:04

How to add a semi colon ; automatically to each generated sql statement using jOOQ

1 Answers1