All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | Development versions: 3.12

A lot of people are using Spring's useful org.springframework.jdbc.core.JdbcTemplate in their projects to simplify common JDBC interaction patterns, such as:

  • Variable binding
  • Result mapping
  • Exception handling

When adding jOOQ to a project that is using JdbcTemplate extensively, a pragmatic first step is to use jOOQ as a SQL builder and pass the query string and bind variables to JdbcTemplate for execution. For instance, you may have the following class to store authors and their number of books in our stores:

public class AuthorAndBooks {
    public final String firstName;
    public final String lastName;
    public final int books;

    public AuthorAndBooks(String firstName, String lastName, int books) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.books = books;
    }
}

You can then write the following code

// The jOOQ part stays the same as always:
Book b = BOOK.as("b");
Author a = AUTHOR.as("a");
BookStore s = BOOK_STORE.as("s");
BookToBookStore t = BOOK_TO_BOOK_STORE.as("t");

ResultQuery<Record3<String, String, Integer>> query =
create.select(a.FIRST_NAME, a.LAST_NAME, countDistinct(s.NAME))
      .from(a)
      .join(b).on(b.AUTHOR_ID.equal(a.ID))
      .join(t).on(t.BOOK_ID.equal(b.ID))
      .join(s).on(t.BOOK_STORE_NAME.equal(s.NAME))
      .groupBy(a.FIRST_NAME, a.LAST_NAME)
      .orderBy(countDistinct(s.NAME).desc());

// But instead of executing the above query, we'll send the SQL string and the bind values to JdbcTemplate:
JdbcTemplate template = new JdbcTemplate(dataSource);
List<AuthorAndBooks> result = template.query(
    query.getSQL(),
    query.getBindValues().toArray(),
    (r, i) -> new AuthorAndBooks(
        r.getString(1),
        r.getString(2),
        r.getInt(3)
    ));

This approach helps you gradually migrate from using JdbcTemplate to a jOOQ-only execution model.

The jOOQ Logo