Java 的 SQL 解析器库 - 检索 SQL 语句中存在的表名列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5572793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL parser library for Java - Retrieve the list of table names present in a SQL statement
提问by Mario Duarte
I am looking for a SQL Library that will parse an SQL statement and return some sort of Object representation of the SQL statement. My main objective is actually to be able to parse the SQL statement and retrieve the list of table names present in the SQL statement (including subqueries, joins and unions).
我正在寻找一个 SQL 库，它将解析 SQL 语句并返回 SQL 语句的某种对象表示。我的主要目标实际上是能够解析 SQL 语句并检索 SQL 语句中存在的表名列表（包括子查询、联接和联合）。
I am looking for a free library with a license business friendly (e.g. Apache license). I am looking for a library and not for an SQL Grammar. I do not want to build my own parser.
我正在寻找具有商业友好许可证（例如 Apache 许可证）的免费库。我正在寻找一个库而不是一个 SQL 语法。我不想构建自己的解析器。
The best I could find so far was JSQLParser, and the examplethey give is actually pretty close to what I am looking for. However it fails parsing too many good queries (DB2 Database) and I'm hoping to find a more reliable library.
到目前为止我能找到的最好的是JSQLParser，他们给出的例子实际上非常接近我正在寻找的。但是它无法解析太多好的查询（DB2 数据库），我希望找到一个更可靠的库。
回答by Nicholas Carey
I doubt you'll find anything prewritten that you can just use. The problem is that ISO/ANSI SQL is a very complicated grammar — something like more than 600 production rules IIRC.
我怀疑您会找到任何可以使用的预先编写的内容。问题在于 ISO/ANSI SQL 是一种非常复杂的语法——类似于 600 多条生产规则 IIRC。
Terence Parr's ANTLR parser generator(Java, but can generate parsers in any one of a number of target languages) has several SQL grammars available, including a couple for PL/SQL, one for a SQL Server SELECT statement, one for mySQL, and one for ISO SQL.
Terence Parr 的ANTLR 解析器生成器（Java，但可以用多种目标语言中的任何一种生成解析器）有几种可用的 SQL 语法，包括一对用于 PL/SQL，一种用于 SQL Server SELECT 语句，一种用于 mySQL，还有一种对于 ISO SQL。
No idea how complete/correct/up-to-date they are.
不知道它们有多完整/正确/最新。
回答by James
You needn't reinvent the wheel, there is already such a reliable SQL parser library there, (it's commerical, not free), and this article shows how to retrieve the list of table names present in the SQL statement (including subqueries, joins and unions) that is exactly what you are looking for.
你不需要重新发明轮子，那里已经有这样一个可靠的 SQL 解析器库，（它是商业的，不是免费的），本文展示了如何检索 SQL 语句中存在的表名列表（包括子查询、连接和工会）这正是您正在寻找的。
This SQL parser library supports Oracle, SQL Server, DB2, MySQL, Teradata and ACCESS.
这个 SQL 解析器库支持 Oracle、SQL Server、DB2、MySQL、Teradata 和 ACCESS。
回答by craftsmannadeem
You need the ultra light, ultra fast library to extract table names from SQL (Disclaimer: I am the owner)
您需要超轻量、超快的库来从 SQL 中提取表名（免责声明：我是所有者）
Just add the following in your pom
只需在你的 pom 中添加以下内容
<dependency>
<groupId>com.github.mnadeem</groupId>
<artifactId>sql-table-name-parser</artifactId>
<version>0.0.1</version>
And do the following
并执行以下操作
new TableNameParser(sql).tables()
For more details, refer the project
更多详情请参考项目
回答by Leonardo Herrera
Old question, but I think this project contains what you need:
老问题，但我认为这个项目包含你需要的东西：
Data Tools Project - SQL Development Tools
Here's the documentation for the SQL Query Parser.
这是SQL 查询解析器的文档。
Also, here's a small sample program. I'm no Java programmer so use with care.
另外，这里有一个小示例程序。我不是 Java 程序员，所以请小心使用。
package org.lala;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.MappedByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.util.Iterator;
import java.util.List;
import org.eclipse.datatools.modelbase.sql.query.QuerySelectStatement;
import org.eclipse.datatools.modelbase.sql.query.QueryStatement;
import org.eclipse.datatools.modelbase.sql.query.TableReference;
import org.eclipse.datatools.modelbase.sql.query.ValueExpressionColumn;
import org.eclipse.datatools.modelbase.sql.query.helper.StatementHelper;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParseErrorInfo;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParserException;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParserInternalException;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParseResult;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManager;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManagerProvider;
public class SQLTest {
private static String readFile(String path) throws IOException {
FileInputStream stream = new FileInputStream(new File(path));
try {
FileChannel fc = stream.getChannel();
MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0,
fc.size());
/* Instead of using default, pass in a decoder. */
return Charset.defaultCharset().decode(bb).toString();
} finally {
stream.close();
}
}
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
try {
// Create an instance the Parser Manager
// SQLQueryParserManagerProvider.getInstance().getParserManager
// returns the best compliant SQLQueryParserManager
// supporting the SQL dialect of the database described by the given
// database product information. In the code below null is passed
// for both the database and version
// in which case a generic parser is returned
SQLQueryParserManager parserManager = SQLQueryParserManagerProvider
.getInstance().getParserManager("DB2 UDB", "v9.1");
// Sample query
String sql = readFile("c:\test.sql");
// Parse
SQLQueryParseResult parseResult = parserManager.parseQuery(sql);
// Get the Query Model object from the result
QueryStatement resultObject = parseResult.getQueryStatement();
// Get the SQL text
String parsedSQL = resultObject.getSQL();
System.out.println(parsedSQL);
// Here we have the SQL code parsed!
QuerySelectStatement querySelect = (QuerySelectStatement) parseResult
.getSQLStatement();
List columnExprList = StatementHelper
.getEffectiveResultColumns(querySelect);
Iterator columnIt = columnExprList.iterator();
while (columnIt.hasNext()) {
ValueExpressionColumn colExpr = (ValueExpressionColumn) columnIt
.next();
// DataType dataType = colExpr.getDataType();
System.out.println("effective result column: "
+ colExpr.getName());// + " with data type: " +
// dataType.getName());
}
List tableList = StatementHelper.getTablesForStatement(resultObject);
// List tableList = StatementHelper.getTablesForStatement(querySelect);
for (Object obj : tableList) {
TableReference t = (TableReference) obj;
System.out.println(t.getName());
}
} catch (SQLParserException spe) {
// handle the syntax error
System.out.println(spe.getMessage());
@SuppressWarnings("unchecked")
List<SQLParseErrorInfo> syntacticErrors = spe.getErrorInfoList();
Iterator<SQLParseErrorInfo> itr = syntacticErrors.iterator();
while (itr.hasNext()) {
SQLParseErrorInfo errorInfo = (SQLParseErrorInfo) itr.next();
// Example usage of the SQLParseErrorInfo object
// the error message
String errorMessage = errorInfo.getParserErrorMessage();
String expectedText = errorInfo.getExpectedText();
String errorSourceText = errorInfo.getErrorSourceText();
// the line numbers of error
int errorLine = errorInfo.getLineNumberStart();
int errorColumn = errorInfo.getColumnNumberStart();
System.err.println("Error in line " + errorLine + ", column "
+ errorColumn + ": " + expectedText + " "
+ errorMessage + " " + errorSourceText);
}
} catch (SQLParserInternalException spie) {
// handle the exception
System.out.println(spie.getMessage());
}
System.exit(0);
}
}
