ResultSet.getXXX() arg - Favour column-name or column-index?

This is something that I've been wondering for a while now; with the ResultSet get methods, you can use either the name of the column or the column index. Which is preferable and why? Or does this depend on personal preferences and from situation to situation? I personally prefer using the column-name but I've seen posts a couple of times advising against it ( without giving reason, of course ).

As I see it, the pros and cons for each:A.Column Name:Pros: - Readability - If you change the query to return fewer columns or in a different order, you needn't worry about the index

Cons: - If the column names change, you need to redo the gets - In case of multiple columns with the same name from different tables, you'll need to qualify the column name

B.Column Index:Pros: - If the column names change, you don't need to worry - No problem in case of multiple columns with the same name

Cons: - Not quite as obvious at first glance - If the query changes, affecting the order/ number of columns, you'll need to re-work

So is there any other reason to prefer one over the other? Something that's not quite obvious like efficiency or overhead?

In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.

So is there any other reason to prefer one over the other? Something that's not quite obvious like efficiency or overhead?

Go for readability and maintainability first. Use column names where possible. Your colleagues will be thankful when they have to understand your code later. It will prevent that they have to count all fields in your query until field 48, to learn what exactly is retrieved by getObject(48). And than have to do that all over again three days later.

Later, when there is a compelling performance reason to do so, and if you can show that it's the use of column names that causes the performance issue, you can switch to indexes. But even then I advise you to mention the column name in a comment.

Tarun, There's also the form of the query. I have a strong preference for: "select columnName1, columnName2, ... columnNameN from table " over "select * from table"

The former makes it explicit what you should be returning. Yes, you have to change your query if the column name changes. Is this a frequent occurrence in your system? For us, it's never happened. And if it did, we would use a view to preserve the old name for some time.

The former also allows you to only return the columns you plan to use. This saves network traffic and therefore time.

Originally posted by Jeanne Boyarsky: Tarun, There's also the form of the query. I have a strong preference for: "select columnName1, columnName2, ... columnNameN from table " over "select * from table"

The former makes it explicit what you should be returning. Yes, you have to change your query if the column name changes. Is this a frequent occurrence in your system? For us, it's never happened. And if it did, we would use a view to preserve the old name for some time.

The former also allows you to only return the columns you plan to use. This saves network traffic and therefore time.

Just to add to Jeanne's point.

The former is always the smart choice... column names are unlikely to change, but table structure changes are not uncommon in an organization. Explicitly referencing your columns preserves your independence from most of those changes. [ October 19, 2007: Message edited by: Paul Campbell ]

Tarun Yadav
Ranch Hand

Joined: Sep 20, 2007
Posts: 134

posted Oct 19, 2007 12:10:00

0

Tarun, There's also the form of the query. I have a strong preference for: "select columnName1, columnName2, ... columnNameN from table " over "select * from table

I've seen this mentioned in forums too and I agree, while it is tempting to use the latter, the former makes it more readable and efficient.

Is this a frequent occurrence in your system? For us, it's never happened

Not really. It's only this unofficial, internal project that I've been handed that's suddenly had all it's database tables modified. I'd used the column names and now I need to modify the code at a fair number of places.

Like I mentioned earlier, I personally prefer using the column names for all the reasons given. It's good to know that I was not doing anything wrong