SQL Parser API
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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.
This parser API allows for parsing an arbitrary SQL string fragment into a variety of jOOQ API elements:
- Parser.parse(String): This produces the org.jooq.Queries type, containing a batch of queries.
- Parser.parseQuery(String): This produces the org.jooq.Query type, containing a single query.
- Parser.parseResultQuery(String): This produces the org.jooq.ResultQuery type, containing a single query.
- Parser.parseTable(String): This produces the org.jooq.Table type, containing a table expression.
- Parser.parseField(String): This produces the org.jooq.Field type, containing a field expression.
- Parser.parseRow(String): This produces the org.jooq.Row type, containing a row expression.
- Parser.parseCondition(String): This produces the org.jooq.Condition type, containing a condition expression.
- Parser.parseName(String): This produces the org.jooq.Name type, containing a name expression.
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 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;