java - 在Java中通过给定名称参数从mysql获取主键

Question

我的 java 代码中出现异常。我想从给定的名称中获取 Id，在本例中 Id 是主键。当我在工作台中查询时，Mysql 查询有效:

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

但是当我在 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;
    }

我得到这个异常:

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

您忘记使用ResultSet#next :

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