Java 结果集：按索引检索列值与按标签检索

ResultSet: Retrieving column values by index versus retrieving by label

提问by Rorick

When using JDBC, I often come across constructs like

在使用 JDBC 时，我经常遇到类似的结构

ResultSet rs = ps.executeQuery();
while (rs.next()) {
    int id = rs.getInt(1);
    // Some other actions
}

I asked myself (and authors of code too) why not to use labels for retrieving column values:

我问自己（以及代码作者）为什么不使用标签来检索列值：

int id = rs.getInt("CUSTOMER_ID");

The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion.
So could someone give me good explanation of avoiding to retrieve column values by column index instead of column label? What are pros and cons of both approaches (maybe, concerning certain DBMS)?

我听过的最好的解释是关于性能的。但实际上，它是否使处理速度极快？我不相信，尽管我从未进行过测量。即使通过标签检索会慢一点，但在我看来，它提供了更好的可读性和灵活性。
那么有人能给我很好的解释，避免通过列索引而不是列标签检索列值吗？这两种方法的优缺点是什么（也许，关于某些 DBMS）？

采纳答案by Martin Klinke

You should use string labels by default.

默认情况下，您应该使用字符串标签。

Pros:

优点：

• Independence of column order
• Better readability/maintainability

• 列顺序的独立性
• 更好的可读性/可维护性

Cons:

缺点：

• You have no control over the column names (access via stored procedures)

• 您无法控制列名（通过存储过程访问）

Which would you prefer?

你更喜欢哪个？

ints?

整数？

int i = 1;  
customerId = resultSet.getInt(i++);  
customerName = resultSet.getString(i++);  
customerAddress = resultSet.getString(i++);

or Strings?

或字符串？

customerId = resultSet.getInt("customer_id");  
customerName = resultSet.getString("customer_name");  
customerAddress = resultSet.getString("customer_address");

And what if there is a new column inserted at position 1? Which code would you prefer? Or if the order of the columns is changed, which code version would you need to change at all?

如果在位置 1 插入了一个新列怎么办？你更喜欢哪个代码？或者，如果更改了列的顺序，您需要更改哪个代码版本？

That's why you should use string labels by default.

这就是默认情况下您应该使用字符串标签的原因。

回答by zloster

The JDBC driver takes care for the column to index look-up. So if you extract values by column name each time the driver makes a look-up (usually in hash map) to check the corresponding index for the column name.

JDBC 驱动程序负责索引查找的列。因此，如果每次驱动程序进行查找（通常在哈希映射中）以检查列名的相应索引时，都按列名提取值。

回答by Cowan

Warning: I'm going to get bombastic here, because this drives me crazy.

警告：我要在这里夸夸其谈，因为这让我发疯。

99%* of the time, it's a ridiculous micro-optimization that people have some vague idea makes things 'better'. This completely ignores the fact that, unless you're in an extremely tight and busy loop over millions of SQL results all the time, which is hopefully rare, you'll never notice it. For everyone who's not doing that, the developer time cost of maintaing, updating, and fixing bugs in the column indexing are far greater than the incremental cost of hardware for your infinitesimally-worse-performing application.

在 99%* 的情况下，人们有一些模糊的想法让事情变得“更好”，这是一种荒谬的微观优化。这完全忽略了，除非你在一个超过百万次的SQL非常紧张，繁忙的循环是导致这一事实所有的时间，这是罕见的希望，你永远也不会注意到它。对于没有这样做的每个人来说，维护、更新和修复列索引中的错误的开发人员时间成本远远大于性能极差的应用程序的硬件增量成本。

Don't code optimizations like this in. Code for the person maintaining it. Then observe, measure, analyse, and optimize. Observe again, measure again, analyse again, and optimize again.

不要像这样编写优化代码。为维护它的人编写代码。然后观察、测量、分析和优化。再观察、再测量、再分析、再优化。

Optimization is pretty much the last step in development, not the first.

优化几乎是开发的最后一步，而不是第一步。

* Figure is made up.

* 图是编出来的。

回答by Sietse

I don't think using the labels impacts performance by much. But there is another reason not to use Strings. Or ints, for that matter.

我认为使用标签不会对性能产生太大影响。但是还有另一个不使用Strings 的原因。或者ints，就此而言。

Consider using constants. Using an intconstant makes the code more readably, but also less likely to have errors.

考虑使用常量。使用int常量会使代码更易读，但也不太可能出错。

Besides being more readable, the constant also prevents you from making typo's in the label names - the compiler will throw an error if you do. And any IDE worth anything will pick it up. This is not the case if you use Strings or ints.

除了更具可读性之外，该常量还可以防止您在标签名称中输入拼写错误 - 如果您这样做，编译器会抛出错误。任何有价值的 IDE 都会选择它。如果您使用Strings 或 ，则情况并非如此ints。

回答by Cha2lenger

I agree with previous answers that performance is not something that can force us to select either of the approaches. It would be good to consider the following things instead:

我同意之前的答案，即性能并不能迫使我们选择任何一种方法。最好考虑以下事项：

• Code readability: for every developer reading your code labels have much more sense than indexes.
• Maintenance: think of the SQL query and the way it is maintained. What is more likely to happen in your case after fixing/improving/refactoring SQL query: changing the order of the columns extracted or changing result column names. It seems for me that changing the order of the columns extracted (as the results of adding/deleting new columns in result set) has greater probability to happen.
• Encapsulation: in spite of the way you choose try to isolate the code where you run SQL query and parse result set in the same component and make only this component aware about the column names and their mapping to the indexes (if you decided to use them).

• 代码可读性：对于每个阅读你的代码标签的开发人员来说，比索引更有意义。
• 维护：考虑 SQL 查询及其维护方式。在修复/改进/重构 SQL 查询后，您的情况更有可能发生什么：更改提取列的顺序或更改结果列名称。在我看来，更改提取列的顺序（作为在结果集中添加/删除新列的结果）发生的可能性更大。
• 封装：不管您选择哪种方式，尝试隔离在同一组件中运行 SQL 查询和解析结果集的代码，并仅让该组件知道列名及其到索引的映射（如果您决定使用它们） ）。

回答by databyss

Using the index is an attempt at optimization.

使用索引是一种优化尝试。

The time saved by this is wasted by the extra effort it takes the developer to look up the necessary data to check if their code will work properly after the changes.

这样节省的时间被开发人员花费额外的精力来查找必要的数据以检查他们的代码在更改后是否可以正常工作而浪费了。

I think it's our built-in instinct to use numbers instead of text.

我认为使用数字而不是文本是我们与生俱来的本能。

回答by Vinod Singh

Besides the look up in Map for labels it also leads to an extra String creation. Though it will happens on stack but still it caries a cost with it.

除了在 Map 中查找标签之外，它还导致额外的 String 创建。虽然它会发生在堆栈上，但它仍然会带来成本。

It all depends on the individual choice and till date I have used only indexes :-)

这一切都取决于个人的选择，到目前为止我只使用了索引:-)

回答by Vinod Singh

I did some performance profiling on this exact subject on an Oracle database. In our code we have a ResultSet with numerous colums and a huge number of rows. Of the 20 seconds (!) the request takes to execute method oracle.jdbc.driver.ScrollableResultSet.findColumn(String name) takes about 4 seconds.

我在 Oracle 数据库上对这个确切的主题进行了一些性能分析。在我们的代码中，我们有一个包含大量列和大量行的 ResultSet。在 20 秒 (!) 中，请求执行方法 oracle.jdbc.driver.ScrollableResultSet.findColumn(String name) 大约需要 4 秒。

Obviously there's something wrong with the overall design, but using indexes instead of the column names would probably take this 4 seconds away.

显然整体设计有问题，但使用索引而不是列名可能会花费 4 秒的时间。

回答by Vinod Singh

Sure, using column names increases readibility and makes maintenance easy. But using column names has a flipside. As you know, SQL allows multiple column names with same name, there's no guarantee that the column name you typed in the getter method of resultSet actually points to the column name you intend to access. In theory, using index numbers instead of column names is preffered, but it reduces the readability...

当然，使用列名会增加可读性并使维护变得容易。但是使用列名有一个反面。如您所知，SQL 允许多个具有相同名称的列名，不能保证您在 resultSet 的 getter 方法中键入的列名实际上指向您要访问的列名。理论上，最好使用索引号而不是列名，但它降低了可读性......

Thanks

谢谢

回答by Kevin Brock

The answer has been accepted, none-the-less, here is some additional information and personal experience that I have not seen put forward yet.

答案已被接受，尽管如此，这里还有一些我尚未看到的其他信息和个人经验。

Use column names (constants and not literals is preferred) in general and if possible. This is both clearer, is easier to maintain, and future changes are less likely to break the code.

如果可能，一般使用列名（首选常量而不是文字）。这既更清晰，更易于维护，并且未来的更改不太可能破坏代码。

There is, however, a use for column indexes. In some cases these are faster, but not sufficiently that this should override the above reasons for names*. These are very valuable when developing tools and general methods dealing with ResultSets. Finally, an index may be required because the column does not have a name (such as an unnamed aggregate) or there are duplicate names so there is no easy way to reference both.

但是，列索引有其用途。在某些情况下，这些速度更快，但不足以覆盖上述名称*的原因。这些在开发处理ResultSets 的工具和通用方法时非常有价值。最后，可能需要索引，因为该列没有名称（例如未命名的聚合）或存在重复名称，因此没有简单的方法来引用两者。

*Note that I have written some JDBC drivers and looked inside some open sources one and internally these use column indexes to reference the result columns. In all cases I have worked with, the internal driver first maps a column name to an index. Thus, you can easily see that the column name, in all those cases, would always take longer. This may not be true for all drivers though.

*请注意，我已经编写了一些 JDBC 驱动程序并查看了一些开源驱动程序，这些驱动程序在内部使用列索引来引用结果列。在我使用过的所有情况下，内部驱动程序首先将列名映射到索引。因此，您可以很容易地看到，在所有这些情况下，列名总是需要更长的时间。但是，这可能不适用于所有驱动程序。