需求：
项目 web-sql模块，需要根据 sql 解析获取数据库表，然后对（金库）表权限进行校验。
金库表：用户查询该表前需要审批。
一、Druid (推荐)
添加依赖：
<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.8</version>
</dependency>
工具类：
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;import java.util.ArrayList;
import java.util.List;/*** @author NanNan Wang*/
public class SqlUtil {public static List<String> getTableNamesFromSQL(String sql, String dbType) {List<String> tableNames = new ArrayList<>();// 解析 SQL 语句List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);for (SQLStatement stmt : stmtList) {if (stmt instanceof SQLSelectStatement) {SQLSelectStatement selectStatement = (SQLSelectStatement) stmt;SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) selectStatement.getSelect().getQuery();extractTableNames(queryBlock.getFrom(), tableNames);} else if (stmt instanceof SQLInsertStatement) {SQLInsertStatement insertStatement = (SQLInsertStatement) stmt;tableNames.add(insertStatement.getTableName().getSimpleName());} else if (stmt instanceof SQLUpdateStatement) {SQLUpdateStatement updateStatement = (SQLUpdateStatement) stmt;tableNames.add(updateStatement.getTableName().getSimpleName());} else if (stmt instanceof SQLDeleteStatement) {SQLDeleteStatement deleteStatement = (SQLDeleteStatement) stmt;tableNames.add(deleteStatement.getTableName().getSimpleName());}}return tableNames;}private static void extractTableNames(SQLTableSource tableSource, List<String> tableNames) {if (tableSource instanceof SQLExprTableSource) {// 如果是简单表名，直接添加SQLExprTableSource exprTableSource = (SQLExprTableSource) tableSource;tableNames.add(exprTableSource.getTableName());} else if (tableSource instanceof SQLJoinTableSource) {// 如果是 JOIN，递归获取左表和右表SQLJoinTableSource joinTableSource = (SQLJoinTableSource) tableSource;extractTableNames(joinTableSource.getLeft(), tableNames);extractTableNames(joinTableSource.getRight(), tableNames);} else if (tableSource instanceof SQLSubqueryTableSource) {// 如果是子查询，递归处理子查询中的表SQLSelect subSelect = ((SQLSubqueryTableSource) tableSource).getSelect();extractTableNames(subSelect.getQueryBlock().getFrom(), tableNames);}}
}
测试样例
@Testvoid getTableNamesFromPGSQL() {String sql = "SELECT e.id, e.name, d.name AS dept_name FROM schema.employee e " +"LEFT JOIN schema.department d ON e.dept_id = d.id WHERE e.salary > 1000";List<String> tableNames = SqlUtil.getTableNamesFromSQL(sql, JdbcConstants.POSTGRESQL.name());System.out.println("Tables: " + tableNames); //Tables: [employee, department]}
二、JSqlParser
添加依赖：
<dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.6</version>
</dependency>
工具类：
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.TablesNamesFinder;import java.util.ArrayList;
import java.util.List;public class SqlUtil {/*** 使用 JSqlParser 根据 SQL 和数据库类型解析 SQL 中的所有表名* @param sql SQL 语句* @return 表名列表* @throws Exception 解析异常*/public static List<String> extractTableNames(String sql) {// 使用 JSqlParser 解析 SQL 语句Statement statement = null;try {statement = CCJSqlParserUtil.parse(sql);} catch (JSQLParserException e) {throw new RuntimeException(e);}// 使用 TablesNamesFinder 提取表名TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();return tablesNamesFinder.getTableList(statement);}
}
重要：要注意如果是 PostgreSQL 这里支持 模式（schema) 的需要进一步处理！
例如：
@Test
void extractPgTableNames () {String sql = "SELECT e.id, e.name, d.name AS dept_name FROM schema.employee e " +"LEFT JOIN schema.department d ON e.dept_id = d.id WHERE e.salary > 1000";List<String> tableNames = SqlUtil.extractTableNames(sql);System.out.println("Tables: " + tableNames); //Tables: [schema.employee, schema.department]
}
