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
PRODUCTSI 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.
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?
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.
Ended up using ZQL Parsing libraries from http://zql.sourceforge.net/.
If you are having simple queries , that should do the job easily
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:
