JSqlParser

JSqlParser Turn any SQL statement into a traversable tree of Java objects -- and back again. An RDBMS-agnostic SQL parser for the JVM: one grammar, twelve dialects, no native extensions.

Website · Samples · Syntax · Change Log · Contributing

What it does

Give it SQL. Get an AST you can walk, rewrite, and print back out.

SELECT 1 FROM dual WHERE a = b

SQL Text
 └─Statements: statement.select.PlainSelect
    ├─selectItems: statement.select.SelectItem
    │  └─LongValue: 1
    ├─Table: dual
    └─where: expression.operators.relational.EqualsTo
       ├─Column: a
       └─Column: b

String sqlStr = "select 1 from dual where a=b";

PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr);

SelectItem selectItem = select.getSelectItems().get(0);
Assertions.assertEquals(new LongValue(1), selectItem.getExpression());

Table table = (Table) select.getFromItem();
Assertions.assertEquals("dual", table.getName());

EqualsTo equalsTo = (EqualsTo) select.getWhere();
Column a = (Column) equalsTo.getLeftExpression();
Column b = (Column) equalsTo.getRightExpression();
Assertions.assertEquals("a", a.getColumnName());
        Assertions.assertEquals("b", b.getColumnName());

The tree is traversable with the Visitor pattern, and the same object model works in reverse: build statements from Java with a fluent API and render them as SQL text.

Install

Use the stable Manticore builds. They are released continuously from the current development line and carry all of the performance and grammar work described below. The upstream com.github.jsqlparser release on Maven Central is considerably older.

<dependency>
    <groupId>com.manticore-projects.jsqlformatter</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>[5.3.218,)</version>
</dependency>

implementation("com.manticore-projects.jsqlformatter:jsqlparser:+")

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>5.3</version>
</dependency>

Performance

11× faster than 5.3, and the fastest parser on real-world SQL of any of the parsers tested, in any language — 19× ahead of sqlglot[c] on JSqlParser’s own SELECT test suite. And the community kept sending performance improvements since. Thank you everyone!

Benchmark                                     (version)  Mode  Cnt    Score   Error  Units
JSQLParserBenchmark.parseSQLStatements           latest  avgt   15   21.138 ± 0.557  ms/op <-- Community sponsored improvements!
JSQLParserBenchmark.parseSQLStatements              5.4  avgt   15   32.528 ± 0.666  ms/op
JSQLParserBenchmark.parseSQLStatements              5.3  avgt   15  983.459 ± 8.197  ms/op
JSQLParserBenchmark.parseSQLStatements              5.1  avgt   15  319.601 ± 4.081  ms/op

Methodology and the full cross-parser comparison against SQLGlot, sqlglot[c] and polyglot-sql: jsqlparser-bench.

What it parses

JSqlParser targets the SQL standard plus all major RDBMS. One grammar covers all of them, and missing syntax gets added on demand — open an issue.

Beyond statement shapes, the grammar handles nested sub-selects, bind parameters (?, :name), window and analytic functions, Oracle hints, and the T-SQL square-bracket versus array-literal ambiguity. The complete reference is on the syntax page.

PostgreSQL dollar-quoted strings, including $tag$…$tag$, retain their delimiter and literal body in StringValue. Tagged quotes are disabled by default to preserve identifier parsing. Enable them with parser.withDialect(Dialect.POSTGRESQL) or parser.withDollarQuotedStringTags(true). Untagged $$…$$ literals remain enabled.

Statement classification

Any parsed statement can say what it actually does — no second parse, no visitor to write:

StatementFeatures features = CCJSqlParserUtil.parse(sqlStr).getFeatures();

// safeguard a read-only client before anything reaches the database
if (connection.isReadOnly() && features.mayModifyData()) {
    throw new SQLException("rejected: " + features.getUnresolvedReferences());
}

// dispatch correctly
if (features.returnsResultSet()) { statement.executeQuery(sqlStr);  }
else                             { statement.executeUpdate(sqlStr); }

This is not sqlStr.startsWith("SELECT") with extra steps. RETURNING turns DML into a row source, a data-modifying CTE hides a DELETE inside a SELECT, and INSERT INTO x SELECT .. contains a query but returns nothing:

Features are not mutually exclusive, and each is three-valued: proven, not excludable, or ruled out. is() answers “did the grammar prove it”, may() answers “could it be ruled out” — so a guard uses may() and a dispatcher uses is(). Function volatility is not a syntactic property, so anything the caller has not declared pure stays unproven and is listed by name.

Legacy MySQL GROUP BY ... ASC/DESC is available with Dialect.MYSQL and the explicit withLegacyMySqlGroupBy(true) option; modern/default parsing keeps it disabled.

Piped SQL

Support is progressing for Piped SQL, which writes queries in the order they actually execute rather than the order SQL historically demanded.

FROM Produce
|> WHERE
    item != 'bananas'
    AND category IN ('fruit', 'nut')
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales
   GROUP BY item
|> ORDER BY item DESC;

Background reading: the Google research paper, BigQuery pipe syntax and DuckDB FROM-first syntax.

ODBC {fn TIMESTAMPADD(...)} and {fn TIMESTAMPDIFF(...)} expose standard SQL_TSI_* interval arguments as time-unit expressions, preserving column traversal.

Java version

Sister projects

• JSQLFormatter — pretty-printing and formatting of SQL text
• JSQLTranspiler — dialect-specific rewriting, column resolution and lineage, by Starlake.ai

Alternatives

The dual-licensed JOOQ ships a hand-written parser with broad RDBMS support, cross-dialect translation, SQL transformation, and a JDBC proxy mode. Worth a look if translation between dialects is your primary need rather than AST access.

Sponsor

A huge thank you to Starlake.ai, who simplify data ingestion, transformation and orchestration for faster delivery of high-quality data. Starlake has been instrumental in providing Piped SQL support and a large number of test cases for BigQuery, Redshift, Databricks and DuckDB. If JSqlParser is useful to you, visit Starlake.ai and give them a star.

Documentation

1. Samples
2. Build instructions and Maven artifact
3. Contribution guide
4. Change log
5. Issues

License

Dual licensed under LGPL 2.1 or the Apache License, Version 2.0. Take your pick.