Retrieving Columns by table-alias

From: Dominik Bruhn <dominik(at)dbruhn(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Retrieving Columns by table-alias
Date: 2007-08-30 20:44:38
Message-ID: 46D72C36.9010309@dbruhn.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hy,
I used MySQL for years and now wanted to try out something new (and
perhaps better).

Assume the following Tables:

post:
postid: serial
postuserid: integer
edituserid: integer

user:
userid: serial
username: varchar

Every "post" got two Users accociated to them, the onw who wrote it at
first time (postuserid) and the last user who edited it (edituserid). So
get a list of all post I use the following query:

SELECT * FROM post
LEFT JOIN user AS postuser ON (post.postuserid=postuser.userid)
LEFT JOIN user AS edituser ON (post.edituserid=edituser.userid)

This works fine until I want to retrieve the columns in JDBC:
As "userid" is not a unique label (could be postuser.userid and
edituser.userid) I need another solution.

When using mysql I could write:
ResultSet.getString("postuser.username") for example, so prefix the
column with the table-alias.

This syntax doesnt seem to be avaliable.

Is there another solution?

The only thing I could think of was to name all columns in the query
like this:

SELECT
post.*,
postuser.userid AS postuser_userid,
postuser.username AS postuser_username,
edituser.userid AS edituser_userid,
edituser.username AS edituser_username
FROM post
LEFT JOIN user AS postuser ON (post.postuserid=postuser.userid)
LEFT JOIN user AS edituser ON (post.edituserid=edituser.userid)

BUT: This solution isnt what I want: Everytime I add another column to
the user-table, I have to rewrite ALL Queries in Order to retrieve the
data. I simply want it to access in the Result-Set.

Can anybody give me a hint?

Thanks in advance
Dominik Bruhn

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Andrew Lazarus 2007-08-30 21:32:17 Re: getGeneratedKeys() support?
Previous Message Mateus Belluzzo 2007-08-30 19:27:46 Problems with Postgre8.2 x JDBC4 X ColdFusion7 X Apache1.3