Geting data from joined tables

Thread view:

Bernd Treine - 22 Oct 2006 10:13 GMT

Hi all,

What would be the best method of getting data from a Resultset that
has multiple joined tables?

Example

select
t1.*,
t2.*
from
table1 t1,
table1 t2
where ....

The issue is that in SQL, I can distinguish between fields of t1 and
t2 although they have the same name. In JDBC I get an error.

I can't write:

Resultset.getString("t1.field1")
and
Resultset.getString("t2.field1")
Resultset just cannot distinguish the 2 fields by the "t1." qualifier.

I know I can work around with
Resultset.getString(columnIndex).

But that does not answer my question.

Many thanks

Bernard

Reply to this Message

Bill - 22 Oct 2006 12:56 GMT

Hi,

you normally never do a SELECT * - 'cos if someone adds a new column /
or rearranges the columns - you'd have problems.

How about

select t1.col1 as t1_col1,
t2.col2 as t2_coll1
from table_1 as t1
table_2 as t2
where t1.key = t2.key etc

Bill

> Hi all,
>
[quoted text clipped - 29 lines]
>
> Bernard

Reply to this Message

Bernd Treine - 26 Oct 2006 07:40 GMT

Bill

I want to be able to do exactly what you recommend to avoid.

1) I don't want to be forced to hand-code field lists in select
statements.

2) I don't want that the code breaks if someone adds a column.

If I could do this within the JDBC API such as
Resultset.getString("t1.col1") than everything would be 100%.

In fact there is a database that just works like this. Look at
quadcap.com.

Regards

Bernard

>Hi,
>
[quoted text clipped - 44 lines]
>>
>> Bernard

Reply to this Message

Mladen Adamovic - 22 Oct 2006 13:02 GMT

You can use

select t1.id as t1_id, t2.id as t2_id, t1.name as t1_name, t2.name as
t2_name from ....

and you can use Resultset.getString("t1_id")

> Hi all,
>
[quoted text clipped - 29 lines]
>
> Bernard

Signature

Mladen Adamovic
http://www.online-utility.org
http://www.cheapvps.info
http://www.vpsreview.com

Reply to this Message

Geting data from joined tables

Free Magazines