JDBC ResultSet 获取带有表别名的列 [英] JDBC ResultSet get columns with table alias

问题描述

假设我有一个类似

SELECT * from table1 a, table2 b where (WHATEVER)

也许两个表都有相同的列名.所以我认为通过

Maybe both tables have the same column name. So I though it would be nice to access the data via

resultSet.getString("a.columnName");
resultSet.getString("b.columnName");

但这适得其反，我一无所获.我阅读了 API，但他们并没有真正谈论这个案例.是否依赖于这样的功能供应商?

But this backfires on me and I get nothing. I read the API, but they don't really talk about this case. Is such a feature vendor dependent?

推荐答案

JDBC 将根据查询中指定的内容简单地命名列 - 它不知道表名等.

JDBC will simply name the columns by what is specified in the query - it doesn't know about table names etc.

您有两个选择:

选项 1:在查询中以不同的方式命名列，即

Option 1: Name the columns differently in the query, ie

SELECT
    a.columnName as columnNameA,
    b.columnName as columnNameB,
    ...
from table1 a, table2 b where (WHATEVER)

然后在你的java代码中引用列别名:

then in your java code refer to the column aliases:

resultSet.getString("columnNameA");
resultSet.getString("columnNameB");

选项 2:在您调用 JDBC API 时参考列position:

Option 2: Refer to the column position in your call to the JDBC API:

resultSet.getString(1);
resultSet.getString(2);

请注意，JDBC API 使用 one-based 索引 - 即它们从 1 开始计数(而不是像 java 索引那样从 0 开始计数)，所以第一列使用 1，第二列使用 2，以此类推

Note that the JDBC API uses one-based indexes - ie they count from 1 (not from 0 like java indexes), so use 1 for the first column, 2 for the second column, etc

我会推荐选项 1，因为引用命名列更安全:有人可能会更改查询中列的顺序，它会默默地破坏您的代码(您将访问错误的列但不知道)，但如果他们更改列名称，您至少会在运行时收到无此类列"异常.

I would recommend option 1, because it's safer to refer to named columns: Someone may change the order of the columns in the query and it would silently break your code (you would be accessing the wrong column but would not know), but if they change the columns names, you'll at least get a "no such column" exception at runtime.

这篇关于JDBC ResultSet 获取带有表别名的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！