Getting Primary key from mysql by given name parameter in Java

Question

I am getting exceptions in my java code. I want to get the Id from the given name, Id in this case is a primary key. The Mysql query works when I do query in the workbench:

SELECT client_id FROM client where concat(lastname, " ", firstname) = "Hudson Kate";

But when I use this query in javacode:

public int getClientID(String name) throws SQLException{
        int ID = 0;
        PreparedStatement myprepStmt = null;
        ResultSet myRs = null;

        try {
            myprepStmt = myConn.prepareStatement("SELECT client_id FROM client "
                    + "where concat(lastname, \" \", firstname) =?");

            myprepStmt.setString(1, name);
            myRs = myprepStmt.executeQuery();

            ID = myRs.getInt("client_id"); //this creates a problem

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally {
            close(myprepStmt, myRs);
        }

        return ID;
    }

I get this exception :

java.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:787)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2460)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2571)
at DBConnection.getClientID(DBConnection.java:135)
at sample.main(sample.java:38)

Answer 1

You forgot to use ResultSet#next:

myRs = myprepStmt.executeQuery();
if (myRS.next()) { //<-- IMPORTANT!
    ID = myRs.getInt("client_id");
}

Here's an explanation of how ResultSet#next works: ResultSet.getString(1) throws java.sql.SQLException: Invalid operation at current cursor position