Internally, how SQL queries are eventually executed by JOOQ?

[acherm]

No description provided.

[maici]

I'll answer to this issue.

[gigouni]

I have the intention to close the issue.

[Florian14]

I'll answer this issue.

[quentin29200]

I'll answer this issue.

[dkaakati]

I'll answer this issue.

[SandraIsabel]

I'll answer this issue

[kaouthare]

I'll answer this issue

[gigouni]

The following content will try to explain how JOOQ execute SQL queries.

Technical explanations

According to the documentation, JOOQ provides a SQL Builder to "integrate SQL itself as an "internal domain specific language" directly into Java". JOOQ is actually wrapping a JDBC. When you're doing a SQL query, the wrapped JDBC will call executable objects "java.sql.Statement". The documentation splits it into three different types.

• java.sql.Statement, or "static statement": This statement type is used for any arbitrary type of SQL statement. It is particularly useful with inlined parameters
• java.sql.PreparedStatement: This statement type is used for any arbitrary type of SQL statement. It is particularly useful with indexed parameters (note that JDBC does not support named parameters)
• java.sql.CallableStatement: This statement type is used for SQL statements that are "called" rather than "executed". In particular, this includes calls to stored procedures. Callable statements can register OUT parameters

Examples

Let's get some examples to understand how does it work.

• 1. Simple SELECT statement

Using SQL

SELECT 1 FROM BOOK

Using JOOQ

create.selectOne().from(BOOK);

JOOQ, to simplify the query writing process, uses obvious function names.

• 2. Complex SELECT statement

Using SQL

  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
   LIMIT 2
  OFFSET 1
     FOR UPDATE

Using JOOQ

Factory create = new Factory(connection, dialect);

create.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)
      .forUpdate();

Complementary information

Querying SQL with JOOQ is well documented and you can get a lot of information following this link here.

Sources:

• http://www.jooq.org/doc/2.5/manual/sql-execution/
• http://www.jooq.org/doc/2.5/manual/sql-building/
• http://www.jooq.org/doc/2.5/manual/sql-building/sql-statements/select-statement/

[quentin29200]

Gigouni has a very good summary. JOOQ is an database-mapping software library in Java. JOOQ uses JDBC to execute SQL queries. JOOQ prevents SQL syntax errors.
JOOQ calls JDBC API and JPA annotations to generate SQL queries.

To see more, read https://blog.jooq.org/tag/jdbc/