Java中jdbc通过给定的name参数从mysql获取主键

Question

我的java代码中出现了异常。我想从给定的名称中获取Id，在本例中，Id是主键。当我在工作台中进行查询时，Mysql查询会起作用：

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

但当我在Java代码中使用这个查询时：

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;
    }

我有一个例外：

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

你忘了使用^{}：

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

下面是对ResultSet#next工作原理的解释：ResultSet.getString(1) throws java.sql.SQLException: Invalid operation at current cursor position