How can I identify columns when SELECTing from multiple tables with JDBC?

Question

I have two tables that I join on the id-column, they look like:

+-------+
| users |
+----+--+---+
| id | name |
+----+------+

+-------+
| posts |
+-------+------+---------+
| id | user_id | message |
+----+---------+---------+

And now I want to select all posts and include the username, with:

SELECT * FROM posts, users WHERE user_id = users.id

And then I try to get the values with:

ResultSet rs = // SQL
if(rs.next()) {
    rs.getInt("posts.id");
    ...
}

But I get SQLException when executing rs.getInt("posts.id") :

java.sql.SQLException: Column 'posts.id' not found.

How can I get the values from the SQL-query above using JDBC, and JavaDB/Derby as database?

How can I distinguish between the id column in the users and posts table when retrieving values with ResultSet?

Answer 1

You're attempting to retrieve the id value, but you're using "posts.id" to reference it. Don't

All you need is the column name or alias, not the table name as well:

ResultSet rs = // SQL
if(rs.next()) {
  rs.getInt("id");
  ...
}

It would've worked if your column name itself was "posts.id", but I recommend using underscore (_) instead of a period should you choose to update the table.

But I have an id column in both tables, how do i distinguish between them?

---

You need to specify a column alias:

SELECT p.id AS post_id,
       p.name,
       u.id AS users_id, 
       p.user_id, --redundant due to relationship, omit if possible
       u.message
  FROM POSTS p
  JOIN USERS u ON u.id = p.user_id

...and reference that column alias in the Java code:

ResultSet rs = // SQL
if(rs.next()) {
  rs.getInt("post_id");
  ...
}

Answer 2

Solution 1 : use alias

select u.id as uid, u.name, p.id as post_id, p.user_id, p.message from
users u inner join posts p on u.id=p.user_id

Solution 2: remove the duplicate user.id since you already have it in the posts table

select p.user_id, u.name, p.id as post_id, p.message from
users u inner join posts p on u.id=p.user_id

Answer 3

Alias the column names

SELECT
    posts.id posts_id,
    name name,
    id id,
    user_id,
    message
FROM posts 
INNER JOIN users 
    ON posts.user_id = users.id

depending on your sql flavour, that alias may need to be

posts.id as posts_id