java - 是否可以从 hibernate 动态添加存储过程？

Question

我想使用 hibernate 从 Java 代码动态添加存储过程。我执行了以下操作，但这不断导致错误“

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

我做错了什么？

String dnyLockProcedure = "DELIMITER ||" 
                        +"\n DROP PROCEDURE IF EXISTS `dyn_lock`; "
                        +"\n CREATE PROCEDURE `dyn_lock`(IN `query` TEXT, IN `lock_name` CHAR(64), IN `timeout` TINYINT UNSIGNED)"
                        -- PROCEDURE HERE 
                        +"\n END;"
                        +"\n || "
                        +"\n DELIMITER ;";

                Query queryTest = mEntityManager.createNativeQuery(dnyLockProcedure);
                queryTest.executeUpdate();

编辑:以下是整个过程:

        String dnyLockProcedure ="DROP PROCEDURE IF EXISTS `dyn_lock`; "
                +" CREATE PROCEDURE `dyn_lock`(IN `query` TEXT, IN `lock_name` CHAR(64), IN `timeout` TINYINT UNSIGNED)"
                +" NO SQL "
                +" LANGUAGE SQL "
                +" COMMENT 'Get a lock for dyn cursor' "
                +" BEGIN "
                +" DECLARE lock_res TINYINT UNSIGNED;"
                +" DECLARE CONTINUE HANDLER"
                +" FOR SQLEXCEPTION"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Dynamic SQL returned an error';"
                +" SET lock_res = GET_LOCK(`lock_name`, IFNULL(`timeout`, 5));"
                +" IF (lock_res IS NULL) THEN"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Unknown error';"
                +" ELSEIF (lock_res = 0) THEN"
                +" SIGNAL SQLSTATE VALUE '45000' SET"
                +" MESSAGE_TEXT  = '[dyn_lock] Could not acquire lock: Timeout expired';"
                +" END IF;"
                +" SET @dyn_sql = CONCAT('CREATE OR REPLACE VIEW `', lock_name, '` AS ', query, ';');"
                +" PREPARE stmt_dyn_view FROM @dyn_sql;"
                +" EXECUTE stmt_dyn_view;"
                +" DEALLOCATE PREPARE stmt_dyn_view;"
                +" END;";

Answer 1

所以我发现上面的存储过程出了什么问题:

1. 不需要使用 DELIMITER，从 CREATE PROCEDURE 开始
2. “\n”的使用也不是必须的，但使用后不会提示错误。
3. 不能在同一查询中使用 DROP 和 CREATE；它们应该从单独的查询中调用。

干杯!