How to extract column names from SQL Query as String SQL query is passed as a String

Question

An SQL Query will be passed to my Method as a String, How can i retrieve the Column names in the query, if the query is e.g.

Select name, dob, age, gender from table1; or 
Select uname AS name, hgt AS height, wgt AS weight from table1;

I want to get the Column names in an Array or List.

Answer 1

have you tried

ResultSetMetaData rsmd = rs.getMetaData();
String name = rsmd.getColumnName(1);

Answer 2

Though somewhat convoluted this works for both the queries listed in the question :

String query = "Select uname AS name, hgt AS height, wgt AS weight from table1";
Pattern p = Pattern.compile("\\s*\\w+,");Pattern p1 = Pattern.compile("\\s+\\w+\\s+from");
Matcher m = p.matcher(query);Matcher m1=p1.matcher(query);
String colsOnly="";
while(m.find()){colsOnly+=(m.group().trim());}
while(m1.find()){colsOnly+=(m1.group().substring(0,m1.group().length()-4).trim());}
String[] cols = colsOnly.split(",");

Answer 3

    String str = "select dept,empid as eid , ename as name,deptname,deptid as department_id from emp";

    String[] arr = str.split("as");
    StringTokenizer str1 = new StringTokenizer(str, " ,");
    ArrayList<String> arrStr = new ArrayList<>();
    while (str1.hasMoreTokens())
    {
        String strT = str1.nextToken().toString();          
        arrStr.add(strT);
    }       
    for (int i = 0; i < arrStr.size(); i++)
    {
        if (arrStr.get(i).equals("as"))
        {
            System.out.println("Orignal\t" + arrStr.get(i - 1));
            System.out.println("Alias\t" + arrStr.get(i + 1));
        }
    }

Output

Orignal empid

Alias eid

Orignal ename

Alias name

Orignal deptid

Alias department_id

Answer 4

Use JSqlParser

Features

• query validation
• support SQL standard as well as all major RDBMS
• translates SQL statements into a traversable hierarchy of Java classes

Maven dependency

<dependency>
  <groupId>com.github.jsqlparser</groupId>
  <artifactId>jsqlparser</artifactId>
  <version>4.5</version>
</dependency>

Implementation

public class SqlParser {

  private static Statement parseQuery(String query) throws JSQLParserException {
    return CCJSqlParserUtil.parse(query);
  }

  private static Select parseSelectQuery(String query) throws JSQLParserException {
    return (Select) parseQuery(query);
  }

  private static List<String> extractColumnNames(Select selectQuery) {
    SelectStatementColumnVisitor visitor = new SelectStatementColumnVisitor();
    for (SelectItem selectItem : ((PlainSelect) selectQuery.getSelectBody()).getSelectItems()) {
      selectItem.accept(visitor);
    }

    return visitor.columnNames();
  }

  public static void main(String[] args) throws JSQLParserException {
    Select selectStatement = parseSelectQuery("SELECT uname AS name, hgt AS height, wgt AS weight from table1");
    List<String> columns = extractColumnNames(selectStatement);
    System.out.println(columns);
  }
}

class SelectStatementColumnVisitor implements SelectItemVisitor {

    private final List<String> columnNames = new ArrayList<>();

    public List<String> columnNames() {
        return this.columnNames;
    }

    @Override
    public void visit(SelectExpressionItem item) {
        if (item.getAlias() != null) {
            columnNames.add(item.getAlias().getName());
            return;
        }

        columnNames.add(item.getExpression().toString());
    }

    @Override
    public void visit(AllColumns columns) {
        columnNames.add(columns.toString());
    }

    @Override
    public void visit(AllTableColumns columns) {
        columnNames.add(columns.toString());
    }
}