jdbctemplate mysql,如何在JdbcTemplate中创建mySQL存储过程

Background

To work around the issue in MySql that certain statements are only permitted within a stored procedure I'm trying to create, run, then drop a stored procedure within sql submitted by a JdbcTemplate. A simplied example would be (this happens to be within 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);

}

}

}

Where migrateScript.sql is

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;

Running this within mySql workbench works fine, but submitted by the JdbcTemplate I get the error

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()

As I understand it thats because those DELIMITER statements are not permitted by JdbcTemplate but just removing them as suggested in that link leads to other syntax errors

Question

How can a mySQL stored procedure be created (or statements usually only allowed with a stored procedure be executed) by a JdbcTemplate

Notes

The error without the deliminator statements is

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()

解决方案

It seems that the driver is not taking the delimited queries into the account.If you want to create an stored procedure on fly using the jdbc.

Using the following property and pass it as the connection parameter in the URL.

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

The above property will allow ';' delimited queries.

You can find more on this at here

Create MySQL stored procedure using JPA Hibernate

The updated migrateScript.sql in this case would be

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;