How to use it

Hint

  1. Quoting: Double Quotes ".." are used for quoting identifiers. Parsing T-SQL on MS SQL Server or Sybase with Squared Brackets [..] depends on Squared Bracket Quotation as shown in section Define the Parser Features below.

  2. JSQLParser uses a more restrictive list of Reserved Keywords and such keywords will need to be quoted.

  3. Escaping: JSQLParser pre-defines standard compliant Single Quote '.. Escape Character. Additional Back-slash \.. Escaping needs to be activated by setting the BackSlashEscapeCharacter parser feature. See section Define the Parser Features below for details.

  4. Oracle Alternative Quoting is partially supported for common brackets such as q'{...}', q'[...]', q'(...)' and q''...''.

  5. Supported Statement Separators are Semicolon ;, GO, Slash / or two empty lines \n\n\n.

Compile from Source Code

You will need to have JDK 8 or JDK 11 installed.

git clone https://github.com/JSQLParser/JSqlParser.git
cd jsqlformatter
mvn install

git clone https://github.com/JSQLParser/JSqlParser.git
cd jsqlformatter
gradle publishToMavenLocal

Build Dependencies

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

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

repositories {
    mavenCentral()
}

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

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

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

Parse a SQL Statement

Parse the SQL Text into Java Objects:

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 guidance with the API, use JSQLFormatter to visualize the Traversable Tree of Java Objects:

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

Use the Visitor Patterns

Traverse the Java Object Tree using the Visitor Patterns:

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

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

// Define a Statement Visitor for dispatching the Statements
StatementVisitorAdapter statementVisitor = new StatementVisitorAdapter() {
    public void visit(Select select) {
        select.getSelectBody().accept(selectVisitorAdapter);
    }
};

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

// Invoke the Statement Visitor
stmt.accept(statementVisitor);

Find Table Names

The class net.sf.jsqlparser.util.TablesNamesFinder can be used to return all Table Names from a Query or an Expression.

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

Build a SQL Statement

Build any SQL Statement from Java Code using a fluent API:

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

Define the Parser Features

JSQLParser interprets Squared Brackets [..] as Arrays, which does not work with MS SQL Server and T-SQL. Please use the Parser Features to instruct JSQLParser to read Squared Brackets as Quotes instead.

JSQLParser allows for standard compliant Single Quote '.. Escaping. Additional Back-slash \.. Escaping needs to be activated by setting the BackSlashEscapeCharacter parser feature.

Additionally there are Features to control the Parser’s effort at the cost of the performance.

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