这篇文章给大家介绍java中怎么利用正则表达式获取sql中的表名,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。
/** * 表名解析 */ private final static Pattern P = Pattern .compile("\\s+from\\s+(\\w\\.)?(\\w+)(\\s+|,)(\\w*)|\\s+join\\s+(\\w\\.)?(\\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<int[]> 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<int[]> 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<String> parseSqlRefTables(String sql) throws Exception { List<String> tableNames = Lists.newArrayList(); String newSql = StringUtils.replaceAll(preHandleSql(sql, false),"\n"," "); Matcher m = p.matcher(newSql); while (m.find()) { tableNames.add(m.group()); } List<String> 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<String> tableNames = parseSqlRefTables(data1); tableNames.forEach(System.out::println); }
关于java中怎么利用正则表达式获取sql中的表名就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。