db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Tablealias as column qualifier in resultset
Date Thu, 07 Dec 2006 07:59:07 GMT
Per Newgro <per.newgro@gmx.ch> writes:

> Hi Rajesh,
>
>> Hello Per,
>>
>> I have never seen any examples of using "tableName.columnName" with the
>> ResultSet.getObject() method.
>> My understanding   is  the ResultSet.getObject () is expected to use
>> only the column names, hence using
>> the table name qualifier will  not work.  Using only the column names
>> should be right approach as
>> you noticed (getObject("A"))
>>
> But this is a valid sql syntax or? Is the meaning of that, that it's not 
> supported by the jdbc-spec or only by derby driver?

This is defined by the JDBC spec, and it is implemented correctly by
Derby as far as I can see. Here's the relevant part of the spec:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html

  Column names used as input to getter methods are case
  insensitive. 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, there is
  no way for the programmer to guarantee that they actually refer to
  the intended columns.

>> For cases where the same column names appear in joins of two tables, I
>> would qualify those
>> columns with the respective tables in the query and  use the
>> *getObject*(int columnIndex)  instead:
> But this means i have to map the column index and the object field. Thats 
> impossible, because the resultset order of fields depends on create table 
> statement, which is externally managed from my application (in a sql script 
> file). I.e. i can not guarantee the correct mapping.

Since you know the column names, you could list the columns explicitly
in your SQL query instead of using *.

  select mytable.a, mytable.b, ...

Then you know that A is the first column, B is the second, and so on.

Another option is to use ResultSet.getMetaData() to retrieve a
ResultSetMetaData object from which you can find the name and the
table of each column. See ResultSetMetaData.getColumnName(int column)
and ResultSetMetaData.getTableName(int column).

-- 
Knut Anders

Mime
View raw message