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.

Maven — Manticore

<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.

Gradle — Manticore

repositories {
    mavenCentral()
}

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

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

Maven Release

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

Maven Snapshot

<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.5.272-SNAPSHOT</version>
</dependency>

Gradle Stable

repositories {
    mavenCentral()
}

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

Gradle Snapshot

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

dependencies {
    implementation 'com.github.jsqlparser:jsqlparser:5.5.272-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. Use withBlankLinesAsSeparator(false) to treat empty lines as plain whitespace instead.

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.

MySQL index hints, insert sources and DELETE … USING

A table reference keeps every MySQL index hint in Table.getIndexHints(), in source order, for example t USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2). MySQLIndexHint.getScope() returns JOIN, ORDER_BY or GROUP_BY for a FOR clause and null without one. USE INDEX () has an empty index list; IGNORE and FORCE require at least one index. getIndexHint() still returns the first hint, and setHint() replaces all hints with one.

WEIGHT_STRING(str AS CHAR(n)) and WEIGHT_STRING(str AS BINARY(n)) keep the cast in Function.getCastType(), a ColDataType as in CAST.

INSERT INTO t VALUES ROW(1, 2), ROW(3, 4) and INSERT INTO t TABLE s use the same Values and TableStatement models as the queries VALUES ROW(...) and TABLE s. INSERT INTO t () VALUES () has an empty, non-null getColumns(); REPLACE accepts the same forms. toString() and InsertDeParser both render through Insert.appendTo, which writes PARTITION before the column list.

DELETE FROM t1 USING t1 JOIN t2 ON t1.id = t2.id keeps the joins of the USING clause in Delete.getUsingJoins(), after getUsingFromItemList(). PostgreSQL’s DELETE FROM t USING s JOIN u ON ... uses the same properties. getJoins() holds the joins of the target, as in DELETE FROM t1, t2 USING ..., and is rendered before USING.

PostgreSQL CHECK inheritance

With Dialect.POSTGRESQL, CREATE and ALTER CHECK constraints support NO INHERIT. CheckConstraint.isNoInherit() reports this flag; use setNoInherit or withNoInherit to change it. The check expression remains an editable Expression visited by the existing table traversal and deparsers.

For ALTER TABLE t ADD CHECK (id > 0) NO INHERIT NOT VALID, the two options are independent: isNoInherit() is true and getConstraintAttributes().isNotValid() is true. Clearing one flag preserves the other when the statement is rendered.

DROP targets

Drop keeps the parts of a DROP statement that are not plain object names in typed properties:

• FUNCTION, PROCEDURE, ROUTINE and AGGREGATE targets are RoutineReference items in getRoutines(), the model that GRANT and COMMENT use. Every target keeps its own signature, so DROP PROCEDURE p(int) and DROP FUNCTION f(int), g(text) render as written. Null arguments mean an omitted signature; an empty list is an explicit ().

• USER, ROLE and GROUP targets and the roles of PostgreSQL DROP OWNED BY are UserAccount items in getAccounts(), read with the MySQL account-name rule that CREATE USER uses. MySQL accounts keep their host, as in 'app'@'%', and a quoted PostgreSQL role name such as "Admin" keeps " in getQuoteStr().

• DROP TRIGGER t ON tbl and DROP RULE r ON tbl expose tbl through getTable(), like DROP INDEX i ON tbl, and table discovery reports it. SQL Server’s ON DATABASE and ON ALL SERVER trigger scopes stay parameters.

• getBehavior() holds an explicit CASCADE or RESTRICT as a CascadeBehavior. It is rendered last.

• isUndo(), getEngine() and isEngineUseEquals() cover MySQL DROP [UNDO] TABLESPACE name [ENGINE [=] engine]. isForce() covers PostgreSQL DROP DATABASE name [WITH] (FORCE); the optional WITH is always rendered.

• getAccessMethod() holds the index access method of PostgreSQL DROP OPERATOR CLASS|FAMILY name USING method, which takes one name. isProcedural() records the PROCEDURAL LANGUAGE spelling.

• PostgreSQL names casts, operators and transforms by types. getCast() is the CastSignature of DROP CAST (source_type AS target_type). getOperators() holds an OperatorSignature per target of DROP OPERATOR [schema.]op ({left_type | NONE}, {right_type | NONE}) [, ...]; its operator is the ExclusionOperator reference that COMMENT ON OPERATOR uses, and a null operand type stands for NONE. getTransform() is the TransformSignature of DROP TRANSFORM FOR type_name LANGUAGE lang_name. Oracle’s DROP OPERATOR name keeps a plain name, which the PostgreSQL dialect rejects.

TYPE, DOMAIN, EVENT, EVENT TRIGGER, TABLESPACE, USER and GROUP are recognized object types, and so are PostgreSQL’s ACCESS METHOD, CAST, LANGUAGE, OPERATOR, OPERATOR CLASS, OPERATOR FAMILY, TRANSFORM and TEXT SEARCH CONFIGURATION, DICTIONARY, PARSER and TEMPLATE. getType() returns their words separated by spaces, as in ACCESS METHOD. MySQL’s DROP SPATIAL REFERENCE SYSTEM [IF EXISTS] srid has the object type SPATIAL_REFERENCE_SYSTEM and its name is the SRID as written. PostgreSQL REASSIGN OWNED BY old_role [, ...] TO new_role is parsed into ReassignOwned, whose roles use the same UserAccount model.

Drop drop = (Drop) CCJSqlParserUtil.parse("DROP FUNCTION f(int), g(text) CASCADE",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
RoutineReference g = drop.getRoutines().get(1);
g.getArguments().get(0).setDataType(new ColDataType("varchar"));
// DROP FUNCTION f(int), g(varchar) CASCADE
String sql = drop.toString();

The legacy accessors remain and are derived from these properties:

• getName() and getNames() return read-only snapshots of routine, account and operator names. Edit getRoutines(), getAccounts() or getOperators() to change them. A cast or transform has no name.

• getTypeToParameters() and getParamsByType() return a read-only map with the argument declarations of the last routine, where the legacy single-signature form kept them. The map now also covers PROCEDURE, ROUTINE and AGGREGATE.

• getParameters() is a snapshot that includes ON with the owner table, the MySQL ALGORITHM and LOCK clauses and the CASCADE or RESTRICT keyword, in source order. setParameters() replaces the clause with raw tokens and clears all three; addParameters() keeps them. Raw tokens are not parsed: addParameters("CASCADE") leaves getBehavior() null.

• setName(), setNames(), addNames() and setTypeToParameters() first convert routine and account targets to the legacy names and single signature, so setName() on DROP FUNCTION f(int) still renders DROP FUNCTION g(int). Use getRoutines() to keep several signatures.

Migration: mutating the map returned by getTypeToParameters() for a parsed routine now throws UnsupportedOperationException; edit its RoutineReference instead. getObjectType() reports the new AGGREGATE, DOMAIN, EVENT_TRIGGER, GROUP, OWNED, ROLE, ROUTINE, RULE, TYPE and USER kinds where it reported OTHER. DROP OWNED BY r no longer returns the name BY.

DROP INDEX owners

DROP INDEX ix ON app.t exposes app.t through Drop.getTable(); getName() continues to identify the index. The owner is populated for the single-index ON form used by MySQL and SQL Server. A DROP INDEX without ON, as in PostgreSQL, has a null owner: resolving the index’s table requires a catalog. SQL Server’s multi-owner list and WITH options are outside this extension.

getParameters() includes ON and the rendered table as a legacy token snapshot when a structured owner exists. Mutate getTable() or use setTable() to update the owner; addParameters() appends options without losing it. setTable(null) removes ON, while setParameters() replaces the entire parameter clause with raw tokens and clears the structured owner. Raw parameter setters do not parse SQL or infer a table.

Table discovery and statement visitors traverse DROP TABLE/VIEW targets and explicit index owners, without reporting catalog-only object names as tables. The statement deparser delegates real tables to its configured select deparser. MySQL ALGORITHM and LOCK clauses retain their order and optional equals signs as typed nodes; see Share MySQL index options.

MySQL named constraint drops

Without a dialect and with Dialect.MYSQL, DROP FOREIGN KEY name and DROP CHECK name expose the target through AlterExpression.getConstraintName(). Dialect.MARIADB accepts the named FOREIGN KEY form; MariaDB has no DROP CHECK. Other dialects drop constraints with DROP CONSTRAINT. A parenthesized DROP FOREIGN KEY (...) keeps the legacy column-list form. Inspect getOperation() for DROP_FOREIGN_KEY or DROP_CHECK. Identifier quotes are preserved, and changing the name changes both SQL renderers. These named targets do not populate the legacy getPkColumns() list.

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE child DROP FOREIGN KEY fk_parent",
    parser -> parser.withDialect(Dialect.MYSQL));
AlterExpression drop = alter.getAlterExpressions().get(0);
drop.setConstraintName("fk_customer");
// ALTER TABLE child DROP FOREIGN KEY fk_customer
String sql = alter.toString();

// The same API also supports constructing an action without parsing SQL.
AlterExpression checkDrop = new AlterExpression()
    .withOperation(AlterOperation.DROP_CHECK).withConstraintName("positive_id");

Edit table and view column renames

AlterExpression.getColumnRename() exposes a ColumnRenameAction with getSourceName() and getTargetName(). Their setters update the existing AST directly, preserving the optional COLUMN keyword. Names include their written identifier quotes; include quotes in replacement names when needed.

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE accounts RENAME COLUMN old_id TO new_id",
    parser -> parser.withDialect(Dialect.MYSQL));
ColumnRenameAction rename = alter.getAlterExpressions().get(0).getColumnRename();
rename.getSourceName(); // old_id
rename.setTargetName("customer_id");
// ALTER TABLE accounts RENAME COLUMN old_id TO customer_id
String sql = alter.toString();

Import ColumnRenameAction from net.sf.jsqlparser.statement.alter. PostgreSQL table renames of columns use the same API. With Dialect.POSTGRESQL, column renames on views and materialized views expose it through the entries in AlterRelation.getActions(). The lookup returns null for other operations, including renaming the table, view, index or constraint itself. Existing action-specific getters and setters remain available and see the same changes.

Shared CASCADE and RESTRICT behavior

AlterDomain, AlterType (including AttributeChange), DropPolicy, Truncate and Revoke use net.sf.jsqlparser.statement.CascadeBehavior for their explicit CASCADE and RESTRICT clauses. A null value preserves an omitted clause; it does not insert an explicit RESTRICT.

import net.sf.jsqlparser.statement.CascadeBehavior;

truncate.setDropBehavior(CascadeBehavior.RESTRICT);
revoke.setBehavior(truncate.getDropBehavior());
revoke.setBehavior(null); // Omit the clause and leave the server default unchanged.

Migration: the nested enums AlterDomain.Behavior, AlterType.Behavior, DropPolicy.Behavior, Truncate.DropBehavior and Revoke.Behavior have been removed. Replace their imports and enum references with CascadeBehavior. The existing getter and setter names remain, but their return and parameter types have changed, so clients must recompile. Truncate.setCascade(false) still clears the clause. Foreign-key referential actions retain their separate enum.

ALTER TABLE drop actions expose the same enum through AlterExpressionDrop.getBehavior(), for example in ALTER TABLE t DROP COLUMN IF EXISTS c RESTRICT and ALTER TABLE t DROP CONSTRAINT c CASCADE. Oracle’s CASCADE CONSTRAINTS and INVALIDATE remain raw parameters. getParameters() still ends with the behavior keyword; when a behavior is set, it returns a read-only snapshot, so use setBehavior to change the clause.

Edit shared table options

CreateTable.getTableOptions() and AlterExpressionTableOption.getStructuredTableOption() use the same mutable TableOption model. Structured values and opaque tokens are alternative representations. setTokens(nonNullList) discards the previous kind, name, value and child nodes: the option becomes Kind.OTHER without parsing the tokens. Raw token lists remain mutable; getTokens() on a structured option returns an unmodifiable snapshot.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TABLE t (id INT) ENGINE=InnoDB");
TableOption option = table.getTableOption(TableOption.Kind.ENGINE).orElseThrow();
option.setTokens(List.of("ROW_FORMAT", "=", "DYNAMIC"));
table.getTableOption(TableOption.Kind.ENGINE).isEmpty(); // true

// Supply structured properties explicitly; the old tokens are discarded.
option.withKind(TableOption.Kind.COMMENT).withName("COMMENT")
        .withValue("'new'").withUseEquals(true);
// CREATE TABLE t (id INT) COMMENT = 'new'

Any structured setter leaves the opaque representation; it never recovers previous values from those tokens. setTokens(null) clears opaque tokens without restoring discarded properties, and leaves structured options alone. setUnionTables, setStorageParameters, setForeignTableOptions and setOnCommitAction replace the current contents with their respective structured payloads. Their lists and child nodes remain editable. An empty UNION list means UNION (); a null payload omits its value. A non-null setTablespaceStorage also selects TABLESPACE; supply its tablespace name with setValue. Choose the new kind and spelling explicitly when using the scalar setters. These methods edit the AST rather than checking database-specific DDL validity.

A temporary table’s ON COMMIT clause and a table access method are single options. Kind.ON_COMMIT exposes getOnCommitAction(): PRESERVE_ROWS, DELETE_ROWS or DROP, plus PRESERVE_DEFINITION and DROP_DEFINITION for Oracle private temporary tables. Kind.ACCESS_METHOD stores the method of USING method as its value, including CREATE TABLE … USING method AS query. Use TableOption.onCommit and TableOption.accessMethod to build them. getTableOptionsStrings() keeps the legacy word tokens, such as ON, COMMIT, DELETE and ROWS.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TEMP TABLE t (id INT) USING heap ON COMMIT DROP",
        parser -> parser.withDialect(Dialect.POSTGRESQL));
table.getTableOption(TableOption.Kind.ACCESS_METHOD).orElseThrow().getValue(); // heap
table.getTableOption(TableOption.Kind.ON_COMMIT).orElseThrow()
        .setOnCommitAction(TableOption.OnCommitAction.PRESERVE_ROWS);
// CREATE TEMP TABLE t (id INT) USING heap ON COMMIT PRESERVE ROWS

Share MySQL index options

CREATE INDEX, the index and key definitions of CREATE TABLE, and ALTER TABLE ADD or ALTER INDEX parse MySQL index options with one grammar rule into one model. IndexOptions.getSpecOptions(), also available on Index and KeyConstraint, returns the options written after the key list as mutable, ordered IndexSpecOption nodes. getKind() selects the payload:

• KEY_BLOCK_SIZE, ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE: getValue() and the optional = reported by isUseEquals()

• INDEX_TYPE (USING BTREE) and PARSER (WITH PARSER ngram): getValue()

• COMMENT: getComment() returns the literal as a StringValue that records how the dialect escapes it, so getNotExcapedValue() returns the stored text, for example it's for 'it''s' or, with MySQL backslash escapes, 'it\'s'. getValue() remains the literal’s SQL text.

• VISIBILITY: getVisible() for VISIBLE or INVISIBLE

• OTHER: opaque tokens that CREATE TABLE and CREATE INDEX accept for other dialects

Values keep their SQL spelling, including the quotes of string literals. An index type written before the key list, as in KEY k USING BTREE (a), PRIMARY KEY USING BTREE (id) or CREATE INDEX k USING HASH ON t (a), remains getUsing(); CreateIndex.isIndexTypeBeforeOn() still distinguishes the two CREATE INDEX positions. After the key list it is an INDEX_TYPE option at its source position. PostgreSQL’s INCLUDE, NULLS DISTINCT, storage parameters and tablespace keep their dedicated properties.

CREATE INDEX keeps these options on getIndex(). Its ALGORITHM and LOCK clauses are AlgorithmOrLockOption nodes in CreateIndex.getAlgorithmOrLockOptions(); DROP INDEX exposes the same type through Drop.getAlgorithmOrLockOptions(). CREATE INDEX renders them after the index options, as MySQL requires.

CreateIndex create = (CreateIndex) CCJSqlParserUtil.parse(
        "CREATE UNIQUE INDEX uk ON t (a) USING BTREE COMMENT 'x' ALGORITHM = INPLACE",
        parser -> parser.withDialect(Dialect.MYSQL));
List<IndexSpecOption> options = create.getIndex().getSpecOptions();
options.get(0).setValue("HASH");                    // INDEX_TYPE
options.add(IndexSpecOption.visibility(false));
create.getAlgorithmOrLockOptions().get(0).setValue("COPY");
// CREATE UNIQUE INDEX uk ON t (a) USING HASH COMMENT 'x' INVISIBLE ALGORITHM = COPY

The kind of an option is fixed. Setters for a payload that the kind does not have, and setTokens on a typed option, throw IllegalStateException; replace the node to change its kind. IndexSpecOption.raw copies its tokens and never parses them.

The legacy accessors are derived from these nodes. getIndexSpec() returns an unmodifiable token snapshot of getSpecOptions(), or null when the options are unset. CreateIndex.getTailParameters() returns the tokens of the index options followed by ALGORITHM and LOCK, and Drop.getParameters() includes ALGORITHM and LOCK at their source position. setIndexSpec and CreateIndex.setTailParameters replace these clauses with one opaque option; Drop.setParameters also clears ALGORITHM and LOCK.

Migration: edits of the getIndexSpec() and getTailParameters() snapshots now throw UnsupportedOperationException; edit the nodes or call the setters. ALTER TABLE key definitions report the same tokens as CREATE TABLE, for example KEY_BLOCK_SIZE, = and 8 instead of the single entry KEY_BLOCK_SIZE = 8. For a constructed statement without trailing clauses, getTailParameters() returns an empty list instead of null. Typed option keywords are rendered in upper case.

MySQL table and partition comments

A table COMMENT in CREATE TABLE, CREATE TABLE … AS SELECT and ALTER TABLE is a StringValue in TableOption.getComment(). Partition and subpartition comments, including those of ALTER TABLE ADD and REORGANIZE PARTITION, are in PartitionDefinition.getCommentValue(). The literal records the escaping of the parser configuration, so getNotExcapedValue() returns the text MySQL stores.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TABLE t (id INT) COMMENT = 'it\\'s' "
                + "PARTITION BY HASH (id) (PARTITION p0 COMMENT 'a\\\\b')",
        parser -> parser.withDialect(Dialect.MYSQL));
TableOption option = table.getTableOption(TableOption.Kind.COMMENT).orElseThrow();
option.getComment().getNotExcapedValue(); // it's
option.getValue();                        // 'it\'s'
table.getPartitioning().getPartitionDefinitions().get(0)
        .getCommentValue().getNotExcapedValue(); // a\b

getValue(), getTokens(), CreateTable.getTableOptionsStrings(), AlterExpressionTableOption.getCommentText() and PartitionDefinition.getComment() still return the literal as written, and the SQL output is unchanged except that a string prefix such as n'…' is printed in upper case, as in other string literals. The table comment literal is rendered by the expression deparser and visited like a column COMMENT literal.

setComment, withComment and the TableOption.comment factory replace the option’s contents with the literal and select COMMENT. They keep the equals spelling, and an existing COMMENT also keeps its name; a null literal omits the value. setValue stores a plain value and setTokens opaque tokens. Neither is parsed, so getComment() then returns null. Selecting another kind keeps the literal’s SQL text as a plain value. A COMMENT value that is not a string literal also stays a plain value, for example a double-quoted string parsed without the MySQL dialect, where it is a quoted identifier.

PartitionDefinition.setComment(String) reads the SQL text of a literal with standard escaping; pass a StringValue with another escaping to setCommentValue.

Inspect PostgreSQL schema statements

PostgreSQL schema clauses extend the existing CreateView, CreateTable, Alter and Sequence models. Use their typed properties to inspect the clauses and preserve distinctions between omitted options and explicit values.

CreateView view = (CreateView) CCJSqlParserUtil.parse(
        "CREATE MATERIALIZED VIEW IF NOT EXISTS account_totals "
        + "AS SELECT id FROM accounts WITH NO DATA");
view.isMaterialized();       // true
view.isIfNotExists();        // true
view.getWithData();          // Boolean.FALSE; null means the clause was omitted

Ordinary views expose ordered ViewOption values for security_barrier, security_invoker and check_option. getCheckOption() describes the trailing WITH CHECK OPTION clause: null means absent, DEFAULT preserves the bare clause, and LOCAL and CASCADED preserve explicit keywords. getEffectiveCheckOption() resolves the bare clause to CASCADED. Materialized views expose their access method, storage parameters and tablespace separately. See the PostgreSQL documentation for CREATE VIEW and CREATE MATERIALIZED VIEW.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TABLE accounts_copy (LIKE accounts INCLUDING ALL EXCLUDING INDEXES)");
LikeClause like = table.getTableElements(LikeClause.class).get(0);
like.getOptions();                                  // ordered INCLUDING/EXCLUDING clauses
like.isIncluding(LikeClause.OptionKind.DEFAULTS);   // true, inherited from ALL
like.isIncluding(LikeClause.OptionKind.INDEXES);    // false, overridden by EXCLUDING

getTableElements() preserves the order of columns, constraints and LIKE clauses, including multiple source tables. The three legacy nullable LikeClause getters report explicit options of that kind; isIncluding(OptionKind) also resolves ALL in declaration order. A typed table’s OF type is available through CreateTable.getOfType().

Table constraints expose their names and ConstraintAttributes independently from indexes. KeyConstraint and ExcludeConstraint expose IndexOptions for null treatment, INCLUDE columns, storage parameters and tablespace options. Their KeyElement nodes expose key expressions, ordering and exclusion operators. Column identity clauses are represented by ColumnOption.Kind.IDENTITY and IdentityDefinition, with a generation mode and ordered Sequence.Parameter values. These APIs cover the schema clauses described in CREATE TABLE.

Inspect indexes and constraints separately

Index represents index declarations. Constraints are independent NamedConstraint nodes: KeyConstraint for PRIMARY KEY/UNIQUE, ForeignKeyConstraint, CheckConstraint, ExcludeConstraint, DefaultConstraint, NotNullConstraint and ConstraintUsingIndex. Constraint nodes do not extend Index. Their ConstraintKind is separate from the physical index classification Index.Kind.

CreateTable.getIndexes() returns only indexes, while getTableConstraints() returns table-level constraints. Both are mutable views of getTableElements() when ordered elements are present; removing a constraint preserves indexes and their source positions. To inspect column and table constraints together, use getConstraints(). This returns ConstraintDeclaration views in declaration order.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
    "CREATE TABLE t (id INT PRIMARY KEY, label TEXT)");
ConstraintDeclaration primary = table.getConstraints().stream()
    .filter(declaration -> declaration.getKind() == ConstraintKind.PRIMARY_KEY)
    .findFirst().orElseThrow();
primary.getColumn().setColumnName("new_id");
// CREATE TABLE t (new_id INT PRIMARY KEY, label TEXT)

getColumn() retains the owner of an inline declaration and is null for a table constraint. getColumnOption() exposes an inline option; getConstraint() exposes its original NamedConstraint when one exists. Column REFERENCES and nullability options retain their own payloads instead of manufacturing a table constraint or a key-column list. Edit the original source nodes to change SQL. Explicit NOT NULL and REFERENCES are classified; ordinary column DEFAULT values are options, while explicit DefaultConstraint nodes are classified as DEFAULT. NULL, raw tokens and implied constraints are not inferred.

The declaration list is an unmodifiable membership snapshot. Its views read live source nodes, so type edits change getKind(). Request a new list after adding, removing or changing column options. Structured constraint nodes remain included when their kind is OTHER. Without ordered table elements, manually constructed columns precede table constraints and indexes.

ColumnOption.getConstraintKind() and AlterExpression.getConstraintKind() also return ConstraintKind. getOperation() distinguishes addition from removal. ALTER ADD constraints use getConstraint(); ADD INDEX uses getIndex(). These getters expose stored definitions; after changing an action, getConstraintKind() describes the active syntax rather than an inactive stored definition. Column actions retain constraints on their column options. RENAME CONSTRAINT uses getConstraintName() for the source and getNewConstraintName() for the target; it does not manufacture index nodes. A named DROP CONSTRAINT pk reports OTHER because SQL alone does not reveal what pk names. This query does not validate a definition’s completeness or consult the database catalogue.

MySQL table-level UNIQUE, UNIQUE KEY and UNIQUE INDEX declarations are KeyConstraint nodes. A standalone CREATE UNIQUE INDEX remains CreateIndex with an Index payload. The parser does not invent backing index nodes for PRIMARY KEY/UNIQUE constraints. PostgreSQL USING INDEX retains the existing index name on ConstraintUsingIndex rather than creating key elements or an index definition.

An ALTER foreign key owns one ForeignKeyConstraint and its mutable ForeignKeyReference. The legacy getFk*/setFk* accessors and referential-action helpers delegate to these same nodes, so edits through either API affect both SQL renderers.

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent (id)");
AlterExpression action = alter.getAlterExpressions().get(0);
ForeignKeyConstraint foreignKey = (ForeignKeyConstraint) action.getConstraint();
foreignKey.getReference().getTable().setSchemaName("archive");
action.setFkSourceTable("parent_history");
// Both APIs now refer to archive.parent_history.
foreignKey.setReferentialAction(ReferentialAction.Type.DELETE,
    ReferentialAction.Action.CASCADE);

New code should use the structured constraint API. getFkColumns() returns a rendered snapshot, like getPkColumns() and getUkColumns(); use setFkColumns()/addFkColumns() to replace or append names, or edit foreignKey.getColumns() directly. Referenced-column lists and referential-action objects are live objects on the shared reference.

A non-null legacy FK setter creates a foreign-key definition when none is present. It rejects an existing non-FK constraint or index with IllegalStateException instead of retaining a second hidden definition; replace the definition explicitly with setConstraint() first. Reading, clearing or removing absent FK properties does not create a definition. Replacing or clearing setConstraint() also replaces or clears what the legacy FK accessors expose. These accessors inspect stored definitions; use getOperation()/getConstraintKind() to identify the active action.

Migration from the former combined model:

• Replace table-constraint reads from getIndexes() with getTableConstraints() and ALTER constraint reads from getIndex() with getConstraint(). ConstraintDeclaration.getIndex() is replaced by getConstraint().

• Replace ForeignKeyIndex with ForeignKeyConstraint and concrete new NamedConstraint() construction with the appropriate constraint class, usually KeyConstraint. NamedConstraint is now abstract.

• Constraint enum values move from Index.Kind to ConstraintKind; Index.Kind retains only UNIQUE, INDEX, FULLTEXT, SPATIAL and OTHER.

• Shared key and option values are now KeyElement (formerly Index.ColumnParams), IndexOption (formerly Index.Option) and IndexOptions.Clustering (formerly Index.Clustering). These are standalone shared components, not aliases to nested index types. Replace getColumnWithParams()/setColumnNamesWithParams() with getColumns()/setColumns() using KeyElement values.

getType() preserves the rendered spelling. setType() refreshes kind metadata for indexes and key constraints; setKind() does not rewrite SQL. Constraints with a fixed renderer, such as CHECK, always report their actual constraint kind. Replacing an index with a table constraint requires replacing the AST node; changing an index’s type string cannot change its Java type.

ALTER key definitions use getKeyConstraint() for typed access to the same object returned by getConstraint(). PRIMARY KEY and UNIQUE builder setters also create this canonical definition; they no longer keep separate column, name or KEY/INDEX state:

Alter add = (Alter) CCJSqlParserUtil.parse(
        "ALTER TABLE t ADD UNIQUE KEY uq (id)",
        parser -> parser.withDialect(Dialect.MYSQL));
KeyConstraint key = add.getAlterExpressions().get(0).getKeyConstraint();
key.setColumns(List.of(new KeyElement("account_id")));
key.setIndexName("uq_account");
System.out.println(add); // ALTER TABLE t ADD UNIQUE KEY uq_account (account_id)

Parenthesized deletion targets have their own getDropColumns() and setDropColumns() API. It covers multi-column DROP (...), DROP UNIQUE (...) and the parser’s legacy parenthesized DROP FOREIGN KEY (...) form. Named constraint drops continue to use getConstraintName(). No key definition is manufactured for a drop:

Alter drop = (Alter) CCJSqlParserUtil.parse("ALTER TABLE t DROP (old_a, old_b)");
drop.getAlterExpressions().get(0).getDropColumns().set(0, "obsolete_a");
System.out.println(drop); // ALTER TABLE t DROP (obsolete_a, old_b)

setDropColumns copies its input; getDropColumns returns the owned mutable list. withDropColumns and addDropColumns also work before selecting the operation. Stored drop targets and key definitions remain independent when an operation changes.

Migration of older ALTER builders:

• getPkColumns() and getUkColumns() now consistently return snapshots, including manually built keys. Use their setters/adders or edit getKeyConstraint().getColumns() to change SQL. The string setters replace elements without retaining expression or ordering attributes.

• The legacy PK accessor remains a DROP-target alias when DROP, DROP UNIQUE or DROP FOREIGN KEY is already selected. Replace builders that set PK columns before selecting DROP with withDropColumns(...); changing the operation does not transfer a key definition into deletion targets.

• getUkName accesses the UNIQUE definition’s index name; the constraint symbol remains getKeyConstraint().getName(). KEY/INDEX flags read that same definition. setUkTypeSpecified(false) removes the spelling; re-enabling it without a spelling selects INDEX.

• Non-null key setters create a definition only when none exists. They throw IllegalStateException for a competing constraint or index; use setConstraint for an explicit replacement. Reads and null clears never create a definition or restore values from a replaced one.

Exclusion keys expose getExclusionOperatorReference() for structured access to an operator’s schemaName, name and useOperatorKeyword flag. In Dialect.POSTGRESQL, both CREATE and ALTER accept WITH OPERATOR(pg_catalog.&&). Editing the reference updates both statement renderers. The existing string getExclusionOperator() returns its complete SQL spelling; its setter remains available for replacing opaque operator text. New code can construct a reference with new ExclusionOperator().withSchemaName("pg_catalog").withName("&&") .withUseOperatorKeyword(true).

Alter alter = (Alter) CCJSqlParserUtil.parse(
        "ALTER TABLE accounts ALTER COLUMN id TYPE bigint USING id + 1");
AlterExpression.ColumnDataType column = alter.getAlterExpressions().get(0)
        .getColDataTypeList().get(0);
Expression conversion = column.getUsingExpression();
column.setUsingExpression(CCJSqlParserUtil.parseExpression("id * 10"));
String updatedSql = alter.toString();

Identity alterations are directly accessible through AlterExpression.getColumnIdentityActions(); the existing ColumnDataType.getIdentityAlterations() API remains available. Sequence ownership is shared by CreateSequence and AlterSequence through Sequence.getOwnership(): null means omitted, isNone() means explicit OWNED BY NONE, and getColumn() identifies an owner. TablesNamesFinder includes LIKE sources and sequence owners without treating sequence or type names as tables. See ALTER TABLE and ALTER SEQUENCE.

For identity columns, RESTART 20 and RESTART WITH 20 produce the same IdentityAlteration with kind RESTART and getRestartWith() == 20L. The renderer consistently uses RESTART WITH 20. A null restart value means bare RESTART, which uses the sequence’s configured start value. Call setRestartWith to edit a parsed action or construct one with new IdentityAlteration(Kind.RESTART).withRestartWith(20L).

Edit column identity actions

getColumnIdentityActions() returns active ALTER COLUMN identity actions as ColumnIdentityAction entries. Each entry exposes the editable column name and its ordered IdentityAlteration list, without requiring a data-type cast:

Alter alter = (Alter) CCJSqlParserUtil.parse(
        "ALTER TABLE accounts ALTER COLUMN id "
        + "ADD GENERATED BY DEFAULT AS IDENTITY (CACHE 10)",
        parser -> parser.withDialect(Dialect.POSTGRESQL));
ColumnIdentityAction column = alter.getAlterExpressions().get(0)
        .getColumnIdentityActions().get(0);
column.setColumnName("account_id");
IdentityAlteration identity = column.getIdentityAlterations().get(0);
identity.setGenerationMode(IdentityDefinition.GenerationMode.ALWAYS);
identity.getParameters().get(0).setValue(20L);
System.out.println(alter);
// ALTER TABLE accounts ALTER COLUMN account_id
// ADD GENERATED ALWAYS AS IDENTITY (CACHE 20)

The outer list has unmodifiable membership; its entries are the original AST nodes. Request a new list after changing action membership or switching the outer ALTER operation. The inner alteration list is mutable: replace an entry to change its kind, or use setIdentityAlterations to replace the ordered actions for a column. Unrelated actions and inactive inherited payloads are excluded. An inline identity declaration in ADD COLUMN remains a ColumnOption rather than an identity alteration.

getGenerationMode/setGenerationMode work for both ADD and SET_GENERATED. getParameters/setParameters work for ADD and SET_PARAMETER. ADD access delegates to its existing IdentityDefinition; replacing or clearing that definition immediately changes both views. Parameters are ordered mutable Sequence.Parameter values, with null meaning omitted. RESTART and DROP retain their distinct getRestartWith and isIfExists properties. A sequence such as SET CACHE 10 RESTART WITH 20 remains two ordered alterations.

For a newly constructed ADD action without a definition, mode and parameter getters return null. Non-null setters create the definition; setting parameters first uses BY DEFAULT. Null setters do not create one. This does not change the SQL grammar or validate whether a column already has an identity in a database.

Column defaults on tables and views

AlterExpression.getColumnDefaults() exposes active SET DEFAULT actions through ColumnDefaultAction for both tables and views. The returned list is read-only, but its entries are the original mutable AST objects:

for (String objectType : List.of("TABLE", "VIEW")) {
    Statement statement = CCJSqlParserUtil.parse(
            "ALTER " + objectType + " t ALTER COLUMN a SET DEFAULT 1",
            parser -> parser.withDialect(Dialect.POSTGRESQL));
    List<? extends AlterExpression> actions = statement instanceof Alter
            ? ((Alter) statement).getAlterExpressions()
            : ((AlterRelation) statement).getActions();
    for (AlterExpression action : actions) {
        for (ColumnDefaultAction column : action.getColumnDefaults()) {
            column.setDefaultExpression(new LongValue(42));
        }
    }
    System.out.println(statement); // SET DEFAULT 42 for either object type
}

getColumnName() and setColumnName() access the affected column. getDefaultExpression() exposes parsed expressions; getDefaultValue() returns their SQL text. Existing ColumnSetDefault constructors still accept opaque strings, for which getDefaultExpression() returns null. Replacing an expression also replaces any opaque value. Unrelated actions return an empty list. This lookup follows the action’s current rendering path: changing an operation or retaining unused metadata does not make a fixed-body action expose SET DEFAULT entries it will not render.

hasActiveColumnDefinitions() reports whether the action currently renders its column definitions through the general ALTER body. getColDataTypeList() continues to expose stored metadata even when another body takes precedence. AlterDeParser uses the active path, so expression visitors rewrite the selected default or column expressions without rendering inactive metadata. TableDefinitionTraversal and the table-name finder visit the expressions and tables belonging to the selected body. The metadata validator uses that same selection. For example, adding a column definition to an action that still stores an earlier foreign key renders the column and does not require the inactive foreign table or its columns to exist. getConstraint() and getIndex() still expose the stored objects for direct inspection.

getActiveTableDefinition() returns the original definition contributing to the current body, or null when another payload takes precedence. A legacy ADD CONSTRAINT header contributes only its keys; its unused storage options are not traversed. Column options likewise visit only the payload selected by their Kind, including after changing an option to FOREIGN_OPTIONS.

Custom AlterExpression subclasses overriding appendBody should also reflect their dispatch in usesColumnActionBody(), usesGeneralBody() and hasActiveConstraintHeader(). Subclasses rendering a separate definition can override getActiveTableDefinition(). The general-body hook also determines hasActiveColumnDefinitions().

Structured column attributes

ColumnDefinition.getColumnOptions() exposes column nullability, COLLATE, COMMENT, ON UPDATE, AUTO_INCREMENT, visibility, inline PRIMARY KEY and parenthesized generated expressions alongside existing defaults, references and identity declarations. CREATE and ALTER column definitions use the same model.

ColumnOption.Kind selects the relevant accessor: getNullable(), getCollation(), getComment(), getOnUpdateExpression(), getVisible(), getConstraint() or getGeneratedDefinition(). The AUTO_INCREMENT kind has no additional payload. A missing option does not imply a server default. Option order and unknown raw extensions are preserved.

GeneratedColumnDefinition contains an Expression, an explicit GENERATED ALWAYS flag, and nullable Storage (STORED/VIRTUAL). Identity columns continue to use IdentityDefinition. Visitors, validators and custom expression deparsers traverse generation and ON UPDATE expressions and comment literals. This is syntax modeling; server restrictions on permissible generation expressions are not evaluated.

Charset remains in ColDataType.getCharacterSet(); column collation is in its COLLATE option. Consumers can map both directly without reconstructing tokens. StringValue comment bodies retain their SQL escape representation.

A column DEFAULT value ends before a COLLATE at its top level, which becomes the column’s COLLATE option: MySQL’s DEFAULT literal and PostgreSQL’s DEFAULT b_expr do not include COLLATE. A COLLATE inside parentheses, function arguments or a CASE expression stays in the default expression. SET DEFAULT actions and CREATE DOMAIN keep an expression COLLATE.

Migration: DEFAULT 'N' COLLATE utf8mb4_bin in a column definition previously produced one DEFAULT option holding a CollateExpression. It now produces a DEFAULT option with 'N' and a COLLATE option with getCollation(); getColumnSpecs() returns [DEFAULT, 'N', COLLATE, utf8mb4_bin]. SQL output is unchanged.

MySQL column attributes have their own kinds: SRID (getSrid()), COLUMN_FORMAT (getColumnFormat(): FIXED, DYNAMIC or DEFAULT), and ENGINE_ATTRIBUTE/SECONDARY_ENGINE_ATTRIBUTE (getEngineAttribute() and isUseEquals() for the optional equals sign). Engine attribute literals are visited and rendered like comment literals. Inline UNIQUE KEY is one KeyConstraint of type UNIQUE KEY. A bare inline KEY, MySQL’s column synonym for PRIMARY KEY, keeps its KEY spelling and reports ConstraintKind.PRIMARY_KEY. Other values, such as Percona Server’s COLUMN_FORMAT COMPRESSED, remain opaque options.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TABLE t (id INT KEY, g POINT SRID 4326 COLUMN_FORMAT FIXED)");
List<ColumnDefinition> columns = table.getColumnDefinitions();
columns.get(0).getColumnOptions().get(0).getConstraintKind(); // PRIMARY_KEY
ColumnOption srid = columns.get(1).getColumnOptions().get(0);
srid.getSrid();                                                // 4326
srid.setSrid(0);
columns.get(1).addColumnOptions(
        ColumnOption.engineAttribute(new StringValue("{}")));
// CREATE TABLE t (id INT KEY, g POINT SRID 0 COLUMN_FORMAT FIXED ENGINE_ATTRIBUTE '{}')

Migration: these attributes were previously one opaque option per token, and UNIQUE KEY was a UNIQUE constraint followed by an opaque KEY. Inspect the kinds instead of the opaque nodes. getColumnSpecs() and getTokens() keep the previous token split, for example [SRID, 4326] and [UNIQUE, KEY]. When constructing the KEY spelling, set the kind after the type: new KeyConstraint().withType("KEY").withKind(ConstraintKind.PRIMARY_KEY). CHAR(n) BINARY remains an opaque option: MySQL treats BINARY as part of the data type’s character set clause, not as a column attribute.

ColumnDefinition stores all options in one ordered list, including opaque ColumnOption.Kind.OTHER nodes. getColumnOptions() returns that mutable list. getColumnSpecs() returns an unmodifiable snapshot for both raw and structured options; expression fragments may occupy one token and structured keywords use canonical capitalization. Unset options return null; an explicitly empty list returns an empty snapshot.

CreateTable table = (CreateTable) CCJSqlParserUtil.parse(
        "CREATE TABLE t (id INT DEFAULT 1)");
ColumnDefinition column = table.getColumnDefinitions().get(0);
column.getColumnOptions().get(0).setDefaultExpression(new LongValue(2));
column.addColumnSpecs("NOT", "NULL"); // keeps the structured DEFAULT node
// CREATE TABLE t (id INT DEFAULT 2 NOT NULL)

Migration: calls such as getColumnSpecs().add(...) or .set(...) now throw UnsupportedOperationException instead of sometimes changing the AST and sometimes editing a discarded copy. Use addColumnSpecs to append raw tokens, edit the option nodes directly, or pass a modified token-list copy to setColumnSpecs to replace every option with opaque text. Raw tokens are never automatically parsed. Consequently getColumnOptions() is also non-null for raw specifications; inspect Kind to distinguish them.

The constructor and setColumnSpecs copy supplied tokens. setColumnOptions copies list membership but retains the mutable child nodes; later changes to the caller’s list do not change the column. Both getters return null after either setter receives null. Appending preserves the current nodes and accepts an immutable input list. An empty append preserves existing options and creates an empty option list when none was set.

ColumnOption.getTokens() and GeneratedColumnDefinition.getTokens() also return unmodifiable, detached snapshots. Reading tokens does not detach the underlying expressions or other child nodes: their setters and direct edits remain visible in SQL output, visitors and newly requested snapshots.

For opaque Kind.OTHER options, copy the tokens, edit the copy, then call setTokens. The setter and both ColumnOption.raw factories copy their inputs; subsequent changes to the supplied list or array do not edit the AST. Null raw tokens remain unset and return null; an empty list returns an empty snapshot. Raw tokens are never parsed or classified.

ColumnOption raw = ColumnOption.raw("SELECTIVITY", "10");
List<String> tokens = new ArrayList<>(raw.getTokens());
tokens.set(1, "20");
raw.setTokens(tokens); // SELECTIVITY 20

Migration: raw token getter edits now throw UnsupportedOperationException, as do edits to structured token snapshots that previously changed a discarded copy. setTokens only accepts opaque options; it throws IllegalStateException for structured options, including SERIAL DEFAULT VALUE. Use typed setters for structured edits. To explicitly replace a structured option with raw text, replace its node in the column’s option list:

column.getColumnOptions().set(0, ColumnOption.raw("DEFAULT", "8"));

The trailing FIRST or AFTER column of an ALTER TABLE ADD, CHANGE or MODIFY column definition is a ColumnPosition on the AlterExpression.ColumnDataType, not a column option. getColumnSpecs() still ends with its tokens, and setColumnSpecs replaces the options and the position. Migration: getColumnOptions() no longer contains the two opaque AFTER and column-name nodes. As in MySQL, the position must follow the column attributes; a position followed by further attributes is now rejected.

Alter alter = (Alter) CCJSqlParserUtil.parse(
        "ALTER TABLE t ADD COLUMN c INT NOT NULL AFTER id");
AlterExpression.ColumnDataType column =
        alter.getAlterExpressions().get(0).getColDataTypeList().get(0);
column.getPosition().getColumnName();                         // id
column.getColumnSpecs();                                      // [NOT, NULL, AFTER, id]
column.setPosition(ColumnPosition.first());
// ALTER TABLE t ADD COLUMN c INT NOT NULL FIRST

Inspect logical replication statements

PostgreSQL publications and subscriptions have separate statement and option models. No database connection is opened when these statements are parsed.

CreatePublication publication = (CreatePublication) CCJSqlParserUtil.parse(
        "CREATE PUBLICATION changes FOR TABLE accounts (id) WHERE (active = true)");
PublicationTable target = publication.getTargets().get(0).getTables().get(0);
Table table = target.getTable();
List<Column> columns = target.getColumns();
Expression filter = target.getWhere();

A PublicationTarget distinguishes a group of explicit tables from TABLES IN SCHEMA. Target order, repeated TABLE groups, ONLY and an explicit descendant * are preserved. CreatePublication.isAllTables() represents FOR ALL TABLES; an empty target list without that flag means no target clause was specified. AlterPublication.getAction() distinguishes adding, replacing or removing targets from option, owner and name changes.

PublicationOption exposes typed operation sets, partition-root booleans and generated-column modes. SubscriptionOption has a separate key enum and typed streaming, origin, synchronous-commit and boolean accessors. The ordered option lists contain only explicitly written options; server defaults, which can differ across PostgreSQL versions, are not injected into the AST. A missing value on a boolean option represents its explicit short form, equivalent to = true.

CreateSubscription subscription = (CreateSubscription) CCJSqlParserUtil.parse(
        "CREATE SUBSCRIPTION changes_sub CONNECTION 'dbname=app' "
        + "PUBLICATION changes WITH (connect = false)");
StringValue connection = subscription.getConnection();
List<String> publications = subscription.getPublications();
Boolean connect = subscription.getOptions().get(0).getBooleanValue();

Connection strings remain string literals for lossless SQL regeneration. They can contain credentials and should not be logged without redaction. SubscriptionOption.isSlotNameNone() distinguishes the unquoted NONE keyword from a literal slot named 'NONE'. AlterSubscription covers connection changes, publication lists and refresh, enable/disable, options, skip LSN, ownership and renaming.

Publication table columns and row filters participate in visitors and custom expression deparsers. TablesNamesFinder reports explicitly named publication tables, but cannot enumerate ALL TABLES or schema-wide targets without a catalog. Publication and subscription names are not table names. Feature classification reports schema modification; a subscription that may start asynchronous replication can additionally report possible data modification.

See CREATE PUBLICATION, ALTER PUBLICATION, CREATE SUBSCRIPTION and ALTER SUBSCRIPTION.

Inspect type, domain and extension statements

PostgreSQL type DDL uses the existing column data-type model. A CreateType exposes its qualified name and a TypeDefinition: EnumTypeDefinition, CompositeTypeDefinition, RangeTypeDefinition or BaseTypeDefinition. A null definition denotes a shell type. Enum labels are ordered StringValue nodes, composite attributes carry a name, ColDataType and optional collation, and range options distinguish the subtype from names of support functions, collations and operator classes.

CreateType type = (CreateType) CCJSqlParserUtil.parse(
        "CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')");
EnumTypeDefinition definition = (EnumTypeDefinition) type.getDefinition();
String firstLabel = definition.getLabels().get(0).getValue();

AlterType alteration = (AlterType) CCJSqlParserUtil.parse(
        "ALTER TYPE mood ADD VALUE IF NOT EXISTS 'fine' AFTER 'ok'");
alteration.getPosition();       // AlterType.Position.AFTER
alteration.getNeighborValue(); // StringValue containing 'ok'

AlterType also models type ownership, renaming, schema moves and ordered composite-attribute changes.

A base type, CREATE TYPE name (INPUT = fn, OUTPUT = fn, ...), has a BaseTypeDefinition, and ALTER TYPE name SET (property = value, ...) has the action SET_PROPERTIES with getProperties(). Both hold DefinitionElement items in source order: a name with its spelling and quoting, and a DefinitionArgument, which is null for an element without a value such as PASSEDBYVALUE. The argument follows PostgreSQL’s grammar: its Kind is TYPE (a ColDataType, with isSetOf() and isTypeReference() for SETOF and %TYPE), KEYWORD (a reserved keyword such as true), OPERATOR, NUMBER, STRING or NONE. PostgreSQL reads a function name or a word such as plain as a type name, so these are TYPE arguments whose name is getDataType().getDataType(). The property names and the kind of value each property takes are not checked, because PostgreSQL checks them when it runs the statement.

CreateType type = (CreateType) CCJSqlParserUtil.parse(
        "CREATE TYPE box3 (INPUT = box3_in, OUTPUT = box3_out, INTERNALLENGTH = 16, PASSEDBYVALUE)");
BaseTypeDefinition definition = (BaseTypeDefinition) type.getDefinition();
definition.getElement("INPUT").getArgument().getDataType().getDataType(); // box3_in
definition.getElement("INTERNALLENGTH").getArgument().getNumber();        // LongValue 16
definition.getElement("PASSEDBYVALUE").getArgument();                     // null

AlterType alteration = (AlterType) CCJSqlParserUtil.parse(
        "ALTER TYPE box3 SET (SEND = box3_send, RECEIVE = NONE)");
alteration.getAction();                                   // AlterType.Action.SET_PROPERTIES
alteration.getProperties().get(1).getArgument().getKind(); // DefinitionArgument.Kind.NONE

See CREATE TYPE and ALTER TYPE.

CreateDomain stores a ColDataType, default expression and ordered DomainConstraint nodes. Each constraint distinguishes nullability from a check expression and preserves its optional name. AlterDomain models default/nullability changes, constraint actions, ownership, renaming and schema moves. Its isNotValid() flag describes the newly added check constraint. Domain expressions participate in statement visitors, expression deparsers and validation.

CreateExtension preserves IF NOT EXISTS, the optional WITH, and ordered schema/version/cascade options. Version identifiers and quoted versions are represented as Column and StringValue, respectively. AlterExtension distinguishes update, schema change, and member addition/removal. An ExtensionObject identifies the object kind and target; function/procedure/aggregate members expose a RoutineReference with typed signature arguments, not call expressions. A null argument list means the signature was omitted, whereas an empty list means explicit ().

Type names, domain names and routine signatures are not reported as tables by TablesNamesFinder; relation members of an extension are included. Extension scripts themselves are not inspected or executed. The obsolete CREATE EXTENSION ... FROM form is not supported.

See PostgreSQL’s documentation for CREATE TYPE, ALTER TYPE, CREATE DOMAIN and ALTER EXTENSION.

Inspect operator and aggregate definitions

PostgreSQL’s operator and aggregate DDL parses to typed statements that reuse the models of DROP OPERATOR, DROP AGGREGATE and CREATE TYPE: an operator is an ExclusionOperator (schema and symbol) or an OperatorSignature (with its operand types, null for NONE), an aggregate is a RoutineReference, and a definition list holds DefinitionElement items with a DefinitionArgument. PostgreSQL checks the names and values of a definition when it runs the statement, so they are not restricted.

Statement	Model
CREATE OPERATOR [schema.]op (definition)	CreateOperator: getOperator(), getDefinition(), getDefinitionElement(name)
ALTER OPERATOR op (left, right) {OWNER TO | SET SCHEMA | SET (...)}	AlterOperator: getOperator(), getAction(), getNewName(), getProperties()
CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE type USING method [FAMILY family] AS ...	CreateOperatorClass: getName(), isDefaultClass(), getDataType(), getAccessMethod(), getFamily(), getItems()
ALTER OPERATOR CLASS name USING method {RENAME TO | OWNER TO | SET SCHEMA}	AlterOperatorClass: getName(), getAccessMethod(), getAction(), getNewName()
CREATE OPERATOR FAMILY name USING method	CreateOperatorFamily: getName(), getAccessMethod()
ALTER OPERATOR FAMILY name USING method {ADD ... | DROP ... | RENAME TO | OWNER TO | SET SCHEMA}	AlterOperatorFamily: getName(), getAccessMethod(), getAction(), getItems(), getNewName()
CREATE [OR REPLACE] AGGREGATE name (arguments) (definition) and the old CREATE AGGREGATE name (BASETYPE = type, ...)	CreateAggregate: isOrReplace(), getAggregate(), isOldStyle(), getDefinition(), getDefinitionElement(name)
ALTER AGGREGATE name (arguments) {RENAME TO | OWNER TO | SET SCHEMA}	AlterAggregate: getAggregate(), getAction(), getNewName()

getNewName() is the new name, the role or the schema of the action. An OperatorClassItem is one member of an operator class or family: its Kind is OPERATOR, FUNCTION or STORAGE, with the strategy or support number, the operator and its optional operand types, the Purpose (FOR SEARCH or FOR ORDER BY with getSortFamily()), the function as a RoutineReference, or the storage type. A member that ALTER OPERATOR FAMILY ... DROP removes has a kind, a number and operand types only.

CreateOperator operator = (CreateOperator) CCJSqlParserUtil.parse(
        "CREATE OPERATOR app.=== (FUNCTION = app.eq, LEFTARG = int, RIGHTARG = int, "
                + "NEGATOR = OPERATOR(app.!==), HASHES)");
operator.getOperator().getSchemaName();                          // app
operator.getOperator().getName();                                // ===
operator.getDefinitionElement("NEGATOR").getArgument().getOperator(); // app.!==
operator.getDefinitionElement("HASHES").getArgument();           // null

CreateOperatorClass operatorClass = (CreateOperatorClass) CCJSqlParserUtil.parse(
        "CREATE OPERATOR CLASS oc DEFAULT FOR TYPE int4 USING btree AS "
                + "OPERATOR 1 < (int4, int4), FUNCTION 1 btint4cmp(int4, int4)");
OperatorClassItem item = operatorClass.getItems().get(0);
item.getNumber();                     // 1
item.getOperator().getName();         // <
item.getOperandTypes();               // [int4, int4]

CreateAggregate aggregate = (CreateAggregate) CCJSqlParserUtil.parse(
        "CREATE AGGREGATE app.total (numeric ORDER BY int) (SFUNC = app.add, STYPE = numeric)");
aggregate.getAggregate().getName();              // app.total
aggregate.getAggregate().getOrderByArguments();  // [int]
aggregate.getDefinitionElement("STYPE").getArgument().getDataType(); // numeric

An operator name is one name with its exact text, such as ===, <==> or @#@, wherever PostgreSQL DDL names an operator. It follows the rules of PostgreSQL’s lexer: the parts must touch each other, a name of several characters ends in + or - only if it contains one of ~ ! @ # % ^ & | ?, => alone is not an operator, and a name has at most 63 characters. A database may precede the schema (db.schema.+); getSchemaName() then returns both. An operator that contains a backtick is not supported, and without Dialect.POSTGRESQL the lexer reads # before #, @ or a letter as the start of a name. An aggregate signature is * or has at least one argument, so agg() is rejected wherever an aggregate is named.

Without a dialect, CREATE OPERATOR and ALTER OPERATOR are read this way when an operator symbol follows, so Oracle’s CREATE OPERATOR name BINDING ... stays an UnsupportedStatement, and so do MySQL’s CREATE AGGREGATE FUNCTION and SQL Server’s CREATE AGGREGATE name (@parameter type) .... With Dialect.POSTGRESQL they are parse errors. Validators report createOperator, alterOperator, createOperatorClass, alterOperatorClass, createOperatorFamily, alterOperatorFamily, createAggregate and alterAggregate, which PostgreSQL enables.

See CREATE OPERATOR, ALTER OPERATOR, CREATE OPERATOR CLASS, ALTER OPERATOR CLASS, CREATE OPERATOR FAMILY, ALTER OPERATOR FAMILY, CREATE AGGREGATE and ALTER AGGREGATE.

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, ALTER TABLE .. TRUNCATE PARTITION
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 the analysis identifies an unresolved function, procedure or dynamic SQL marker, it records the reference so you can consult your own catalogue or allow-list:

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]
}

The generic fallback for an unclassified statement preserves OPAQUE and the possible effects READS_DATA, RETURNS_RESULT_SET, MODIFIES_DATA and MODIFIES_SCHEMA, but does not add a Java statement or container class name to getUnresolvedReferences(). The reference set can therefore be empty while the effects remain unknown. Check isOpaque() and may(..); an empty reference set alone does not establish safety.

Existing explicit references and markers remain, including procedure and function names, do, unsupported and explain. Stored routine declarations also retain their existing statement-type markers, such as createfunction; these are independent of the generic fallback.

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

A name is left out only where it resolves to a WITH item in scope. An alias hides no table, and without RECURSIVE a WITH item does not see itself or the items after it. The target of a DML statement is always reported. So the result may hold names that are no tables on some databases, such as a WITH item that recurses without RECURSIVE: check it against the metadata of the database if that matters.

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

The same visitor can render an entire statement list:

Statements statements = CCJSqlParserUtil.parseStatements("SELECT 1; SELECT 2;");
StringBuilder script = new StringBuilder();
statements.accept(new StatementDeParser(script), null);
Assertions.assertEquals("SELECT 1;\nSELECT 2;\n", script.toString());

Statement lists and nested blocks share the separator policy used by Statements.toString(). Blocks and IF/ELSE statements retain their own semicolon settings. Custom deparsers receive each child statement and the IF/ELSE condition through the visitor API, with the supplied context.

Customize JSON expression output

ExpressionDeParser visits structured children of JsonFunction and JsonTableFunction while rendering them. A custom expression printer can rewrite inputs, paths, PASSING values and DEFAULT expressions, including nested JSON_TABLE columns, without changing the AST. JSON object keys and values are visited when they are Expression objects; opaque objects and additional query-path strings retain their existing rendering.

Statement statement = CCJSqlParserUtil.parse(
        "SELECT JSON_VALUE((SELECT doc FROM source_table), '$.a')");
ExpressionDeParser expressions = new ExpressionDeParser() {
    @Override
    public <S> StringBuilder visit(Column column, S context) {
        return getBuilder().append("payload");
    }
};
statement.accept(new StatementDeParser(expressions, new SelectDeParser(),
        expressions.getBuilder()), null);
// SELECT JSON_VALUE((SELECT payload FROM source_table), '$.a')

StatementDeParser connects the select and expression printers, so subqueries inside JSON expressions use the same printer. For expression-only use with subqueries, connect an appropriate SelectDeParser explicitly.

JSON nodes also expose appendTo(StringBuilder, Consumer<Expression>) for direct rendering. The consumer prints each child into the same builder; ExpressionDeParser forwards the visitor context through that callback. JsonFunction additionally has consumer overloads of appendObject, appendArray, appendValue, appendQuery and appendExists.

Subclass migration: override these consumer overloads or appendTo to customize both default and visitor output. The deparser enters through appendTo; overriding only a legacy single-argument append* or toString() method does not customize that path. JsonFunction also uses the consumer overloads for type-specific default rendering. Subclasses of concrete JSON_TABLE column definitions should move toString() customization to appendTo. Direct subclasses of the abstract JsonTableColumnDefinition retain their opaque toString() fallback until they override appendTo.

ODBC timestamp intervals

In ODBC escapes such as {fn TIMESTAMPADD(SQL_TSI_YEAR, 2, travel_date)} and {fn TIMESTAMPDIFF(SQL_TSI_DAY, start_date, end_date)}, the first argument is a DateUnitExpression for the nine standard SQL_TSI_* interval keywords. The original ODBC keyword is preserved on output and is not visited as a column. This applies only to unqualified, escaped calls with three arguments and a bare interval keyword. Ordinary calls, qualified names, quoted identifiers and other arguments keep their existing expression interpretation.

Handle Parse Errors

CCJSqlParserUtil.parse(String, ...) requires a statement: null and empty string inputs throw JSQLParserException, matching the default behavior for whitespace-only and comment-only input. CCJSqlParserUtil.parseStatements(String, ...) returns a new, mutable empty Statements list for null or empty input, as it already does for whitespace-only and comment-only input. This applies to the overloads with parser configuration callbacks and caller-provided executors; caller-provided executors remain open. These empty-input results replace the previous null returns of these methods.

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) captures an unrecognised statement root as UnsupportedStatement; it may be the first statement. Failures in recognised statement grammar still throw, or use withErrorRecovery(true) and record the error. Thus SELECT * FROM, INSERT INTO t (a and UPDATE t SET do not become opaque statements. The two options serve different purposes; enabling unsupported capture does not suppress recognised syntax errors.

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; shutdown defrag; 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("shutdown defrag", statements.get(1).toString());

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

Note

Opaque capture is not SQL validation. An unrecognised root or an existing opaque grammar branch cannot establish validity in an unknown dialect. Callers that need validity must validate against that dialect. Recognised syntax failures formerly captured by the generic fallback now throw or produce an error-recovery placeholder; partial IF results and typed prefixes with invalid trailing clauses are not published as successful statements. SingleStatement() remains an incremental entry point; use Statement() or Statements() to check complete statement boundaries.

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 and CLUSTERED / NONCLUSTERED options on table-level primary key and unique constraints and CREATE INDEX
POSTGRESQL	tagged dollar strings, the newline rule for ordinary string literals, literal-local E'...' escapes, and preservation of dots inside quoted names
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
INFORMIX	Informix ALTER TABLE ... ADD CONSTRAINT definitions with optional trailing constraint names
SPANNER	GoogleSQL CREATE [UNIQUE] NULL_FILTERED INDEX with a separate null-filtering flag
DORIS	JOIN [shuffle] and JOIN [broadcast] distribution hints
COCKROACHDB	ALTER TABLE ... ALTER PRIMARY KEY USING COLUMNS (...) with optional hash sharding and storage parameters
TERADATA	UPDATE target FROM sources SET ... with the FROM clause before SET
SQLITE	INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE and INSERT OR ROLLBACK conflict clauses; UPDATE OR with any of ABORT, FAIL, IGNORE, REPLACE and ROLLBACK

Features set explicitly after the preset win over it.

With Dialect.MYSQL, -- starts a line comment only when followed by whitespace or a control character. Thus SELECT 1--2 contains subtraction and a negative operand. SQL inside an unversioned executable comment is parsed normally: SELECT 1 /*! + 2 */ becomes SELECT 1 + 2. Optimizer hints (/*+ ... */) remain separate from executable comments.

Conditional executable comments require an explicit target server version:

Statement stmt = CCJSqlParserUtil.parse(
        "SELECT 1 /*!90702 + 2 */",
        parser -> parser.withDialect(Dialect.MYSQL).withMySqlServerVersion(90702));

The value is major * 10000 + minor * 100 + patch; for example, 90702 means 9.7.2 and 260700 means 26.7.0. Five-digit and six-digit comment version prefixes follow MySQL’s whitespace rules. SQL for a newer server is ignored; SQL for the configured version or an older one participates in the AST. Both SQL renderers produce the resolved SQL for that target, without the conditional wrappers. Without a target, a conditional executable comment raises an error instead of silently discarding its SQL. This replaces the previous treatment of all executable comments as ordinary discarded comments. The same configuration applies to parsers constructed with a StreamProvider or FeatureConfiguration. Other dialects retain their comment behavior. Nested executable comments are rejected; nested-comment acceptance in other dialects is not a guarantee that the same SQL is valid on MySQL.

Non-reserved names such as comment work unquoted in ADD, MODIFY, CHANGE, DROP and RENAME column actions. The COLUMN keyword does not change how the name is interpreted. Column definitions remain editable:

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE t MODIFY COLUMN comment TEXT",
    parser -> parser.withDialect(Dialect.MYSQL));
AlterExpression.ColumnDataType column = alter.getAlterExpressions().get(0)
    .getColDataTypeList().get(0);
column.setColumnName("notes");
column.getColDataType().setDataType("LONGTEXT");
// ALTER TABLE t MODIFY COLUMN notes LONGTEXT
String sql = alter.toString();

Select Dialect.POSTGRESQL when parsing PostgreSQL SQL. For example, DROP INDEX "a.b" has the single name "a.b" and no schema; the dot inside the quotes is not a separator. This applies to table and column references throughout DDL and DML. The default configuration retains its historical name-splitting behavior for compatibility with BigQuery names.

COMMENT ON TABLE t IS $tag$body$tag$ accepts tagged dollar strings with the PostgreSQL preset, or with withDollarQuotedStringTags(true). Tagged strings remain disabled by default. Table, column and view comments preserve their dollar delimiter and literal body. Ordinary single-quoted strings separated by a newline concatenate; dollar-quoted strings do not. E'...' enables backslash escapes for that literal without changing the treatment of ordinary strings elsewhere in the statement.

The shared type grammar accepts negative scales, including numeric(2, -3), in type fragments, DDL and casts. getPrecision() returns 2 and getScale() returns -3; an omitted scale returns null. PostgreSQL’s precision and scale range checks remain the database’s responsibility. When constructing a ColDataType directly, ColDataType.fromNumericParameters("numeric", 2, -3) accepts negative scales; use null for omitted parameters. The legacy int constructor continues to treat negative arguments as omitted parameters.

StringValue.getValue() returns the literal body as written, and toString() renders it unchanged. getNotExcapedValue() returns the characters it denotes. A doubled quote of the literal’s own quote character is resolved, so "it""s" yields it"s. getEscaping() records how the parser read backslashes:

• MYSQL_BACKSLASH without a dialect or with Dialect.MYSQL/MARIADB when backslash escapes are enabled: 'it\'s' yields it's, \n, \t, \0 and \Z become their characters, and \% and \_ keep their backslash.

• POSTGRESQL_ESCAPE_STRING for E'...' with Dialect.POSTGRESQL, including \x, octal and \u escapes; octal and hexadecimal escapes are UTF-8 bytes.

• POSTGRESQL_UNICODE_ESCAPE for U&'...', see `PostgreSQL Unicode escape strings`_.

• STANDARD otherwise, including dollar-quoted strings and constructed values; backslashes stay literal. Other dialects with backslash escapes keep this reading.

setEscaping() selects the reading for a constructed value. Escaping is not part of equals(), like the quote character.

With Dialect.POSTGRESQL, U&'...' and an optional UESCAPE 'c' clause form one StringValue. Its prefix is U&, getValue() returns the body as written, and toString() renders the literal with its clause. getUnicodeEscapeCharacter() returns the UESCAPE character, or null without the clause, when the escape character is a backslash. getNotExcapedValue() resolves the escapes as PostgreSQL does:

• the escape character followed by four hexadecimal digits, or by + and six, is that code point. A character above U+FFFF can also be written as a UTF-16 surrogate pair of two escapes;

• a doubled escape character and a doubled quote stand for themselves.

Comment comment = (Comment) CCJSqlParserUtil.parse(
        "COMMENT ON TABLE t IS U&'d!0061t!+000061' UESCAPE '!'",
        parser -> parser.withDialect(Dialect.POSTGRESQL));
comment.getComment().getNotExcapedValue(); // "data"
comment.toString(); // COMMENT ON TABLE t IS U&'d!0061t!+000061' UESCAPE '!'

• The parser rejects what PostgreSQL rejects: an escape without its digits, code point 0 or one above U+10FFFF, an unpaired surrogate, and an escape character that is a hexadecimal digit, +, a quote, whitespace or not ASCII. getNotExcapedValue() throws IllegalArgumentException for such a constructed value.

• Without Dialect.POSTGRESQL, U&'x' keeps its previous reading: the column U, the & operator and the string 'x'.

• UESCAPE may follow the literal after spaces, tabs or newlines, but not after a comment or a continuation line.

• U&"..." quoted identifiers are not supported.

• The UESCAPE character is not part of equals(), like the escaping.

MySQL user-variable targets in SELECT ... INTO @variable require Dialect.MYSQL or Dialect.MARIADB. They are stored in PlainSelect.getMySqlSelectIntoClause().getVariables() as UserVariable expressions, with the clause position preserved before FROM or at the end of the query. They are not table targets in getIntoTables().

Doris distribution hints require parser.withDialect(Dialect.DORIS). Join.getJoinHint() exposes the keyword and Position.AFTER_JOIN; the existing SQL Server hints use Position.BEFORE_JOIN. Rendering preserves both the position and the brackets around a Doris hint.

CockroachDB primary-key changes require parser.withDialect(Dialect.COCKROACHDB). Their action is an AlterExpressionPrimaryKey with key elements and storage parameters in getConstraint(). isUsingHash() preserves USING HASH, while getBucketCount() holds the legacy WITH BUCKET_COUNT = expression value. The newer WITH (bucket_count = expression) form uses the index storage parameters.

With Dialect.TERADATA, UPDATE a FROM target a, source b SET a.id = b.id uses the existing Update model’s fromItem and joins properties. isFromBeforeSet() preserves the clause position in both SQL renderers. Table discovery and metadata validation recognize a target alias declared in that FROM clause. Other dialects retain the existing FROM-after-SET syntax.

Dialect.SQLSERVER supports methods on expression results, including (SELECT ... FOR XML PATH(''), TYPE).value('.', 'varchar(max)'). MethodCallExpression exposes the receiver expression and a Function containing the method name and arguments. Field access and method calls share the navigation grammar; expression visitors and deparsers traverse both the receiver and method arguments. XQuery strings remain string literals.

Dialect.POSTGRESQL enables DO [LANGUAGE name] code [LANGUAGE name], with the language clause allowed once, before or after the body. DoStatement.getCode() is a StringValue that preserves the literal’s quotes, dollar tag and body text. The optional language and its position have separate properties; an omitted language remains unspecified in the AST. The body is language-specific source, not a parsed PL/pgSQL statement tree. Expression visitors can inspect or replace the body literal. Feature analysis reports OPAQUE; table discovery rejects this statement because the body’s table accesses are unknown. Validation checks the doStatement capability, without validating the procedural language inside the literal.

Enable the PostgreSQL dialect when parsing a script containing a DO block:

Statements statements = CCJSqlParserUtil.parseStatements(
        "DO $$BEGIN RAISE NOTICE 'hello'; END$$; SELECT 1;",
        parser -> parser.withDialect(Dialect.POSTGRESQL));
DoStatement block = (DoStatement) statements.get(0);
String body = block.getCode().getValue();
// body: BEGIN RAISE NOTICE 'hello'; END
// statements.get(1) is the following SELECT.

Semicolons and SQL statements inside the body remain part of its string literal; they do not split the surrounding script into additional statements.

PostgreSQL CREATE FUNCTION and CREATE PROCEDURE declarations also preserve AS '...', AS E'...' and dollar-quoted bodies as opaque text. Their getFunctionDeclarationParts() list retains the body and trailing options, while a following statement is parsed separately. Newline-separated body string continuations retain the newline needed when the declaration is rendered again. Use Dialect.POSTGRESQL for tagged dollar quotes and PostgreSQL-specific DDL such as schema-qualified index collations and operator classes. This support does not validate PL/pgSQL source or build an AST for statements inside the body.

With Dialect.POSTGRESQL, # terminates an unquoted identifier, so JSON operators such as js#>>'{a}' and js#>'{a}' work without surrounding spaces. Quote identifiers containing #, for example "js#". Other dialects retain their existing identifier and hash-comment rules.

With Dialect.SQLSERVER, SET NOCOUNT ON and grouped boolean options such as SET QUOTED_IDENTIFIER, ANSI_NULLS OFF use SetStatement.getOnOffOptions(). The ordered OnOffOption list and shared isOn() value are editable; setOnOffOptions() replaces generic assignments and their scope. Both SQL renderers share statement punctuation while generic assignments retain expression visitor support. Parsing a SET directive records it without changing lexer settings. Dialect.SQLSERVER enables INSERT BULK table (name type, ...) WITH (...). InsertBulk exposes the target table, existing ColumnDefinition models, and ordered typed options, including ROWS_PER_BATCH and ORDER keys. The SQL declaration is preserved; the following binary bulk-load data stream is outside the SQL parser. Visitors and deparsers traverse option values and ordering expressions. Validation uses the insertBulk capability.

With Dialect.SQLSERVER, PRIMARY KEY NONCLUSTERED (id) and UNIQUE CLUSTERED (id) store their clustering option in Index.getClustering() for both CREATE TABLE and ALTER TABLE. Without that dialect, these words retain their existing interpretation as optional index names. SQL Server CREATE TABLE also accepts a trailing comma after the final column or table constraint. SQL output normalizes the definition by omitting that comma.

CREATE UNIQUE NONCLUSTERED INDEX ix ON t (id) also requires Dialect.SQLSERVER. Uniqueness remains in Index.getType() and clustering is stored separately in Index.getClustering(). With Dialect.SPANNER, CREATE UNIQUE NULL_FILTERED INDEX ix ON t (id) stores null filtering in CreateIndex.isNullFiltered(). An omitted clustering or null-filtering option is not supplied from database defaults. The Spanner preset currently selects this index syntax; it does not configure GoogleSQL string-literal rules.

With Dialect.POSTGRESQL, index keys accept schema-qualified collation and operator-class names, for example name COLLATE pg_catalog."C" pg_catalog.text_ops ASC NULLS LAST. Function keys such as lower(name) are stored as expressions. Key attributes are available through getCollation(), getOperatorClass(), getOperatorClassParameters(), getSortOrder() and getNullOrdering() on KeyElement. Under this dialect these attributes are not duplicated in the legacy getParams() list, so changing or removing them is reflected when rendering SQL. Other dialects retain the legacy parameter representation, including MySQL prefix lengths.

CreateIndexDeParser and StatementDeParser pass key expressions, structured option values and the partial-index predicate to their expression visitor. StatementVisitorAdapter, TablesNamesFinder and index validation traverse the same structured expressions.

Informix’s constraint form requires an explicit dialect selection:

Statement stmt = CCJSqlParserUtil.parse(
        "ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (id) "
                + "REFERENCES parent(id) CONSTRAINT fk_child",
        parser -> parser.withDialect(Dialect.INFORMIX));

Row pattern matching (MATCH_RECOGNIZE)

MatchRecognize is a FromItem wrapping the input relation. Its input alias and output alias are independent. getMeasures() and getDefinitions() expose ordinary SQL expressions; getPattern() exposes an editable RowPattern tree with variables, groups, ordered alternatives, sequences, anchors and quantifiers. Oracle and Snowflake additionally support PERMUTE and exclusion nodes. Permutations remain compact in the AST.

PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(
    "SELECT * FROM events MATCH_RECOGNIZE ("
        + "ORDER BY seq MEASURES SUM(A.price) AS total "
        + "PATTERN (A+) DEFINE A AS price > 0 "
        + "OPTIONS (use_longest_match = TRUE))",
    parser -> parser.withDialect(Dialect.BIGQUERY));
MatchRecognize match = (MatchRecognize) select.getFromItem();
RowPattern.Quantified repetition = (RowPattern.Quantified) match.getPattern();
repetition.setReluctant(true);
System.out.println(select); // PATTERN (A+?)

FromItemVisitorAdapter traverses the input and clause expressions. RowPatternVisitorAdapter traverses pattern nodes and optionally visits expressions in quantifier bounds. RowPatternFunction represents an explicit RUNNING or FINAL prefix while exposing the underlying Function to existing expression visitors. Both toString() and the deparsers render the current AST, including edited nested STRUCT arguments.

Select the source dialect explicitly:

• BIGQUERY requires ORDER BY, MEASURES, PATTERN and DEFINE. It supports empty alternatives, literal or query-parameter bounds, adjacent anchors such as ^A+$, and OPTIONS (use_longest_match = TRUE/FALSE).

• ORACLE supports optional ordering/measures, row output modes, targeted AFTER MATCH SKIP, SUBSET, permutations, exclusions and function modes.

• SNOWFLAKE supports the corresponding documented forms except SUBSET and BigQuery options. Its documented alternation-before-concatenation precedence is used for the pattern AST. Other presets use concatenation-before-alternation. Rendering adds explicit parentheses at mixed operator boundaries; it does not translate expression syntax between DBs.

Validation checks the matchRecognize and matchRecognizeOptions features. With an explicit dialect, it also checks selected static rules: pattern variable names, bounds, subset/skip targets and incompatible clause options. Table finding does not treat pattern qualifiers such as A.price as table names. Metadata validation does not bind these columns to the input relation. Function eligibility, aggregation/type rules, data-dependent skip failures and query execution remain the database’s responsibility.

The test fixtures were compared before and after deparsing using GoogleSQL’s reference evaluator and Oracle 26ai Free. The reference evaluator is not the BigQuery service. Snowflake coverage follows its documentation and parser round-trip tests; it has not been executed against a Snowflake account.

See the official BigQuery MATCH_RECOGNIZE syntax, Oracle row pattern matching guide and Snowflake MATCH_RECOGNIZE reference.

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); combined with withBackslashEscapeCharacter, \\" does not end the literal, as in MySQL "it\\"s"
withHashLineComments	# starts a line comment
withBlankLinesAsSeparator	false stops two empty lines (\n\n\n) ending a statement, so they read as plain whitespace
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.

Maven

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

Gradle

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

PostgreSQL roles, privileges and triggers

CreateRole and AlterRole model role attributes and configuration changes, including PostgreSQL’s USER/GROUP aliases. Role options are ordered and typed; passwords and configuration values are expressions. Omitted options remain omitted. CREATE USER name without attributes retains the existing MySQL CreateUser AST by default. Select Dialect.POSTGRESQL explicitly to obtain the PostgreSQL CreateRole AST for this ambiguous form:

CreateRole user = (CreateRole) CCJSqlParserUtil.parse(
    "CREATE USER app",
    parser -> parser.withDialect(AbstractJSqlParser.Dialect.POSTGRESQL));

MySQL account names (user[@host]) of CREATE USER and of view, trigger and routine DEFINER clauses share one grammar rule and are read into separate user and host StringValue parts (UserAccount and MySqlDefiner). Every MySQL spelling is accepted, including app@localhost, app@'%' and 'app'@localhost. A part quoted with ', " or a backquote keeps its quote character in getQuoteStr() and its text in getValue():

CreateUser createUser = (CreateUser) CCJSqlParserUtil.parse(
    "CREATE USER `app`@'%' IDENTIFIED BY 'secret'");
UserAccount account = createUser.getAccounts().get(0);
account.getUser().getValue();    // app
account.getUser().getQuoteStr(); // `
account.getHost().getValue();    // %

Grant retains its string-based getters, setters and fluent methods. Its PrivilegeClause exposes typed Privilege items, column lists, PrivilegeTarget kinds, multiple role memberships and grantor information. getPrivileges() is a mutable string view of the typed privilege list. getRole() and getObjectName() expose the first role or named target for legacy callers; use getRoleItems() and getTarget() for the complete lists. Names in PrivilegeTarget are multipart identifiers, not SQL clause text. Routine targets use RoutineReference data-type signatures, with null arguments for an omitted signature and an empty list for explicit ().

Revoke shares the privilege payload and adds the revoked option and CASCADE/RESTRICT behavior. AlterDefaultPrivileges has separate role/schema scope and one nested GRANT or REVOKE. Table-name discovery reports explicit table targets, not schema-wide targets, routine/type names or role names.

CreateTrigger supports both its existing MySQL statement body and a distinct PostgreSQL routine invocation. PostgreSQL fields include multiple events, UPDATE OF columns, constraint attributes, transition relations, row/statement orientation and a WHEN expression. The absence of FOR ROW/STATEMENT is retained. Expression visitors and StatementDeParser traverse privilege columns, role values, trigger conditions and invocation arguments. Declaring a trigger is classified as a schema change, not execution of its body.

Capability validation covers these statement families, not every server-version or catalog-dependent restriction. Event triggers use separate statements, described below. Serialized role SQL can contain passwords; avoid logging real credentials.

References: CREATE ROLE, ALTER ROLE, GRANT, REVOKE, ALTER DEFAULT PRIVILEGES, CREATE TRIGGER.

MySQL privileges and accounts in GRANT and REVOKE

Grant and Revoke also parse the MySQL forms, in every dialect:

• Privilege levels: PrivilegeTarget.getWildcard() is DEFAULT_DATABASE for *, GLOBAL for *.* and DATABASE for db.*, whose database is the single entry of getNames(). Named levels such as db.tbl keep a null wildcard. Only named table targets are reported by table-name discovery.

• Privileges: MySQL static privileges are Privilege.Kind constants, for example CREATE_TEMPORARY_TABLES or REPLICATION_SLAVE; getName() returns the SQL spelling. Another identifier, such as BACKUP_ADMIN, is a dynamic privilege of kind DYNAMIC with getDynamicName().

• Grantees: PrivilegeClause.getGranteeItems() returns Grantee values, which MySQL and PostgreSQL share. ACCOUNT holds a UserAccount: a MySQL user[@host], read like the CREATE USER account names above, or a PostgreSQL role name without host. The other kinds are PostgreSQL GROUP name, PUBLIC, CURRENT_USER (MySQL also CURRENT_USER()), CURRENT_ROLE and SESSION_USER. getGrantor() types GRANTED BY the same way.

• Role memberships: getRoleItems() holds the granted roles as UserAccount values, so 'reader' and 'writer'@'%' keep their quoting and host. A membership has no privileges and no target.

• MySQL clauses: PROXY ON user is a PROXY privilege on an ACCOUNT target (PrivilegeTarget.getAccount()). REVOKE ALL [PRIVILEGES], GRANT OPTION FROM ... has the privileges ALL and GRANT_OPTION and no target. Revoke records IF EXISTS and IGNORE UNKNOWN USER; Grant.getAsUser() and getWithRole() hold AS user [WITH ROLE ...]; the role selection is a RoleSelection from net.sf.jsqlparser.statement.create.user.

Grant grant = (Grant) CCJSqlParserUtil.parse(
    "GRANT SELECT, BACKUP_ADMIN ON db1.* TO 'app'@'%', CURRENT_USER");
grant.getTarget().getWildcard();                    // DATABASE
grant.getPrivilegeItems().get(1).getDynamicName();  // BACKUP_ADMIN
UserAccount app = grant.getClause().getGranteeItems().get(0).getAccount();
app.getHost().getValue();                           // %

Grant.getOptions() lists every WITH option, including PostgreSQL membership options such as WITH INHERIT TRUE, SET FALSE. Privileges take only GRANT OPTION; memberships take distinct ADMIN, INHERIT and SET options.

The string accessors are kept and derived from the typed state: getUsers(), getGrantees() and getRoles() are mutable string views that write through, getOption() is the first option and getGrantedBy() renders the grantor. getObjectName() returns a wildcard level as written, for example db1.*, and setObjectName() replaces it with a named target. Assigned text is converted with Grantee.valueOf(): special role names are recognized and other text is kept as the account name. Parsed memberships have no target; new Grant() still starts with an empty table target, and setRole() clears it.

The MySQL and MariaDB validation capabilities now include revoke.

References: GRANT, REVOKE, Specifying Account Names.

PostgreSQL event triggers, REINDEX, SECURITY LABEL and schema elements

CREATE EVENT TRIGGER parses to CreateEventTrigger. getEvent() keeps the event name as written and getFilters() the WHEN … IN (…) conditions in source order. The invoked function reuses CreateTrigger.ExecuteKeyword and a Function without arguments. ALTER EVENT TRIGGER parses to AlterEventTrigger; its states reuse RelationAlterAction.EnableState, and each non-null setter selects the STATE, OWNER or RENAME action. A MySQL event named trigger still parses to CreateEvent or AlterEvent.

CreateEventTrigger trigger = (CreateEventTrigger) CCJSqlParserUtil.parse(
    "CREATE EVENT TRIGGER etg ON ddl_command_start "
    + "WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION abort_any_command()");
trigger.getEvent();                          // ddl_command_start
trigger.getFilters().get(0).getVariable();   // TAG
trigger.getFunction().getName();             // abort_any_command

REINDEX parses to Reindex: a Target (INDEX, TABLE, SCHEMA, DATABASE or SYSTEM), the CONCURRENTLY keyword, the parenthesized CONCURRENTLY, TABLESPACE and VERBOSE options with their values as written, and the object name. Only DATABASE and SYSTEM may omit the name. getTable() returns the name only for REINDEX TABLE, which table visitors report. REINDEX is a non-reserved keyword.

SECURITY LABEL parses to SecurityLabel with the provider as written and one target, told apart by getObjectType(): a named object (getObjectName()), a qualified column (getColumn()), a FUNCTION, PROCEDURE, ROUTINE or AGGREGATE signature (getRoutine()) or a large object OID. A null label stands for IS NULL. Table visitors report tables, views, materialized views, foreign tables and a column’s table.

CREATE SCHEMA keeps its schema elements in getStatements() and renders them after the AUTHORIZATION and PATH clauses. The element kinds are PostgreSQL’s: CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT; CREATE SCHEMA IF NOT EXISTS cannot include them. Elements keep their names as written, and unqualified names refer to the new schema. The parser no longer copies the schema name into CREATE TABLE elements. Table visitors, validation and StatementDeParser traverse the elements.

Validators report createEventTrigger, alterEventTrigger, reindex, reindexConcurrently (PostgreSQL 12) and securityLabel. Feature analysis classifies these statements as schema changes; REINDEX also reads data.

References: CREATE EVENT TRIGGER, ALTER EVENT TRIGGER, REINDEX, SECURITY LABEL, CREATE SCHEMA.

Migrating shared ALTER enums

AlterServer.Action and AlterForeignDataWrapper.Action have been replaced by ForeignObjectAlterAction in net.sf.jsqlparser.statement.alter. Both statements’ getAction() and setAction() now use this enum, with the same OPTIONS, OWNER and RENAME constants. Update imports and recompile code using these APIs; the removed nested enums are not compatibility aliases.

AlterServer server = (AlterServer) CCJSqlParserUtil.parse(
    "ALTER SERVER s VERSION NULL");
server.setAction(ForeignObjectAlterAction.OWNER);
server.setOwner("CURRENT_USER");
// ALTER SERVER s OWNER TO CURRENT_USER

AlterForeignDataWrapper wrapper = (AlterForeignDataWrapper) CCJSqlParserUtil.parse(
    "ALTER FOREIGN DATA WRAPPER w NO HANDLER");
wrapper.setAction(ForeignObjectAlterAction.OWNER);
wrapper.setOwner("CURRENT_USER");
// ALTER FOREIGN DATA WRAPPER w OWNER TO CURRENT_USER

OPTIONS remains the initial action. It includes server VERSION changes and wrapper handler/validator changes as well as foreign-data options. These payloads remain on their respective statement classes. Switching to OWNER or RENAME leaves the inactive payload available for switching back to OPTIONS.

RelationAlterAction.TriggerState, getTriggerState() and setTriggerState() have been removed. Use the existing RelationAlterAction.EnableState, getEnableState() and setEnableState() for both trigger and rewrite-rule actions:

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE t ENABLE REPLICA TRIGGER trg",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
RelationAlterAction trigger = (RelationAlterAction) alter.getAlterExpressions().get(0);
trigger.setEnableState(RelationAlterAction.EnableState.DISABLE);
// ALTER TABLE t DISABLE TRIGGER trg

The action’s Kind still distinguishes TRIGGER_STATE from RULE_STATE. Trigger targets also retain their NAME, ALL and USER distinctions.

Oracle anonymous blocks

With Dialect.ORACLE, OracleBlock extends Block with variable declarations and exception handlers. Initializers and OracleAssignment values are expressions; nested blocks and handler bodies contain statements. Calls without CALL use Execute.ExecType.IMPLICIT, preserving qualified names, parentheses and bind arguments. OracleNullStatement represents the PL/SQL NULL statement. Implicit calls are recognized inside Oracle blocks, so application procedure names need no keyword registration.

Shared traversal and rendering include declaration initializers, assignments and exception handler bodies. Procedure side effects remain unknown; table discovery reports unsupported procedure calls, and feature analysis remains conservative. This covers anonymous blocks with variable declarations, SQL statements, assignments, calls, nesting and handlers, not all PL/SQL declarations, loops, packages or procedure definitions.

SQL Server routine declarations

Dialect.SQLSERVER uses a shared declaration path for CREATE, ALTER and CREATE OR ALTER FUNCTION/PROCEDURE. CreateFunctionalStatement.getOperation() identifies the operation. For functions, getReturnType() exposes scalar types, inline RETURNS TABLE, and a return variable with ordered TableElement column and constraint definitions. Table elements reuse the existing definition traversal and deparser, including custom expression visitors.

With a structured return type, getFunctionDeclarationParts() contains the name and parameter tokens; getRoutineBodyParts() contains the following options and body. These remain opaque tokens, so this does not implement a T-SQL body AST or resolve tables used inside a routine. Other dialects retain the existing token-list representation. New operations have separate validation capabilities.

Parse procedure definitions one SQL Server batch at a time: a procedure consumes the remaining batch, including SQL after an END. Client-side GO batch splitting is not performed by this routine declaration parser.

PostgreSQL table-level NOT NULL constraints

Dialect.POSTGRESQL supports PostgreSQL 18’s table-level CONSTRAINT nn NOT NULL id, in both CREATE TABLE and ALTER TABLE ADD. NotNullConstraint exposes the constraint name, target Column and noInherit flag; getConstraintAttributes().isNotValid() represents an ALTER NOT VALID clause when present.

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE t ADD CONSTRAINT nn NOT NULL id NOT VALID",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
NotNullConstraint constraint = (NotNullConstraint)
    alter.getAlterExpressions().get(0).getConstraint();
constraint.getColumn().setColumnName("other_id");
constraint.setName("other_nn");

The target column participates in expression visitors and deparsers. New nodes can be built with new NotNullConstraint().withName("nn") .withColumn(new Column("id")). This is distinct from column definitions and ALTER COLUMN ... SET NOT NULL; those retain their existing APIs.

MySQL and PostgreSQL tablespaces

CREATE TABLESPACE parses to CreateTablespace and ALTER TABLESPACE to AlterTablespace in every dialect; isUndo() records the MySQL UNDO keyword. A PostgreSQL definition exposes getOwner(), getLocation() and the WITH parameters as IndexOption values in getParameters(). A MySQL definition exposes getDataFile() and keeps its options in source order in getOptions(). They reuse the TableOption kinds ENGINE, ENCRYPTION, AUTOEXTEND_SIZE and ENGINE_ATTRIBUTE; CREATE also accepts FILE_BLOCK_SIZE.

CreateTablespace create = (CreateTablespace) CCJSqlParserUtil.parse(
    "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' FILE_BLOCK_SIZE = 8192 ENGINE = InnoDB");
create.getDataFile().getValue();                              // ts1.ibd
create.getOption(TableOption.Kind.ENGINE).get().getValue();   // InnoDB

AlterTablespace alter = (AlterTablespace) CCJSqlParserUtil.parse(
    "ALTER TABLESPACE fast SET (random_page_cost = 1.1)");
alter.getAction();       // SET_PARAMETERS
alter.getParameters();   // [random_page_cost = 1.1]

getAction() is RENAME, OWNER, SET_PARAMETERS or RESET_PARAMETERS, SET_STATE for ALTER UNDO TABLESPACE … SET ACTIVE|INACTIVE, or OPTIONS for MySQL options. Undo tablespaces accept only ENGINE, CREATE UNDO TABLESPACE requires ADD DATAFILE, and ALTER rejects FILE_BLOCK_SIZE. Other ALTER TABLESPACE forms, such as Oracle and NDB data file clauses, still parse as UnsupportedStatement, and ALTER TABLE ALL IN TABLESPACE remains AlterTablespaceMove. Validators report the createTablespace and alterTablespace features, which the MySQL and PostgreSQL capabilities enable; feature analysis reports MODIFIES_SCHEMA.

MySQL spatial reference systems and ALTER INSTANCE

MySQL CREATE SPATIAL REFERENCE SYSTEM parses to CreateSpatialReferenceSystem. isOrReplace() and isIfNotExists() exclude each other, getSrid() is the number as written (a LongValue, or a HexValue for 0x... and X'...'), and getAttributes() keeps the NAME, DEFINITION, ORGANIZATION … IDENTIFIED BY and DESCRIPTION attributes in source order as SpatialReferenceSystemAttribute values. getName(), getDefinition(), getOrganization(), getOrganizationId() and getDescription() return the first attribute of their kind. The parser follows MySQL’s grammar, which takes the attributes in any order and any number: the server reports a repeated attribute, a missing NAME or DEFINITION and an SRID above 4294967295 when it runs the statement, not as syntax errors. DROP SPATIAL REFERENCE SYSTEM parses to Drop with the object type SPATIAL_REFERENCE_SYSTEM.

CreateSpatialReferenceSystem srs = (CreateSpatialReferenceSystem) CCJSqlParserUtil.parse(
    "CREATE SPATIAL REFERENCE SYSTEM 4120 NAME 'Greek' ORGANIZATION 'EPSG' "
        + "IDENTIFIED BY 4120 DEFINITION 'GEOGCS[\"Greek\"]'");
srs.getSrid();                     // 4120
srs.getName().getValue();          // Greek
srs.getOrganizationId();           // 4120

MySQL ALTER INSTANCE parses to AlterInstance. getAction() is ENABLE_INNODB_REDO_LOG, DISABLE_INNODB_REDO_LOG, ROTATE_INNODB_MASTER_KEY, ROTATE_BINLOG_MASTER_KEY, RELOAD_TLS or RELOAD_KEYRING. For RELOAD TLS, getChannel() is the identifier after FOR CHANNEL as written, or null, and isNoRollbackOnError() records NO ROLLBACK ON ERROR. MySQL reads INNODB, BINLOG and REDO_LOG as identifiers, so they may be written in backticks, and the engine of ROTATE also as a string; the statement prints them as plain words. The grammar takes any identifier as the channel; the server accepts mysql_main and mysql_admin.

These statements are read with Dialect.MYSQL and without a dialect. With Dialect.MYSQL, a CREATE SPATIAL statement that is neither this one nor CREATE SPATIAL INDEX, and an ALTER INSTANCE with another action, are errors; without a dialect they stay UnsupportedStatement, as in the other dialects, where DROP SPATIAL REFERENCE SYSTEM is still rejected. REFERENCE, INSTANCE, ROTATE, RELOAD and the other words remain ordinary identifiers. Validators report the createSpatialReferenceSystem and alterInstance features, which the MySQL capabilities enable, and drop. Feature analysis reports MODIFIES_SCHEMA for CREATE SPATIAL REFERENCE SYSTEM and MODIFIES_SESSION for ALTER INSTANCE, a server operation like ALTER SYSTEM.

SQL Server identity inserts

With Dialect.SQLSERVER, SET IDENTITY_INSERT dbo.actor ON uses SetIdentityInsertStatement. getTable() reuses the qualified Table AST; isOn() and setOn() expose the session setting. Table names may include a database and schema, including SQL Server bracket-quoted identifiers. Table visitors, both SQL renderers and metadata validation use this structured target. Feature analysis reports MODIFIES_SESSION; the directive itself inserts no rows. The dedicated validation capability is setIdentityInsert.

Database options and configuration parameters

CreateDatabase, MySQL’s CREATE SCHEMA and AlterDatabase share the DatabaseOption model. MySQL options are CHARACTER_SET (getName() keeps the CHARACTER SET, CHAR SET or CHARSET spelling), COLLATE, ENCRYPTION and, for ALTER only, READ_ONLY. PostgreSQL options have one kind each, from OWNER and TEMPLATE to CONNECTION_LIMIT, IS_TEMPLATE and OID; isUseWith() records the optional WITH. Values keep their SQL text, such as 'UTF8' or -1. Other option text remains one raw OTHER option.

AlterDatabase.getAction() selects what is rendered: OPTIONS (the default), RENAME, OWNER, SET_TABLESPACE, REFRESH_COLLATION_VERSION or CONFIGURATION_PARAMETER. Payloads of the other actions are kept. SET and RESET of a configuration parameter use ConfigurationParameterChange, which AlterRole shares:

AlterDatabase alter = (AlterDatabase) CCJSqlParserUtil.parse(
    "ALTER DATABASE app SET search_path TO app, public",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
ConfigurationParameterChange change = alter.getParameterChange();
change.getParameter();  // search_path
change.getValues();     // [app, public], a mutable ExpressionList
change.setValueKind(ConfigurationParameterChange.ValueKind.DEFAULT);
// ALTER DATABASE app SET search_path TO DEFAULT

getValueKind() is VALUES, DEFAULT or FROM_CURRENT; RESET ALL uses the parameter name ALL. AlterRole keeps getParameter(), isUseEquals(), isFromCurrent(), isUseDefault(), getValues() and their setters; they read and edit getParameterChange(). FROM CURRENT and DEFAULT are now mutually exclusive: setFromCurrent(false) and setUseDefault(false) return to the values.

Without the MySQL dialect, ALTER DATABASE forms that are neither MySQL nor PostgreSQL syntax, such as ALTER DATABASE d SET RECOVERY SIMPLE, remain an UnsupportedStatement; Dialect.MYSQL reports them as errors. ALTER SCHEMA followed by MySQL options is an AlterDatabase in every dialect, while PostgreSQL’s ALTER SCHEMA ... RENAME TO/OWNER TO remains an AlterSchema. Validation reports the createDatabase and alterDatabase features, which the MySQL, MariaDB and PostgreSQL capabilities enable.

CreateDatabase.getOptions() is now a live list that is never null, and setOptions copies its argument. getDatabaseOptions() is an unmodifiable token snapshot derived from the options, including WITH, and is null without options. setDatabaseOptions replaces all options with one raw option, and addDatabaseOptions appends one while keeping the typed options. Parsed statements no longer end with an empty raw option.

References: MySQL CREATE DATABASE, ALTER DATABASE; PostgreSQL CREATE DATABASE, ALTER DATABASE.

Routine and trigger alterations

PostgreSQL ALTER FUNCTION, ALTER PROCEDURE and ALTER ROUTINE and MySQL ALTER FUNCTION and ALTER PROCEDURE parse to AlterRoutine. getRoutineType() names the keyword and getRoutine() is a RoutineReference with the optional PostgreSQL argument types. getAction() is CHARACTERISTICS, RENAME, OWNER, SET_SCHEMA or DEPENDS_ON_EXTENSION (isNoDependency() for NO DEPENDS); payloads of the other actions are kept. getCharacteristics() is a mutable list of RoutineCharacteristic in source order, shared by both dialects. Each Kind renders its own keywords, such as IMMUTABLE, CALLED_ON_NULL_INPUT, PARALLEL_SAFE, COST or MySQL’s READS_SQL_DATA. SECURITY_DEFINER and SECURITY_INVOKER keep the [EXTERNAL] SECURITY or SQL SECURITY spelling in getSecurityClause(). COST, ROWS, SUPPORT and COMMENT keep their value as SQL text in getValue(). getComment() returns the COMMENT literal as a StringValue read with the dialect’s escaping, so getNotExcapedValue() is the text MySQL stores. SET and RESET reuse ConfigurationParameterChange:

AlterRoutine alter = (AlterRoutine) CCJSqlParserUtil.parse(
    "ALTER FUNCTION f(int) IMMUTABLE SET search_path = public",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
alter.getRoutine().getArguments();                // [int]
alter.getCharacteristics().get(0).getKind();      // IMMUTABLE
alter.getCharacteristics().get(1).getParameterChange().getParameter(); // search_path

The characteristics of one statement belong to one dialect, and MySQL’s take no argument list. Other forms after ALTER FUNCTION or ALTER PROCEDURE, such as Oracle’s COMPILE or SQL Server routine definitions without the SQL Server dialect, remain an UnsupportedStatement. PostgreSQL’s ALTER TRIGGER name ON table RENAME TO and [NO] DEPENDS ON EXTENSION parse to AlterTrigger; table visitors report its table. Validators report alterFunction, alterProcedure, alterRoutine and alterTrigger; PostgreSQL enables all of them, MySQL and MariaDB the first two.

PostgreSQL’s ALTER RULE name ON table RENAME TO new_name (ALTER RULE) parses to AlterRule with getName(), getTable() and getNewName(); table visitors report its table and validators report alterRule, which PostgreSQL enables. It is the only ALTER RULE form, so any other text after ALTER RULE is a parse error.

PostgreSQL’s cast, transform and conversion definitions are typed statements:

• CREATE CAST (source_type AS target_type) {WITH FUNCTION function [(argument_type [, ...])] | WITHOUT FUNCTION | WITH INOUT} [AS ASSIGNMENT | AS IMPLICIT] (CREATE CAST) parses to CreateCast. getCast() is the CastSignature that DROP CAST uses, getMethod() is WITH_FUNCTION, WITHOUT_FUNCTION or WITH_INOUT, getFunction() is the RoutineReference of WITH FUNCTION, and getContext() is ASSIGNMENT, IMPLICIT or null for a cast that applies only when written.

• CREATE [OR REPLACE] TRANSFORM FOR type_name LANGUAGE lang_name (FROM SQL WITH FUNCTION function, TO SQL WITH FUNCTION function) (CREATE TRANSFORM) parses to CreateTransform. getTransform() is the TransformSignature that DROP TRANSFORM uses. getFromSqlFunction() and getToSqlFunction() are RoutineReference objects, one of them null when the statement has only the other item, and isToSqlFirst() keeps the order of the two items.

• CREATE [DEFAULT] CONVERSION name FOR 'source_encoding' TO 'dest_encoding' FROM function_name (CREATE CONVERSION) parses to CreateConversion with isDefaultConversion(), getName(), getSourceEncoding() and getDestinationEncoding() as StringValue and getFunction().

• ALTER CONVERSION name {RENAME TO new_name | OWNER TO role | SET SCHEMA schema} (ALTER CONVERSION) parses to AlterConversion with getAction() and getNewName(), getOwner() or getSchemaName().

CreateCast cast = (CreateCast) CCJSqlParserUtil.parse(
        "CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT");
cast.getCast().getSourceType();   // ColDataType bigint
cast.getMethod();                 // CreateCast.Method.WITH_FUNCTION
cast.getFunction().getName();     // int4
cast.getContext();                // CreateCast.Context.ASSIGNMENT

CreateTransform transform = (CreateTransform) CCJSqlParserUtil.parse(
        "CREATE TRANSFORM FOR hstore LANGUAGE plpython3u ("
                + "FROM SQL WITH FUNCTION hstore_to_plpython(internal), "
                + "TO SQL WITH FUNCTION plpython_to_hstore(internal))");
transform.getTransform().getLanguage();     // plpython3u
transform.getToSqlFunction().getName();     // plpython_to_hstore

Validators report createCast, createTransform, createConversion and alterConversion, which PostgreSQL enables. With Dialect.POSTGRESQL these are the only statements that CREATE CAST, CREATE TRANSFORM, CREATE [DEFAULT] CONVERSION and ALTER CONVERSION start, so any other text after them is a parse error. In another dialect and without one, a statement is read this way when it has PostgreSQL’s form (CAST (...) WITH FUNCTION, WITH INOUT or WITHOUT FUNCTION, TRANSFORM FOR type LANGUAGE, CONVERSION name FOR, and ALTER CONVERSION name followed by RENAME, OWNER or SET); a statement of another product that starts with the same words, such as CREATE CAST (a AS b) WITH SPECIFIC FUNCTION f, remains an UnsupportedStatement.

CreateFunctionalStatement.getDefiner() returns the MySQL DEFINER of CREATE DEFINER = ... FUNCTION|PROCEDURE as the MySqlDefiner that views and triggers use. With Dialect.MYSQL or Dialect.MARIADB, CreateFunctionalStatement.getCharacteristics() lists the characteristics between the routine header and its body, such as COMMENT, DETERMINISTIC, READS SQL DATA or SQL SECURITY INVOKER, with the same RoutineCharacteristic kinds; DETERMINISTIC and NOT_DETERMINISTIC occur only here. getFunctionDeclarationParts() still holds every token and renders the statement unchanged, so the list is read-only. Replacing the tokens with setFunctionDeclarationParts(parts) clears it, while setFunctionDeclarationParts(parts, characteristics) sets both, and appending tokens keeps it. Without a MySQL dialect the list stays empty. MySQL CREATE TRIGGER accepts IF NOT EXISTS (isIfNotExists()) and keeps MariaDB’s OR REPLACE. A BEGIN ... END trigger body with only SQL statements remains a Block; a compound body with procedural statements such as IF, DECLARE or loops is an UnsupportedStatement with its tokens, captured with the boundary of routine bodies. In both cases the semicolon after END separates the next statement.

References: PostgreSQL ALTER FUNCTION, ALTER PROCEDURE, ALTER ROUTINE, ALTER TRIGGER; MySQL ALTER FUNCTION, ALTER PROCEDURE, CREATE TRIGGER.

MySQL table maintenance statements

CHECK TABLE, CHECKSUM TABLE, OPTIMIZE TABLE and REPAIR TABLE parse to TableMaintenanceStatement in every dialect; ANALYZE TABLE remains Analyze. getOperation() identifies the statement, getModifier() returns NO_WRITE_TO_BINLOG or LOCAL for OPTIMIZE and REPAIR, and isTablesKeyword() records the TABLES spelling. getOptions() keeps the trailing options in source order. CHECK accepts FOR UPGRADE, QUICK, FAST, MEDIUM, EXTENDED and CHANGED; CHECKSUM accepts one of QUICK and EXTENDED; REPAIR accepts QUICK, EXTENDED and USE_FRM. Other combinations are rejected, and the option words and CHECKSUM remain ordinary identifiers elsewhere. With unsupported statements enabled, these statements are parsed rather than captured as unsupported statements.

TableMaintenanceStatement repair = (TableMaintenanceStatement) CCJSqlParserUtil.parse(
    "REPAIR NO_WRITE_TO_BINLOG TABLE t1, t2 QUICK");
repair.getTables();   // [t1, t2]
repair.getOptions();  // [QUICK]

Table visitors and metadata validation use the table list. Validators report the tableMaintenance feature, which the MySQL and MariaDB capabilities enable. Feature analysis reports READS_DATA and RETURNS_RESULT_SET; OPTIMIZE and REPAIR also report MODIFIES_SCHEMA, and REPAIR may report MODIFIES_DATA.

ANALYZE TABLE ... UPDATE HISTOGRAM ON keeps WITH n BUCKETS (getHistogramBucketCount()), MySQL 8.4’s AUTO UPDATE or MANUAL UPDATE (getHistogramUpdateMode()) and USING DATA 'json' (getHistogramData(), a StringValue). USING DATA excludes the other two options, and DROP HISTOGRAM takes none of them.

PostgreSQL maintenance statements

With Dialect.POSTGRESQL and without a dialect, PostgreSQL’s maintenance statements parse to typed statements:

• VACUUM [(option [value], ...)] [target, ...] and VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [target, ...] parse to VacuumStatement.

• ANALYZE [(option [value], ...)] [target, ...] and ANALYZE [VERBOSE] [target, ...] extend Analyze, which keeps the MySQL ANALYZE TABLE form and its histogram clause. A statement takes one form: options, or targets with ONLY, * or columns, cannot be combined with NO_WRITE_TO_BINLOG, LOCAL, TABLE or UPDATE/DROP HISTOGRAM. The PostgreSQL form can be spelled ANALYSE, which getCommand() keeps.

• CLUSTER [(option [value], ...)] [table [USING index]], CLUSTER [VERBOSE] [table [USING index]] and the pre-8.3 CLUSTER [VERBOSE] index ON table parse to ClusterStatement; getIndexSyntax() tells USING and ON apart.

• CHECKPOINT parses to CheckpointStatement.

The three option lists share UtilityOption: getName() keeps the name as written, and getValueType() tells whether the value is a Boolean word, a number, a string or another word. getBooleanValue(), getLongValue(), getStringValue() and getEnumValue(Class) read it, and getKind(Class) maps the name to VacuumStatement.OptionKind, Analyze.OptionKind or ClusterStatement.OptionKind. The unparenthesized keywords are options too: VACUUM FULL t holds the option FULL like VACUUM (FULL) t, and isParenthesizedOptions() tells the forms apart. As in PostgreSQL’s grammar, an option list takes any name and any Boolean word, number, string or word as its value, as long as an unquoted name or word is not one that PostgreSQL reserves (ANALYZE and ANALYSE are names); PostgreSQL checks the names and values when it runs the statement. getKind(Class) is null for a name the statement does not know, such as VACUUM (FOO) t, and the typed readers are null for a value of another type, such as PARALLEL 1.5. A double-quoted name keeps its case, as in PostgreSQL: "verbose" is VERBOSE, and "VERBOSE" is an unknown option. EXPLAIN (...) reads its options through the same production but keeps ExplainStatement.Option, and accepts only the names and values it accepted before.

A MaintenanceTarget of VACUUM and ANALYZE is [ONLY] table [*] [(column, ...)]: its getReference() is the TableReference of LOCK and TRUNCATE, and getColumns() lists the columns. Analyze.getTargets() holds the tables of both forms; getTable() and getTables() are derived from it, and getTables() now returns a read-only snapshot.

VacuumStatement vacuum = (VacuumStatement) CCJSqlParserUtil.parse(
    "VACUUM (PARALLEL 2, INDEX_CLEANUP OFF) t (a)",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
vacuum.getOptions().get(0).getLongValue();       // 2
vacuum.getOptions().get(1).getBooleanValue();    // false
vacuum.getTargets().get(0).getColumns();         // [a]

VACUUM, CLUSTER, FREEZE, CHECKPOINT and ANALYSE remain identifiers elsewhere. As in PostgreSQL, FULL, FREEZE, VERBOSE and ANALYZE directly after VACUUM are options, never table names. With Dialect.POSTGRESQL, ANALYZE verbose now means the VERBOSE option; without a dialect it is still a table named verbose unless a table name follows, and other dialects keep that reading. Without a dialect, a statement starting with one of these words is a PostgreSQL statement only when its next tokens take the PostgreSQL form, so other products’ variants such as VACUUM INTO 'file' remain unsupported statements. Other dialects do not parse these statements, nor ANALYSE.

Table visitors report the tables of the targets and the CLUSTER table, but not the column lists or the index. Validators report the analyze, vacuum, cluster and checkpoint features, which the PostgreSQL capabilities enable; the MySQL and MariaDB capabilities now enable analyze as well. Feature analysis reports MODIFIES_SCHEMA and READS_DATA for VACUUM and CLUSTER, as for ANALYZE, and MODIFIES_SESSION for CHECKPOINT, a server operation like ALTER SYSTEM CHECKPOINT. MySQL table and backup locks —————————-

MySQL and MariaDB LOCK TABLES parses to the LockStatement of PostgreSQL and Oracle LOCK TABLE. Each entry of getTargets() keeps its table, the alias as getTable().getAlias(), and getLockType(): READ, READ_LOCAL, WRITE, LOW_PRIORITY_WRITE or MariaDB’s WRITE_CONCURRENT. MySQL 8.4 has only READ [LOCAL] and WRITE; LOW_PRIORITY WRITE is accepted because MySQL 8.0 (deprecated, without effect) and MariaDB accept it. The lock type is null for a PostgreSQL or Oracle target, and isTablesKeyword() records the TABLES spelling. MariaDB’s trailing WAIT n and NOWAIT use getWaitSeconds() and isNoWait(), like PostgreSQL and Oracle. UNLOCK TABLE and UNLOCK TABLES parse to UnlockStatement, and LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE to BackupLockStatement, whose getAction() is LOCK or UNLOCK.

LockStatement lock = (LockStatement) CCJSqlParserUtil.parse(
    "LOCK TABLES t AS a READ LOCAL, u WRITE");
lock.getTargets().get(0).getTable().getAlias().getName();   // a
lock.getTargets().get(0).getLockType();                    // READ_LOCAL

The MySQL and MariaDB dialects read these forms, and so does the parser without a dialect: an alias or a lock type after the first table name tells MySQL’s form from PostgreSQL’s LOCK [TABLE] name, so LOCK tables NOWAIT still locks a table named tables. WRITE CONCURRENT, WAIT and NOWAIT after a MySQL target list require Dialect.MARIADB, and Dialect.MARIADB rejects the backup lock statements, which MariaDB does not have. PostgreSQL and the other dialects keep rejecting the MySQL forms. UNLOCK, INSTANCE and BACKUP remain ordinary identifiers.

Table visitors report the locked tables but not their aliases. Validators report the lock, unlockTables and backupLock features; the MySQL capabilities enable all three, MariaDB the first two, and PostgreSQL and Oracle lock. Feature analysis reports MODIFIES_TRANSACTION.

MySQL servers, plugins and components

MySQL CREATE SERVER and ALTER SERVER parse to the CreateServer and AlterServer of the PostgreSQL statements. A ForeignDataOption keeps its value in getValueExpression(): a StringValue, or for MySQL’s PORT the number as written (LongValue, DoubleValue or HexValue). getValue() still returns the string value and is null for a number. A server or wrapper name written as a string literal, as in FOREIGN DATA WRAPPER 'mysql', is kept with its quotes.

CreateServer server = (CreateServer) CCJSqlParserUtil.parse(
    "CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (HOST 'h', PORT 3306)");
ForeignDataOption port = server.getOptions().get(1);
port.getValueExpression();   // LongValue 3306
port.getValue();             // null

Numeric values and quoted names are read with the MySQL and MariaDB dialects and without a dialect, and only in these two statements; PostgreSQL and the other option lists keep requiring string constants. Dialect.MYSQL follows the server grammar: the options are USER, HOST, DATABASE, OWNER, PASSWORD and SOCKET with a string and PORT with a number, OPTIONS is required, and the PostgreSQL clauses (IF NOT EXISTS, TYPE, VERSION, ADD/SET/DROP options, RENAME TO, OWNER TO) are rejected. DROP SERVER stays a Drop.

INSTALL PLUGIN, UNINSTALL PLUGIN, INSTALL COMPONENT and UNINSTALL COMPONENT parse to one PluginStatement with an Action (INSTALL, UNINSTALL) and an ObjectType (PLUGIN, COMPONENT). A plugin statement has getPluginName() and, for INSTALL, the getLibrary() after SONAME. A component statement lists its URNs in getComponents(), and INSTALL COMPONENT ... SET its assignments in getVariables(): each ComponentVariable has a SetScope (NONE, GLOBAL or PERSIST), the name as written, the operator (= or :=) and the value expression; the word ON is read as a column. As in MySQL, a value is not DEFAULT or a subquery.

PluginStatement install = (PluginStatement) CCJSqlParserUtil.parse(
    "INSTALL COMPONENT 'file://component1' SET GLOBAL component1.var1 = 12 + 3",
    parser -> parser.withDialect(Dialect.MYSQL));
ComponentVariable variable = install.getVariables().get(0);
// GLOBAL, component1.var1, 12 + 3
String parts = variable.getScope() + ", " + variable.getName() + ", " + variable.getValue();

The statements are read with the MySQL and MariaDB dialects and without a dialect; MariaDB has no components. With the MySQL and MariaDB dialects every INSTALL is this statement. Without a dialect DuckDB’s INSTALL extension keeps producing an ExtensionStatement, also for extensions named plugin or component: SONAME after the plugin name or a component URN selects the MySQL statement. UNINSTALL, PLUGIN, COMPONENT and SONAME remain ordinary identifiers. The validator checks installPlugin (MySQL and MariaDB) and installComponent (MySQL), and createServer and alterServer are now part of the MySQL and MariaDB capabilities. Feature analysis reports MODIFIES_SESSION.

PostgreSQL session statements and COPY options

With Dialect.POSTGRESQL and without a dialect, PostgreSQL’s session statements parse to typed statements:

• DISCARD {ALL | PLANS | SEQUENCES | TEMP | TEMPORARY} parses to DiscardStatement. getTarget() keeps the target as written; TEMP and TEMPORARY name the same state.

• LISTEN channel and UNLISTEN {channel | *} parse to ListenStatement, next to NotifyStatement. getAction() is LISTEN or UNLISTEN, and getChannel() is null for UNLISTEN * (isAllChannels()).

• SHOW TIME ZONE, SHOW TRANSACTION ISOLATION LEVEL and SHOW SESSION AUTHORIZATION parse to the ShowStatement of other SHOW name statements instead of UnsupportedStatement, and RESET takes the same names. SET, RESET and SHOW read them through one rule: getName() is Time Zone, as before for SET and RESET, TRANSACTION ISOLATION LEVEL or SESSION AUTHORIZATION.

COPY also accepts the option syntax that PostgreSQL used before 9.0 and still accepts: keywords without parentheses or commas after the file name and the optional WITH, in any order (BINARY, FREEZE, DELIMITER [AS] 'x', NULL [AS] 'x', CSV, HEADER, QUOTE [AS] 'x', ESCAPE [AS] 'x', FORCE QUOTE {column, ... | *}, FORCE NOT NULL {column, ... | *}, FORCE NULL {column, ... | *} and ENCODING 'x'), and COPY BINARY table. CopyStatement.getLegacyOptions() holds a CopyLegacyOption per option, in source order, with its Kind, its string (getValue()) and whether AS preceded it, or the columns of a FORCE option (isAllColumns() for *). isBinary() records COPY BINARY. The options in parentheses keep their reading through getOptions(); a statement takes one of the two forms.

CopyStatement copy = (CopyStatement) CCJSqlParserUtil.parse(
    "COPY t TO STDOUT WITH CSV HEADER FORCE QUOTE a, b");
copy.getLegacyOptions().get(0).getKind();         // CSV
copy.getLegacyOptions().get(2).getColumns();      // [a, b]

Other dialects keep rejecting these forms, and SHOW TIME ZONE remains an UnsupportedStatement there. LISTEN and UNLISTEN become non-reserved keywords and, like DISCARD, remain usable as names. As in PostgreSQL’s grammar, a channel or a FORCE column cannot be an unquoted word that PostgreSQL reserves, such as ALL.

Table visitors report the copied table and no table for the session statements. Validators report the discard and listen features, which the PostgreSQL capabilities enable together with show. Feature analysis reports MODIFIES_SESSION for DISCARD, LISTEN and UNLISTEN, and a possible MODIFIES_SCHEMA for DISCARD ALL, TEMP and TEMPORARY, which drop the temporary tables of the session.

MySQL server administration statements

With Dialect.MYSQL and without a dialect, these MySQL 8.4 statements parse to typed statements:

• FLUSH parses to FlushStatement. getModifier() returns NO_WRITE_TO_BINLOG or LOCAL, the TableMaintenanceStatement.Modifier of OPTIMIZE, REPAIR and the partition operations. getOptions() keeps the flush options in source order. Each is a FlushOption whose getKind() is one of MySQL 8.4’s options, from BINARY_LOGS to USER_RESOURCES, and whose getChannel() holds the string literal of RELAY LOGS FOR CHANNEL 'name'. Without options the statement is FLUSH {TABLE | TABLES} [tbl_name, ...] [WITH READ LOCK | FOR EXPORT] (isFlushTables()), with getTables(), isTablesKeyword() and getTableLock(). As in MySQL, that form takes no flush option, FOR EXPORT requires a table list, only RELAY LOGS names a channel, and FLUSH HOSTS, which MySQL 8.4 removed, is rejected; with unsupported statements enabled it remains an UnsupportedStatement.

• KILL [CONNECTION | QUERY] processlist_id parses to KillStatement. getKind() is CONNECTION, QUERY or null when neither is given, and getProcesslistId() is an expression, since MySQL accepts @id or 1 + 2 there.

• RESET PERSIST [[IF EXISTS] system_var_name] extends ResetStatement with isPersist() and isIfExists(); getName() is the variable as written, with a component or DEFAULT prefix such as validate_password.length, or null when the statement names none. Without a dialect only a variable name or IF EXISTS after PERSIST selects this form, so a bare RESET PERSIST still resets a parameter named persist, as it does in the other dialects.

• HANDLER parses to HandlerStatement, whose getAction() is OPEN, READ or CLOSE. getTable() is the table that OPEN opens, with the handler name as its alias, or the unqualified handler name that READ and CLOSE take; getHandlerName() returns the name in both cases. A READ has getIndexName() and either getKeyComparison() (=, <=, >=, < or >) with getKeyValues(), or getDirection() (FIRST, NEXT, PREV or LAST, and only FIRST or NEXT without an index), followed by getWhere(), getLimit() and getOffset(). As in MySQL, a READ takes no subquery.

FlushStatement flush = (FlushStatement) CCJSqlParserUtil.parse(
    "FLUSH LOCAL TABLES t1, t2 WITH READ LOCK");
flush.getTables();         // [t1, t2]
flush.getTableLock();      // WITH_READ_LOCK

HandlerStatement read = (HandlerStatement) CCJSqlParserUtil.parse(
    "HANDLER h READ i1 >= (1, 2) WHERE a > 0 LIMIT 3");
read.getKeyComparison();   // GREATER_THAN_OR_EQUALS
read.getKeyValues();       // (1, 2)

Other dialects keep rejecting these statements. HANDLER becomes a non-reserved keyword and remains usable as a name, like FLUSH, KILL and the option words.

Table visitors report the tables of FLUSH TABLES and the table that HANDLER ... OPEN opens, but not the handler name of READ and CLOSE, which may be an alias. They find the tables of subqueries in the KILL expression, and they now return no table for every RESET statement instead of throwing. Validators report the flush, kill, handler and resetPersist features, which the MySQL capabilities enable; PostgreSQL’s RESET keeps reset. Feature analysis reports MODIFIES_SESSION for FLUSH, KILL and HANDLER OPEN and CLOSE, adds MODIFIES_TRANSACTION for the table locks of FLUSH TABLES, and reports READS_DATA and RETURNS_RESULT_SET for HANDLER READ.

PostgreSQL IMPORT FOREIGN SCHEMA

With Dialect.POSTGRESQL and without a dialect, IMPORT FOREIGN SCHEMA remote_schema [{LIMIT TO | EXCEPT} (table [, ...])] FROM SERVER server INTO local_schema [OPTIONS (option 'value' [, ...])] parses to ImportForeignSchema, a ForeignDataStatement like CreateServer and CreateUserMapping. getRemoteSchema(), getServer() and getLocalSchema() hold the names. getQualification() is LIMIT_TO, EXCEPT or null when the whole schema is imported, and getTables() lists the tables as TableReference values, the relation targets of LOCK and TRUNCATE, because PostgreSQL’s grammar reads the same relation syntax there. setTables(qualification, tables) changes both together. getOptions() holds the ForeignDataOption values that the other foreign-data statements use; ADD, SET and DROP are rejected as in their CREATE forms.

ImportForeignSchema imported = (ImportForeignSchema) CCJSqlParserUtil.parse(
    "IMPORT FOREIGN SCHEMA remote LIMIT TO (t1, t2) FROM SERVER srv INTO local "
        + "OPTIONS (import_default 'true')");
imported.getQualification();                  // LIMIT_TO
imported.getTables();                         // [t1, t2]
imported.getOptions().get(0).getName();       // import_default

Other dialects keep rejecting the statement, and the EXASOL dialect keeps IMPORT for its own statement. Table visitors report no table: the listed names belong to the remote schema. Validators report the importForeignSchema feature, which the PostgreSQL capabilities and FeaturesAllowed.DDL enable. Feature analysis reports MODIFIES_SCHEMA. MySQL replication statements —————————-

The MySQL 8.4 replication statements parse with Dialect.MYSQL and without a dialect, into the classes of net.sf.jsqlparser.statement.replication:

• CHANGE REPLICATION SOURCE TO is a ChangeReplicationSourceStatement. Each ReplicationSourceOption keeps its spelling and getKind(), and Kind.getValueType() says what the option takes: a string, a number, NULL, 0 or 1, a keyword such as OFF (held as a Column, like ON in PostgreSQL publication options), or the ParenthesedExpressionList of IGNORE_SERVER_IDS. A value of another type is a parse error, as in MySQL. PRIVILEGE_CHECKS_USER keeps its account in getAccount(), and getPrimaryKeyCheck() and getGtidAssignment() read the keyword options.

• CHANGE REPLICATION FILTER is a ChangeReplicationFilterStatement of ReplicationFilter rules. A rule’s list, which may be empty, holds database names, db.table names, quoted patterns or (from, to) pairs, as getDatabases(), getTables(), getPatterns() and getRewrites().

• START REPLICA and STOP REPLICA are StartReplicaStatement and StopReplicaStatement, with getThreads() (IO_THREAD, SQL_THREAD). START keeps its UNTIL conditions as ReplicaUntilOption and the USER, PASSWORD, DEFAULT_AUTH and PLUGIN_DIR strings.

• RESET BINARY LOGS AND GTIDS [TO n] and RESET REPLICA [ALL], also as the comma list of the RESET statement, are a ResetReplicationStatement of ResetReplicationStatement.Option entries.

• PURGE BINARY LOGS extends Oracle’s PurgeStatement: getPurgeObjectType() is BINARY_LOGS, getLogBound() is TO or BEFORE, and getLogExpression() is the log name literal or the point in time. As in MySQL, the BEFORE expression takes no subquery at any depth. Dialect.MARIADB reads it as well, and MariaDB’s PURGE MASTER LOGS as MASTER_LOGS.

FOR CHANNEL 'name' takes a string, which getChannel() returns as a StringValue.

ChangeReplicationSourceStatement change = (ChangeReplicationSourceStatement)
    CCJSqlParserUtil.parse("CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'h', "
        + "IGNORE_SERVER_IDS = (1, 2) FOR CHANNEL 'c1'");
change.getOptions().get(0).getKind();          // SOURCE_HOST
change.getChannel().getValue();                // c1

MySQL 8.4 removed CHANGE MASTER TO, START SLAVE, RESET MASTER and the MASTER_ options, so they stay unsupported. Without a dialect a bare RESET REPLICA remains the PostgreSQL ResetStatement of a parameter named replica; Dialect.MYSQL, or ALL, FOR CHANNEL or a further option after it, makes it MySQL’s. PostgreSQL, Oracle and MariaDB keep their readings of RESET, PURGE and START. STOP becomes a non-reserved keyword and remains usable as a name.

Table visitors report the tables of REPLICATE_DO_TABLE and REPLICATE_IGNORE_TABLE. Validators report the changeReplicationSource, changeReplicationFilter, startReplica, stopReplica, resetReplication and purgeBinaryLogs features; the MySQL capabilities enable all of them and MariaDB purgeBinaryLogs. Feature analysis reports MODIFIES_SESSION, as for ALTER SYSTEM.

MySQL LOAD DATA and LOAD XML

MySQL and MariaDB LOAD DATA and LOAD XML parse to MySqlLoadDataStatement; BigQuery’s LOAD DATA {INTO | OVERWRITE} remains LoadDataStatement. getFormat() is DATA or XML. As in the server’s grammar, both forms take every clause: getLockOption() (LOW_PRIORITY or CONCURRENT), isLocal(), getFileName(), getDuplicateHandling() (REPLACE or IGNORE, the enum of CREATE TABLE … SELECT), getTable(), getPartitions(), getCharacterSet() with isUseCharsetKeyword() for the CHARSET spelling, getRowsIdentifiedBy(), getFileFormat(), getIgnoreCount() with getIgnoreUnit() (LINES or ROWS), getColumns() (columns and UserVariable targets, empty for ()) and getUpdateSets() for the SET clause.

MySqlLoadDataStatement load = (MySqlLoadDataStatement) CCJSqlParserUtil.parse(
    "LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE t FIELDS TERMINATED BY ',' "
        + "IGNORE 1 LINES (a, @b) SET c = @b + 1");
load.getFileFormat().getFieldsTerminatedBy().getValue();   // ,
load.getUpdateSets().get(0).getValue(0);                   // @b + 1

MySqlFileFormat holds the {FIELDS | COLUMNS} and LINES clauses, which SELECT … INTO OUTFILE shares: MySqlSelectIntoClause.getFileFormat() returns it, and the clause’s earlier getters and setters, such as getFieldsTerminatedBy(), read and write it. Each clause needs at least one subclause, so a bare FIELDS or LINES, which MySQL rejects, is now a parse error. The subclauses may come in any order and repeat, as MySQL allows; like the server, the format keeps the last value of each and prints them in the documented order. INTO OUTFILE also takes CHARSET and a quoted character set name.

The MySQL and MariaDB dialects read these forms, and so does the parser without a dialect: INFILE after LOAD DATA or LOAD XML and the optional LOW_PRIORITY, CONCURRENT and LOCAL tells them from BigQuery’s LOAD DATA INTO and from DuckDB’s and PostgreSQL’s LOAD name, so LOAD xml still loads an extension named xml. The other dialects keep rejecting the MySQL forms. INFILE and CONCURRENT remain ordinary identifiers.

Table visitors report the target table and the tables of subqueries in the SET clause. Validators report the loadData feature, which the MySQL and MariaDB capabilities now enable. Feature analysis reports MODIFIES_DATA.

PostgreSQL cursors

PostgreSQL’s cursor statements parse to the net.sf.jsqlparser.statement.cursor package:

• DECLARE name [BINARY] [ASENSITIVE | INSENSITIVE] [[NO] SCROLL] CURSOR [{WITH | WITHOUT} HOLD] FOR query parses to DeclareCursorStatement. getOptions() lists the CursorOption values in their order; as in PostgreSQL’s grammar they may come in any order and repeat, and PostgreSQL rejects contradicting ones such as SCROLL NO SCROLL when it runs the statement. getHoldability() is WITH_HOLD, WITHOUT_HOLD or null, and getQuery() is the Select: a SELECT, VALUES, TABLE t or a query with WITH.

• FETCH and MOVE [direction] [FROM | IN] cursor parse to FetchCursorStatement, whose getKind() is FETCH or MOVE. The direction is an optional FetchDirection keyword (NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE, FORWARD, BACKWARD) with getCount() or isAll(): ABSOLUTE and RELATIVE take a count, FORWARD and BACKWARD an optional count or ALL, and a count or ALL can stand alone. A count is a signed 32-bit integer, as in PostgreSQL. getFromKeyword() is FROM, IN or null.

• CLOSE {name | ALL} parses to CloseCursorStatement; isAll() is true when it has no cursor name.

FetchCursorStatement fetch = (FetchCursorStatement) CCJSqlParserUtil.parse(
    "FETCH ABSOLUTE -1 FROM c", parser -> parser.withDialect(Dialect.POSTGRESQL));
fetch.getDirection();     // ABSOLUTE
fetch.getCount();         // -1
fetch.getCursorName();    // c

A cursor name is any name PostgreSQL takes as a column identifier, so its reserved words and type or function name keywords such as table or binary are rejected. As in PostgreSQL, a direction word that ends the statement names the cursor: FETCH next fetches from a cursor named next.

Dialect.POSTGRESQL reads FETCH, MOVE, CLOSE and DECLARE followed by a name as cursor statements. Without a dialect they parse when they take the PostgreSQL form: DECLARE needs a name, the cursor options and CURSOR, and FETCH, MOVE and CLOSE must end at a semicolon or the end of the input, so other products’ variants such as FETCH NEXT FROM c INTO @x or CLOSE GLOBAL c remain unsupported statements. Other dialects keep their readings, and DECLARE @variable still parses to the SQL Server DeclareStatement in every dialect that read it before. MOVE becomes a non-reserved keyword; it remains usable as a name, as do CLOSE, CURSOR and the option and direction words. SELECT ... FETCH FIRST is unchanged.

Table visitors report the tables of the query of DECLARE and none for the other statements, whose cursor name is not a table. Validators report the declareCursor, fetchCursor (FETCH and MOVE) and closeCursor features, which the PostgreSQL capabilities enable, and validate the query of DECLARE like a SELECT. Feature analysis reports MODIFIES_SESSION for all of them, READS_DATA for a DECLARE whose query reads a table, RETURNS_RESULT_SET for FETCH, and a possible READS_DATA for FETCH and MOVE, which run the query of the cursor.

MySQL account management statements

CREATE USER and ALTER USER share UserOptions (getOptions()) in MySQL’s clause order: the DEFAULT ROLE roles, the REQUIRE options (TlsOption), the WITH resource limits (ResourceLimit), the password and locking options (PasswordLockOption) in source order, and COMMENT or ATTRIBUTE. Omitted clauses stay omitted. UserAuthentication adds ALTER USER’s REPLACE 'current' (getCurrentCredential()), RETAIN CURRENT PASSWORD and the DISCARD_OLD_PASSWORD mode.

AlterUser alter = (AlterUser) CCJSqlParserUtil.parse(
    "ALTER USER 'app'@'%' IDENTIFIED BY 'new' RETAIN CURRENT PASSWORD "
        + "PASSWORD EXPIRE INTERVAL 90 DAY ACCOUNT LOCK");
alter.getAccounts().get(0).getAuthentication().isRetainCurrentPassword(); // true
alter.getOptions().getPasswordLockOptions(); // [PASSWORD EXPIRE INTERVAL 90 DAY, ACCOUNT LOCK]

MySQL multi-factor authentication is part of the same model. CREATE USER keeps the second and third factor of IDENTIFIED ... AND IDENTIFIED ... [AND IDENTIFIED ...] in UserAccount.getAdditionalAuthentications(), and the temporary first factor of a passwordless plugin, IDENTIFIED WITH plugin INITIAL AUTHENTICATION IDENTIFIED ..., in UserAuthentication.getInitialAuthentication(). ALTER USER keeps {ADD | MODIFY} n FACTOR IDENTIFIED ..., DROP n FACTOR and the registration steps n FACTOR INITIATE REGISTRATION, n FACTOR FINISH REGISTRATION SET CHALLENGE_RESPONSE AS '...' and n FACTOR UNREGISTER as UserFactorOperation objects (operation, factor number, authentication or challenge response) in UserAccount.getFactorOperations(). As on the server, an account takes one or two changes of the same kind, and the factor number is checked only when the statement runs.

AlterUser factors = (AlterUser) CCJSqlParserUtil.parse(
    "ALTER USER app ADD 2 FACTOR IDENTIFIED WITH authentication_ldap_sasl AS 'uid=app' "
        + "ADD 3 FACTOR IDENTIFIED WITH authentication_webauthn");
UserFactorOperation second = factors.getAccounts().get(0).getFactorOperations().get(0);
second.getOperation();                  // ADD
second.getFactor();                     // 2
second.getAuthentication().getPlugin(); // authentication_ldap_sasl

ALTER USER USER() ... keeps USER() as the unquoted account name, as DEFINER = CURRENT_USER does; isUserFunction() identifies it. ALTER USER acct DEFAULT ROLE {NONE | ALL | role, ...} stores a RoleSelection in getOptions().getDefaultRoles(). PostgreSQL’s ALTER USER, an alias of ALTER ROLE (WITH LOGIN, PASSWORD 'x', SET, RENAME TO …), still parses to AlterRole. MySQL syntax is recognized by IF EXISTS, USER(), quoted names, hosts, account lists, a missing action or a MySQL account clause.

RENAME USER parses to RenameUser with ordered from/to pairs. MySQL CREATE ROLE [IF NOT EXISTS] role, ... with quoted names, hosts or several roles keeps them in CreateRole.getRoles(), and getName() is null. A single unquoted name, which both databases accept, keeps the PostgreSQL getName(); so does an unquoted r@h, which the lexer reads as one identifier, unless the MySQL or MariaDB dialect is selected. Role names use the shared account spelling and are UserAccount values without authentication.

SET DEFAULT ROLE (MySQL’s TO, MariaDB’s FOR) and SET PASSWORD FOR parse to SetDefaultRoleStatement and SetPasswordStatement in every dialect. SET ROLE and SET PASSWORD without FOR share the variable assignment shape that PostgreSQL uses for SET ROLE name and SET ROLE NONE, so they remain a SetStatement unless Dialect.MYSQL or Dialect.MARIADB is selected. With these dialects every form parses to SetRoleStatement (DEFAULT, NONE, ALL [EXCEPT ...] or a role list) or SetPasswordStatement (= password or TO RANDOM, then REPLACE and RETAIN CURRENT PASSWORD). The MySQL-only SET ROLE ALL EXCEPT ... and role names with a quoted user or host, such as 'r'@'%', parse to SetRoleStatement in every dialect.

SetRoleStatement setRole = (SetRoleStatement) CCJSqlParserUtil.parse(
    "SET ROLE ALL EXCEPT 'admin'@'%'",
    parser -> parser.withDialect(Dialect.MYSQL));
setRole.getRoles().getKind();  // ALL_EXCEPT
setRole.getRoles().getRoles(); // ['admin'@'%']

Validators report alterUser, renameUser, setRole, setDefaultRole and setPassword; the MySQL and MariaDB capabilities enable them and createRole. Feature analysis reports MODIFIES_SCHEMA, or MODIFIES_SESSION for SET ROLE. Multi-factor authentication clauses are not modeled. Serialized account SQL can contain passwords; avoid logging real credentials.

Legacy MySQL GROUP BY ordering

MySQL before 8.0.13 accepted ASC and DESC on individual GROUP BY items. Select the existing MYSQL dialect and explicitly enable this legacy syntax:

Statement statement = CCJSqlParserUtil.parse(
    "SELECT a FROM t GROUP BY a DESC",
    parser -> parser.withDialect(Dialect.MYSQL).withLegacyMySqlGroupBy(true));

The option is disabled by default and does not enable this syntax in other dialects. GroupByElement keeps its existing expression list; getGroupBySortDirection(index) returns each explicit direction, or null when omitted. Directions follow list positions; replacing the expression list clears them. Validators report the separate selectGroupByOrdering feature, which is not enabled in the MySQL 8.0 capability.

PostgreSQL COMMENT targets

COMMENT ON supports these PostgreSQL object kinds in addition to the existing TABLE, COLUMN and VIEW forms:

• INDEX, SCHEMA, SEQUENCE, DOMAIN, TYPE, MATERIALIZED VIEW, FOREIGN TABLE, COLLATION, STATISTICS, CONVERSION and TEXT SEARCH {CONFIGURATION | DICTIONARY | PARSER | TEMPLATE}

• ACCESS METHOD, DATABASE, EVENT TRIGGER, EXTENSION, FOREIGN DATA WRAPPER, [PROCEDURAL] LANGUAGE, PUBLICATION, ROLE, SERVER, SUBSCRIPTION and TABLESPACE

• FUNCTION, PROCEDURE, ROUTINE and AGGREGATE signatures

• CONSTRAINT, TRIGGER, POLICY and RULE with their owning table

• CAST (source_type AS target_type)

• OPERATOR [schema.]op (left_type, right_type), where NONE marks the missing operand of a unary operator

• OPERATOR CLASS and OPERATOR FAMILY with USING access_method

• LARGE OBJECT oid and TRANSFORM FOR type_name LANGUAGE lang_name

These unambiguous target forms also parse without a dialect preset. Kind words such as ROLE, SERVER, RULE, OPERATOR or CONVERSION are not reserved and remain ordinary identifiers. Tagged dollar strings still require Dialect.POSTGRESQL or withDollarQuotedStringTags(true).

The additional targets are exposed through Comment.getTarget() as a CommentTarget. Its Kind identifies the object, and getName() preserves the individual identifier components. Table is used as the name container; an index or type name does not thereby represent a table dependency. isProcedural() records PROCEDURAL LANGUAGE. The original Comment.getTable(), getColumn() and getView() accessors continue to describe their respective existing forms.

COMMENT ON FUNCTION app.f(IN value integer) IS 'description' uses the shared RoutineReference in getTarget().getRoutine(); Kind.isRoutine() identifies these kinds and getName() is null. Argument mode, optional name and ColDataType are preserved. An omitted signature has null arguments; f() has an empty argument list. The signature identifies a routine and is not a function call. An AGGREGATE requires a signature and also accepts (*) and ordered-set signatures such as (float8 ORDER BY integer) or (ORDER BY integer).

For COMMENT ON CONSTRAINT ck ON app.t IS NULL, getName() is the constraint name and getRelation() is its owning table. TRIGGER, POLICY and RULE targets use the same fields; Kind.isOwnedByRelation() identifies these kinds. Only a constraint accepts ON DOMAIN app.d, which sets isOnDomain() and stores the domain as the owner instead. A CAST target exposes getSourceType() and getTargetType(). NULL removes the comment and remains represented by a null Comment.getComment().

COMMENT ON OPERATOR app.-> (jsonb, NONE) reads the operator with the reference shared by exclusion constraints and ALTER EXTENSION: getOperator() returns an ExclusionOperator with getSchemaName() and the operator symbol in getName(). getLeftOperandType() and getRightOperandType() hold the operand types, and null stands for NONE; at least one operand type is required. The lexer reads some PostgreSQL operators, such as <<= or -|-, as several tokens. Parts that touch each other are joined into one operator, while whitespace or a comment separates two operators as in PostgreSQL. Exclusion constraints and ALTER EXTENSION accept the same operators. Operators that the lexer reads as part of an identifier, such as ## without Dialect.POSTGRESQL, are not supported.

OPERATOR CLASS and OPERATOR FAMILY targets keep their name in getName() and the index access method in getAccessMethod(); Kind.isOwnedByAccessMethod() identifies these kinds. LARGE OBJECT exposes getLargeObjectOid(), which accepts an unsigned 32-bit integer. TRANSFORM exposes getTransformType() and getLanguage(). Each TEXT SEARCH object type has its own Kind, for example TEXT_SEARCH_DICTIONARY.

Table discovery visits tables, column owners, views, materialized views, foreign tables and the tables owning constraints, triggers, policies and rules. It does not infer an index’s table or treat domains, sequences, types, routines or other catalog objects as tables. Statement visitors visit comment literals, and custom SQL deparsers can replace the explicit relation or literal. Feature analysis reports a schema modification. Validation exposes a separate commentOn... capability for each additional target kind.

Current date/time expression metadata

TemporalExpressionInfo.from(expression, dialect) provides a common read-only view of MySQL and PostgreSQL current-date/time expressions. It recognizes the existing TimeKeyExpression, Function and Column representations without replacing nodes or changing their SQL rendering:

PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(
        "SELECT CURRENT_TIMESTAMP(6)", p -> p.withDialect(Dialect.POSTGRESQL));
TemporalExpressionInfo info = TemporalExpressionInfo.from(
        select.getSelectItem(0).getExpression(), Dialect.POSTGRESQL).orElseThrow();
// info.getKind() == TemporalExpressionInfo.Kind.CURRENT_TIMESTAMP
// info.getPrecision() == 6

Import TemporalExpressionInfo from net.sf.jsqlparser.util. Precision is null when omitted, and explicit zero is preserved. getName() retains the original keyword or function name; hasParentheses() distinguishes bare and call forms. Results are snapshots: call from again after editing an AST.

The dialect is significant. MySQL LOCALTIME and LOCALTIMESTAMP are aliases of CURRENT_TIMESTAMP; PostgreSQL exposes LOCAL_TIME and LOCAL_TIMESTAMP separately. MySQL NOW, CURDATE and CURTIME aliases are recognized in their function-call forms. PostgreSQL now() is recognized without precision arguments. Quoted or qualified identifiers, unrelated expressions and unsupported dialects return Optional.empty().

This API identifies expression metadata rather than performing database validation. Precision reports the requested value, without applying defaults, server range checks or clamping. For example, PostgreSQL accepts precision 7 with a warning and clamps it to 6, whereas MySQL rejects it.

With Dialect.POSTGRESQL, ALTER TABLE ... ADD UNIQUE USING INDEX and ADD PRIMARY KEY USING INDEX expose a ConstraintUsingIndex through AlterExpression.getConstraint(). Its getName() is the optional new constraint name, while getExistingIndexName() identifies the existing index. This is separate from an index declaration’s name, columns and access method.

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE t ADD CONSTRAINT uq UNIQUE USING INDEX i",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
ConstraintUsingIndex constraint = (ConstraintUsingIndex)
    alter.getAlterExpressions().get(0).getConstraint();
constraint.setExistingIndexName("replacement_index");
constraint.setName("replacement_constraint");

getConstraintAttributes() exposes deferrability and initial timing when present. The same AST can be constructed with new ConstraintUsingIndex() and its fluent withName, withType and withExistingIndexName methods.

Transaction statements

START TRANSACTION produces a StartTransaction with an ordered list of typed TransactionMode values. PostgreSQL isolation levels, READ ONLY / READ WRITE, and [NOT] DEFERRABLE are supported, as is MySQL WITH CONSISTENT SNAPSHOT. PostgreSQL accepts mode lists with or without commas; output uses commas. The explicit MySQL dialect rejects a transaction containing both READ ONLY and READ WRITE, while retaining repeated occurrences of the same mode.

Choose the dialect explicitly to parse BEGIN as a transaction command:

StartTransaction transaction = (StartTransaction) CCJSqlParserUtil.parse(
    "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
transaction.getModes().set(1, TransactionMode.READ_WRITE);
// BEGIN ISOLATION LEVEL SERIALIZABLE, READ WRITE
String sql = transaction.toString();

Without a dialect, BEGIN retains the existing procedural block interpretation. MySQL and PostgreSQL dialects distinguish BEGIN transactions from BEGIN SELECT ...; END blocks by the following tokens. Oracle blocks and opaque routine bodies retain their existing handling.

Commit and RollbackStatement share TransactionCompletionStatement, which exposes the optional TransactionKeyword (WORK or PostgreSQL TRANSACTION), TransactionChain (CHAIN or NO_CHAIN) and MySQL TransactionRelease (RELEASE or NO_RELEASE). Null fields preserve the server’s default behavior; AND CHAIN cannot be combined with RELEASE. The PostgreSQL spellings END and ABORT produce a Commit and a RollbackStatement whose getCommand() is END or ABORT, so consumers handle one model per completion:

Commit end = (Commit) CCJSqlParserUtil.parse("END WORK AND CHAIN",
    parser -> parser.withDialect(Dialect.POSTGRESQL));
// END, WORK, CHAIN
String sql = end.getCommand() + ", " + end.getKeyword() + ", " + end.getChain();

END is a statement only with the PostgreSQL dialect, at the start of a statement and outside BEGIN ... END blocks; ABORT is also read without a dialect. The existing rollback work/savepoint/force accessors remain available. RELEASE [SAVEPOINT] produces ReleaseSavepointStatement; the MySQL dialect requires SAVEPOINT. Savepoint names retain identifier quoting.

SET [scope] TRANSACTION, PostgreSQL SET [scope] SESSION CHARACTERISTICS AS TRANSACTION and SET [scope] TRANSACTION SNAPSHOT 'id' produce a SetTransactionStatement. It reuses TransactionMode for the characteristics and the SetScope of SetStatement for the scope keyword written after SET; isSessionCharacteristics() marks the PostgreSQL form and getSnapshotId() returns the snapshot as a StringValue. Each dialect accepts the scopes of its server: MySQL GLOBAL, SESSION, LOCAL, PERSIST and PERSIST_ONLY, MariaDB GLOBAL, SESSION and LOCAL, and PostgreSQL SESSION and LOCAL, also before SESSION CHARACTERISTICS and SNAPSHOT. The MySQL dialect requires commas and accepts one isolation level and one access mode. Variable assignments such as SET transaction_isolation = 'READ-COMMITTED' remain SetStatement, whose getScope() now types the getEffectParameter() keyword.

SET CONSTRAINTS {ALL | name [, ...]} {DEFERRED | IMMEDIATE} produces a SetConstraintsStatement. An empty getConstraintNames() list means ALL, and getTiming() reuses ConstraintAttributes.Initially. The MySQL dialect rejects it.

SetTransactionStatement set = (SetTransactionStatement) CCJSqlParserUtil.parse(
    "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY",
    parser -> parser.withDialect(Dialect.MYSQL));
set.setScope(SetScope.GLOBAL);
// SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
String sql = set.toString();

These statements are traversed by statement visitors, deparsers and transaction feature analysis. The validator checks rollback, savepoint, setTransaction, setConstraints and the clause features (transactionChain, transactionRelease, transactionEndAbort, setTransactionGlobal, setTransactionPersist, setTransactionSessionCharacteristics, setTransactionSnapshot) against the MySQL and PostgreSQL capability sets. PostgreSQL AND [NO] CHAIN is enabled beginning with its version 12 capability set.

Two-phase commit

The two-phase commit statements share one TransactionId: a global transaction identifier, and for MySQL’s xid an optional branch qualifier and format ID (gtrid [, bqual [, formatID]]). The identifier parts are the literals as written, a StringValue or a HexValue.

PostgreSQL PREPARE TRANSACTION 'id' produces a PrepareTransactionStatement; it is read before PREPARE name AS statement, which keeps producing PrepareStatement. COMMIT PREPARED 'id' and ROLLBACK PREPARED 'id' produce a Commit and a RollbackStatement whose getPreparedTransactionKeyword() is PREPARED and whose getPreparedTransactionId() names the transaction. Oracle’s ROLLBACK FORCE 'id' uses the same pair with the keyword FORCE; getForceDistributedTransactionIdentifier() still returns the identifier as written. The PostgreSQL forms are read with the PostgreSQL dialect or without one, and take a PostgreSQL string constant.

MySQL XA {START | BEGIN} xid [JOIN | RESUME], XA END xid [SUSPEND [FOR MIGRATE]], XA PREPARE xid, XA COMMIT xid [ONE PHASE], XA ROLLBACK xid and XA RECOVER [CONVERT XID] produce an XaStatement with an Action and an optional Option. They are read with the MySQL and MariaDB dialects or without one; MariaDB does not accept CONVERT XID. The gtrid and bqual are string, hexadecimal or bit-value literals, and the formatID is an unsigned integer.

XaStatement xa = (XaStatement) CCJSqlParserUtil.parse("XA START X'0a', 'b', 1 JOIN",
    parser -> parser.withDialect(Dialect.MYSQL));
TransactionId xid = xa.getTransactionId();
// START, X'0a', 'b', 1, JOIN
String parts = xa.getAction() + ", " + xid.getGlobalTransactionId() + ", "
    + xid.getBranchQualifier() + ", " + xid.getFormatId() + ", " + xa.getOption();

The validator checks twoPhaseCommit (PostgreSQL) and xa (MySQL and MariaDB). Feature analysis reports XA RECOVER as returning a result set and the other statements as transaction control.

MySQL resource groups

MySQL CREATE RESOURCE GROUP name TYPE [=] {SYSTEM | USER} [VCPU [=] vcpu_spec [, ...]] [THREAD_PRIORITY [=] N] [ENABLE | DISABLE] produces a CreateResourceGroup and ALTER RESOURCE GROUP name [VCPU ...] [THREAD_PRIORITY ...] [ENABLE | DISABLE] [FORCE] an AlterResourceGroup. Both extend ResourceGroupDefinition, which holds the name, the VCPU list as VcpuRange elements (a single CPU or start-end), the thread priority and the State. Each option remembers whether it was written with =, := or nothing (OptionAssignment); the optional commas of the VCPU list are always printed. SET RESOURCE GROUP name [FOR thread_id [, ...]] produces a SetResourceGroup whose thread IDs are the numbers as written (LongValue or HexValue); getThreadIds() is null without FOR. DROP RESOURCE GROUP name [FORCE] is a Drop with the object type RESOURCE_GROUP and isForce().

CreateResourceGroup group = (CreateResourceGroup) CCJSqlParserUtil.parse(
    "CREATE RESOURCE GROUP rg1 TYPE = USER VCPU = 0-3, 5 THREAD_PRIORITY = 10 ENABLE",
    parser -> parser.withDialect(Dialect.MYSQL));
// USER, [0-3, 5], 10, ENABLE
String parts = group.getType() + ", " + group.getVcpus() + ", "
    + group.getThreadPriority() + ", " + group.getState();

The statements are read with the MySQL dialect or without one and follow MySQL’s grammar: the options come in the order above, and CPU numbers and the priority are plain integers. What MySQL checks when it runs the statement, such as the CPU numbers of the machine or the priority range of the group type, is not checked. In other dialects CREATE and ALTER RESOURCE GROUP stay an UnsupportedStatement and DROP RESOURCE GROUP rg keeps the generic Drop reading. The validator checks createResourceGroup, alterResourceGroup and setResourceGroup (MySQL); feature analysis reports SET RESOURCE GROUP as a session change and the others as schema changes.

MySQL loadable functions

MySQL CREATE [AGGREGATE] FUNCTION [IF NOT EXISTS] name RETURNS {STRING | INTEGER | REAL | DECIMAL} SONAME 'library' produces a CreateFunction whose getLoadableFunction() holds the name, the ReturnType, the library as a StringValue and isIfNotExists(); isAggregate() tells the two forms apart. The synonyms INT, INT4 and DEC keep their spelling in getReturnTypeKeyword(). getFunctionDeclarationParts() is derived from the definition, and setting declaration parts removes it.

CreateFunction function = (CreateFunction) CCJSqlParserUtil.parse(
    "CREATE AGGREGATE FUNCTION IF NOT EXISTS f RETURNS REAL SONAME 'udf.so'",
    parser -> parser.withDialect(Dialect.MYSQL));
LoadableFunction definition = function.getLoadableFunction();
// true, f, REAL, udf.so
String parts = function.isAggregate() + ", " + definition.getName() + ", "
    + definition.getReturnType() + ", " + definition.getLibrary().getValue();

With the MySQL dialect the statement follows MySQL’s grammar: AGGREGATE, or a function name directly followed by RETURNS, must continue as a loadable function, without OR REPLACE. With the MariaDB dialect or without one, a statement of exactly this shape gets the definition (OR REPLACE included) and anything else keeps its previous reading. Other dialects are unchanged: CREATE AGGREGATE FUNCTION stays an UnsupportedStatement there. TablesNamesFinder finds no tables in a loadable function.

MySQL ALTER TABLE validation modifier

MySQL ALTER TABLE ... {WITH | WITHOUT} VALIDATION is an AlterExpressionTableOption with the operation VALIDATION and getValidationMode(), like the ALGORITHM and LOCK modifiers it may be mixed with in the action list. The mode written directly after EXCHANGE PARTITION ... WITH TABLE t still belongs to that action (AlterExpressionPartition.getExchangeValidationMode()).

Alter alter = (Alter) CCJSqlParserUtil.parse(
    "ALTER TABLE t1 WITHOUT VALIDATION, EXCHANGE PARTITION p0 WITH TABLE t2",
    parser -> parser.withDialect(Dialect.MYSQL));
AlterExpressionTableOption modifier =
    (AlterExpressionTableOption) alter.getAlterExpressions().get(0);
// VALIDATION, WITHOUT, EXCHANGE_PARTITION
String parts = modifier.getOperation() + ", " + modifier.getValidationMode() + ", "
    + alter.getAlterExpressions().get(1).getOperation();

With the MySQL dialect, WITH or WITHOUT at the start of an action must be this modifier, and it is rejected after a comma that follows a command MySQL accepts only at the end of the list (a partition management command, DISCARD or IMPORT TABLESPACE, PARTITION BY, REMOVE PARTITIONING). With the MariaDB dialect or without one, the modifier is read where it is a whole item of the list; other dialects keep the raw-text action.

MySQL SHOW statements

The MySQL statements that read table, column and object definitions parse to typed statements in every dialect:

• SHOW [EXTENDED] [FULL] TABLES [{FROM | IN} db] [LIKE 'p' | WHERE expr] parses to ShowTablesStatement, as before.

• SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS} {FROM | IN} t [{FROM | IN} db] [LIKE 'p' | WHERE expr] extends ShowColumnsStatement. getTable() returns the Table; getTableName() and setTableName(String) remain and are derived from it. isExtended(), isFull() and isFieldsKeyword() record the modifiers and the FIELDS spelling, and getTableSelectionMode() the FROM/IN spelling before the table.

• SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} t [{FROM | IN} db] [WHERE expr] extends ShowIndexStatement in the same way: getTable(), isExtended(), getKeyword() (INDEX, INDEXES or KEYS), getTableSelectionMode(), getDbSelection() and getWhereCondition(). The String constructor, getTableName() and setTableName(String) remain. SHOW INDEX FROM t FROM db used to fail.

• SHOW TABLE STATUS [{FROM | IN} db] [LIKE 'p' | WHERE expr] parses to ShowTableStatusStatement.

• SHOW CREATE parses to ShowCreateStatement. getObjectType() is TABLE, VIEW, PROCEDURE, FUNCTION, TRIGGER, EVENT, DATABASE or USER. The first six name the object with getName(), a Table. DATABASE uses getDatabaseName(), isSchemaKeyword() and isIfNotExists(). USER uses getAccount(), a UserAccount without authentication; isCurrentUser() and isCurrentUserParentheses() describe CURRENT_USER and CURRENT_USER().

The {FROM | IN} db clause is a ShowDbSelection (getSelectionMode() and getName()) and the LIKE 'p' | WHERE expr filter a ShowFilter (getKind() and getExpression()). ShowTablesStatement, ShowColumnsStatement, ShowTableStatusStatement and MySqlShowStatement return them from getDbSelection() and getFilter(); ShowIndexStatement has the database clause only. The separate accessors remain and read and write these values: getSelectionMode()/getDbSelectionMode(), getDbName(), getLikeExpression() and getWhereCondition(). A selection mode set before the database name is kept for it. A statement holds one filter, so setWhereCondition replaces a LIKE filter and setLikeExpression a WHERE filter; ShowTablesStatement used to keep and render both. ShowTablesStatement.toString() no longer fails without modifiers or without a selection mode.

The other MySQL 8.4 SHOW statements parse to MySqlShowStatement. getKind() tells which one it is and getKeyword() how its keywords were spelled:

• VARIABLES and STATUS take a scope and a filter;

• PROCESSLIST takes FULL;

• DATABASES (also SCHEMAS), PROCEDURE_STATUS, FUNCTION_STATUS, CHARACTER_SET (also CHARSET and CHAR SET) and COLLATION take a filter;

• TRIGGERS (with FULL), EVENTS and OPEN_TABLES take a database and a filter;

• PROCEDURE_CODE and FUNCTION_CODE take the routine name, and ENGINE the engine name and STATUS, MUTEX or LOGS;

• GRANTS takes FOR account [USING role, ...];

• WARNINGS and ERRORS take COUNT(*) or a limit;

• BINLOG_EVENTS takes IN 'log', FROM pos and a limit, and RELAYLOG_EVENTS also FOR CHANNEL 'c';

• REPLICA_STATUS takes FOR CHANNEL 'c';

• PROFILE takes types such as CPU or BLOCK IO, FOR QUERY n and a limit;

• ENGINES (also STORAGE ENGINES), PLUGINS, PRIVILEGES, PROFILES, BINARY_LOGS, BINARY_LOG_STATUS and REPLICAS take nothing.

The parts have typed accessors: getScope() (GLOBAL, SESSION or LOCAL), isFull(), isCount(), getRoutineName() (a Table), getEngineName() and getEngineInfo(), getDbSelection(), getFilter(), getAccount() and getUsingRoles() (UserAccount values, read like the accounts of CREATE USER), getLogName(), getPosition(), getLimit() (LIMIT [offset,] n) with getOffset() (LIMIT n OFFSET offset), getChannel(), getProfileTypes() and getProfileQuery(). Kind.allows(Part) tells which parts a kind takes; setting another part throws IllegalArgumentException, and setKind drops the parts the new kind does not take. setKeyword("SCHEMAS") selects the kind together with its spelling.

ShowColumnsStatement columns = (ShowColumnsStatement) CCJSqlParserUtil.parse(
    "SHOW FULL COLUMNS FROM orders FROM shop LIKE 'c%'");
columns.getTableName();   // orders
columns.getDbName();      // shop

ShowCreateStatement create = (ShowCreateStatement) CCJSqlParserUtil.parse(
    "SHOW CREATE USER 'app'@'%'");
create.getAccount().getHost().getNotExcapedValue();   // %

MySqlShowStatement grants = (MySqlShowStatement) CCJSqlParserUtil.parse(
    "SHOW GRANTS FOR 'app'@'%' USING 'reader'");
grants.getKind();                   // GRANTS
grants.getUsingRoles().size();      // 1

MySqlShowStatement events = (MySqlShowStatement) CCJSqlParserUtil.parse(
    "SHOW BINLOG EVENTS IN 'binlog.000001' FROM 4 LIMIT 10",
    parser -> parser.withDialect(Dialect.MYSQL));
events.getLogName().getValue();     // binlog.000001
events.getLimit().getRowCount();    // 10

MySqlShowStatement is read with Dialect.MYSQL and Dialect.MARIADB and without a dialect; other dialects keep UnsupportedStatement. Without a dialect, a single word such as SHOW VARIABLES or SHOW GRANTS remains a ShowStatement, as in every dialect but MySQL and MariaDB. The spellings MySQL 8.4 removed, SHOW MASTER STATUS, SHOW MASTER LOGS, SHOW SLAVE STATUS and SHOW SLAVE HOSTS, are read with Dialect.MARIADB and without a dialect.

With Dialect.MYSQL or Dialect.MARIADB the LIKE pattern of every SHOW statement is a string literal, as in the server, and parses to a StringValue; without a dialect it is any simple expression, as before. Limit values are unsigned integers, names or ? parameters, log names and channels are strings, and a log position is an unsigned number.

A typed statement is used only when it covers the whole statement. Other forms, such as Snowflake’s SHOW COLUMNS IN SCHEMA s, ClickHouse’s SHOW CREATE TABLE t FORMAT f or SHOW WARNINGS LIKE 'a', remain UnsupportedStatement; SHOW INDEX forms with such trailing clauses used to fail. A WHERE filter is read to its end, so SHOW COLUMNS FROM t WHERE a = 1 AND b = 2 no longer becomes an UnsupportedStatement, and text the expression does not take is a parse error.

StatementDeParser prints the expressions of the filters, limits and log positions with its ExpressionDeParser. Table visitors report the table of SHOW COLUMNS and SHOW INDEX (qualified by the FROM db clause) and the table or view of SHOW CREATE TABLE and SHOW CREATE VIEW; until now SHOW INDEX raised UnsupportedOperationException, as SHOW TABLES still does. SHOW TABLE STATUS and MySqlShowStatement name no table. Validators report the showColumns, showIndex, showTableStatus, showCreate and mySqlShow features, which the MySQL and MariaDB capabilities enable, and look up the database of MySqlShowStatement and the procedure of SHOW PROCEDURE CODE. Feature analysis reports RETURNS_RESULT_SET.

MySQL EXPLAIN, prepared statements and DO

The MySQL forms of these utility statements extend the existing models. They are read with Dialect.MYSQL and Dialect.MARIADB and, unless noted, without a dialect; other dialects are unchanged.

• EXPLAIN, DESCRIBE and DESC take ANALYZE [FORMAT = name] or [FORMAT = name] [INTO @variable], then FOR CONNECTION id or [FOR {SCHEMA | DATABASE} name] and a SELECT, TABLE, VALUES, INSERT, REPLACE, UPDATE or DELETE statement. They parse to ExplainStatement, whose getKeyword() keeps the spelling. ANALYZE and FORMAT are Option values like PostgreSQL’s options; Option.isUseEqual() marks MySQL’s FORMAT = name, and getFormat() reads the format as ExplainStatement.Format. A format other than TRADITIONAL, JSON or TREE is rejected, as MySQL rejects it while it parses. getIntoVariable(), getConnectionId(), getSchemaName() and isSchemaKeyword() hold the other clauses, and getTargetKind() tells a statement, a table and a connection apart. EXPLAIN SELECT, EXPLAIN ANALYZE SELECT and DESCRIBE SELECT keep their readings, and DESCRIBE TABLE t needs the MySQL dialect.

• {DESCRIBE | DESC | EXPLAIN} table {column | 'pattern'} keeps DescribeStatement and ExplainStatement; getColumnPattern() is the Column or the StringValue. It needs the MySQL dialect, since DESCRIBE x y names an object kind in Snowflake.

• PREPARE name FROM {‘text’ | @variable} extends PrepareStatement: getSource() is the StringValue or UserVariable and getStatement() is null. The statement text is not parsed.

• EXECUTE name USING @a, @b fills Execute.getUsingVariables().

• DROP PREPARE name parses to DeallocateStatement with getKeyword() DROP.

• DO expr [, expr] … extends DoStatement: getSelectItems() holds the expressions, which MySQL reads as a select list, and getCode() is null. Without a dialect, DO before a string or LANGUAGE remains PostgreSQL’s code block, which needs Dialect.POSTGRESQL.

ExplainStatement explain = (ExplainStatement) CCJSqlParserUtil.parse(
    "EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM t",
    parser -> parser.withDialect(Dialect.MYSQL));
explain.getFormat();                                    // TREE
explain.getOption(ExplainStatement.OptionType.ANALYZE); // present

Execute execute = (Execute) CCJSqlParserUtil.parse("EXECUTE s USING @a, @b");
execute.getUsingVariables().size();                     // 2

Table visitors report the tables of the explained statement, of EXPLAIN table (until now none) and of subqueries in MySQL’s DO. PREPARE ... FROM raises UnsupportedOperationException, like EXECUTE and PostgreSQL’s DO, because the tables of the statement text are unknown. Validators report explain, prepare, executeUsing, deallocate and doStatement; the MySQL and MariaDB capabilities now include prepare, executeExecute, executeUsing and doStatement. Feature analysis reports MODIFIES_SESSION instead of RETURNS_RESULT_SET for EXPLAIN ... INTO, and reads the expressions of MySQL’s DO instead of reporting OPAQUE.

On this page

• How to use it
  • Add JSQLParser to your Project
  • Parse a SQL Statement
  • Explore the Parsed Tree
    • MySQL index hints, insert sources and DELETE … USING
    • PostgreSQL CHECK inheritance
    • DROP targets
    • DROP INDEX owners
      • MySQL named constraint drops
    • Edit table and view column renames
    • Shared CASCADE and RESTRICT behavior
    • Edit shared table options
    • Share MySQL index options
    • MySQL table and partition comments
    • Inspect PostgreSQL schema statements
      • Inspect indexes and constraints separately
    • Edit column identity actions
    • Column defaults on tables and views
  • Structured column attributes
  • Inspect logical replication statements
  • Inspect type, domain and extension statements
  • Inspect operator and aggregate definitions
  • Classify a Statement
    • Why bother
    • The features
    • Proven, possible, excluded
    • Scripts
  • Find Table Names
  • Use the Visitor Patterns
  • Build a SQL Statement
    • Customize JSON expression output
  • ODBC timestamp intervals
  • Handle Parse Errors
  • Choose a Dialect
    • Row pattern matching (MATCH_RECOGNIZE)
    • The individual features
    • Things that trip people up
  • Compile from Source Code
    • PostgreSQL roles, privileges and triggers
    • MySQL privileges and accounts in GRANT and REVOKE
    • PostgreSQL event triggers, REINDEX, SECURITY LABEL and schema elements
    • Migrating shared ALTER enums
    • Oracle anonymous blocks
    • SQL Server routine declarations
    • PostgreSQL table-level NOT NULL constraints
    • MySQL and PostgreSQL tablespaces
    • MySQL spatial reference systems and ALTER INSTANCE
    • SQL Server identity inserts
    • Database options and configuration parameters
    • Routine and trigger alterations
    • MySQL table maintenance statements
    • PostgreSQL maintenance statements
    • MySQL servers, plugins and components
    • PostgreSQL session statements and COPY options
    • MySQL server administration statements
    • PostgreSQL IMPORT FOREIGN SCHEMA
    • MySQL LOAD DATA and LOAD XML
    • PostgreSQL cursors
    • MySQL account management statements
  • Legacy MySQL GROUP BY ordering
  • PostgreSQL COMMENT targets
  • Current date/time expression metadata
  • Transaction statements
    • Two-phase commit
  • MySQL resource groups
  • MySQL loadable functions
  • MySQL ALTER TABLE validation modifier
  • MySQL SHOW statements
  • MySQL EXPLAIN, prepared statements and DO