Using ResultSetMetaData with same column name in 2 tables

Question

I want to get column names from 2 tables and then their value using ResultSetMetaData and ResultSet. Let's say table myTable has columns x, y and z. I do

 Select * from table myTable A,myTable B where A.x = B.y

and I want to use B.z 's value in my code.

ResultSetMetaData will list columns as x y z x y z , so how do I differentiate z's value from aliases A and B?

I need to use * in the query since it improves my performance. So I can't mention column names there. Is there any way I can get the value of column B.z using column names of ResultSet? I mean is there any way that ResultSetMetaData will list columns as A.x, A.y, A.z, B.x, B.y, and B.z ?

Answer 1

If you can't or don't want to specify the columnnames (with aliases), then you will need to retrieve the values by index, not by column label. The ResultSet API doc explicitly says:

When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

(emphasis mine)

If you want to differentiate between columns then you can use ResultSetMetaData.getTableName, but it won't help you with retrieval (except to find the "right" index to use). Note that not all databases support providing the table name.

Answer 2

Looks more duplicate of JDBC ResultSet get columns with table alias.

Use rs.getMetaData().getTableName(colid) which provides the table name of each column and you have to generate your own hashmap with key as tablename and value as list of columnnames.