Using JOOQ

Create a Java project

Create an empty Java project.

Right-click on the project node in the Project Explorer view, then select Configure > ‘Convert to

Maven project’. Leave fields as default in the dialog box.

Double-click the pom.xml file and add H2 and JOOQ dependencies (above the <build> element) :

<groupId>com.h2database</groupId>

</dependency>

</dependency>

</dependency>

<artifactId>jooq-codegen</artifactId>

</dependency>

</dependencies>

As soon as you save the pom.xml file with Ctrl+S, Eclipse starts to resolve/download JOOQ and H2.

Create a jooq-config.xml file (next to pom.xml) containing the following text :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<jdbc>

<driver>org.h2.Driver</driver>

<url>jdbc:h2:../fc.Database.Northwind/northwind;AUTO_SERVER=TRUE</url>

</jdbc>

<name>org.jooq.codegen.DefaultGenerator</name>

<name>org.jooq.meta.h2.H2Database</name>

<inputSchema>PUBLIC</inputSchema>

</database>

CC BY-NC-ND

<packageName>customer.database</packageName>

</target>

</generator>

</configuration>

northwind is the name of the H2 database file, without the .mv.db extension. The database file must

be in your project folder (and should be visible in the Project Explorer view ; if not, refresh the

Project Node), next to pom.xml and jooq-config.xml.

Note that <user> must be « sa » and <inputSchema> must be filled with the schema of the

embedded H2 database (if using northwind, the schema name is NORTHWIND).

Refresh the project (F5).

Go to the Run menu and select Run Configurations. In the left pane, go to the Java application item,

right-click it and select New Configuration :

Make sure your project is selected (if not, click the Browse button and select your project).

Enter

org.jooq.codegen.GenerationTool in the Main class section

and

jooq-config.xml in the Program arguments section (in the Arguments tab, see further below)

CC BY-NC-ND

Then click ‘Run’.

Observe the console. Some database-related minor exceptions may be raised, but table data will be

generated and visible in Project Explorer once you have refreshed the project node :

CC BY-NC-ND

Using JOOQ with SQLite instead of H2

You only need to read this section if using a SQLite (not H2) database. H2 is more powerful than

SQLite but SQLite might be more appropriate in some specific situations for test or learning

purposes.

The pom.xml file needs to be changed to :

<groupId>org.xerial</groupId>

<artifactId>sqlite-jdbc</artifactId>

</dependency>

</dependency>

</dependency>

<artifactId>jooq-codegen</artifactId>

</dependency>

</dependencies>

The jooq-config.xml needs to be changed to :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<jdbc>

<driver>org.sqlite.JDBC</driver>

<url>jdbc:sqlite:northwind.sqlite</url>

</jdbc>

<name>org.jooq.codegen.DefaultGenerator</name>

<name>org.jooq.meta.sqlite.SQLiteDatabase</name>

</database>

<packageName>northwind.database</packageName>

</target>

</generator>

</configuration>

Note that the inputSchema field should be left blank for SQLite (this is not the case for H2 where the

schema name has to be provided). Likewise, <user> is left blank (« sa » must be used for H2).

CC BY-NC-ND

jdbc:sqlite:northwind.sqlite is the relative filename of the SQLite database file. This file

should be placed in the project folder, next to pom.xml and jooq-config.xml. The file should be visible

in the Project Explorer view. If not, Refresh the project node.

CC BY-NC-ND

Creating a small program using JOOQ

Create a Java class and a main() method.

In the import section, you will need to add an import static declaration that reflects the name of the

package you chose earlier as a host for the generated classes. For example :

import static northwind.database.Tables.*;

If the package name in jooq-config.xml was :

<packageName>northwind.database</packageName>

Now you just have to connect to the H2 database and issue a simple query with JOOQ. Note that you

need to re-enter the JDBC connection details (JDBC will automatically find the driver based on the

jdbc connection string) :

String userName = "sa";

String password = "";

String url =

"jdbc:h2:../fc.Database.Northwind/northwind;AUTO_SERVER=TRUE";

// Connection is the only JDBC resource that we need

// PreparedStatement and ResultSet are handled by jOOQ, internally

Connection conn = null;

try

{

conn = DriverManager.getConnection(url, userName, password);

}

catch (Exception e)

{

// For the sake of this tutorial, let's keep exception handling simple

e.printStackTrace();

}

// This line will show a red JOOQ message in the console, with a logo

DSLContext create = DSL.using(conn, SQLDialect.H2);

Result<Record> result = create.select().from(CUSTOMER).fetch();

for (Record r : result)

{

String fname = r.getValue(CUSTOMER.FIRSTNAME);

String lname = r.getValue(CUSTOMER.LASTNAME);

System.out.println("First: " + fname + " last: " + lname);

}

If using a SQLite database, change the JDBC connection string appropriately, as well as the SQLDialect

used :

DSLContext create = DSL.using(conn, SQLDialect.H2);

Because of a minor bug in the SQLite JDBC driver, you need to force the loading of the SQLite JDBC

driver on application start by adding the following code at the beginning of your main() method :

try {

CC BY-NC-ND

Class.forName("org.sqlite.JDBC");

} catch (ClassNotFoundException e1) {

e1.printStackTrace();

}

CC BY-NC-ND