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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
SQL Parser API
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Goal
Historically, jOOQ implements an internal domain-specific language in Java, which generates SQL (an external domain-specific language) for use with JDBC. The jOOQ API is built from two parts: The DSL API and the model API, where the DSL API adds lexical convenience for programmers on top of the model API, which is really just a SQL expression tree, similar to what a SQL parser does inside of any database.
With this parser, the whole set of jOOQ functionality will now also be made available to anyone who is not using jOOQ directly, including JDBC and/or JPA users, e.g. through the parsing connection, which proxies all JDBC Connection calls to the jOOQ parser before forwarding them to the database, or through the DSLContext.parser() API, which allows for a more low-level access to the parser directly, e.g. for tool building on top of jOOQ.
The possibilities are endless, including standardised, SQL string based database migrations that work on any SQLDialect that is supported by jOOQ.
Example
This parser API allows for parsing an arbitrary SQL string fragment into a variety of jOOQ API elements:
-
Parser.parse(String): This produces theorg.jooq.Queriestype, containing a batch of queries. -
Parser.parseQuery(String): This produces theorg.jooq.Querytype, containing a single query (any type of query). -
Parser.parseResultQuery(String): This produces theorg.jooq.ResultQuerytype, containing a single result query (SELECTor other). -
Parser.parseSelect(String): This produces theorg.jooq.Selecttype, containing a single SELECT query. -
Parser.parseStatement(String): This produces theorg.jooq.Statementtype, containing a single procedural statement. -
Parser.parseTable(String): This produces theorg.jooq.Tabletype, containing a table expression. -
Parser.parseField(String): This produces theorg.jooq.Fieldtype, containing a field expression. -
Parser.parseRow(String): This produces theorg.jooq.Rowtype, containing a row expression. -
Parser.parseCondition(String): This produces theorg.jooq.Conditiontype, containing a condition expression. -
Parser.parseName(String): This produces theorg.jooq.Nametype, containing a name expression.
See this blog post for more information about the various jOOQ types.
The parser is able to parse any unspecified dialect to produce a jOOQ representation of the SQL expression, for instance:
ResultQuery<?> query =
DSL.using(configuration)
.parser()
.parseResultQuery("SELECT * FROM (VALUES (1, 'a'), (2, 'b')) t(a, b)")
The above SQL query is valid standard SQL and runs out of the box on PostgreSQL and SQL Server, among others. The jOOQ ResultQuery that is generated from this SQL string, however, will also work on any other database, as jOOQ can emulate the two interesting SQL features being used here:
- The VALUES() constructor
- The derived column list syntax (aliasing table and columns in one go)
The query might be rendered as follows on the H2 database, which supports VALUES(), but not derived column lists:
select
t.a,
t.b
from (
(
select
null a,
null b
where 1 = 0
)
union all (
select *
from (values
(1, 'a'),
(2, 'b')
) t
)
) t;
Or like this on Oracle, which supports neither feature:
select
t.a,
t.b
from (
(
select
null a,
null b
from dual
where 1 = 0
)
union all (
select *
from (
(
select
1,
'a'
from dual
)
union all (
select
2,
'b'
from dual
)
) t
)
) t;
Feedback
Do you have any feedback about this page? We'd love to hear it!