Spring 和 MySQL 存储过程 [英] Spring and MySQL stored procedure

问题描述

我有一个在 MySql 工作台中运行良好的存储过程，但是当它由 Spring 运行时，使用 ResourceDatabasePopulator，它失败并出现语法错误.阅读周围有类似的问题，应该在脚本中设置分隔符，我已经遵循了这些，但同样的错误仍然存​​在.

I have a stored procedure that works fine in the MySql workbench but when it is run by Spring, using the ResourceDatabasePopulator, it fails with a syntax error. Reading around there are similar issues where the delimiter should be set in the script, I have followed these but the same error persists.

这是SP

DELIMITER $$
CREATE PROCEDURE userAttributesOrder()
READS SQL DATA
    BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i INT DEFAULT 0;
    DECLARE attCount INT DEFAULT 0;
        DECLARE cod VARCHAR(64) DEFAULT NULL;
        DECLARE curs CURSOR FOR SELECT CODE FROM PA_ATTR_TYPE ORDER BY NAME ASC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        SET i = 0;    
        OPEN curs;
            read_loop: LOOP
                FETCH curs INTO cod;
                IF done THEN
                    LEAVE read_loop;
                END IF;
                INSERT INTO LAYOUT(ELEMENT_TYPE, ELEMENT_CODE, LAYOUT_ORDER) VALUES ('PA_ATTRIB', cod, i);
                SET i = i + 1;
            END LOOP;
        CLOSE curs;
    END$$
DELIMITER ;

这是个例外

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #4 of resource class path resource [database/updates/23-update.sql]: DELIMITER $$ CREATE PROCEDURE user
AttributesOrder() READS SQL DATA BEGIN DECLARE done INT DEFAULT FALSE; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corre
sponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE userAttributesOrder() READS SQL DATA BEGIN DECLARE' at line 1
        at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:472) ~[spring-jdbc-4.1.6.RELEASE.jar:4.1.6.RELEASE]

有人知道如何解决这个问题吗?正是 Spring 令我感到沮丧，因为使用纯 Java 我不会遇到这个问题.

Does anyone have a clue how to fix this? It is just Spring which I find frustrating as using plain Java I would not have the issue.

包含更新 Spring 代码根据这里的要求，我继承了运行脚本的 Spring 代码.

UPDATE Spring code included As requested here is the Spring code I have inherited that runs the scripts.

DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();

for (String script : orderedScripts) {
    databasePopulator.addScript(new ClassPathResource(script));
}

initializer.setDatabasePopulator(databasePopulator);
initializer.afterPropertiesSet();

推荐答案

我使用 ;; 作为我的分隔符，使用 ResourceDatabasePopulator.setSeperator()

I use ;; as my separator setting it with ResourceDatabasePopulator.setSeperator()

这似乎不会破坏其他东西并在我的 DB IDE 中运行.我的 DB IDE (dbVisualizer) 看到 2 个分隔符并且不在乎.

This doesn't seem to break other stuff and runs in my DB IDE. My DB IDE (dbVisualizer) sees 2 seperators and doesn't care.

这篇关于Spring 和 MySQL 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！