Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JDBC ResultSet get columns with table alias

Imagine I have a query like

SELECT * from table1 a, table2 b where (WHATEVER) 

Maybe both tables have the same column name. So I though it would be nice to access the data via

resultSet.getString("a.columnName"); resultSet.getString("b.columnName"); 

But this backfires on me and I get nothing. I read the API, but they don't really talk about this case. Is such a feature vendor dependent?

like image 845
Franz Kafka Avatar asked Aug 28 '11 21:08

Franz Kafka


People also ask

How do I get a list of column names in ResultSet?

You can get the name of a particular column using the getColumnName() method of the ResultSetMetadata interface. This method accepts an integer value representing the index of a column and returns a String value representing the name of the specified column.

How do I get all columns from ResultSet?

You can get the column count in a table using the getColumnCount() method of the ResultSetMetaData interface. On invoking, this method returns an integer representing the number of columns in the table in the current ResultSet object.

Are column aliases allowed in SELECT clause?

Column aliases can be used in the SELECT list of a SQL query in PostgreSQL. Like all objects, aliases will be in lowercase by default.


2 Answers

JDBC will simply name the columns by what is specified in the query - it doesn't know about table names etc.

You have two options:

Option 1: Name the columns differently in the query, ie

SELECT     a.columnName as columnNameA,     b.columnName as columnNameB,     ... from table1 a, table2 b where (WHATEVER) 

then in your java code refer to the column aliases:

resultSet.getString("columnNameA"); resultSet.getString("columnNameB"); 


Option 2: Refer to the column position in your call to the JDBC API:

resultSet.getString(1); resultSet.getString(2); 

Note that the JDBC API uses one-based indexes - ie they count from 1 (not from 0 like java indexes), so use 1 for the first column, 2 for the second column, etc


I would recommend option 1, because it's safer to refer to named columns: Someone may change the order of the columns in the query and it would silently break your code (you would be accessing the wrong column but would not know), but if they change the columns names, you'll at least get a "no such column" exception at runtime.

like image 120
Bohemian Avatar answered Sep 23 '22 06:09

Bohemian


ResultSetMetadata.getColumnLabel() is what you need

(edit) sample example, as stated by bharal in comment

SELECT * from table1 a, table2 b where (WHATEVER)  ResultSetMetaData rsmd = rset.getMetaData(); rsmd.getColumnLabel(1); 
like image 44
Mateen Avatar answered Sep 22 '22 06:09

Mateen