Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

java - Spring Boot MySQL Database Initialization Error with Stored Procedures

In a Spring Boot application I am attempting to initialize some MySQL database tables and stored procedures before running integration tests by placing a schema.sql file in my resources directory as recommended in the documentation.

The create table statements work but the create procedure statements throw an exception. A sample schema.sql file statement that causes the exception is shown below:

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END;

The issue is that the ; character within the stored procedure is being parsed out by the Spring ScriptUtils class that parses the schema.sql file before executing it, which then causes MySQL to throw a syntax error on the script.

I have looked at the ScriptUtils class and have not been able to find a way to escape the ; characters with the procedures. Using \ and as escape characters did not work either, as well as the MySQL DELIMITER command.

Has anyone been able to create MySQL stored procedures using the schema.sql file with Spring Boot? If so could they show an example?

For some additional information, the following Spring JIRA issue addresses the same topic but was closed with a Won't Fix label.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The answer turned out to be very simple. Spring Boot has a DataSource separator property that can be set in the application.properties file:

spring.datasource.separator=^;

Then in the schema.sql file all ; statements not within the stored procedure need to be updated with the new separator.

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END ^;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...