CODEWITHSUNDEEP
javasqljdbc
Hoffmann
Hoffmann

Reputation: 14719

JDBC: Get table names involved in a SQL query

Is there any way to know the name of the tables involved in a SQL query executed via JDBC?

For example:

SELECT r.roleId FROM User u, UserRole r where r.userId = u.userId and u.name = "Jonh Smith"

I don't want only the result set of this query but the actual table names ("User" and "Role"). I don't care about view names, but if there is no way to get the underlying table used in the view it's not a big problem, but this has to work with nested queries.

As for why I need this information it's because I am making a platform that lets other developers build applications on top of it. In my platform I let other developers register their own queries and I store them in a database. I don't want the developers to have to bother with actually manually registering the table names along with the query itself. So at the time I am executing the query I do not really know what that query is doing.

Upvotes: 8

Views: 6252

Answers (2)

Denis Abakumov
Denis Abakumov

Reputation: 422

The JSQLParser library (LGPL V2.1) seems to be capable of doing exactly what is required:

Extract table names from SQL

Statement statement = CCJSqlParserUtil.parse("SELECT * FROM MY_TABLE1");
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
Set<String> tableList = tablesNamesFinder.getTables(selectStatement);

Upvotes: 4

Daniel Kaplan
Daniel Kaplan

Reputation: 67310

I haven't found out how to do exactly what you need but I'm going to post this anyway since it's too long to be a comment. The closest thing I've found is the ResultSetMetaData.

This is a tutorial on how to get it. But that tutorial doesn't show how to get the table names. The method to do that is called getTableName. 

/**
 * Gets the designated column's table name. 
 *
 * @param column the first column is 1, the second is 2, ...
 * @return table name or "" if not applicable
 * @exception SQLException if a database access error occurs
 */
String getTableName(int column) throws SQLException;

I think you'll have to iterate through all the columns and put the table names in a Set. The result should be all the tables used but... the problem is, if the column isn't exposed in the select, then you won't be able to discover the table. I haven't been able to find a way around this.

Upvotes: 4

Related Questions