java 正则 sql_java正则表达式获取sql中所有的表名

项目中有这么个需求，参照网上相关资料，稍微改动，用了下面的方式，应该不会完全都能获取，或者可能获取有问题，但是暂时先这样吧。也差不多。

/**

* 表名解析

*/

private static Pattern p = Pattern.compile(

"\\s+from\\s+(\\w+)(\\s+|,)(\\w*)|\\s+join\\s+(\\w+)\\s+(\\w*)\\s+on",

Pattern.CASE_INSENSITIVE);

/**

* 预处理sql

* @param sql sql

* @return 处理后sql

* @throws Exception 异常

*/

private static String preHandleSql(String sql,boolean removeBrackets) throws Exception {

// 1. 统一换行符

if (sql.contains("\r\n")) {

sql = sql.replaceAll("\r\n", "\n");

} else {

sql = sql.replaceAll("\r", "\n");

}

// 2. 去掉注释

String[] rows = sql.split("\n");

StringBuilder sb = new StringBuilder(sql.length());

for (String row : rows) {

int indexOfComment = row.indexOf("--");

if (indexOfComment == -1) {

sb.append(row).append("\n");

} else {

sb.append(row, 0, indexOfComment).append("\n");

}

}

if (removeBrackets) {

sql = removeBrackets(sb);

}

// 4. 找到第一个select和from，其间的字段即是要查询的字段列表

return sql.toLowerCase();

}

/**

* 括号消除：消除括号和括号中包含的内容，非贪心模式

* 例如：

* 输入select (ssss(sadf)sdfsdf(sdssf(sssdf)dsssf)dsslf) as a, (asdfsdf) as b, (asdfsdf) as c from dsfdf

* a组 b组 a组 c组 d组 b组 c组 d组 m组 m组 x组 x组

* A组 A组 B组 B组 C组 C组

* 去掉select (去 掉 部 分) as a, (去掉部分) as b, (去掉部分) as c from dsfdf

* 输出select as a, as b, as c from dsfdf

*

* @param str 消除前的字符串

* @return 消除后的字符串

* @throws Exception 括号不匹配

*/

private static String removeBrackets(StringBuilder str) throws Exception {

// 1. 收集括号组

List bracketsContainer = new ArrayList<>();

collectBrackets(str, 0, bracketsContainer);

if (bracketsContainer.isEmpty()) {

return str.toString();

}

// 2. 消除括号组

StringBuilder newStr = new StringBuilder(str.length());

int groupSize = bracketsContainer.size();

for (int i = 0; i < groupSize; i++) {

int[] currentBrackets = bracketsContainer.get(i);

if (i == 0) {

// 刚到第一组

newStr.append(str.subSequence(0, currentBrackets[0]));

}

if (i + 1 == groupSize) {

// 已到最后一组

newStr.append(str.subSequence(currentBrackets[1] + 1, str.length()));

} else {

// 未到最后一组

int[] nextBrackets = bracketsContainer.get(i + 1);

newStr.append(str.subSequence(currentBrackets[1] + 1, nextBrackets[0]));

}

}

return newStr.toString();

}

/**

* 收集括号组

*

* @param str 消除前的字符串

* @param fromIndex 从哪开始找括号

* @param bracketsContainer 括号组容器

* @throws Exception 括号不匹配、括号嵌套层级过多

*/

private static void collectBrackets(StringBuilder str, int fromIndex, List bracketsContainer) throws Exception {

int firstLeftBracket = str.indexOf("(", fromIndex + 1);

int nextLeftBracket = firstLeftBracket;

// SQL中不包含左括号时，直接返回

if (firstLeftBracket == -1) {

return;

}

// 括号层级(因为前面已经找到一个左括号，所以初始值为1)

int level = 1;

int nextRightBracket = str.indexOf(")", fromIndex + 1);

if (nextRightBracket == -1) {

throw new Exception("括号不匹配");

}

// 避免死循环

int maxLevel = 1000;

do {

int tempLeftBracket = str.indexOf("(", nextLeftBracket + 1);

if (tempLeftBracket == -1 || tempLeftBracket > nextRightBracket) {

// 找不到下一个左括号或者下一个左括号已属于下一个括号组

break;

} else {

nextLeftBracket = tempLeftBracket;

}

nextRightBracket = str.indexOf(")", nextRightBracket + 1);

if (nextRightBracket == -1) {

throw new Exception("括号不匹配");

}

level++;

} while (level <= maxLevel);

if (level >= maxLevel) {

throw new Exception("括号嵌套层级过多");

}

// 把收集到的括号组放入容器

bracketsContainer.add(new int[] {firstLeftBracket, nextRightBracket});

// 递归

collectBrackets(str, nextRightBracket, bracketsContainer);

}

/**

* 解析sql中的表名

* @param sql sql

* @return 表名

* @throws Exception 异常

*/

public static List parseSqlRefTables(String sql) throws Exception {

List tableNames = Lists.newArrayList();

String newSql = StringUtils.replaceAll(preHandleSql(sql, false),"\n"," ");

Matcher m = p.matcher(newSql);

while (m.find()) {

tableNames.add(m.group());

}

List result = Lists.newArrayList();

for (String tableName : tableNames) {

String trimTableName = StringUtils.split(tableName, " ")[1].trim();

if (trimTableName.contains(",")) {

result.addAll(Arrays.asList(StringUtils.split(trimTableName, ",")));

} else {

result.add(trimTableName);

}

}

return result.parallelStream().distinct().collect(Collectors.toList());

}

public static void main(String[] args) throws Exception {

String data1 = "SELECT dma_t.\"id\", dma_t.\"name\", dma_t.area_border, dma_t.lat_lng, dma_t.level, leak_t.lossf, leak_t.\"day\", leak_t.avgf, leak_t.background_loss, leak_t.nmf, leak_t.normal_use, leak_t.supply FROM la_leak_t leak_t, ( SELECT MAX (dma_id) AS dma_id, MAX (\"day\") AS \"day\" FROM la_leak_t WHERE org_id = ${orgId} GROUP BY dma_id ) recent_t, mdm_dmaarea_m_t dma_t WHERE leak_t.dma_id = recent_t.dma_id AND leak_t.\"day\" = recent_t.\"day\" AND leak_t.dma_id = dma_t.\"id\"";

List tableNames = parseSqlRefTables(data1);

tableNames.forEach(System.out::println);

}