从SQL/HQL Java解析表和列名 [英] Parsing table and column names from SQL/HQL Java

问题描述

我正在寻找Java中的开源API来解析SQL/HQL查询，以便为我提供其中使用的列名和表名.我尝试使用JSQLParser，它为我提供了查询中使用的表名.但是我看不到获取查询中使用的列名的支持.还有其他API可以帮助我吗?我知道通用SQL解析器，但它似乎是付费的.

I am looking for an open source API in Java to parse an SQL / HQL query so that it gives me the column names and the table names used in it. I tried using JSQLParser which gives me the table names used in the query. But I don't see a support for getting the column names used in the query. Is there any other API which can help me with this? I am aware of General SQL parser but it seems to be a paid one.

以下是使用JSQLParser的代码段，该代码段为我提供了查询中的表名:

Here is the code snippet using JSQLParser which gives me the table names in the query:

Statement statement;
    try
    {
        statement = CCJSqlParserUtil.parse( " SELECT * FROM  ( ( SELECT TBL.ID AS rRowId, TBL.NAME AS name, TBL.DESCRIPTION as description, TBL.TYPE AS type, TBL1.SHORT_NAME AS shortName  FROM ROLE_TBL TBL WHERE ( TBL.TYPE = 'CORE' OR  TBL1.SHORT_NAME = 'TNG' AND  TBL.IS_DELETED <> 1  ) ) MINUS ( SELECT TBL.ID AS rRowId, TBL.NAME AS name, TBL.DESCRIPTION as description, TBL.TYPE AS type, TBL3.SHORT_NAME AS shortName,TBL3.NAME AS tenantName FROM ROLE_TBL TBL INNER JOIN TYPE_ROLE_TBL TBL1 ON TBL.ID=TBL1.ROLE_FK LEFT OUTER JOIN TNT_TBL TBL3 ON TBL3.ID = TBL.TENANT_FK LEFT OUTER JOIN USER_TBL TBL4 ON TBL4.ID = TBL1.USER_FK WHERE ( TBL4.ID =771100 AND  TBL.IS_DELETED <> 1  ) ) ) ORDER BY name ASC" );
        Select selectStatement = (Select) statement;
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList( selectStatement );
        System.out.println( tableList.size() );
        for( String s : tableList )
            System.out.println( s );
    }
    catch( JSQLParserException e )
    {
        e.printStackTrace();
    }

在此方面，谢谢您的帮助.

Thanks in advance for any help in this regard.

推荐答案

使用JSqlParser( https://github.com/JSQLParser/JSqlParser ):

There are multiple ways to achieve this using JSqlParser (https://github.com/JSQLParser/JSqlParser):

1. 您也可以使用 TableNamesFinder 遍历所有列.正如您在结果列表中看到的那样， TableNamesFinder 不会遍历 Columns 的所有出现，因为这不是必需的.因此，这里的人也必须完成遍历实现，而我没有.

1. You could pimp TableNamesFinder to traverse all columns as well. As you could see at the result list, TableNamesFinder does not traverse all occurences of Columns, because it is not necessary for it. So one has to complete the traversal implementation here as well, which I did not.

您可以使用 JSqlParser AST-节点功能来获取所有列.对于特定的产品，JSqlParser为解析树生成节点. 列是其中之一.

You could use JSqlParser AST - Node feature to get all Columns. For specific productions JSqlParser produces nodes for a parse tree. Column is one of them.

要完成实施，必须收集所有列并使该列表与众不同(大小写，表等)

To complete the implementation one has to collect all columns and make this list distinct (case, table, etc.)

String sql = "SELECT * FROM  ( ( SELECT TBL.ID AS rRowId, TBL.NAME AS name, TBL.DESCRIPTION as description, TBL.TYPE AS type, TBL1.SHORT_NAME AS shortName  FROM ROLE_TBL TBL WHERE ( TBL.TYPE = 'CORE' OR  TBL1.SHORT_NAME = 'TNG' AND  TBL.IS_DELETED <> 1  ) ) MINUS ( SELECT TBL.ID AS rRowId, TBL.NAME AS name, TBL.DESCRIPTION as description, TBL.TYPE AS type, TBL3.SHORT_NAME AS shortName,TBL3.NAME AS tenantName FROM ROLE_TBL TBL INNER JOIN TYPE_ROLE_TBL TBL1 ON TBL.ID=TBL1.ROLE_FK LEFT OUTER JOIN TNT_TBL TBL3 ON TBL3.ID = TBL.TENANT_FK LEFT OUTER JOIN USER_TBL TBL4 ON TBL4.ID = TBL1.USER_FK WHERE ( TBL4.ID =771100 AND  TBL.IS_DELETED <> 1  ) ) ) ORDER BY name ASC";

    System.out.println("using TableNamesFinder to get column names");
    Statement statement = CCJSqlParserUtil.parse(sql);
    Select selectStatement = (Select) statement;
    TablesNamesFinder tablesNamesFinder = new TablesNamesFinder() {
        @Override
        public void visit(Column tableColumn) {
            System.out.println(tableColumn);
        }
    };
    tablesNamesFinder.getTableList(selectStatement);

    System.out.println("-------------------------------------------");
    System.out.println("using ast nodes to get column names");
    SimpleNode node = (SimpleNode) CCJSqlParserUtil.parseAST(sql);

    node.jjtAccept(new CCJSqlParserDefaultVisitor() {
        @Override
        public Object visit(SimpleNode node, Object data) {
            if (node.getId() == CCJSqlParserTreeConstants.JJTCOLUMN) {
                System.out.println(node.jjtGetValue());
                return super.visit(node, data);
            } else {
                return super.visit(node, data);
            }
        }
    }, null);

必须记住，JSqlParser只是一个解析器.因此，如果不像(table.column)中那样指定列表名称，就不可能获得列表名称.为了实现此目的，数据库模式必须可用.如果您查看以下内容，这将变得很清楚:

One has to have in mind, that JSqlParser is only a parser. Therefore it is not possible to get the columns table name without having it specified like in (table.column). To get this right the database schema must be available. This becomes clear if you look at:

select a from table1, table2

这是有效的SQL.

这篇关于从SQL/HQL Java解析表和列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！