Using SUM(*) with ResultSet

Using the following code works well:

int i = resultSet.findColumn("MY_COLUMN")

However, what do I use if my SQL call contains a:

"SELECT SUM(MY_COLUMN) FROM ....."?

The first statement no longer locates the column index for MY_COLUMN when I use SUM. How do I find the column for this?

LVL 2
BergJCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mantishCommented:
you can use the prepare statement. that way it will make the execution of the query more effecient .
 
Connection theConnexion = null ;
PreparedStatement theStatement = null ;
String str_col_name = "MY_COLUMN" ;
theStatement = theConnexion.prepareStatement("SELECT SUM(?) FROM .....");
 theStatement.setString(1,str_col_name);
 thestatement.executeQuery();


                                                           mantish
0
BergJCAuthor Commented:
The query itself is not what I'm having a problem with. My code above is after the query has already been executed. I have many SQL statements that select different columns. I have only one method that executes these statements and retrieves the data from the resultSet. That's why I need to use the findColumn method. I know all the columns in the table, but I don't know if they were in the SQL call, so I look for them all.

My problem comes when I try to select the SUM of a column. The findColumn method for that column name no longer "finds" it. I need to know how to use findColumn with a SUM...
0
Ravindra76Commented:
Hi BergJc,

  In my view, when you are locating the column, the reuslt set is becoming in valid or crosiing the your column. Just post the code and the exceptionStackTrace() you encountered

Best of luck
0
siva_gunturuCommented:
hi berqjc

try this one

int i= resultSet.findColumn("SUM(MY_COLUMN)");


:-)
0
JodCommented:
There are two simple solutions to your problem.

1)

Use an alias for the summed column name so that it is called something useful. An alias is just a name you can superimpose on a column. For example:

"SELECT SUM(MY_COLUMN) as SUM FROM table"

[you may need to check exactly how your database specifies an alias name for a column]

Now you can identify the column as

int i = resultSet.findColumn("SUM")

This is the preferred way to handle the problem. You can however also do:

2)

Index into the column directly by using

public int getInt(int columnIndex)
           throws SQLException

Gets the value of a column in the current row as a Java int.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the result is 0

Not as good a solution as it require you to know which column the value is in, but will still work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.