Spring @Sql, @SqlGroup and @SqlMergeMode

The @org.springframework.test.context.jdbc.Sql annotation is part of Spring test framework to execute DDL and DML SQL scripts and statements. These scripts/statements can be executed before or after the test execution to setup or clear the test data.

1. @Sql Annotation

The @Sql annotation is a repeatable annotation so we can apply it multiple times either at the class level or at the method level. Method-level declarations override class-level declarations.

Script execution is performed by the SqlScriptsTestExecutionListener, which is enabled by default.

1.1. Executing SQL Scripts

In the following declaration, items-books.sql script will be executed before the test method ‘testBooksData’ from the location /resources/sql/. The schema.sql will be executed once when the TestSqlAnnotation class is loaded before all the test methods execution.

@Sql("/sql/schema.sql")
@SpringBootTest
public class TestSqlAnnotation {

  @Sql({"/sql/items-books.sql"})
  @Test
  void testBooksData() {

    //...
  }
}

The resolution of the script’s path is interpreted as Resource instances using the following strategy:

  • The path that begins without a slash is a classpath resource relative to the test class where it is declared. For example, sql/items.sql script will be loaded from the /sql directory inside the package where test class is present.
  • The path that begins with a slash is an absolute classpath resource, and spring will look into a directory relative to /resources folder.
  • A protocol-specific path (e.g. classpath:, file:, http:, etc.) will be loaded using the specified resource protocol.

1.2. Executing SQL Statements

Sometimes, we want to only execute one or two statements in the script. It is a good idea to write the statements themselves rather than managing a script in the code.

@Sql(statements = {
  "INSERT INTO ITEM (name) VALUES ('Book-1');",
  "INSERT INTO ITEM (name) VALUES ('Book-2');"
})
@Test
void testBooksData() {

  //...
}

The SQL statements will be executed after the SQL scripts, if both have been defined.

1.3. No Script or Statement

Consider the following declaration:

@Sql
@Test
void testBooksData() { ... }

If no SQL scripts or statements are specified, Spring tries to locate a default script based on where the annotation is used i.e. at the class level or method level.

  • When declared at the class level (e.g. in com.howtodoinjava.demo.MyTestClass), Spring will search for classpath:com/howtodoinjava/demo/MyTestClass.sql.
  • When declared at the method level (e.g. in com.howtodoinjava.demo.MyTestClass.testBooksData), Spring will search for classpath:com/howtodoinjava/demo/MyTestClass.testBooksData.sql.

If the default scripts are not found, an IllegalStateException is thrown.

1.4. Execution Phase

Two possible execution phases are:

  • ExecutionPhase.BEFORE_TEST_METHOD: This is the default execution phase. Scripts are executed BEFORE the test method/class execution.
  • ExecutionPhase.AFTER_TEST_METHOD: Scripts are executed AFTER the test method/class execution.
@Sql(scripts = {"/sql/items-books.sql"},
    executionPhase = ExecutionPhase.BEFORE_TEST_METHOD)
@Test
void testBooksData() {

  //...
}

2. @SqlGroup Annotation

The @SqlGroup groups multiple @Sql declarations. Since @Sql is a repeatable annotation, using the @SqlGroup is optional.

@SqlGroup({
  @Sql({"/sql/items-grocery.sql"}),
  @Sql({"/sql/items-books.sql"})
})
@Test
void testBooksData() { ... }

The preceding declaration is equivalent to the following declarations:

@Sql({"/sql/items-grocery.sql"})
@Sql({"/sql/items-books.sql"})
@Test
void testBooksData() { ... }

3. @SqlMergeMode Annotation

When we use the @Sql annotation at the class level and at the method level, too, it is a good idea to use the @SqlMergeMode.

By default, the method-level @Sql scripts override the class-level @Sql scripts so when we define scripts at both locations, the class-level scripts are not executed. So if we want to execute both scripts, we must use the @SqlMergeMode(MERGE) either at the class level or method level – based on the tests that need the merging.

In the following example, if we remove the @SqlMergeMode(MERGE) declaration then the test will fail because grocery items will not be created in the database.

@SpringBootTest
@SqlMergeMode(MERGE)
@Sql({"/sql/items-grocery.sql"})
public class TestSqlAnnotation {

  @Sql({"/sql/items-books.sql"})
  @Test
  void testBooksData() { 

    Optional<Item> gItem = itemRepository.findByName("Grocery-1");
    Optional<Item> bItem = itemRepository.findByName("Book-1");

    Assertions.assertNotNull(gItem.get());   //Fails here if we remove the merge mode
    Assertions.assertNotNull(bItem.get());
  }
}

4. @SqlConfig Annotation

The @SqlConfig is used to configure how to parse SQL scripts configured via @Sql. It can be defined at the class level as follows:

@SpringBootTest
@SqlConfig(errorMode = ErrorMode.CONTINUE_ON_ERROR)
public class TestSqlAnnotation {

  //...
}

We can also define the test method-specific configuration using the ‘config’ attribute of @Sql annotation.

@Sql(
    scripts = {"/sql/items-grocery.sql", "/sql/items-books.sql"},
    config = @SqlConfig(errorMode = ErrorMode.CONTINUE_ON_ERROR)
)
@Test
void testBooksData() { ... }

Note that method-level configuration overrides the class-level configuration. See the Java docs for the list of supported attributes and their usages.

5. Conclusion

The @Sql annotation is an excellent tool for loading initial data during the integration test execution in Spring. As discussed earlier in the post, it is very important to remember the use of merge mode to avoid accidental test failures.

Happy Learning !!

Sourcecode on Github

Comments

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

About Us

HowToDoInJava provides tutorials and how-to guides on Java and related technologies.

It also shares the best practices, algorithms & solutions and frequently asked interview questions.

Our Blogs

REST API Tutorial

Dark Mode

Dark Mode