Skip to content
Advertisement

ResultSet: Retrieving column values by index versus retrieving by label

When using JDBC, I often come across constructs like 

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)?

Advertisement

Answer

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?

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

Advertisement