Hello I've been trying to test my JPA repository in Spring using Mysql as BD, but I could have made it.
Specs: Spring boot 3.2 MySql 8.0.36 JUnit5
Mysql statement:
DROP PROCEDURE IF EXISTS get_user_by_id;
DELIMITER $$
CREATE PROCEDURE get_user_by_id(IN END_ID INT)
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= END_ID
DO
INSERT INTO users (id, alias_name, name, last_name, provider, role)
VALUES (counter,
CONCAT('user_', CAST(counter AS CHAR(2))),
CONCAT('user_name_', CAST(counter AS CHAR(2))),
CONCAT('lastname_', CAST(counter AS CHAR(2))),
'google',
'USER');
INSERT INTO credentials (id, email, user_id)
VALUES (counter,
CONCAT('user_', CAST(counter AS CHAR(2))),
counter);
SET counter = counter + 1;
END WHILE;
end $$
DELIMITER ;
CALL get_user_by_id(1, 30);
Error log
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of class path resource [sql/user_test_config.sql]: DELIMITER $$ CREATE PROCEDURE `createUserNum`(IN endColumn INT) BEGIN DECLARE numID INT DEFAULT 1
Caused by: java.sql.SQLSyntaxErrorException: 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 'DELIMITER $$ CREATE PROCEDURE `createUserNum`(IN endColumn INT) BEGIN DECLARE nu' at line 1.
When I run any test in the next class for testing it throws the previous exceptions.
@DataJpaTest(useDefaultFilters = false)
@ActiveProfiles("dev_mysql")
@Sql("classpath:sql/user_test_config.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS)
@Sql("classpath:sql/cleanup.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_CLASS)
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class UserRepositoryTest {}