How to use it¶

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.

TYPE, DOMAIN, EVENT, EVENT TRIGGER, TABLESPACE, USER and GROUP are recognized object types. 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 and account names. Edit getRoutines() or getAccounts() to change them.

• 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 and the CASCADE or RESTRICT keyword. setParameters() replaces the clause with raw tokens and clears both; 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/LOCK tokens retain their order and optional equals signs.

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

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.

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)

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

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)

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)

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

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

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:

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¶

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

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:

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

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.

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.

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¶

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¶

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 getRoles() 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 TRIGGER is outside this support. Serialized role SQL can contain passwords; avoid logging real credentials.

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

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.

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.

MySQL table maintenance statements¶

CHECK 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; REPAIR accepts QUICK, EXTENDED and USE_FRM. Other combinations are rejected, and the option words remain ordinary identifiers elsewhere.

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.