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
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 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.Queries
type, containing a batch of queries. -
Parser.parseQuery(String)
: This produces theorg.jooq.Query
type, containing a single query (any type of query). -
Parser.parseResultQuery(String)
: This produces theorg.jooq.ResultQuery
type, containing a single result query (SELECT
or other). -
Parser.parseSelect(String)
: This produces theorg.jooq.Select
type, containing a single SELECT query. -
Parser.parseStatement(String)
: This produces theorg.jooq.Statement
type, containing a single procedural statement. -
Parser.parseTable(String)
: This produces theorg.jooq.Table
type, containing a table expression. -
Parser.parseField(String)
: This produces theorg.jooq.Field
type, containing a field expression. -
Parser.parseRow(String)
: This produces theorg.jooq.Row
type, containing a row expression. -
Parser.parseCondition(String)
: This produces theorg.jooq.Condition
type, containing a condition expression. -
Parser.parseName(String)
: This produces theorg.jooq.Name
type, 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!