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时列位置：

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

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

请注意，JDBC API使用基于一个的索引 - 即它们计数来自 1 （不是来自 0 ，如java索引），因此请使用 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屋！