How to execute an scalar query ?

[raminaa niilian]

Hi
Thank you for reading my post.
I need to retrive just one column of one rows of a table , so i need just one value .
for example my statement is :

select total_login from login where userid=12

now i want to know , what is best way to execute and retrive the value total_login.
should i create a ResultSet for this query or jdbc provide specefic querying for this condition.


thanks

[stu derby]

ResultSet is the only way.

And by the way, a common mistake of people getting just one row is to forget to call next() on the ResultSet, to advance to the first row....

[Jeanne Boyarsky]

Raminaa,
As Stu noted, you still need a ResultSet. This is a common pattern:

If you know a value will be returned, like when doing "select count(*)" you can use:

rs.next();
int total = rs.getInt(1);

In your case, you don't know whether a record will be found, so you should use:

int total = 0;
if (rs.next() ) {
  total = rs.getInt(1);
}}

[raminaa niilian]

Thank you for your helps.
I thought there is some way to get just one value out of statement.
i remember that there was such thing in .net .

but i looks like that in java land we must use a resultSet and bear its creation distruction fee :-)

Thanks again

[Nguyen Van Huong]

Hi,
I am trying to create a function to return a string value, that is

public String generateEmpID(Connection conn){
        String empid="";
        int sequence=0;
        try {
            Statement st=conn.createStatement();
            ResultSet rs=st.executeQuery("select empid_seq.NEXTVAL from dual;");
            if (rs.next() ) {  
              sequence = rs.getInt(1);  
            }
            empid="EMP"+Integer.toString(sequence);
        } catch (SQLException ex) {
            Logger.getLogger(Global.class.getName()).log(Level.SEVERE, null, ex);
        }
        return empid;
    }

I also tested the function but it wasn't sucess
error:
Nov 28, 2011 12:40:47 AM generateEmpID
SEVERE: null
java.sql.SQLException: ORA-00911: invalid character

Thanks.

[Rob Spoor]

Welcome to the Ranch!

Please UseCodeTags next time. See how much better your post looks with them. And it immediately pointed me to the ; inside your query. Remove that and try again.

[Nguyen Van Huong]

Of course,

 ResultSet rs=st.executeQuery("select empid_seq.NEXTVAL from dual");  

Thanks!

[Rob Spoor]

You're welcome

[Pete Nelson]

raminaa niilian wrote:I thought there is some way to get just one value out of statement. i remember that there was such thing in .net .

but i looks like that in java land we must use a resultSet and bear its creation distruction fee :-)

The difference is in coding semantics, the same creation/destruction "fees" are incurred regardless of using Java or C#. If you find yourself using single-row result sets a lot, write your own SingleRowResultSet as a wrapper around ResultSet. Then your SingleRowResultSet wrapper can determine what to do when zero rows are returned, as Jeanne Boyarsky mentioned.