How to use it¶
Hint
Quoting: Double Quotes
".."
are used for quoting identifiers. Parsing T-SQL on MS SQL Server or Sybase with Squared Brackets[..]
depends onSquared Bracket Quotation
as shown in section Define the Parser Features below.JSQLParser uses a more restrictive list of
Reserved Keywords
and such keywords will need to be quoted.Escaping: JSQLParser pre-defines standard compliant Single Quote
'..
Escape Character. Additional Back-slash\..
Escaping needs to be activated by setting theBackSlashEscapeCharacter
parser feature. See section Define the Parser Features below for details.Oracle Alternative Quoting is partially supported for common brackets such as
q'{...}'
,q'[...]'
,q'(...)'
andq''...''
.Supported Statement Separators are Semicolon
\;
,GO
, Slash\/
or 2 empty lines.
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 build
Build Dependencies¶
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.6</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.7-SNAPSHOT</version>
</dependency>
repositories {
mavenCentral()
}
dependencies {
implementation 'com.github.jsqlparser:jsqlparser:4.6'
}
repositories {
maven {
url = uri('https://oss.sonatype.org/content/groups/public/')
}
}
dependencies {
implementation 'com.github.jsqlparser:jsqlparser:4.7-SNAPSHOT'
}
Parse a SQL Statement¶
Parse the SQL Text into Java Objects:
String sqlStr="select 1 from dual where a=b";
Statement statement = CCJSqlParserUtil.parse(sqlStr);
if (statement instanceof Select) {
Select select = (Select) statement;
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) plainSelect.getSelectItems().get(0);
Assertions.assertEquals( new LongValue(1), selectExpressionItem.getExpression());
Table table = (Table) plainSelect.getFromItem();
Assertions.assertEquals("dual", table.getName());
EqualsTo equalsTo = (EqualsTo) plainSelect.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
└─selectBody: net.sf.jsqlparser.statement.select.PlainSelect
├─selectItems -> Collection<SelectExpressionItem>
│ └─selectItems: net.sf.jsqlparser.statement.select.SelectExpressionItem
│ └─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);
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
SelectExpressionItem selectExpressionItem =
new SelectExpressionItem().withExpression(new LongValue().withValue(1));
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 plainSelect = new PlainSelect().addSelectItems(selectExpressionItem)
.withFromItem(table).withWhere(whereExpression);
Select select = new Select().withSelectBody(plainSelect);
// 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)
);