Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Creating SQL Queries Using jOOQ
Firebird Home Firebird Home Prev: Dependency InjectionFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: Working with Transactions

Creating SQL Queries Using jOOQ

Table of Contents

The jOOQ DSL
Named and Unnamed Parameters
Returning Values from SELECT Queries
Other Types of Queries
Stored Procedures with jOOQ

Before we move on to the implementation of managers and grids, we will examine briefly how to work with the database via jOOQ. You can find the full documentation on this issue in the SQL-building section of the jOOQ documentation.

The org.jooq.impl.DSL class is the main one from which jOOQ objects are created. It acts as a static factory for table expressions, column (or field) expressions, conditional expressions and many other parts of a query.

DSLContext references the org.jooq.Configuration object that configures the behavior of jOOQ during the execution of queries. Unlike with static DSL, with DSLContext you can to create SQL statements that are already “configured” and ready for execution.

In our application, DSLContext is created in the getDsl method of the JooqConfig configuration class. Configuration for DSLContext is returned by the getDslConfig method. In this method we specify the Firebird dialect that we will use, the connection provider that determines how we get a connection via JDBC and the SQL query execution listener.

The jOOQ DSL

jOOQ comes with its own DSL (for Domain Specific Language) that emulates SQL in Java. It allows you to write SQL statements almost as though Java actually supported them. Its effect is similar to what .NET in C# does with LINQ to SQL.

jOOQ uses an informal BNF notation modelling a unified SQL dialect suitable for most database engines. Unlike other, simpler frameworks that use the Fluent API or the chain method, the jOOQ-based BNF interface does not permit bad query syntax.

A simple SQL query:
SELECT *
  FROM author a
  JOIN book b ON a.id = b.author_id
 WHERE a.year_of_birth > 1920
   AND a.first_name = 'Paulo'
 ORDER BY b.title
        

In jOOQ it looks like this:

Result<Record> result =
dsl.select()
   .from(AUTHOR.as("a"))
   .join(BOOK.as("b")).on(a.ID.equal(b.AUTHOR_ID))
   .where(a.YEAR_OF_BIRTH.greaterThan(1920)
   .and(a.FIRST_NAME.equal("Paulo")))
   .orderBy(b.TITLE)
   .fetch();
        

The AUTHOR and BOOK classes describing the corresponding tables must be generated beforehand. The process of generating jOOQ classes according to the specified database schema was described earlier.

We specified table aliases for the AUTHOR and BOOK tables using the AS clause. Here is the same query in DSL without aliases:

Result<Record> result =
dsl.select()
   .from(AUTHOR)
   .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
   .where(AUTHOR.YEAR_OF_BIRTH.greaterThan(1920)
   .and(AUTHOR.FIRST_NAME.equal("Paulo")))
   .orderBy(BOOK.TITLE)
   .fetch();
        

Now we take a more complex query with aggregate functions and grouping:

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
  JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
  AND BOOK.PUBLISHED > '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
  OFFSET 1 ROWS
  FETCH FIRST 2 ROWS ONLY
        

In jOOQ:

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
   .from(AUTHOR)
   .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
   .where(BOOK.LANGUAGE.equal("DE"))
   .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
   .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .having(count().greaterThan(5))
   .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
   .limit(2)
   .offset(1)
   .fetch();
        

Note

'Dialect' in the jOOQ context represents not just the SQL dialect of the database but also the major version number of the database engine. The field 'limit', limiting the number of records returned, will be generated according to the SQL syntax available to the database engine. The example above used FIREBIRD_3_0, which supports OFFSETFETCH. If we had specified the FIREBIRD_2_5 or just the FIREBIRD dialect, the ROWS clause would have been used instead.

You can build a query in parts. This will allow you to change it dynamically, to change the sort order or to add additional filter conditions.

SelectFinalStep<?> select
    = dsl.select()
         .from(PRODUCT);

SelectQuery<?> query = select.getQuery();
switch (searchOper) {
    case "eq":
         query.addConditions(PRODUCT.NAME.eq(searchString));
         break;
    case "bw":
         query.addConditions(PRODUCT.NAME.startsWith(searchString));
         break;
    case "cn":
         query.addConditions(PRODUCT.NAME.contains(searchString));
         break;
}
switch (sOrd) {
    case "asc":
         query.addOrderBy(PRODUCT.NAME.asc());
         break;
    case "desc":
         query.addOrderBy(PRODUCT.NAME.desc());
         break;
}
return query.fetchMaps();
        

Named and Unnamed Parameters

By default, any time you present a query containing a parameter that is string literal, a date, a number literal or an external variable, jOOQ uses unnamed parameters to bind that variable or literal. To illustrate, the following expression in Java:

dsl.select()
   .from(BOOK)
   .where(BOOK.ID.equal(5))
   .and(BOOK.TITLE.equal("Animal Farm"))
   .fetch();
        

is equivalent to the full form:

dsl.select()
   .from(BOOK)
   .where(BOOK.ID.equal(val(5)))
   .and(BOOK.TITLE.equal(val("Animal Farm")))
   .fetch();
        

and is converted into the SQL query:

SELECT *
FROM BOOK
WHERE BOOK.ID = ?
  AND BOOK.TITLE = ?
        

You need not concern yourself with the index position of the field value that corresponds to a parameter, as the values will be bound to the appropriate parameter automatically. The index of the parameter list is 1-based. If you need to change the value of a parameter, you just select it by its index number.

Select<?> select = 
  dsl.select()
     .from(BOOK)
     .where(BOOK.ID.equal(5))
     .and(BOOK.TITLE.equal("Animal Farm"));
Param<?> param = select.getParam("2");
Param.setValue("Animals as Leaders");
        

Another way to assign a new value to a parameter is to call the bind method:

Query query1 = 
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal("Poe"));
query1.bind(1, "Orwell");
        

jOOQ supports named parameters, too. They need to be created explicitly using org.jooq.Param:

// Create a query with a named parameter. You can then use that name for 
// accessing the parameter again
Query query1 = 
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param("lastName", "Poe")));
Param<?> param1 = query.getParam("lastName");

// Or, keep a reference to the typed parameter in order 
// not to lose the <T> type information:
Param<String> param2 = param("lastName", "Poe");
Query query2 = 
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param2));

// You can now change the bind value directly on the Param reference:
param2.setValue("Orwell");

Another way to assign a new value to a parameter is to call the bind method:
// Or, with named parameters
Query query2 = 
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param("lastName", "Poe")));
query2.bind("lastName", "Orwell");
        

Returning Values from SELECT Queries

jOOQ offers several methods for fetching data from SQL queries. We are not covering all of them here but you can find more details about them in the Fetching section of the jOOQ documentation.

For our example, we will return the data to a map list (the fetchMaps method) which is handy to use for serializing a result for JSON.

Other Types of Queries

We'll take a look at other types of queries. This query inserts a record:

INSERT INTO AUTHOR
       (ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse');
        

In jOOQ:

dsl.insertInto(AUTHOR,
        AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(100, "Hermann", "Hesse")
   .execute();
        

A query to update a record:

UPDATE AUTHOR
   SET FIRST_NAME = 'Hermann',
       LAST_NAME = 'Hesse'
 WHERE ID = 3;
        

In jOOQ:

dsl.update(AUTHOR)
   .set(AUTHOR.FIRST_NAME, "Hermann")
   .set(AUTHOR.LAST_NAME, "Hesse")
   .where(AUTHOR.ID.equal(3))
   .execute();
        

A query to delete a record:

DELETE FROM AUTHOR
 WHERE ID = 100;
        

In jOOQ:

dsl.delete(AUTHOR)
   .where(AUTHOR.ID.equal(100))
   .execute();
        

More complex update queries can be built in jOOQ, such as a MERGE query, for example.

Stored Procedures with jOOQ

A great benefit of jOOQ is its support for working with stored procedures. Stored procedures are extracted to the *.Routines.* package. From there, you can work with them easily. For instance, the following code in Java:

int invoiceId = dsl.nextval(GEN_INVOICE_ID).intValue();

spAddInvoice(dsl.configuration(), 
             invoiceId,
             customerId,
             invoiceDate);
        

is equivalent to getting the next value of the generator using the following SQL query:

SELECT NEXT VALUE FOR GEN_INVOICE_ID 
  FROM RDB$DATABASE
        

and calling the stored procedure after that:

EXECUTE PROCEDURE SP_ADD_INVOICE   (
   :INVOICE_ID, :CUSTOMER_ID, :INVOICE_DATE );
        

jOOQ also provides tools to build simple DDL queries, but we do not cover them here.

Prev: Dependency InjectionFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: Working with Transactions
Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Creating SQL Queries Using jOOQ