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
jOOQ as a SQL builder without code generation
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
We strongly recommend to use jOOQ with its code generator to get the most out of jOOQ!
However, if you have a dynamic schema, you don't have to use the code generator. This is the most simple of all use cases, allowing for construction of valid SQL for any database. In this use case, you will not use jOOQ's code generator and maybe not even jOOQ's query execution facilities. Instead, you'll use jOOQ's query DSL API to wrap strings, literals and other user-defined objects into an object-oriented, type-safe AST modelling your SQL statements. An example is given here:
// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
// For simplicity reasons, we're using the API to construct case-insensitive object references, here.
Query query = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))
.from(table("BOOK"))
.join(table("AUTHOR"))
.on(field("BOOK.AUTHOR_ID").eq(field("AUTHOR.ID")))
.where(field("BOOK.PUBLISHED_IN").eq(1948));
String sql = query.getSQL();
List<Object> bindValues = query.getBindValues();
The SQL string built with the jOOQ query DSL can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools (note that since jOOQ uses java.sql.PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind variables here).
You can also avoid getting the SQL string and bind values separately:
String sql = query.getSQL(ParamType.INLINED);
If you wish to use jOOQ only as a SQL builder, the following sections of the manual will be of interest to you:
- SQL building: This section contains a lot of information about creating SQL statements using the jOOQ API
- Plain SQL: This section contains information useful in particular to those that want to supply table expressions, column expressions, etc. as plain SQL to jOOQ, rather than through generated artefacts
- Bind values: This section explains how bind values are managed and/or inlined in jOOQ.
Feedback
Do you have any feedback about this page? We'd love to hear it!