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