Need java API to parse SQL statements

Question

I am in need of a java API to parse a SQL statement.

For example, I have a SQL query

SELECT
  PRODUCTS.PROD_ID,
  PRODUCTS.PROD_NAME,
  PRODUCTS.PROD_CATEGORY,
  PRODUCTS.PROD_TOTAL_ID
FROM
  PRODUCTS

I need to retrieve the table name "Products" and the column names as "Prog_ID" etc.

Please provide any links to tutorials or code snippets if possible.

possible duplicate of [SQL parser library for Java](http://stackoverflow.com/questions/660609/sql-parser-library-for-java) — codingbadger, Jun 15 '11 at 12:51

score 4 · Answer 1 · edited Aug 16 '13 at 12:36

4

ANTLR is a parser generator that has a SQL grammar, but that might be more than you bargained for.

It would be simpler to write one on your own, as long as your requirements didn't get too crazy. How general do you need to be?

edited Aug 16 '13 at 12:36

Gábor Lipták

9,646
2
59
113

answered May 04 '11 at 10:25

duffymo

305,152
44
369
561

5

Been there done that! Parsing SQL is a bit of a nightmare -- you need to parse a specific dialect (ORACLE,SQLServer etc.). The vast number of keywords, the inconsistent syntax, the overloading of keywords means you need a massive and complex grammar. – James Anderson May 04 '11 at 10:42
I have a very simple requirement, the query posted above is the only format i ll be using. – user727272 May 04 '11 at 12:36
@user727272, in that case, you can construct the ANTLR grammar to suit only that format, and parse it. I would suggest getting a copy of Terrence Parr's book - [The Definitive ANTLR Reference](http://pragprog.com/titles/tpantlr/the-definitive-antlr-reference), if you are finding the tutorials on the ANTLR site to be difficult. – Vineet Reynolds Jun 15 '11 at 06:45
1

parsing SQL is beyond a nightmare... see the post i just made: http://stackoverflow.com/questions/12863532/java-api-for-parsing-recognition-but-not-executing-sql-against-a-db – amphibient Oct 12 '12 at 17:13
No wonder - you tried to mangage vendor-specific SQL with regex? Your situation sounds like a case study for "do not do this." – duffymo Oct 12 '12 at 17:17

score 3 · Answer 2 · answered Jun 15 '11 at 08:27

If you have a connection to the database, you can do that using a prepared statement:

String sql = "....";
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSetMetaData meta = pstmt.getMetaData();

Then you can use ResultSetMetaData.getColumnCount() and getColumnName(int) to retrieve the column names. If your JDBC driver supports it, you can even get the underlying table name using getTableName(int)

Note that not all drivers support getting the metadata before actually executing the statement, you need to test that with the one you are using.

score 2 · Accepted Answer · answered May 05 '11 at 07:08

2

Ended up using ZQL Parsing libraries from http://zql.sourceforge.net/.

If you are having simple queries , that should do the job easily

answered May 05 '11 at 07:08

user727272

475
2
9
20

Please mark this as the Accepted answer unless there is better answer by now. – Shadow The GPT Wizard Jan 08 '12 at 12:32
Does ZQL parser allow us to get table names if we have Joins? – sky May 31 '19 at 05:29

score 1 · Answer 4 · answered Jan 08 '12 at 12:27

Regex matching is only work for simple query, SQL has a complex recursive grammer, and, there will always be some sub select, group by, or literal that will break your regex based parser.

Here is a demo illustrates how to achieve what you need by using general sql parser:

Need java API to parse SQL statements

4 Answers4

Linked