ResultSet column not explicitly named in the query

Question

Reading this official documentation:

For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

What is a column not explicitly named in the query? What do the they mean with that?

Thanks,

Indeed ItIs

Bohemian · Accepted Answer · 2013-09-22 07:10:26Z

1

Columns that are calculated/expressions have arbitrary names, eg

select max(salary) from employee

if you examined the JDBC column name, it is usually called the same as the expression - ie in the rowset the column may be called literally "max(salary)", but it's database specific and unreliable.

You have two choices:

Use the column number (one-based), eg rowset.get(1)
Name the column in the query,

Ie

select max(salary) as max_salary from employee

I much prefer the second option - it's much less brittle and easier to understand.

edited Sep 22, 2013 at 7:10

answered Sep 22, 2013 at 6:59

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Indeed ItIs Over a year ago

what if it was SELECT ID, max(salary) from employee. Would it be guaranteed that max(salary would be the second row?

Bohemian Over a year ago

@IndeedItIs (you mean column) yes. The order of columns is exactly as per the query. It's OK to use numbers, but if someone adds a column to the query at the start, your code will break (you'll have to change all the column numbers in JDBC code), but names will not have to be altered. It's also easier to read the code.

Indeed ItIs Over a year ago

cool Bohemian nice point, it's funny that the API I linked suggest to use numbers instead as it's more efficient. I would go wit your insight if I had to choose.

Coffee Monkey · Accepted Answer · 2013-09-22 07:01:50Z

It's columns like count(*) or a function result like lower() that are not explicitly named in the query.

M Sach · Accepted Answer · 2013-09-22 07:03:08Z

If you have

 select count(*) from employees // query 1

Now you can not use rs.getString(columnLabel) unless you do

select count(*) as total Number from employees

 rs.getString("total Number") unless you do

So it makes sense to use

 getString(int columnIndex) with query 1 instead of rs.getString(columnLabel)

With above example below sentence makes complete sense

For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

Collectives™ on Stack Overflow

ResultSet column not explicitly named in the query

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related