使用JDBC从多个表中进行选择时，如何识别列? [英] How can I identify columns when SELECTing from multiple tables with JDBC?

问题描述

我在id列上加入了两个表，它们看起来像:

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");
    ...
}

但是执行rs.getInt("posts.id")时得到SQLException:

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

如何使用JDBC和JavaDB/Derby作为数据库从上述SQL查询中获取值?

使用ResultSet检索值时，如何区分users和posts表中的id列?

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

推荐答案

您正试图检索id值，但是您正在使用"posts.id"来引用它. 不要

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");
  ...
}

如果您的列名本身是"posts.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.

您需要指定列别名:

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

...并在Java代码中引用该列别名:

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

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

这篇关于使用JDBC从多个表中进行选择时，如何识别列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！