Home > OS >  the jooq according to sqlite datebase to generate java code failed
the jooq according to sqlite datebase to generate java code failed

Time：07-21

I use jooq 3.16.5 to generate java code，when I run the code，the terminal tell me successed，but the code is not generate，I don’t know why ，so I copy the code to here and hope someone to help me

1.this is build.gradle

    buildscript {
    repositories {
        mavenLocal()
        mavenCentral()
        gradlePluginPortal()
        maven { url "https://oss.sonatype.org/content/repositories/snapshots/" }
        maven { url 'https://maven.aliyun.com/repository/public/'}
        google()
    }
    dependencies {

        classpath "org.jooq:jooq-codegen:3.16.5"
        classpath 'org.postgresql:postgresql:42.2.14'
        classpath 'org.xerial:sqlite-jdbc:3.30.1'
        classpath 'org.codehaus.groovy:groovy-all:3.0.11'
    }
}

plugins {
    id "org.flywaydb.flyway" version "8.5.13"
}

dependencies {
    implementation project(":core")
    api "com.badlogicgames.gdx:gdx-backend-lwjgl3:$gdxVersion"
    api "com.badlogicgames.gdx:gdx-platform:$gdxVersion:natives-desktop"
    api "com.badlogicgames.gdx:gdx-bullet-platform:$gdxVersion:natives-desktop"
    api "com.badlogicgames.gdx:gdx-freetype-platform:$gdxVersion:natives-desktop"
    api "com.badlogicgames.gdx-controllers:gdx-controllers-desktop:$gdxControllersVersion"
    api "com.badlogicgames.gdx:gdx-box2d-platform:$gdxVersion:natives-desktop"

    //flyway
    implementation group: 'org.flywaydb', name: 'flyway-core', version: '8.5.13'
    //jooq
    implementation group: 'org.jooq', name: 'jooq', version: '3.16.5'
    implementation group: 'org.jooq', name: 'jooq-meta', version: '3.16.5'
    implementation group: 'org.jooq', name: 'jooq-codegen', version: '3.16.5'
    implementation group: 'org.jooq', name: 'jooq-meta-extensions', version: '3.16.5'
    //groovy
    implementation 'org.codehaus.groovy:groovy-all:3.0.11'
    //sqlitejdbc
    implementation 'sqlitejdbc:sqlitejdbc:0.5.6'
    //postgresql
    implementation 'org.postgresql:postgresql:42.2.14'
}

2.this is java code to generate pojo class and other files

import org.jooq.codegen.GenerationTool;
import org.jooq.meta.jaxb.Configuration;
import org.jooq.meta.jaxb.Database;
import org.jooq.meta.jaxb.Generate;
import org.jooq.meta.jaxb.Generator;
import org.jooq.meta.jaxb.Jdbc;
import org.jooq.meta.jaxb.Property;
import org.jooq.meta.jaxb.Target;

public class JooqConfig {
    public static void main(String[] args) {

        Configuration configuration = new Configuration()

                // Configure the database connection here
                .withJdbc(new Jdbc()
                        .withDriver("org.sqlite.JDBC")
                        .withUrl("jdbc:sqlite:/home/myusername/sql/changdao.sqlite")
                )
                .withGenerator(new Generator()
                        .withDatabase(new Database()
                                .withName("org.jooq.meta.sqlite.SQLiteDatabase")
                                .withIncludes(".*")
                                .withExcludes(""  
                                        "UNUSED_TABLE                # This table (unqualified name) should not be generated"  
                                        "| PREFIX_.*                   # Objects with a given prefix should not be generated"  
                                        "| SECRET_SCHEMA\\.SECRET_TABLE # This table (qualified name) should not be generated"  
                                        "| SECRET_ROUTINE              # This routine (unqualified name) ..."  
                                        "")
                                .withInputSchema("public")
                        )

                        // Generation flags: See advanced configuration properties
                        .withGenerate(new Generate()
                                .withPojos(true)
                                .withComments(true)
                                .withCommentsOnCatalogs(true)
                                .withRelations(true)
                                .withImmutablePojos(false) // if true, cannot use 'into()' method
                                .withInterfaces(true)
                                .withDaos(true))
                        .withTarget(new Target()
                                .withPackageName("com.changdao.game.db")
                                .withDirectory("desktop/src")
                        )
                );
        try {
            GenerationTool.generate(configuration);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

this is my database

https://img.codepudding.com/202207/58a46528d5794203a7992b06fd2566bb.png

and sql is here，by a way，I use flyway to create table

create table product(
     id INTEGER primary key,
     create_time TEXT
);

I use jdk 11 and I don't konw is it important

I hope someone can help me and whatever thank you very much

CodePudding user response：

You've specified:

withInputSchema("public")

But SQLite doesn't support schemas. so it doesn't have a public schema (such as e.g. PostgreSQL). Just remove this and it should work.

Note, there should have been an INFO message about this in your code generation output.

CodePudding user response：

I change the code just like this

                    .withDatabase(new Database()
                            .withName("org.jooq.meta.sqlite.SQLiteDatabase")
                            .withIncludes(".*")
                            .withExcludes(""  
                                    "sqlite_master                # This table (unqualified name) should not be generated"  
                                    "| PREFIX_.*                   # Objects with a given prefix should not be generated"  
                                    "| SECRET_SCHEMA\\.SECRET_TABLE # This table (qualified name) should not be generated"  
                                    "| SECRET_ROUTINE              # This routine (unqualified name) ..."  
                                    "")
                            .withInputSchema("")
                    )

and console this message

   SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
org.jooq.codegen.GeneratorException: Error generating code for catalog 
    at org.jooq.codegen.JavaGenerator.generate0(JavaGenerator.java:488)
    at org.jooq.codegen.AbstractGenerator.generate(AbstractGenerator.java:182)
    at org.jooq.codegen.JavaGenerator.generate(JavaGenerator.java:202)
    at org.jooq.codegen.GenerationTool.run0(GenerationTool.java:912)
    at org.jooq.codegen.GenerationTool.run(GenerationTool.java:239)
    at org.jooq.codegen.GenerationTool.generate(GenerationTool.java:234)
    at com.changdao.game.publicclass.JooqConfig.main(JooqConfig.java:57)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error
    at org.jooq.meta.AbstractDatabase.onError(AbstractDatabase.java:3369)
    at org.jooq.meta.AbstractDatabase.getEmbeddables(AbstractDatabase.java:2045)
    at org.jooq.meta.AbstractDatabase.getEmbeddables(AbstractDatabase.java:2065)
    at org.jooq.codegen.JavaGenerator.generateSchemaIfEmpty(JavaGenerator.java:527)
    at java.base/java.util.stream.MatchOps$1MatchSink.accept(MatchOps.java:90)
    at java.base/java.util.ArrayList$ArrayListSpliterator.tryAdvance(ArrayList.java:1632)
    at java.base/java.util.stream.ReferencePipeline.forEachWithCancel(ReferencePipeline.java:127)
    at java.base/java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:502)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:488)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.MatchOps$MatchOp.evaluateSequential(MatchOps.java:230)
    at java.base/java.util.stream.MatchOps$MatchOp.evaluateSequential(MatchOps.java:196)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.anyMatch(ReferencePipeline.java:528)
    at org.jooq.codegen.JavaGenerator.generateCatalogIfEmpty(JavaGenerator.java:520)
    at org.jooq.codegen.JavaGenerator.generate0(JavaGenerator.java:482)
    ... 6 more
Caused by: java.lang.RuntimeException: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error
    at org.jooq.meta.AbstractDatabase.onError(AbstractDatabase.java:3369)
    at org.jooq.meta.AbstractDatabase.getTables(AbstractDatabase.java:1783)
    at org.jooq.meta.AbstractDatabase.getEmbeddables0(AbstractDatabase.java:2087)
    at org.jooq.meta.AbstractDatabase.lambda$getEmbeddables$13(AbstractDatabase.java:2046)
    at org.jooq.meta.AbstractDatabase.onError(AbstractDatabase.java:3359)
    ... 21 more
Caused by: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error
    at org.jooq_3.16.5.SQLITE.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:3102)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:670)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:284)
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2843)
    at org.jooq.impl.ResultQueryTrait.iterator(ResultQueryTrait.java:300)
    at org.jooq.meta.sqlite.SQLiteDatabase.getTables0(SQLiteDatabase.java:373)
    at org.jooq.meta.AbstractDatabase.lambda$getTables$11(AbstractDatabase.java:1784)
    at org.jooq.meta.AbstractDatabase.onError(AbstractDatabase.java:3359)
    ... 25 more
Caused by: java.sql.SQLException: near "with": syntax error
    at org.sqlite.DB.throwex(DB.java:288)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:114)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
    at org.sqlite.Conn.prepareStatement(Conn.java:231)
    at org.sqlite.Conn.prepareStatement(Conn.java:224)
    at org.sqlite.Conn.prepareStatement(Conn.java:213)
    at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)
    at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:82)
    at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:210)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:307)
    ... 31 more
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error

Caused by: java.lang.RuntimeException: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error

Caused by: org.jooq.exception.DataAccessException: SQL [with virtual_tables(name) as (select coalesce(sqlite_master.name, '') from sqlite_master where lower(sqlite_master.sql) like lower('%create virtual table%')) select sqlite_master.name, case when sqlite_master.type = 'view' then 'VIEW' else 'TABLE' end as table_type, sqlite_master.sql from sqlite_master where (sqlite_master.type in (?, ?) and ((sqlite_master.name not like '%!_content' escape '!' and sqlite_master.name not like '%!_segments' escape '!' and sqlite_master.name not like '%!_segdir' escape '!' and sqlite_master.name not like '%!_docsize' escape '!' and sqlite_master.name not like '%!_stat' escape '!') or (sqlite_master.name like '%!_content' escape '!' and "replace"(sqlite_master.name, '_content', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segments' escape '!' and "replace"(sqlite_master.name, '_segments', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_segdir' escape '!' and "replace"(sqlite_master.name, '_segdir', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_docsize' escape '!' and "replace"(sqlite_master.name, '_docsize', '') not in (select virtual_tables.name from virtual_tables)) or (sqlite_master.name like '%!_stat' escape '!' and "replace"(sqlite_master.name, '_stat', '') not in (select virtual_tables.name from virtual_tables)))) order by sqlite_master.name]; near "with": syntax error

Caused by: java.sql.SQLException: near "with": syntax error

I watch my database,the sqlite_master is not have this thing the sqlite_maset like this enter image description here

and the sqlite_master.sql like this

CREATE TABLE "schema_version" (
    "installed_rank" INT NOT NULL PRIMARY KEY,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INT,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f','now')),
    "execution_time" INT NOT NULL,
    "success" BOOLEAN NOT NULL
)


CREATE INDEX "schema_version_s_idx" ON "schema_version" ("success")


CREATE TABLE product(
     id INT primary key,
     create_time TEXT
)

Page link：https//www.codepudding.com/os/483078.html

Prev:Python, Float Data Sorting Problem in Dataframe
Next:Error 403- When trying to retrieve single entity from Azure Table Storage using IHttpClientFactory
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding