从MySql返回计数（*）会在Java中出现语法错误

I struggle to solve a problem, but I can't understand what is wrong. I have defined the StoredProcedure(below). When I call it from the command-line, it works as expected. But when I call it from my java-class (below) it gives an erreor (below). I have debugged, the in parameters have the expected values. I really hope someone can help me. Thanks !

我很难解决问题,但我无法理解什么是错的。我已经定义了StoredProcedure(下面)。当我从命令行调用它时,它按预期工作。但是当我从我的java类(下面)调用它时,它会给出一个错误(下面)。我已经调试过,in参数有预期的值。我真的希望有人可以帮助我。谢谢 !

error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1

您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在第1行的“()”附近使用正确的语法

JAVA procedure calling Stored Procedure:

调用存储过程的JAVA过程:

boolean CheckProjectExistence(String dbname, int projectid){              
        try(Connection con = init();
            CallableStatement stmt = con.prepareCall("{call sp_project_exists(?,?}"))
        {       
            stmt.setString(1,dbname);
            stmt.setInt(2, projectid);
            rs = stmt.executeQuery();

            while (rs.next()) {
              stmt.getInt("count");
            }
        }catch(SQLException ex){
            System.out.println("Feil med GeneralDAO.CheckProjectExistence "+   ex.getMessage());
        }   

        return false;
    }

MySQL 5.6.30 Stored Procedure definition:

MySQL 5.6.30存储过程定义:

USE PP_Master;
DROP PROCEDURE IF EXISTS `sp_project_exists`; 

DELIMITER //
CREATE PROCEDURE sp_project_exists (IN p_dbname VARCHAR(30),IN p_projectid INT)
BEGIN

SET @tableName = concat(p_dbname, '.Project');

SET @sql_text = 
concat('SELECT COUNT(ProjectId) AS count FROM ',
@tableName, ' WHERE ProjectId=', p_projectid, ';');


PREPARE stmt FROM @sql_text;
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;  

END //
DELIMITER ;

1 个解决方案

#1

Just change the following line...

只需更改以下行...

CallableStatement stmt = con.prepareCall("{call sp_project_exists(?,?}"))

to

CallableStatement stmt = con.prepareCall("{call sp_project_exists(?,?)}"))