Hint
Quoting: Double Quotes".."
are used for quoting identifiers. Parsing T-SQL onMS SQL Server orSybase with Squared Brackets[..]
depends onSquaredBracketQuotation
as shown in sectionDefine the Parser Features below.
JSQLParser uses a more restrictive list ofReservedKeywords
and such keywords willneed to be quoted.
Escaping: JSQLParser pre-defines standard compliantSingle Quote'..
Escape Character. Additional Back-slash\..
Escaping needs to be activated by setting theBackSlashEscapeCharacter
parser feature. See sectionDefine the Parser Features below for details.
Oracle Alternative Quoting is partially supported for common brackets such asq'{...}'
,q'[...]'
,q'(...)'
andq''...''
.
Supported Statement Separators are Semicolon;
,GO
, Slash/
or two empty lines\n\n\n
.
You will need to haveJDK8
orJDK11
installed. Please note that JSQLParser-4.9 is the lastJDK8
compatible release and all development after will depend onJDK11
. Building JSQLParser-5.1 and newer with Gradle will depend on a JDK17 toolchain due to the used plugins.
gitclone--depth1https://github.com/JSQLParser/JSqlParser.gitcdJSqlParsermvninstall
gitclone--depth1https://github.com/JSQLParser/JSqlParser.gitcdJSqlParsergradlepublishToMavenLocal
<dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>5.3</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>5.4-SNAPSHOT</version></dependency>
repositories{mavenCentral()}dependencies{implementation'com.github.jsqlparser:jsqlparser:5.3'}
repositories{maven{url=uri('https://oss.sonatype.org/content/groups/public/')}}dependencies{implementation'com.github.jsqlparser:jsqlparser:5.4-SNAPSHOT'}
Parse the SQL Text into Java Objects:
StringsqlStr="select 1 from dual where a=b";PlainSelectselect=(PlainSelect)CCJSqlParserUtil.parse(sqlStr);SelectItemselectItem=select.getSelectItems().get(0);Assertions.assertEquals(newLongValue(1),selectItem.getExpression());Tabletable=(Table)select.getFromItem();Assertions.assertEquals("dual",table.getName());EqualsToequalsTo=(EqualsTo)select.getWhere();Columna=(Column)equalsTo.getLeftExpression();Columnb=(Column)equalsTo.getRightExpression();Assertions.assertEquals("a",a.getColumnName());Assertions.assertEquals("b",b.getColumnName());
For guidance with the API, useJSQLFormatter 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
There are two features for handling errors
parser.withErrorRecovery(true)
will continue to the next statement separator and return an empty statement.
parser.withUnsupportedStatements(true)
will return an instance of theUnsupportedStatement class, although the first statementmust be a regular statement
CCJSqlParserparser=newCCJSqlParser("select * from mytable; select from; select * from mytable2");Statementsstatements=parser.withErrorRecovery().Statements();// 3 statements, the failing one set to NULLassertEquals(3,statements.size());assertNull(statements.get(1));// errors are recordedassertEquals(1,parser.getParseErrors().size());
Statementsstatements=CCJSqlParserUtil.parseStatements("select * from mytable; select from; select * from mytable2; select 4;",parser->parser.withUnsupportedStatements());// 4 statements with one Unsupported Statement holding the contentassertEquals(4,statements.size());assertInstanceOf(UnsupportedStatement.class,statements.get(1));assertEquals("select from",statements.get(1).toString());// no errors records, because a statement has been returnedassertEquals(0,parser.getParseErrors().size());
Traverse the Java Object Tree using the Visitor Patterns:
// Define an Expression Visitor reacting on any Expression// Overwrite the visit() methods for each Expression ClassExpressionVisitorAdapter<Void>expressionVisitorAdapter=newExpressionVisitorAdapter<>(){public<S>Voidvisit(EqualsToequalsTo,Scontext){equalsTo.getLeftExpression().accept(this,context);equalsTo.getRightExpression().accept(this,context);returnnull;}public<S>Voidvisit(Columncolumn,Scontext){System.out.println("Found a Column "+column.getColumnName());returnnull;}};// Define a Select Visitor reacting on a Plain Select invoking the Expression Visitor on the Where ClauseSelectVisitorAdapter<Void>selectVisitorAdapter=newSelectVisitorAdapter<>(){@Overridepublic<S>Voidvisit(PlainSelectplainSelect,Scontext){returnplainSelect.getWhere().accept(expressionVisitorAdapter,context);}};// Define a Statement Visitor for dispatching the StatementsStatementVisitorAdapter<Void>statementVisitor=newStatementVisitorAdapter<>(){public<S>Voidvisit(Selectselect,Scontext){returnselect.getSelectBody().accept(selectVisitorAdapter,context);}};StringsqlStr="select 1 from dual where a=b";Statementstmt=CCJSqlParserUtil.parse(sqlStr);// Invoke the Statement Visitor without a contextstmt.accept(statementVisitor,null);
The classnet.sf.jsqlparser.util.TablesNamesFinder
can be used to return all Table Names from a Query or an Expression.
// find in StatementsStringsqlStr="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 ExpressionsStringexprStr="A.id=B.id and A.age = (select age from C)";tableNames=TablesNamesFinder.findTablesInExpression(exprStr);assertThat(tableNames).containsExactlyInAnyOrder("A","B","C");
Build any SQL Statement from Java Code using a fluent API:
StringexpectedSQLStr="SELECT 1 FROM dual t WHERE a = b";// Step 1: generate the Java Object Hierarchy forTabletable=newTable().withName("dual").withAlias(newAlias("t",false));ColumncolumnA=newColumn().withColumnName("a");ColumncolumnB=newColumn().withColumnName("b");ExpressionwhereExpression=newEqualsTo().withLeftExpression(columnA).withRightExpression(columnB);PlainSelectselect=newPlainSelect().addSelectItem(newLongValue(1)).withFromItem(table).withWhere(whereExpression);// Step 2a: Print into a SQL StatementAssertions.assertEquals(expectedSQLStr,select.toString());// Step 2b: De-Parse into a SQL StatementStringBuilderbuilder=newStringBuilder();StatementDeParserdeParser=newStatementDeParser(builder);deParser.visit(select);Assertions.assertEquals(expectedSQLStr,builder.toString());
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 theBackSlashEscapeCharacter
parser feature.
Additionally there are Features to control the Parser’s effort at the cost of the performance.
StringsqlStr="select 1 from [sample_table] where [a]=[b]";// T-SQL Square Bracket QuotationStatementstmt=CCJSqlParserUtil.parse(sqlStr,parser->parser.withSquareBracketQuotation(true));// Set Parser Timeout to 6000 msStatementstmt1=CCJSqlParserUtil.parse(sqlStr,parser->parser.withSquareBracketQuotation(true).withTimeOut(6000));// Allow Complex Parsing (which allows nested Expressions, but is much slower)Statementstmt2=CCJSqlParserUtil.parse(sqlStr,parser->parser.withSquareBracketQuotation(true).withAllowComplexParsing(true).withTimeOut(6000));// Allow Back-slash escapingsqlStr="SELECT ('\\'Clark\\'', 'Kent')";Statementstmt2=CCJSqlParserUtil.parse(sqlStr,parser->parser.withBackslashEscapeCharacter(true));