Movatterモバイル変換


[0]ホーム

URL:


ContentsMenuExpandLight modeDark modeAuto light/dark, in light modeAuto light/dark, in dark modeSkip to content
JSQLParser 5.3 documentation
Logo
JSQLParser 5.3 documentation
Back to top

How to use it

Hint

  1. 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.

  2. JSQLParser uses a more restrictive list ofReservedKeywords and such keywords willneed to be quoted.

  3. 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.

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

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

Compile from Source Code

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

Build Dependencies

<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 a SQL Statement

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

Error Handling

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

Error Recovery
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());
Unsupported Statement
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());

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

Find Table Names

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 a SQL Statement

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

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 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));
On this page

[8]ページ先頭

©2009-2025 Movatter.jp