The jOOQ User Manual SQL building SQL Parser SQL Parser API

previous : next

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 the org.jooq.Queries type, containing a batch of queries.
• Parser.parseQuery(String): This produces the org.jooq.Query type, containing a single query (any type of query).
• Parser.parseResultQuery(String): This produces the org.jooq.ResultQuery type, containing a single result query (SELECT or other).
• Parser.parseSelect(String): This produces the org.jooq.Select type, containing a single SELECT query.
• Parser.parseStatement(String): This produces the org.jooq.Statement type, containing a single procedural statement.
• 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.

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;

The jOOQ User Manual SQL building SQL Parser SQL Parser API

previous : next