Getting one value from SQL select statement in Jav

Question

I'm trying to return a value from a select statement. Its only one value because the value I'm returning is from the primary key column.

The SQL statement is SELECT itemNo FROM item WHERE itemName = 'astringvalue';

My method for getting the value looks like this:

private String viewValue(Connection con, String command) throws SQLException 
    {
        String value = null;
        Statement stmt = null;

        try 
        {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(command);

            while (rs.next())
                value = rs.toString();
        } 

        catch (SQLException e ) 
        {
            e.printStackTrace();
        } 

        finally
        {
            if (stmt != 
            null) { stmt.close(); }
        }

        return value;

    }

I do have a getConnection() method too.

Here is what im using to call the viewValuemethod:

if((action.getSource() == btnSave) ||(action.getSource() == btnSavePrint) )
        {
            String findItemNoCommand = "SELECT itemNo FROM `item` WHERE itemName = '" + itemList.getSelectedItem() + "'";

            try 
            {
                itemNo = viewValue(conn, findItemNoCommand);
            }
            catch (SQLException e) 
            {
                e.printStackTrace();
            }

            System.out.println(itemNo);
        }

The code above was written for a ButtonHandler

Right now, for the println I'm getting a "com.mysql.jdbc.JDBC4ResultSet@1e72cae" .. I don't understand how it is so.. but I'm assuming that ResultSet is the wrong choice here.

My question is.. what can i use there that can work?

Any help or clue as to what im doing wrong is much appreciated.

Answer 1

You need to change

while (rs.next())
    value = rs.toString();

to

 while (rs.next())
    value = rs.getString("itemNo");

Answer 2

Try This:

private String viewValue(Connection con, String command) throws SQLException 
{
    String value = null;
    Statement stmt = null;

    try 
    {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(command);

        while (rs.next())
            value = rs.getString(1);
    } 

    catch (SQLException e ) 
    {
        e.printStackTrace();
    } 

    finally
    {
        if (stmt != 
        null) { stmt.close(); }
    }

    return value;

}

Answer 3

change it to

if(rs.next())

and

rs.getString("itemNo");

works !!!

Answer 4

Use rs.getInt(1) or rs.getString(1) to retrieve the actual value from the ResultSet. Then read a JDBC tutorial.

Answer 5

Change :

value = rs.toString();

To:

value = rs.getString(1);

rs.toString returns the result of the toString method of the Object ResultSet. rs.getString(1) gives you the first parameter of the resultset as a String.

Answer 6

Right now, for the println I'm getting a "com.mysql.jdbc.JDBC4ResultSet@1e72cae"

is because you return the ResultSet object here , value = rs.toString();

From docs,

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row. The method ResultSet.next moves the cursor to the next row. This method returns false if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next method with a while loop to iterate through all the data in the ResultSet.

You should tell the result set to get the value from the column ,

value = rs.getString(1);

through index

value = rs.getString("itemNo");

or through column name