SQL execution

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In a previous section of the manual, we've seen how jOOQ can be used to build SQL that can be executed with any API including JDBC or ... jOOQ. This section of the manual deals with various means of actually executing SQL with jOOQ.

SQL execution with JDBC

JDBC calls executable objects "java.sql.Statement". It distinguishes between three types of statements:

• 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

Today, the JDBC API may look weird to users being used to object-oriented design. While statements hide a lot of SQL dialect-specific implementation details quite well, they assume a lot of knowledge about the internal state of a statement. For instance, you can use the PreparedStatement.addBatch() method, to add a the prepared statement being created to an "internal list" of batch statements. Instead of returning a new type, this method forces user to reflect on the prepared statement's internal state or "mode".

jOOQ is wrapping JDBC

These things are abstracted away by jOOQ, which exposes such concepts in a more object-oriented way. For more details about jOOQ's batch query execution, see the manual's section about batch execution.

The following sections of this manual will show how jOOQ is wrapping JDBC for SQL execution

Alternative execution modes

Just because you can, doesn't mean you must. At the end of this chapter, we'll show how you can use jOOQ to generate SQL statements that are then executed with other APIs, such as Spring's JdbcTemplate, or Hibernate. For more information see the section about alternative execution models.

Table of contents

4.1.

Comparison between jOOQ and JDBC

4.2.

Query vs. ResultQuery

4.3.

Fetching

4.3.1.

Record vs. TableRecord

4.3.2.

Record1 to Record22

4.3.3.

Arrays, Maps and Lists

4.3.4.

ResultQuery as Iterable

4.3.5.

RecordMapper

4.3.6.

POJOs

4.3.7.

RecordMapperProvider

4.3.8.

Ad-hoc Converter

4.3.9.

ConverterProvider

4.3.10.

Lazy fetching

4.3.11.

Lazy fetching with Streams

4.3.12.

Many fetching

4.3.13.

Later fetching

4.3.14.

Reactive Fetching

4.3.15.

ResultSet fetching

4.3.16.

Auto data type conversion

4.3.17.

Custom data type conversion

4.3.18.

Data type lookups

4.3.19.

Context Converter

4.4.

Static statements vs. Prepared Statements

4.5.

Reusing a Query's PreparedStatement

4.6.

JDBC flags

4.7.

Using JDBC batch operations

4.8.

Sequence execution

4.9.

Stored procedures and functions

4.9.1.

Oracle Packages

4.9.2.

Oracle member procedures

4.10.

Exporting to XML, CSV, JSON, HTML, Text, Charts

4.10.1.

Exporting XML

4.10.2.

Exporting CSV

4.10.3.

Exporting JSON

4.10.4.

Exporting HTML

4.10.5.

Exporting Text

4.10.6.

Exporting Charts

4.10.7.

FormattingProvider

4.11.

Importing data

4.11.1.

The Loader API

4.11.2.

Import options

4.11.2.1.

Throttling

4.11.2.2.

Duplicate handling

4.11.2.3.

Error handling

4.11.3.

Import data sources

4.11.3.1.

Importing CSV

4.11.3.2.

Importing JSON

4.11.3.3.

Importing records

4.11.3.4.

Importing arrays

4.11.3.5.

Importing XML

4.11.4.

Import listeners

4.11.5.

Import result and error handling

4.12.

CRUD with UpdatableRecords

4.12.1.

Simple CRUD

4.12.2.

Records' internal flags

4.12.3.

IDENTITY values

4.12.4.

Navigation methods

4.12.5.

Non-updatable records

4.12.6.

Optimistic locking

4.12.7.

Batch execution

4.12.8.

CRUD SPI: RecordListener

4.13.

DAOs

4.14.

Transaction management

4.15.

Exception handling

4.16.

ExecuteListeners

4.17.

Database meta data

4.17.1.

JDBC meta data

4.17.2.

Interpreted meta data

4.17.3.

XML meta data

4.17.4.

Generated meta data

4.18.

JDBC Connection

4.19.

Batched Connection

4.20.

Mocking Connection

4.21.

Mock File Database

4.22.

Parsing Connection

4.23.

Diagnostics

4.23.1.

Too Many Rows

4.23.2.

Too Many Columns

4.23.3.

Duplicate Statements

4.23.4.

Repeated statements

4.23.5.

Consecutive aggregation

4.23.6.

WasNull calls

4.23.7.

Concatenation in predicates

4.23.8.

Possibly wrong expressions

4.23.9.

Trivial condition

4.23.10.

Transform patterns

4.24.

Logging with LoggerListener

4.25.

Logging with SQLExceptionLoggerListener

4.26.

Logging Connection

4.27.

Logging system properties

4.28.

Performance considerations

4.29.

Alternative execution models

4.29.1.

Using jOOQ with Spring's JdbcTemplate

4.29.2.

Using jOOQ with JPA

4.29.2.1.

Using jOOQ with JPA Native Query

4.29.2.2.

Using jOOQ with JPA entities

4.29.2.3.

Using jOOQ with JPA EntityResult