解析SQL获取查询的列名

今天来说下解析一条查询SQL，怎么获取SQL语句中的列名，可以通过两种方式来实现：

1. 解析SQL语句

解析SQL可以通过借助工具jar包来实现

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>1.4-SNAPSHOT</version>
</dependency>

优点：可以不用与数据库产生交互，速度快

缺点：对于select * from table语句中的*就无法解析，会被当做字符串处理

public static void main(String[] args) {
       String sql1 = "select * from soa_sys_user";
       List<String> column1 = analyzeSelectSQL(sql1);
       System.out.println("sql1的解析结果如下：");
       for (String str : column1) {
           System.out.print(str + ", ");
       }
       
       System.out.println("");
       System.out.println("sql2的解析结果如下：");
       String sql2 = "select user_id, user_name as userName, email, weixin, to_date(sysdate, 'yyyy-mm-dd hh24:mi:ss') from soa_sys_user";
       List<String> column2 = analyzeSelectSQL(sql2);
       for (String str : column2) {
           System.out.print(str + ", ");
       }
    }
    
    
    public static List<String> analyzeSelectSQL(String sql){
        List<String> result = new ArrayList<String>();
        try {
            CCJSqlParserManager pm = new CCJSqlParserManager();
            Statement statement = pm.parse(new StringReader(sql));
            if (statement instanceof Select) {
                Select selectStatement = (Select) statement;
                PlainSelect selectBody = (PlainSelect) selectStatement
                        .getSelectBody();
                List<SelectItem> selectItemlist = selectBody.getSelectItems();
                SelectItem selectItem = null;
                SelectExpressionItem selectExpressionItem = null;
                AllTableColumns allTableColumns = null;
                Alias alias = null;
                SimpleNode node = null;
                if (selectItemlist != null) {
                    for (int i = 0; i < selectItemlist.size(); i++) {
                        selectItem = selectItemlist.get(i);
                        if (selectItem instanceof SelectExpressionItem) {
                            selectExpressionItem = (SelectExpressionItem) selectItemlist
                                    .get(i);
                            alias = selectExpressionItem.getAlias();
                            node = selectExpressionItem.getExpression()
                                    .getASTNode();
                            Object value = node.jjtGetValue();
                            String columnName = "";
                            if (value instanceof Column) {
                                columnName = ((Column) value).getColumnName();
                            } else if (value instanceof Function) {
                                columnName = ((Function) value).toString();
                            }else {
                                // 增加对select 'aaa' from table; 的支持
                                columnName = ObjectUtils.praseObjectToString(value);
                                columnName = columnName.replace("'", "");
                                columnName = columnName.replace("\"", "");
                            }
                            if (alias != null) {
                                columnName = alias.getName();
                            }
                            result.add(columnName);
                        } else if (selectItem instanceof AllTableColumns) {
                            allTableColumns = (AllTableColumns) selectItemlist
                                    .get(i);
                            result.add(allTableColumns.toString());
                        } else {
                            result.add(selectItem.toString());
                        }

                    }
                }
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return result;
    }

执行结果：

sql1的解析结果如下：
*, 
sql2的解析结果如下：
user_id, userName, email, weixin, to_date(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 

2. 执行SQL，与数据库产生交互，获取查询结果的列名

优点：只要数据库能查询的出来，就可以都解析到

缺点：需要与数据库产生交互，速度会相对慢

代码如下：

public static void main(String[] args) {
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        try {
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("select * from soa_sys_user order by user_id ");
            conn = JdbcUtils.getConnection();
            stm = conn.createStatement();
            rs = stm.executeQuery(sqlBuffer.toString());
            ResultSetMetaData rsmd = rs.getMetaData();
            List<String> columnList = new ArrayList<String>();
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                String columnName = rsmd.getColumnName(i).toLowerCase();
                columnList.add(columnName);
            }

            for (String str : columnList) {
                System.out.print(str + ", ");
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != conn) {
                    conn.close();
                }
                if (null != stm) {
                    stm.close();
                }
                if (null != rs) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

输出结果：

user_id, user_account, user_name, user_password, email, phone, mobile, weixin, group_code, rev_mail, rev_sms, rev_weixin, province_code, attribute1, attribute2, attribute3, attribute4, attribute5, enabled_flag, created_by, created_date, last_update_by, last_update_date, user_type, mgt_province_code, user_flag, login_time, error_times,