java-How to parse sql columns with JDBC or jSqlParser ?

1. The purpose of this post

This post would demo how to parse sql column names with JDBC or jSqlParser.

2. Environments

• Jdk 1.7+

3. The code

3.1 The database table environment

Assume that we have a table named tbl_city, which has two column names like this:

CREATE TABLE tbl_city
(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(255)
);

3.2 Parse column names with JDBC

The most easiest way is to execute the sql and get the columns from the ResultSetMetaData.

public static void main(String[] args) {
        System.out.println("Mysql JDBC Parse Column Names Testing ~");

        try {

            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {
            System.err.println("Unable to find the mysql JDBC Driver!");
            e.printStackTrace();
            return;
        }

        // default database: mysql
        // JDK 1.7+, auto close connection with try-with-resources, you do not need to use try-finally to close the resource.
        try (Connection connection =
                     DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
                             "root", "123456")) { //would close the Connection automatically


            String sql = "select * from tbl_city"; // the sql to parse
            java.sql.Statement statement = connection.prepareStatement(sql);


            try (ResultSet rs = statement.executeQuery(sql)) {//would close the ResultSet automatically

                ResultSetMetaData rsMeta = rs.getMetaData();
                int columnCount = rsMeta.getColumnCount();

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsMeta.getColumnName(i);
                    System.out.format("%s\n", columnName);
                }
            }

        } catch (SQLException e) {
            System.err.println("Something went wrong!");
            e.printStackTrace();
            return;
        }
    }

Execute the code and we get this result:

id
name

3.2 Parse with jSqlParser library

But what if we don’t want to execute the sql , or we can’t execute the sql, how to parse sql column names without execute it? You can try the jSqlParser library.

3.2.1 Add the dependency

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

3.2.2 The Utility Method

public static List<String> getColumns(String sql) throws JSQLParserException {
    CCJSqlParserManager parserRealSql = new CCJSqlParserManager();

    Statement stmt = parserRealSql.parse(new StringReader(sql)); // create a jSqlParser Statement from the sql

    List<String> list=new ArrayList<String>(); // contains the columns result

    if (stmt instanceof Select) { // only parse select sql
        Select selectStatement = (Select) stmt; //convert to Select Statement
        PlainSelect ps = (PlainSelect)selectStatement.getSelectBody();

        List<SelectItem> selectitems = ps.getSelectItems();
        selectitems.stream().forEach(selectItem -> list.add(selectItem.toString()));//add the selected items to result
    }
    return list;
}

3.2.3 Now we test it

    public static void main(String[] args) throws JSQLParserException {
        String sql = "select a,b from tbl_city";
        getColumns(sql).stream().forEach(c-> System.out.println(c)); // get a b

        System.out.println("----");

        String sql2 = "select a from (select b from table1 left join table2 on table2.c=table1.d)"; // get a
        getColumns(sql2).stream().forEach(c-> System.out.println(c));

        System.out.println("----");

        String sql3 = "select * from tbl_city";
        getColumns(sql).stream().forEach(c-> System.out.println(c)); // get *


    }

Run the code we got this:

a
b
----
a
---
*

As you can see, the jSqlParser can parse the sql columns without executing it, but if you do not specify the column names in your sql, for example, select * from test, it can not get the right column names. So ,use it with caution.

4. Conclusion

You can parse the sql columns with JDBC, if you choose this way, you must execute the sql on the database, then you would get the real column names. If you can’t or don’t want to execute the sql ,you can try the jSqlParser library. But use it with caution.