java - 参数 2 未注册为输出参数

Question

我在运行时遇到此错误:

try {
        String email = request.getParameter("email");
        String password = request.getParameter("password");
        System.out.println(password);
        statement = connection.createStatement();
        CallableStatement cStatement = connection.prepareCall("{call login(?,?,?)}");
        cStatement.setString(1, email);
        cStatement.setString(2, password);
        cStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
        cStatement.execute();

        String mail = cStatement.getString(2);
        System.out.println("your mail is:" + mail);

        response.sendRedirect("confirm.jsp");

    }catch (Exception e) {
        // TODO: handle exception

    System.out.println(e + " sorry it error");
    }

存储过程是:

 DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(IN emailName varchar(128),pass varchar(50),OUT emailid varchar(255) )
BEGIN
Select email INTO emailid from magento_user_java where email = emailName and password = pass;
END

请解决这个问题。

谢谢

Answer 1

您的语句String mail = cStatement.getString(2);参数输入错误。

在您的存储过程中，您有 3 个参数，其中第 3 个是输出参数。并且您已按照以下声明注册了相同的内容。

cStatement.registerOutParameter(3, java.sql.Types.VARCHAR);

因此，只能使用getXXX方法调用参数3。
以下声明可能会解决您的问题。

String mail = cStatement.getString( 3 );

<小时/>

一些在线代码片段供您引用:

存储过程:

CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
       p_userid IN DBUSER.USER_ID%TYPE,
       o_username OUT DBUSER.USERNAME%TYPE,
       o_createdby OUT  DBUSER.CREATED_BY%TYPE,
       o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN

  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  DBUSER WHERE USER_ID = p_userid;

END;
/

通过 CallableStatement 调用存储过程:

//getDBUSERByUserId is a stored procedure
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);

// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();

String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);

<小时/>

引用:

• MySQL: java.sql.CallableStatement
• JDBC CallableStatement – Stored Procedure OUT Parameter Example