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
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 |