GitHub

How to use it

JSQLParser turns SQL text into a tree of Java objects, lets you inspect or rewrite that tree, and prints it back out as SQL. Everything on this page is built on those three moves.

The whole idea in five lines
Statement statement = CCJSqlParserUtil.parse("SELECT a FROM my_table WHERE id = 42");

// inspect it
PlainSelect select = (PlainSelect) statement;
Table table = (Table) select.getFromItem();      // my_table

// print it back
String sql = statement.toString();

Tip

New here? Read Add JSQLParser to your Project, then Parse a SQL Statement, then Explore the Parsed Tree. Everything after that is optional and can be read in any order.

What is on this page

Section

Use it when you want to …

Add JSQLParser to your Project

pull in the dependency, and pick between the Manticore and upstream builds

Parse a SQL Statement

turn SQL text into Java objects

Explore the Parsed Tree

find your way around the object model

Classify a Statement

know whether SQL reads, writes or returns rows — before you run it

Find Table Names

list every table a statement touches

Use the Visitor Patterns

walk the whole tree and react to specific nodes

Build a SQL Statement

construct SQL from Java instead of from text

Handle Parse Errors

keep going when one statement in a script is broken

Choose a Dialect

parse T-SQL brackets, MySQL escapes, BigQuery quoting …

Compile from Source Code

build JSQLParser yourself or contribute

Add JSQLParser to your Project

There are two sets of artifacts on Maven Central, built from the same source under the same dual licence:

Artifact

groupId

Cut from

Manticore build (recommended)

com.manticore-projects.jsqlformatter

the current development line, released continuously

Upstream release

com.github.jsqlparser

the official release cadence

Upstream snapshot

com.github.jsqlparser

the latest commit, overwritten in place

Upstream releases are cut infrequently. Between two of them a lot of grammar and performance work lands — the 11× parse speed-up, JavaCC 8 support, new dialect syntax — and waiting for the next official version to catch up can mean months on a build that already has the fix you need.

Snapshots are not the answer either: a -SNAPSHOT coordinate is mutable, so the same version string can resolve to different bytes tomorrow. That is fine for trying something out and wrong for a reproducible build.

The Manticore builds fill that gap. Each one is an immutable, versioned release published to Maven Central from the current development line, so you get the fixes early and a build that stays reproducible. Use them unless you have a reason to pin to the official release — and note the different groupId, the artifact name is the same.

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

The range [5.3.218,) takes the newest available build. Pin an exact version instead once you ship.

repositories {
    mavenCentral()
}

dependencies {
    implementation 'com.manticore-projects.jsqlformatter:jsqlparser:+'
}

+ takes the newest available build. Pin an exact version instead once you ship.

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

<repositories>
    <repository>
        <id>jsqlparser-snapshots</id>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
        <url>https://oss.sonatype.org/content/groups/public/</url>
    </repository>
</repositories>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>5.4-SNAPSHOT</version>
</dependency>

repositories {
    mavenCentral()
}

dependencies {
    implementation 'com.github.jsqlparser:jsqlparser:5.3'
}

repositories {
    maven {
        url = uri('https://oss.sonatype.org/content/groups/public/')
    }
}

dependencies {
    implementation 'com.github.jsqlparser:jsqlparser:5.4-SNAPSHOT'
}

Note

Features documented here may reach the Manticore builds before the next upstream release. If a class or method on this page is missing, check which of the two you are resolving.

Parse a SQL Statement

CCJSqlParserUtil.parse() is the entry point. It returns a Statement, which you cast to the concrete type you expect.

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());

For several statements at once, use CCJSqlParserUtil.parseStatements(), which returns a Statements — an ArrayList<Statement>.

Statements script = CCJSqlParserUtil.parseStatements(
        "UPDATE t SET a = 1; SELECT a FROM t;");

assertEquals(2, script.size());

Note

Supported statement separators are semicolon ;, GO, slash / and two empty lines \n\n\n.

If parsing fails on syntax JSQLParser does not know, see Handle Parse Errors — and please open an issue, missing syntax gets added on demand.

Explore the Parsed Tree

The fastest way to learn the object model is to look at it. Paste your SQL into JSQLFormatter and it will draw the tree, with the Java class of every node:

 SQL Text
       └─Statements: net.sf.jsqlparser.statement.select.Select
           ├─selectItems -> Collection<SelectItem>
           │  └─LongValue: 1
           ├─Table: dual
           └─where: net.sf.jsqlparser.expression.operators.relational.EqualsTo
              ├─Column: a
              └─Column: b

Read that as a map: each line is a getter away. select.getSelectItems(), select.getFromItem(), select.getWhere(). Once the tree gets deeper than a couple of levels, stop casting by hand and use Use the Visitor Patterns.

Classify a Statement

Every Statement can tell you what it does — whether it reads, writes, changes the schema, or sends rows back — without a second parse and without writing a visitor:

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

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

Why bother

Two jobs come up constantly, and both are traps if you approach them with string matching:

Safeguarding a read-only client. Reporting tools, BI front-ends, LLM-generated SQL, user-supplied filters — plenty of code paths need to reject anything that writes, before the statement reaches the database. Checking whether the text starts with SELECT is not a safeguard.

Dispatching correctly. JDBC wants executeQuery() for row-returning statements and executeUpdate() for the rest. Get it backwards and you get an exception, not a wrong answer, but you still have to decide.

The reason a keyword check fails is that SQL is not organised into tidy Query/DML/DDL buckets. RETURNING turns a DELETE into a row source. A data-modifying CTE hides that DELETE inside something that begins with WITH. An INSERT can contain a whole SELECT and still return nothing:

SQL

returns rows

reads

writes

SELECT * FROM t

yes

yes

no

INSERT INTO x SELECT * FROM t

no

yes

yes

DELETE FROM t RETURNING *

yes

no

yes

WITH c AS (DELETE FROM t RETURNING *) SELECT * FROM c

yes

no

yes

INSERT INTO x WITH c AS (DELETE FROM t RETURNING *) SELECT * FROM c

no

no

yes

CREATE TABLE t AS SELECT a FROM u

no

yes

no (schema)

SELECT a INTO new_table FROM t

no

yes

yes

Note the last two rows of the fourth and fifth entries: RETURNING appearing somewhere in the statement is not the question. What matters is whether rows reach the client, and that is a property of the statement’s own result position, not of any nested one.

The features

StmtFeature

Meaning

Typical statements

READS_DATA

reads persistent rows

SELECT .. FROM t, MERGE, CREATE TABLE .. AS SELECT

RETURNS_RESULT_SET

rows are sent back to the client

SELECT, DELETE .. RETURNING, SHOW, DESCRIBE, EXPLAIN

MODIFIES_DATA

rows are written or destroyed

INSERT, UPDATE, DELETE, MERGE, UPSERT, TRUNCATE, DROP

MODIFIES_SCHEMA

the catalogue changes

CREATE, ALTER, DROP, TRUNCATE, GRANT, COMMENT

MODIFIES_SESSION

session state changes

SET, RESET, USE, DECLARE, ALTER SESSION

MODIFIES_TRANSACTION

transaction state or locks change

COMMIT, ROLLBACK, SAVEPOINT, LOCK, SELECT .. FOR UPDATE

OPAQUE

nothing further can be known statically

CALL, EXECUTE, dynamic SQL, unsupported statements

They are not mutually exclusive. INSERT .. RETURNING * carries MODIFIES_DATA and RETURNS_RESULT_SET; TRUNCATE carries MODIFIES_SCHEMA and MODIFIES_DATA, so that a guard looking only for data changes still stops it.

Proven, possible, excluded

Each feature is three-valued, because some questions cannot be answered from syntax alone. SELECT nextval('s') writes; SELECT upper(name) does not; the parser cannot tell them apart, because volatility lives in the database catalogue, not in the SQL text.

So a feature is either proven, not excludable, or ruled out, and you pick which side you want to be wrong on:

StatementFeatures features = statement.getFeatures();

features.is(StmtFeature.MODIFIES_DATA);   // the grammar proves it
features.may(StmtFeature.MODIFIES_DATA);  // proven, or could not be excluded

Caller

Uses

Because

read-only guard

may(..)

a false negative lets a write through

JDBC dispatcher

is(..)

a false positive picks executeQuery for CREATE INDEX

Convenience methods wrap the common combinations:

features.returnsResultSet();   // is(RETURNS_RESULT_SET)
features.modifiesData();       // is(MODIFIES_DATA)
features.mayModifyData();      // may(MODIFIES_DATA)
features.modifiesSchema();     // is(MODIFIES_SCHEMA)
features.isOpaque();           // CALL, EXECUTE, dynamic SQL

When something is merely possible, the analysis tells you why, so you can resolve it against your own catalogue or allow-list rather than guessing:

Safeguarding a read-only connection
StatementFeatures features = CCJSqlParserUtil.parse(sqlStr).getFeatures();

if (connection.isReadOnly() && features.mayModifyData()) {
    throw new SQLException(
            "rejected, unresolved: " + features.getUnresolvedReferences());
    // e.g. [nextval]
}

If you can prove some functions side-effect free, hand in a predicate and the uncertainty collapses:

Set<String> pure = Set.of("upper", "lower", "coalesce");

StatementFeatures features = statement.getFeatures(pure::contains);

// SELECT upper(name) FROM t
features.mayModifyData();              // false
features.getUnresolvedReferences();    // empty

Warning

The verdict is a syntactic claim, not a semantic guarantee. A user-defined function, a trigger on the target table or a CALL can do anything. Use this to reject obviously dangerous SQL early; it does not replace database-side permissions.

Scripts

Statements is an ArrayList<Statement> and not a Statement, so it has no getFeatures() of its own. Two entry points, for two different questions:

Statements script = CCJSqlParserUtil.parseStatements(
        "UPDATE t SET a = 1; SELECT a FROM t;");

// one union verdict — for guards
StatementFeatures all = StatementFeatureVisitor.analyse(script);
all.modifiesData();        // true
all.returnsResultSet();    // true

// one verdict per statement, in order — for dispatchers
List<StatementFeatures> each = StatementFeatureVisitor.analyseEach(script);
each.get(0).returnsResultSet();   // false, the UPDATE
each.get(1).returnsResultSet();   // true, the SELECT

The union answers “may this script write anything?”. It cannot answer “executeQuery or executeUpdate?”, because it never says which statement returns the rows.

Note

Nothing is cached. The tree is mutable and you may build statements by hand, so the verdict is recomputed on every call — microseconds against a millisecond-scale parse.

Find Table Names

net.sf.jsqlparser.util.TablesNamesFinder returns every table name in a statement or an expression, including the ones buried in sub-selects.

// find in Statements
String sqlStr = "select * from A left join B on A.id=B.id and A.age = (select age from C)";
Set<String> tableNames = TablesNamesFinder.findTables(sqlStr);
assertThat( tableNames ).containsExactlyInAnyOrder("A", "B", "C");

// find in Expressions
String exprStr = "A.id=B.id and A.age = (select age from C)";
tableNames = TablesNamesFinder.findTablesInExpression(exprStr);
assertThat( tableNames ).containsExactlyInAnyOrder("A", "B", "C");

Use the Visitor Patterns

Casting your way down the tree works for one known shape. For anything general — every column in a query, every table in a script — use a visitor: you override only the node types you care about and the adapters walk the rest.

There is one visitor interface per layer of the model, and an ..Adapter base class for each that already implements the full traversal:

Adapter

Reacts to

StatementVisitorAdapter

statements: Select, Insert, CreateTable, …

SelectVisitorAdapter

query bodies: PlainSelect, SetOperationList, WithItem, …

ExpressionVisitorAdapter

expressions: Column, Function, EqualsTo, …

FromItemVisitorAdapter

FROM items: Table, ParenthesedSelect, TableFunction, …

 
// Define an Expression Visitor reacting on any Expression
// Overwrite the visit() methods for each Expression Class
ExpressionVisitorAdapter<Void> expressionVisitorAdapter = new ExpressionVisitorAdapter<>() {
    public <S> Void visit(EqualsTo equalsTo, S context) {
        equalsTo.getLeftExpression().accept(this, context);
        equalsTo.getRightExpression().accept(this, context);
        return null;
    }
    public <S> Void visit(Column column, S context) {
        System.out.println("Found a Column " + column.getColumnName());
        return null;
    }
};

// Define a Select Visitor reacting on a Plain Select invoking the Expression Visitor on the Where Clause
SelectVisitorAdapter<Void> selectVisitorAdapter = new SelectVisitorAdapter<>() {
    @Override
    public <S> Void visit(PlainSelect plainSelect, S context) {
        return plainSelect.getWhere().accept(expressionVisitorAdapter, context);
    }
};

// Define a Statement Visitor for dispatching the Statements
StatementVisitorAdapter<Void> statementVisitor = new StatementVisitorAdapter<>() {
    public <S> Void visit(Select select, S context) {
        return select.getSelectBody().accept(selectVisitorAdapter, context);
    }
};

String sqlStr="select 1 from dual where a=b";
Statement stmt = CCJSqlParserUtil.parse(sqlStr);

// Invoke the Statement Visitor without a context
stmt.accept(statementVisitor, null);

Tip

The second parameter of every visit() is a free-form context object of your choosing, threaded through the traversal. Pass null when you do not need it.

Build a SQL Statement

The object model works in both directions. Build the tree from Java and print it as SQL:

String expectedSQLStr = "SELECT 1 FROM dual t WHERE a = b";

// Step 1: generate the Java Object Hierarchy for
Table table = new Table().withName("dual").withAlias(new Alias("t", false));

Column columnA = new Column().withColumnName("a");
Column columnB = new Column().withColumnName("b");
Expression whereExpression =
        new EqualsTo().withLeftExpression(columnA).withRightExpression(columnB);

PlainSelect select = new PlainSelect().addSelectItem(new LongValue(1))
        .withFromItem(table).withWhere(whereExpression);

// Step 2a: Print into a SQL Statement
Assertions.assertEquals(expectedSQLStr, select.toString());

// Step 2b: De-Parse into a SQL Statement
StringBuilder builder = new StringBuilder();
StatementDeParser deParser = new StatementDeParser(builder);
deParser.visit(select);

Assertions.assertEquals(expectedSQLStr, builder.toString());

Handle Parse Errors

By default a syntax error aborts the whole parse. Two features let a script survive one bad statement:

  • parser.withErrorRecovery(true) skips to the next statement separator and returns an empty statement.

  • parser.withUnsupportedStatements(true) returns an UnsupportedStatement holding the raw text instead — though the first statement must be a regular one.

Error Recovery
CCJSqlParser parser = new CCJSqlParser(
        "select * from mytable; select from; select * from mytable2" );
Statements statements = parser.withErrorRecovery().Statements();

// 3 statements, the failing one set to NULL
assertEquals(3, statements.size());
assertNull(statements.get(1));

// errors are recorded
assertEquals(1, parser.getParseErrors().size());
Unsupported Statement
Statements statements = CCJSqlParserUtil.parseStatements(
        "select * from mytable; select from; select * from mytable2; select 4;"
        , parser -> parser.withUnsupportedStatements() );

// 4 statements with one Unsupported Statement holding the content
assertEquals(4, statements.size());
assertInstanceOf(UnsupportedStatement.class, statements.get(1));
assertEquals("select from", statements.get(1).toString());

// no errors records, because a statement has been returned
assertEquals(0, parser.getParseErrors().size());

Note

An UnsupportedStatement is reported as OPAQUE by Classify a Statement — nothing about its effects is knowable.

Choose a Dialect

One grammar covers every supported RDBMS, but a few pieces of syntax mean different things in different products. Those are switched with parser features, and a Dialect preset turns on the right set for you.

// MySQL: backslash escapes, hash line comments, double-quoted strings
Statement stmt = CCJSqlParserUtil.parse(
        "SELECT `col` FROM t WHERE a = 'x\\'yz' AND b = 42#24"
        , parser -> parser.withDialect(Dialect.MYSQL) );

Dialect

Turns on

MYSQL

withBackslashEscapeCharacter, withHashLineComments, withDoubleQuotedStrings (MySQL and MariaDB, the last for the default sql_mode)

SQLSERVER

withSquareBracketQuotation

POSTGRESQL, ANSI_SQL

the newline rule for adjacent string literals

BIGQUERY

withDoubleQuotedStrings, withBackslashEscapeCharacter, withHashLineComments, any-whitespace rule for adjacent string literals

DATABRICKS

withDoubleQuotedStrings, withBackslashEscapeCharacter, any-whitespace rule for adjacent string literals

SNOWFLAKE

withBackslashEscapeCharacter only, double quotes stay quoted identifiers

Features set explicitly after the preset win over it.

The individual features

Feature

What it changes

withSquareBracketQuotation

[..] reads as a quoted identifier instead of an array — needed for T-SQL on MS SQL Server and Sybase

withBackslashEscapeCharacter

\\.. escaping inside string literals, in addition to the standard '.. doubling

withDoubleQuotedStrings

".." reads as a string literal instead of a quoted identifier (BigQuery, Spark/Databricks, MySQL default sql_mode)

withHashLineComments

# starts a line comment

withAdjacentStringLiterals

adjacent string literals concatenate: NEWLINE (SQL standard, PostgreSQL) or WHITESPACE (GoogleSQL, Spark/Databricks); true selects NEWLINE, false switches it off

withAllowComplexParsing

permits deeply nested expressions, at a significant performance cost

withTimeOut

aborts parsing after N milliseconds

 
String sqlStr="select 1 from [sample_table] where [a]=[b]";

// T-SQL Square Bracket Quotation
Statement stmt = CCJSqlParserUtil.parse(
        sqlStr
        , parser -> parser
            .withSquareBracketQuotation(true)
);

// Set Parser Timeout to 6000 ms
Statement stmt1 = CCJSqlParserUtil.parse(
        sqlStr
        , parser -> parser
            .withSquareBracketQuotation(true)
            .withTimeOut(6000)
);

// Allow Complex Parsing (which allows nested Expressions, but is much slower)
Statement stmt2 = CCJSqlParserUtil.parse(
        sqlStr
        , parser -> parser
            .withSquareBracketQuotation(true)
            .withAllowComplexParsing(true)
            .withTimeOut(6000)
);

// Allow Back-slash escaping
sqlStr="SELECT ('\\'Clark\\'', 'Kent')";
Statement stmt2 = CCJSqlParserUtil.parse(
        sqlStr
        , parser -> parser
            .withBackslashEscapeCharacter(true)
);

Things that trip people up

Hint

  1. Quoting: Double quotes ".." quote identifiers. Square brackets [..] are arrays unless you turn on withSquareBracketQuotation.

  2. Reserved keywords: JSQLParser uses a more restrictive list than most databases, and such keywords need to be quoted.

  3. Escaping: standard single-quote '.. escaping is always on. Backslash escaping is not — set withBackslashEscapeCharacter.

  4. Oracle alternative quoting is partially supported, for common brackets: q'{...}', q'[...]', q'(...)' and q''...''.

Compile from Source Code

You need JDK 8 or JDK 11. JSQLParser-4.9 is the last JDK 8 compatible release; everything after depends on JDK 11. Building JSQLParser-5.1 and newer with Gradle needs a JDK 17 toolchain, because of the plugins used.

git clone --depth 1 https://github.com/JSQLParser/JSqlParser.git
cd JSqlParser
mvn install

git clone --depth 1 https://github.com/JSQLParser/JSqlParser.git
cd JSqlParser
gradle publishToMavenLocal