Regex to Parse DB Columns like JDBC ResultSet

Question

I am using JDBC to get columns from query results.

For example:

String query = "SELECT foo, bar AS barAlias FROM table";
PreparedStatement preparedStatement = connection.prepareStatement(query);
ResultSet resultSet = preparedStatement.executeQuery();

//Show my "foo" column results
while (resultSet.next()) {
    log.debug(resultSet.getString("foo"));
}

I would like to parse my queries BEFORE running them. Essentially, I want to create an array for column labels that will match the value expected by the resultSet.get** method. For illustration purpose, I would like to replace the loop above by this and get the same results:

//Show my "foo" column results
while (resultSet.next()) {
    log.debug(resultSet.getString(arrayOfColumns.get(0)));
}

This seems easy. I could parse my statement with a simple regex that takes the string between SELECT and FROM, creating groups using the column delimiter, and building arrayOfColumns from the groups. But JDBC has specific ways to deal with aliases for example. For the second column, this naive parser would return the entire "bar AS barAlias" where I believe that resultSet.get** expects "barAlias". It will tell me "The column name bar As barAlias is not valid."

Essentially, I would like to understand the JBDC column label parsing behavior in order to replicate it.

NOTE, I am aware that I can get columns by index from the resultSet object. That is not the objective here. Also, I do not want to use any JDBC related method (I understand that the preparedStatement metadata is available). Think of this as a theoretical questions where do not have a JDBC library and MUST use regular expressions.

Sergey Kalinichenko · Accepted Answer · 2014-08-29 15:04:24Z

3

The best way of obtaining the columns of the result set is to ask JDBC to do it for you. You do not need a ResultSet object for it - ResultSetMetaData would be enough.

PreparedStatement preparedStatement = connection.prepareStatement(query);
ResultSetMetaData metaData = preparedStatement.getMetaData();

for (int i = 1 ; i <= metaData.getColumnCount() ; i++) {
    listOfColumns.add(metaData.getColumnLabel(i));
}

answered Aug 29, 2014 at 15:04

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Frederic Fortier Over a year ago

Thanks! I know that this answer is accurate. I added clarification to my question. What I am interested in is how JDBC arrives to those labels. I am trying to reverse engineer the behavior, not use an existing method.

Sergey Kalinichenko Over a year ago

@FredericFortier JDBC driver usually passes the statement to RDBMS server, which uses its own SQL parser to parse the statement. It may have its own parser as well. Parsing SQL is not a simple task. Consider getting an existing parser (here is one). This is way to hard for a regex, unless your SQL has a very specific format. At the very least you should roll your own recursive descent parser; using ANTRL or some other parser gives you an even better chance to get it right.

Frederic Fortier Over a year ago

This might work based on the description. Have you used this particular parser yourself? The key is that I do not want to use the JDBC drivers but yet arrive to the same column labels. I will give it a try.

Frederic Fortier Over a year ago

Okay :-) So you feel confident that it is aligned with JDBC in the way that it parses column labels?

Sergey Kalinichenko Over a year ago

@FredericFortier Moreover, SQL-92 parser lacks access to metadata from your database, so SELECT * will tell you only that there is an asterisk *; unlike the metadata that you get from your JDBC driver, it wouldn't expand to a list of columns.

|

John Bollinger · Accepted Answer · 2014-08-29 15:04:46Z

1

To get the column names that are associated with your ResultSet, you should consult its ResultSetMetaData. That's going to be both easier and more reliable than parsing the query text.

answered Aug 29, 2014 at 15:04

John Bollinger

191k11 gold badges103 silver badges206 bronze badges

1 Comment

Frederic Fortier Over a year ago

Of course it would be... however, it does not answer the question.

Yanek Martinson · Accepted Answer · 2014-08-29 15:15:03Z

0

So, after having split on the delimiter (comma) to get column names between SELECT and FROM, check each one to see if it contains the string " AS " (you may need to account for upper and lowercase), then if present, split on that, and use the rightmost value.

answered Aug 29, 2014 at 15:15

Yanek Martinson

4164 silver badges3 bronze badges

1 Comment

Frederic Fortier Over a year ago

Not all dbms use AS to represent aliases.

Lukas Eder · Accepted Answer · 2022-09-06 07:48:26Z

0

While I agree with others you should probably use ResultSetMetaData in "ordinary" use-cases, you seem to insist that the query string itself should be parsed. Parsing SQL with regular expressions is brittle, and you should avoid doing that. Better use an off-the-shelf SQL parser, which can do the parsing for you. There are several, including:

With jOOQ, you could just write this:

ctx.parser()
   .parseSelect("SELECT a, b ...")
   .getSelect()
   .stream()
   .forEach(f -> System.out.println(f.getName()));

Disclaimer: I work for the company behind jOOQ

answered Sep 6, 2022 at 7:48

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

Regex to Parse DB Columns like JDBC ResultSet

4 Answers 4

7 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related