This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes ResultSet.getXXX() arg - Favour column-name or column-index? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet.getXXX() arg - Favour column-name or column-index?" Watch "ResultSet.getXXX() arg - Favour column-name or column-index?" New topic
Author

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

Tarun Yadav
Ranch Hand

Joined: Sep 20, 2007
Posts: 134
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?

[ October 19, 2007: Message edited by: Tarun Yadav ]
[ October 19, 2007: Message edited by: Tarun Yadav ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

B.Column Index:
Pros:
- If the column names change, you don't need to worry
....
You will still have to rewrite the sql statement.

B.Column Index:
Pros:
....
- If you change the query to return fewer columns or in a different order, you needn't worry about the index
When the order of the fields change, you will have to change the index too.

From Institute of Applied Mathematics - University of British Columbia:
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.
Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Tarun Yadav
Ranch Hand

Joined: Sep 20, 2007
Posts: 134
You will still have to rewrite the sql statement.

Ah, yes, didn't realize that!

When the order of the fields change, you will have to change the index too.

Eep! That was mistake while posting . I copy-pasted the first set to avoid having to re-type most of it. I've edited that now.

Thank you Jan, that about clears it up.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

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.

Regards, Jan
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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
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

Thanks everyone, your inputs are much appreciated.
Tarun
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet.getXXX() arg - Favour column-name or column-index?
 
Similar Threads
Doubt in forwarding to Servlet/JSP in another Application
A big confusion -- pls help...
reading from XML
Wanted: conceptual tutorial on JTable
Passed SCWCD 1.4 with 89%