Unable to run @Sql with procedure creation before running @DataJpaTest

26 views Asked by At

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 {}
0

There are 0 answers