如何在JdbcTemplate中创建一个mySQL存储过程

Question

背景

为了绕过MySql中只允许在存储过程中使用某些语句的问题，我正在尝试在由JdbcTemplate提交的SQL中创建、运行并删除存储过程。一个简化的示例（这恰好是在Spring Boot中）：

@Service
public class StartupDatabaseCheck {
    private JdbcTemplate template;

    @Autowired
    public StartupDatabaseCheck(JdbcTemplate template){
        this.template = template;
    }

    @PostConstruct
    public void init() {
        log.info("Running custom fields table creation (if required)");
        try {
            String migrateSql = Resources.toString(Resources.getResource("migrateScript.sql"), Charsets.UTF_8);
            template.execute(migrateSql);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }
}

migrateScript.sql所在的位置

DELIMITER //
CREATE PROCEDURE migrate()
BEGIN
    IF ((SELECT count(1)
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE table_name = 'custom_field_instance_data'
           and column_name='entity_id' and is_nullable = false) > 0)
    THEN
        alter table custom_field_instance_data MODIFY COLUMN entity_id char(32) null;
    END IF;
END //
DELIMITER ;

call migrate;

drop procedure migrate;

在MySQL Workbench中运行这个命令没有问题，但是在JdbcTemplate中提交时出现错误。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE PROCEDURE migrate_custom_fields()

我理解的情况是因为JdbcTemplate不允许使用那些DELIMITER语句，但是按照该链接中建议的只是删除这些语句会导致其他语法错误。

问题

如何通过JdbcTemplate创建一个MySQL存储过程(或者执行通常只在存储过程中允许的语句)？

注意事项

没有分隔符语句的错误为：

MySQLSyntaxErrorException: 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 'CREATE PROCEDURE migrate_custom_fields()

- Richard Tingle

---

你能否检查一下这篇帖子。看起来驱动程序没有考虑分隔查询。https://stackoverflow.com/questions/56483637/create-mysql-stored-procedure-using-jpa-hibernate/56484367#56484367 - user06062019

@user06062019 太棒了！这个方法可行（更改连接字符串，删除DELIMITER行并将//替换为;）。你想写一个答案吗？我会接受它。如果不是，我会自己回答的。 - Richard Tingle

存储过程应在应用程序部署期间部署到服务器上。应用程序只需调用它们。 - PeterHe

@PeterHe 这不是一个真正的存储过程。真正的用例是“使用IF”，但在mySql中似乎只能在存储过程中实现。 - Richard Tingle

Answer 1

看起来驱动程序没有考虑分隔查询。如果您想使用JDBC动态创建存储过程， 请使用以下属性并将其作为连接参数传递到URL中。

jdbc:mysql://localhost:3306/test?allowMultiQueries=true

上述属性将允许使用 ';' 分隔的查询。您可以在此处找到更多信息： 使用JPA Hibernate创建MySQL存储过程 在这种情况下，更新后的migrateScript.sql将是：

drop procedure IF EXISTS migrate_custom_fields;

CREATE PROCEDURE migrate_custom_fields()
BEGIN
    IF ((SELECT count(1)
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE table_name = 'custom_field_instance_data'
           and column_name='entity_id' and is_nullable = false) > 0)
    THEN
        alter table custom_field_instance_data MODIFY COLUMN entity_id char(32) null;
    END IF;
END ;

call migrate_custom_fields;

drop procedure migrate_custom_fields;

- user06062019