java - Java(Eclipse)调用MySQL存储过程OUT参数错误

Question

我试图从 Eclipse 中的 Java 代码调用 MySql 存储过程，但出现以下错误。存储过程编译没有任何错误。

导入语句:

import java.sql.CallableStatement;    
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

这是java代码片段:

>cStmt = (CallableStatement) conn.prepareCall("{ call getEmpName(?,?)}");

>cStmt.setString(1, "xxxyyyzzz");

>cStmt.registerOutParameter(2, Types.INTEGER);

>cStmt.execute();

下面是存储过程

>USE `mcaSchema`;
>DROP procedure IF EXISTS `getEmpName`;
>DELIMITER $$
>USE `mcaSchema`$$
>CREATE DEFINER=`root`@`localhost` PROCEDURE `getEmpName`(IN empID int,OUT eName varchar(30)) 
>BEGIN
>>  select ename from mcaSchema.emp where empno = empID; 
>END$$
>DELIMITER ;

请帮我弄清楚我做错了什么。如果这看起来有点天真，请原谅我。我四处寻找解决方案，但未能解决。 ...

Answer 1

尝试:

/* CODE FOR DEMONSTRATION PURPOSES */
DROP PROCEDURE IF EXISTS `getEmpName`;

DELIMITER $$

CREATE PROCEDURE `getEmpName`(IN `empID` VARCHAR(30), OUT `eName` VARCHAR(30)) 
BEGIN
    SELECT CONCAT('NAME: ' , `empID`) INTO `eName`;
END$$

DELIMITER ;
/* CODE FOR DEMONSTRATION PURPOSES */

/* CODE FOR DEMONSTRATION PURPOSES */
...
cStmt = con.prepareCall("{ call `getEmpName`(?, ?) }");
cStmt.setString(1, "MYNAME");
cStmt.registerOutParameter(2, Types.VARCHAR);
boolean hadResults = cStmt.execute();
while (hadResults) {
   rs = cStmt.getResultSet();
   hadResults = cStmt.getMoreResults();
}
String outputValue = cStmt.getString(2);
...
/* CODE FOR DEMONSTRATION PURPOSES */

更新

DROP PROCEDURE IF EXISTS `getEmpName`;

DELIMITER $$

CREATE PROCEDURE `getEmpName`(IN `p_empID` INT, OUT `p_eName` VARCHAR(30)) 
BEGIN
    SELECT `ename` INTO `p_eName`
    FROM `mcaSchema`.`emp`
    WHERE `empno` = `p_empID`; 
END$$

DELIMITER ;