jOOQ is a Java library used for mapping the data bases. The thus resulted API is domain-specific (DSL), representing an abstraction built on the relational and object oriented models (ORM) of the data bases. As it is object oriented, it is type safe.
jOOQ does not represent a new interrogation language, but a tool for building the SQL clauses from jOOQ objects generated from the data base. jOOQ uses JDBC to the calls on the data base resource.
Though jOOQ does not have as many functionalities as ORMs (for instance: the management of entity states, inheritance, polymorphic interrogations, etc), it allows the creation of complex interrogations that imply reunions, aliases, imbricated selects or complex junctions, meaning that it covers the extended functionality of SQL.
The main artefacts generated as predefined structures, but which can be modified and adjusted on the model and accessibility level, for jOOQ are:
Keys.java, which contains the primary, foreign keys and the referential identities as static members, the instance of the org.jooq.Key class;
Tables.java, which contains all the table objects as static members, the instance of the org.jooq.Table class;
• an extension of the org.jooq.impl.SchemaImpl class, which contains an array of all the tables of the data base;
• an extension of the org.jooq.impl.TableImpl class, which defines the structure of a table in the data base. One such extension appears for each table in the data base;
• an extension of the org.jooq.impl.UpdatableRecordImpl class, which defines the operations of display and alteration of an entry in a table. Such an extension appears for every table in the data base.
We will use as a demo for this article the default generation for a Postgre SQL data base. In a Maven project, pom.xml is going to have the following content:
<dependencies>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.6.1</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.6.1</version>
</dependency>
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901-1.jdbc4</version>
</dependency>
<dependency>
<groupId>org.postgis</groupId>
<artifactId>postgis-jdbc</artifactId>
<version>1.3.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.6.1</version>
<configuration>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<!-- JDBC connection parameters -->
<jdbc>
<driver>org.postgresql.Driver</driver>
<url>dbc:postgresql://localhost:5432/mydatabase</url>
<user>username</user>
<password>password</password>
</jdbc>
<!-- Generator parameters -->
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database> <name>org.jooq.util.postgres.PostgresDatabase</name>
<includes>.*</includes>
<excludes></excludes> <inputSchema>myschema</inputSchema>
</database>
<target>
<packageName>test.generated</packageName>
<directory>src/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
</plugins>
</build> The first two dependencies, jooq and jooq-meta, are used as basic packages for running jOOQ, respectively for the metadata used in the mapping of the data base in Java classes.
The third jOOQ dependency, jooq-codegen-maven, is a basic operating system for the actual generation of the data base layout.
Other important tags involved in pom are:
jdbc, which contains the metadata of connection to the data base;
• database, which establishes the dialect used from jooq-meta. The name is created after the default construction:
org.util.[numeVendor].[NumeVendor]Database
• includes, which establishes, in the form of some regular Java expressions (the expression separator being the pipe), the items that are going to be generated from the data base schema;
• excludes, which establishes, in the form of the same regular expressions, the items excluded from the general mapping (it may include table names or routines);
• target, with two sub-elements:
◊ packageName, representing the name of the package where the classes mentioned in the previous paragraphs will be generated;
◊ directory, representing the name of the directory where the previous packages will be created.
An application that interacts with a PostgreSQL data base, which contains a table, takes the following steps:
the creation of a connection, instance of the Connection class, from java.sql (this is actually the single JDBC element present in the application)
DSLContext create = DSL.*using*(conn, SQLDialect.*POSTGRES_9_4*);The classes involved are: DSLContext is the interface containing the main features for the interrogation object, and DSL is a factory for different types of dialects;
the creation of the interrogation is easy, by calling methods from the previously created object;
We present below the entire code of this demo application:
final String userName = "postgres";
final String password = "fcdss";
final String url = "jdbc:postgresql://localhost:5432/mytests";
try (final Connection conn = DriverManager.getConnection(url, userName, password)) {
final DSLContext create = DSL.using(conn,SQLDialect.POSTGRES_9_4);
final Result result = create.select().from(Tables.AUTHOR).fetch();
for (final Record r : result) {
final Integer id = r.getValue(Tables.AUTHOR.ID);
final String firstName = r.getValue(Tables.AUTHOR.FIRST_NAME);
final String lastName = r.getValue(Tables.AUTHOR.LAST_NAME);
System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}
} catch (final Exception e) {
e.printStackTrace();
} We shall develop the subject in the future numbers of the magazine, with performance arguments and a parallel between jOOQ and ORMs such as JPA.
We hope you have enjoyed the article!