7.4. Generating classes for jOOQ

Work with the database will be carried out using the jOOQ library. jOOQ builds SQL queries from jOOQ objects and code (similarly to LINQ). jOOQ is more closely integrated with the database than ORM, enabling more database features to be utilized, rather than just the simple CRUD SQL queries used in Active Record. jOOQ can work with stored procedures and functions, sequences, and use window functions and other Firebird-specific features.

You can find the full documentation for jOOQ at https://www.jooq.org/doc/3.9/manual-single-page/.

7.4.1. jOOQ Classes

jOOQ classes for working with the database are generated on the basis of the database schema described in the earlier chapter, The examples.fdb Database.

To generate jOOQ classes for working with our database, you will need to download these binary files at https://www.jooq.org/download or via the maven repository:

  • jooq-3.9.2.jar — The main library included in our application for working with jOOQ

  • jooq-meta-3.9.2.jar — The tool included in your build for navigating the database schema via generated objects

  • jooq-codegen-3.9.2.jar — The tool included in your build for generating the database schema

Along with those, of course, you will need to download the Jaybird driver for connecting to the Firebird database via JDBC: jaybird-full-3.0.5.jar.

Configuration for Database Schema Classes

For generating the classes for the database schema, we create the configuration file example.xml:

  1. <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  2. <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.8.0.xsd">
  3.   <!-- Configuration of connection to the database -->
  4.   <jdbc>
  5.     <driver>org.firebirdsql.jdbc.FBDriver</driver>
  6.     <url>jdbc:firebirdsql://localhost:3050/examples</url>
  7.     <user>SYSDBA</user>
  8.     <password>masterkey</password>
  9.     <properties>
  10.       <property>
  11.         <key>charSet</key>
  12.         <value>utf-8</value>
  13.       </property>
  14.     </properties>
  15.   </jdbc>
  17.   <generator>
  18.     <name>org.jooq.util.JavaGenerator</name>
  20.     <database>
  21.       <!-- The type of the database. Format:
  22.            org.util.[database].[database]Database -->
  23.       <name>org.jooq.util.firebird.FirebirdDatabase</name>
  25.       <inputSchema></inputSchema>
  27.       <!-- All objects that are generated from your schema
  28.            (Java regular expression. Use filters to limit number of objects).
  29.            Watch for sensitivity to the register. Depending on your database,
  30.            this can be important!
  31.        -->
  32.       <includes>.*</includes>
  34.       <!-- Objects that are excluded when generating from your schema.
  35.            (Java regular expression).
  36.            In this case, we exclude system tables RDB$, monitoring tables MON$
  37.            and security pseudo-tables SEC$.
  38.       -->
  39.       <excludes>
  40.           RDB\$.*
  41.         | MON\$.*
  42.         | SEC\$.*
  43.       </excludes>
  44.     </database>
  46.     <target>
  47.       <!-- The name of the package to which the generated -->
  48.       <packageName>ru.ibase.fbjavaex.exampledb</packageName>
  50.       <!-- Directory for posting the generated classes.
  51.            Here, the Maven directory structure is used. -->
  52.       <directory>e:/OpenServer/domains/localhost/fbjavaex/src/main/java/</directory>
  53.     </target>
  54.   </generator>
  55. </configuration>

Generating the Schema Classes

In the command shell, execute the following command to create the classes needed for writing queries to database objects in Java:

  1. java -cp jooq-3.9.2.jar;jooq-meta-3.9.2.jar;jooq-codegen-3.9.2.jar;jaybird-full-3.0.0.jar;. org.jooq.util.GenerationTool example.xml

You can find more details about the process of generating classes at https://www.jooq.org/doc/3.9/manual-single-page/#code-generation.