解析sql 查询sql中存在的表名

墨蓝 2021-11-13 22:22 539阅读 0赞

package com.example.demo.test;
    
    
    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.*;
    
    
    
    public class SqlAnalysis {
        public void rr() throws JSQLParserException {
            String sql = "SELECT * FROM bbb.TABLE1";
            String sq = "\r\n" +
                    "SELECT DISTINCT\r\n" +
                    "  opb_subject.subj_name,\r\n" +
                    "  frominstances.Widget_Id,\r\n" +
                    "  opb_mapping.mapping_name,\r\n" +
                    "SUBSTR(opb_src.source_name, 1, INSTR(opb_src.source_name, '_')-1) as pwx,\r\n" +
                    "  SUBSTR(opb_src.source_name, INSTR(opb_src.source_name, '_')+1) as src,\r\n" +
                    "\r\n" +
                    "  opb_src.source_name src_table_nm,\r\n" +
                    "  opb_targ.target_name tgt_table_nm\r\n" +
                    "  FROM\r\n" +
                    "  dd.opb_subject,\r\n" +
                    "  dd.opb_mapping,\r\n" +
                    "  dd.opb_src,\r\n" +
                    "  dd.opb_src_fld,\r\n" +
                    "  dd.opb_targ,\r\n" +
                    "  dd.opb_targ_fld,\r\n" +
                    "  dd.opb_Swidget_inst frominstances,\r\n" +
                    "  dd.opb_Swidget_inst toinstances\r\n" +
                    "  WHERE\r\n" +
                    "  opb_subject.subj_id = opb_mapping.subject_id\r\n" +
                    "  AND opb_mapping.mapping_id = frominstances.mapping_id\r\n" +
                    "  AND opb_mapping.mapping_id = toinstances.mapping_id\r\n" +
                    "  AND opb_targ.target_id = toinstances.widget_id\r\n" +
                    "  AND toinstances.widget_type = 2\r\n" +
                    "  AND opb_targ_fld.target_id = opb_targ.target_id\r\n" +
                    "  AND opb_src.src_id = frominstances.widget_id\r\n" +
                    "  AND frominstances.widget_type = 1\r\n" +
                    "  AND opb_src_fld.src_id = opb_src.src_id";
            Statement statement = (Statement) CCJSqlParserUtil.parse(sq);
            TablesNamesFinder tablesNamesFinder =new TablesNamesFinder();
    
            List<String> result = tablesNamesFinder.getTableList(statement);
                System.out.println("size:"+result.size());
                for(String table:result) {
                    System.out.println(table);
            }
        }
    }

在网上查找了一个工具类，测试下，还是很靠谱的。

顺带debug了下，发现statement真的存了好多东西。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70 2][]可惜解析sql这个类我也是刚接触，还不知道怎么去其他的数据。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70]: /images/20211113/c337ae26ba71478688167e517bd6adc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70 1]: /images/20211113/698521167af941b2aa0f6bd7dbf0807f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1BldGVyX1M_size_16_color_FFFFFF_t_70 2]: /images/20211113/c85239e9d4c544b4a2d02ff4017cd084.png