背景
为了绕过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()