Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Using jOOQ with Spring's JdbcTemplate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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(),
(r, i) -> new AuthorAndBooks(
r.getString(1),
r.getString(2),
r.getInt(3)
),
query.getBindValues().toArray()
);
This approach helps you gradually migrate from using JdbcTemplate to a jOOQ-only execution model.
Feedback
Do you have any feedback about this page? We'd love to hear it!