Java - 如何从 MySQL 过程中获取 ReturnCode？

Question

我在 MySQL 中有这个存储过程

DELIMITER //
create procedure spResetPassword (IN in_email VARCHAR (50))
Begin
 Declare UserId int;
 Declare GUID varchar(64);

 Select UserId = nrIdentifikues
 from punetori
 where Email = in_email;

if UserId IS NOT NULL then
    Set GUID = UUID();

    Insert into tblResetPasswordRequests (ID, UserID, ResetRequestDateTime) Values(GUID, UserId, NOW());

    Select 1 as ReturnCode, GUID as UniqueId, in_email as Email;
else
    SELECT 0 as ReturnCode, NULL as UniqueId, NULL as Email;
end if;
end; //
DELIMITER ;

Java 代码:

Connection con = DB.Connect2DB("semp");

try {
    CallableStatement sm = con.prepareCall("{call spResetPassword(?)}");
    sm.setString(1, txtEmail.getText());
    sm.execute();

    res = sm.getResultSet();
    int rCode = res.getInt("ReturnCode");
    while (res.next()) {
        if (rCode == 1) {
            SendPasswordResetEmail(res.getString("Email"), res.getString("GUID"));
            pnRequestReset.setVisible(false);
            con.close();
        }
    }
} catch (Exception e2) {
    JOptionPane.showMessageDialog(null, e2.toString());
}

问题是 ReturnCode 不是列，因此 res.getInt("ReturnCode"); 将不起作用。我怎样才能访问那个变量？这在 C# 中工作得很好

Answer 1

你必须使用这个:

CallableStatement sm = connection.prepareCall("{? = call spResetPassword(?)}");
//this is the return value----------------------^
sm .registerOutParameter(1, Types.INTEGER);
sm .execute();
int code = sm .getInt(1);

所以如果你的函数返回一些你必须使用的东西:

{? = call name_function(?, ?)}
                        ^--^---------parameters of your function
 ^-----------------------------------return value of your function

---

要么你的函数没有正确定义，它没有返回任何东西，它应该是这样的:

CREATE PROCEDURE spResetPassword (
IN in_email VARCHAR (50)
OUT code INT(1) -- <<--------------note this is how your procedure should return values
)
...