HOWTO execute CREATE PROCEDURE statement (from a file or String) in Spring Boot FW

create-sp_TEST_STORED_PROC.sql

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

DAOTests.java

// import(s) are omitted
@SpringBootTest
@Log4J2
class DAOTests {
	JdbcTemplate jdbcTemplate;
	@Autowired DataSource testSetupDataSource;

	@BeforeEach
	void setup1() throws SQLException { //using Java String & JdbcTemplate
		jdbcTemplate = new JdbcTemplate(testSetupDataSource);

		jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

		ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
		String sqlSPfile = FileCopyUtils.copyToString(new InputStreamReader(resource.getInputStream()));		

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

		log.info("SQL for creating the SP from String: {}", sqlSPstring);
		log.info("SQL for creating the SP from file: {}", sqlSPfile);
		jdbcTemplate.execute(sqlSPstring);
		//jdbcTemplate.execute(sqlSPfile);
	}
	void setup2() throws SQLException { //using an isolated SQL script and ResourceDatabasePopulator
		jdbcTemplate = new JdbcTemplate(testSetupDataSource);
		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);
	}

	void setup3() throws SQLException { //using an isolated SQL script and ScriptUtils from SpringBoot FW
		jdbcTemplate = new JdbcTemplate(testSetupDataSource);
		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);
	}
}

This sample code here is to demonstrate an answer to Stackoverflow question: Issue with executing procedure in spring boot schema.sql file