I have various sql requests.For example

SELECT COUNT(project_id) AS 'count', project.name AS 'projects' FROM test
JOIN project ON project_id = project.id 

or

SELECT count(*) AS 'browsers' FROM `test`

I want to receive words that are in ''. For example 'count', 'projects', 'browsers'. Thus, I want to get the names of the columns from the table.But how can I do this, something I don't understand.So far I just wrote

  String[] str = sqlRequest.split("FROM");

but what can I write next, I don’t guess. Tell me how you can get words from these brackets?

CodePudding user response：

Don't do this with String split, replace etc. At some point, it will fail.

You should parse the sql statement. Example with JsqlParser like

public class Main {

    public static void main(String[] args) throws JSQLParserException {

        Select stmt = (Select) CCJSqlParserUtil.parse(
                "SELECT COUNT(project_id) AS 'count', project.name AS 'projects' FROM test\n"
                  "JOIN project ON project_id = project.id ");

        for (SelectItem selectItem : ((PlainSelect) stmt.getSelectBody()).getSelectItems()) {
            selectItem.accept(new SelectItemVisitorAdapter() {
                @Override
                public void visit(SelectExpressionItem item) {
                    System.err.println(item.getAlias().getName());
                }
            });
        }
    }
}

Output:

'count'
'projects'

CodePudding user response：

You can do it as follow:

String query = "SELECT COUNT(project_id) AS 'count', project.name AS 'projects' FROM test JOIN project ON project_id = project.id";
LinkedList<String> queryColumns = new LinkedList<>();
String[] test = query.replace("SELECT", "").split("FROM")[0].split(","); //You have a string from which you need to extract single names, taking care of aliases.
for(String s : test) {
    int aliasIndex = s.indexOf("AS");
    String column;
    if(aliasIndex != -1) {
        //if there is an alias
        column = s.substring(aliasIndex   2).replace("'","").strip();
    } else {
        //if there isn't an alias
        column = s.replace("'","").strip();
    }
    queryColumns.add(column);
}

I assume that the alias is indicated as 'AS' in uppercase, if it can be lowercase too, it's easy to change and adapt the code.