Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group