How to fix: Jooq code does not generate java code for sqlite in memory db from sql script

Question

I am trying to generate Jooq code to use with in memory SQLite. The problem is, that each new Connection creates a fresh SQLite instance which makes the code creation rely on sql scripts. But when I try to fit my code generation to the documentation the code wont be generated. I believe it still tries to access the database which is empty.

I am also not sure if jooq will work with a non persistent database. The database is intented to only exist during the program run. When the program shuts down the database should be gone. If jooq fetches new connections all troughout the runtime, I have to switch anyway.

Jooq generation:

public static void runJooqCodeGen() throws Exception {
    String url = "jdbc:sqlite:";
    String driver = "org.sqlite.JDBC";
    Configuration configuration = new Configuration().withJdbc(new Jdbc().withDriver(driver).withUrl(url))
            .withGenerator(new Generator()
                    .withDatabase(new Database()
                            .withProperties(new Property()
                                    .withKey("scripts")
                                    .withValue("src/main/resources/db/Schema.sql")))
                    .withGenerate(new Generate()
                            .withPojos(Boolean.TRUE)
                            .withDeprecationOnUnknownTypes(Boolean.FALSE))
                    .withTarget(new Target()
                            .withPackageName("me.leslie.generals.server.persistence.jooq")
                            .withDirectory("Generals-Server/src/main/java")));

    GenerationTool.generate(configuration);
}

src/main/resources/db/Schema.sql:

CREATE TABLE IF NOT EXISTS TROOP(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    current_health INTEGER NOT NULL,
    max_health INTEGER NOT NULL,
    pos_x DOUBLE NOT NULL,
    pos_y DOUBLE NOT NULL,
    normal_speed DOUBLE NOT NULL,
    street_speed DOUBLE NOT NULL,
    difficult_terrain_speed DOUBLE NOT NULL,
    close_combat_range DOUBLE NOT NULL,
    ranged_combat_range DOUBLE NOT NULL,
    normal_view_distance DOUBLE NOT NULL,
    disadvantaged_view_distance DOUBLE NOT NULL,
    advantaged_view_distance DOUBLE NOT NULL
);

CREATE TABLE IF NOT EXISTS ARMY(
    id INTEGER,
    hq INTEGER,
    troop INTEGER,
    FOREIGN KEY(hq) REFERENCES TROOP(id),
    FOREIGN KEY(troop) REFERENCES TROOP(id),
    UNIQUE(hq, troop),
    PRIMARY KEY (id, hq, troop)
);

The generation does not finish with an error, but this is the console output.

19:02:57.342 [main] DEBUG org.jooq.codegen.GenerationTool - Input configuration      : <onError>FAIL</onError><jdbc><driver>org.sqlite.JDBC</driver><url>jdbc:sqlite:</url></jdbc><generator><name>org.jooq.codegen.DefaultGenerator</name><database><regexMatchesPartialQualification>true</regexMatchesPartialQualification><sqlMatchesPartialQualification>true</sqlMatchesPartialQualification><includes>.*</includes><excludes></excludes><includeExcludeColumns>false</includeExcludeColumns><includeTables>true</includeTables><includeEmbeddables>true</includeEmbeddables><includeRoutines>true</includeRoutines><includeTriggerRoutines>false</includeTriggerRoutines><includePackages>true</includePackages><includePackageRoutines>true</includePackageRoutines><includePackageUDTs>true</includePackageUDTs><includePackageConstants>true</includePackageConstants><includeUDTs>true</includeUDTs><includeSequences>true</includeSequences><includeIndexes>true</includeIndexes><includePrimaryKeys>true</includePrimaryKeys><includeUniqueKeys>true</includeUniqueKeys><includeForeignKeys>true</includeForeignKeys><includeCheckConstraints>true</includeCheckConstraints><includeInvisibleColumns>true</includeInvisibleColumns><recordVersionFields></recordVersionFields><recordTimestampFields></recordTimestampFields><syntheticIdentities></syntheticIdentities><syntheticPrimaryKeys></syntheticPrimaryKeys><overridePrimaryKeys></overridePrimaryKeys><dateAsTimestamp>false</dateAsTimestamp><ignoreProcedureReturnValues>false</ignoreProcedureReturnValues><unsignedTypes>true</unsignedTypes><integerDisplayWidths>true</integerDisplayWidths><inputCatalog></inputCatalog><outputCatalogToDefault>false</outputCatalogToDefault><inputSchema></inputSchema><outputSchemaToDefault>false</outputSchemaToDefault><schemaVersionProvider></schemaVersionProvider><catalogVersionProvider></catalogVersionProvider><orderProvider></orderProvider><forceIntegerTypesOnZeroScaleDecimals>true</forceIntegerTypesOnZeroScaleDecimals><logSlowQueriesAfterSeconds>5</logSlowQueriesAfterSeconds><logSlowResultsAfterSeconds>5</logSlowResultsAfterSeconds><properties><property><key>scripts</key><value>src/main/resources/db/Schema.sql</value></property></properties></database><generate><indexes>true</indexes><relations>true</relations><implicitJoinPathsToOne>true</implicitJoinPathsToOne><deprecated>true</deprecated><deprecationOnUnknownTypes>false</deprecationOnUnknownTypes><instanceFields>true</instanceFields><generatedAnnotation>true</generatedAnnotation><generatedAnnotationType>DETECT_FROM_JDK</generatedAnnotationType><routines>true</routines><sequences>true</sequences><udts>true</udts><queues>true</queues><links>true</links><keys>true</keys><tables>true</tables><embeddables>true</embeddables><records>true</records><recordsImplementingRecordN>true</recordsImplementingRecordN><pojos>true</pojos><pojosEqualsAndHashCode>false</pojosEqualsAndHashCode><pojosToString>true</pojosToString><immutablePojos>false</immutablePojos><serializablePojos>true</serializablePojos><interfaces>false</interfaces><immutableInterfaces>false</immutableInterfaces><serializableInterfaces>true</serializableInterfaces><daos>false</daos><jpaAnnotations>false</jpaAnnotations><validationAnnotations>false</validationAnnotations><springAnnotations>false</springAnnotations><globalObjectReferences>true</globalObjectReferences><globalCatalogReferences>true</globalCatalogReferences><globalSchemaReferences>true</globalSchemaReferences><globalTableReferences>true</globalTableReferences><globalSequenceReferences>true</globalSequenceReferences><globalUDTReferences>true</globalUDTReferences><globalRoutineReferences>true</globalRoutineReferences><globalQueueReferences>true</globalQueueReferences><globalLinkReferences>true</globalLinkReferences><globalKeyReferences>true</globalKeyReferences><javadoc>true</javadoc><comments>true</comments><commentsOnCatalogs>true</commentsOnCatalogs><commentsOnSchemas>true</commentsOnSchemas><commentsOnTables>true</commentsOnTables><commentsOnColumns>true</commentsOnColumns><commentsOnUDTs>true</commentsOnUDTs><commentsOnAttributes>true</commentsOnAttributes><commentsOnPackages>true</commentsOnPackages><commentsOnRoutines>true</commentsOnRoutines><commentsOnParameters>true</commentsOnParameters><commentsOnSequences>true</commentsOnSequences><commentsOnLinks>true</commentsOnLinks><commentsOnQueues>true</commentsOnQueues><commentsOnKeys>true</commentsOnKeys><fluentSetters>false</fluentSetters><javaBeansGettersAndSetters>false</javaBeansGettersAndSetters><varargSetters>true</varargSetters><fullyQualifiedTypes></fullyQualifiedTypes><emptyCatalogs>false</emptyCatalogs><emptySchemas>false</emptySchemas><javaTimeTypes>false</javaTimeTypes><primaryKeyTypes>false</primaryKeyTypes><newline>\n</newline></generate><target><packageName>me.leslie.generals.server.persistence.jooq</packageName><directory>Generals-Server/src/main/java</directory><encoding>UTF-8</encoding><clean>true</clean></target></generator>
19:02:57.424 [main] INFO org.jooq.codegen.GenerationTool - Database                 : Inferring database org.jooq.meta.sqlite.SQLiteDatabase from URL jdbc:sqlite:
19:02:57.426 [main] INFO org.jooq.codegen.GenerationTool - No <inputCatalog/> was provided. Generating ALL available catalogs instead.
19:02:57.426 [main] INFO org.jooq.codegen.GenerationTool - No <inputSchema/> was provided. Generating ALL available schemata instead.
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator - License parameters       
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator - ----------------------------------------------------------
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   Thank you for using jOOQ and jOOQ's code generator
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -                          
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator - Database parameters      
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator - ----------------------------------------------------------
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   dialect                : SQLITE
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   URL                    : jdbc:sqlite:
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   target dir             : Generals-Server/src/main/java
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   target package         : me.leslie.generals.server.persistence.jooq
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   includes               : [.*]
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   excludes               : []
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator -   includeExcludeColumns  : false
19:02:57.524 [main] INFO org.jooq.codegen.AbstractGenerator - ----------------------------------------------------------
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -                          
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator - JavaGenerator parameters 
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator - ----------------------------------------------------------
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   annotations (generated): true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   annotations (JPA: any) : false
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   annotations (JPA: version): 
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   annotations (validation): false
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments               : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on attributes : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on catalogs   : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on columns    : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on keys       : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on links      : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on packages   : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on parameters : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on queues     : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on routines   : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on schemas    : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on sequences  : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on tables     : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   comments on udts       : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   daos                   : false
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   deprecated code        : true
19:02:57.524 [main] INFO org.jooq.codegen.JavaGenerator -   global references (any): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (catalogs): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (keys): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (links): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (queues): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (routines): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (schemas): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (sequences): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (tables): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   global references (udts): true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   indexes                : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   instance fields        : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   interfaces             : false
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   interfaces (immutable) : false
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   javadoc                : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   keys                   : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   links                  : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   pojos                  : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   pojos (immutable)      : false
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   queues                 : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   records                : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   routines               : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   sequences              : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   table-valued functions : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   tables                 : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   udts                   : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -   relations              : true
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator - ----------------------------------------------------------
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -                          
19:02:57.525 [main] INFO org.jooq.codegen.AbstractGenerator - Generation remarks       
19:02:57.525 [main] INFO org.jooq.codegen.AbstractGenerator - ----------------------------------------------------------
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator -                          
19:02:57.525 [main] INFO org.jooq.codegen.JavaGenerator - ----------------------------------------------------------
19:02:57.526 [main] INFO org.jooq.codegen.JavaGenerator - Generating catalogs      : Total: 1
19:02:57.526 [main] INFO org.jooq.meta.AbstractDatabase - ARRAYs fetched           : 0 (0 included, 0 excluded)
19:02:57.526 [main] INFO org.jooq.meta.AbstractDatabase - Enums fetched            : 0 (0 included, 0 excluded)
19:02:57.526 [main] INFO org.jooq.meta.AbstractDatabase - Packages fetched         : 0 (0 included, 0 excluded)
19:02:57.526 [main] INFO org.jooq.meta.AbstractDatabase - Routines fetched         : 0 (0 included, 0 excluded)
19:02:57.526 [main] INFO org.jooq.meta.AbstractDatabase - Sequences fetched        : 0 (0 included, 0 excluded)
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.jooq.tools.reflect.Reflect (file:/home/leslie/.m2/repository/org/jooq/jooq/3.12.1/jooq-3.12.1.jar) to constructor java.lang.invoke.MethodHandles$Lookup(java.lang.Class)
WARNING: Please consider reporting this to the maintainers of org.jooq.tools.reflect.Reflect
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
19:02:57.725 [main] INFO org.jooq.Constants - 

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.12.1

19:02:57.732 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select 1 one
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +----+
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener -                          : | one|
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----+
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener -                          : |   1|
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----+
19:02:57.788 [main] DEBUG org.jooq.tools.LoggerListener - Fetched row(s)           : 1
19:02:57.791 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select sqlite_master.name from sqlite_master where sqlite_master.type in (?, ?) order by sqlite_master.name
19:02:57.792 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind values      : select sqlite_master.name from sqlite_master where sqlite_master.type in ('table', 'view') order by sqlite_master.name
19:02:57.793 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +----+
19:02:57.793 [main] DEBUG org.jooq.tools.LoggerListener -                          : |name|
19:02:57.793 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----+
19:02:57.793 [main] DEBUG org.jooq.tools.LoggerListener - Fetched row(s)           : 0
19:02:57.793 [main] INFO org.jooq.meta.AbstractDatabase - Tables fetched           : 0 (0 included, 0 excluded)
19:02:57.793 [main] INFO org.jooq.meta.AbstractDatabase - UDTs fetched             : 0 (0 included, 0 excluded)
19:02:57.793 [main] INFO org.jooq.codegen.JavaGenerator - Excluding empty catalog  : 
19:02:57.793 [main] INFO org.jooq.codegen.JavaGenerator - Removing excess files    

Process finished with exit code 0

Lukas Eder · Accepted Answer · 2019-10-21 07:17:44Z

1

If you want to use the DDLDatabase as documented in the link you've posted, you should:

remove the JDBC connection parameters
add the DDLDatabase as a database implementation, to replace the default SQLiteDatabase (which is derived from your JDBC connection parameters).

In other words, try this:

public static void runJooqCodeGen() throws Exception {
    // Removed this
    /* String url = "jdbc:sqlite:";
    String driver = "org.sqlite.JDBC"; */
    Configuration configuration = new Configuration()/*.withJdbc(new Jdbc()
            .withDriver(driver).withUrl(url))*/
        .withGenerator(new Generator()
            .withDatabase(new Database()
                // Added this
                .withName("org.jooq.meta.extensions.ddl.DDLDatabase")
                .withProperties(new Property()
                    .withKey("scripts")
                    .withValue("src/main/resources/db/Schema.sql")))
                .withGenerate(new Generate()
                    .withPojos(Boolean.TRUE)
                    .withDeprecationOnUnknownTypes(Boolean.FALSE))
                .withTarget(new Target()
                    .withPackageName("me.leslie.generals.server.persistence.jooq")
                    .withDirectory("Generals-Server/src/main/java")));

    GenerationTool.generate(configuration);
}

answered Oct 21, 2019 at 7:17

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

2 Comments

Leslie Over a year ago

Thank you very much. This helped. Additionally I had to change the path to the sql file to "/db/Schema.sql" and then everything works like intended.

Lukas Eder Over a year ago

@Leslie: To clarify: The /dbSchema.sql notation will look for the file on the classpath. The src/main/resources/db/Schema.sql notation will look for it from the execution directory, which may or may not be where you think it is. You can also use **/ and */ wildcards.

Collectives™ on Stack Overflow

How to fix: Jooq code does not generate java code for sqlite in memory db from sql script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related