logo标签列表

Spring和MySQL存储过程

mysqlspringstored-proceduresspring-jdbc
3
我有一个存储过程在MySQL Workbench中运行良好，但是当Spring使用ResourceDatabasePopulator运行时，会出现语法错误。 查阅资料发现类似问题应该在脚本中设置分隔符，我已经按照这个方法操作，但是仍然存在相同的错误。
这是存储过程：
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，我就不会遇到这个问题。

更新：包括Spring代码 按要求，这是我继承的运行脚本的Spring代码。

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();
1
那个脚本无法与ResourceDatabasePopulator一起使用，因为它期望;作为分隔符，并且使用它来确定行的结束。 - M. Deinum
@M. Denium你好，感谢回复。是的，我意识到这是个问题，但问题是如何修复它以使其运行。您肯定不能说ResourceDatabasePopulator不能与存储过程一起使用！设置DELIMITER没有任何区别，并且在Spring中使用databasePopulator.setSeparator("$$")设置分隔符会导致所有其他脚本失败，这是可以预料的。有人知道我如何修复这个问题，以便我可以使用ResourceDatabasePopulator运行存储过程吗？ - Gurnard
你不能这样做。而且你不是在运行存储过程，你是在定义它。作为一种解决方法，你可以尝试将其放入单独的 ResourceDatabasePopulator 中或者实现自己的 DatabasePopulator 并设置分隔符，并对其他脚本使用默认的 DatabasePopulator。 - M. Deinum
是的，我在下一行定义并运行它...但还是谢谢。很高兴知道Spring再次限制了我想做的事情。我将创建一个临时表，形成我的数据，然后复制过去。感谢您的回复。 - Gurnard
2个回答
1
我使用 ;; 作为分隔符，通过 ResourceDatabasePopulator.setSeperator() 进行设置。
这似乎不会影响其他内容，并且可以在我的数据库 IDE 中运行。我的数据库 IDE（dbVisualizer）看到两个分隔符也没有关系。
0

我之前也遇到了这个问题，但现在我找到了解决方案。实际上有三种！所以你可以选择任何一种适合你的需求。但关键是正确格式化的String，它代表了创建SP的DB脚本。 我还没有想出如何将删除和创建SP组合成一个SQL脚本/字符串，但无论如何，我对这些解决方案感到满意。

FYI： 无需在类或方法级别上进行特殊注释或设置。 我正在使用Spring FW，它简化了事情，因此不需要对所有内容进行低级实现。 在搜索和尝试各种绝对有效的“提示”之后，以下资源帮助我确保了有效的解决方案：

解决方案1：使用JdbcTemplate和Java String

在格式良好的String sql脚本中保留尾随空格非常重要。

        String sqlSP = "CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) " +
                "BEGIN " +
                    "SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; " +
                "END";

        jdbcTemplate.execute(sqlSP);

解决方案2：使用ResourceDatabasePopulator和ClassPathResource从文件中加载SQL脚本

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
        databasePopulator.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);
        databasePopulator.execute(testSetupDataSource);

解决方案三：使用ScriptUtils和ClassPathResource从文件中加载SQL脚本

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ScriptUtils.executeSqlScript(Objects.requireNonNull(testSetupDataSource).getConnection(), new EncodedResource(resource),
                false, false,
                ScriptUtils.DEFAULT_COMMENT_PREFIX, ScriptUtils.EOF_STATEMENT_SEPARATOR,
                ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER, ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);

create-sp_TEST_STORED_PROC.sql脚本的内容

对于解决方案＃1和＃2，适用与第一个解决方案相同的规则：

  • 格式和空格字符的存在非常重要，尤其是每行末尾的额外尾随空格。

因此，下面的代码由我的vim设置表示空格字符进行存档。

CREATE·PROCEDURE·test_stored_proc·(IN·pInstanceId·varchar(255))~¬
BEGIN~¬
–→SELECT·*·FROM·vw_subscriptions·WHERE·instanceId·=·pInstanceId;~¬
END;~¬

我相信它在内部被表示为一行字符串：

CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) BEGIN SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; END

几乎完整的源代码可以在我的GitHub上找到。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接