@Sql Example in Spring Test

By Arvind Rai, November 13, 2019

This page will walk through using Spring @Sql annotations in integration test classes. We will also go through using @SqlConfig, @SqlMergeMode and @SqlGroup annotations with @Sql annotation in integration test classes. The @Sql annotation executes SQL scripts and SQL statements using datasource for testing. The @SqlConfig helps to parse SQL scripts configured via @Sql annotation. The @SqlMergeMode decides whether method level @Sql declarations are merged with class level @Sql declarations. The @SqlGroup aggregates several @Sql annotations in integration test classes. In Java 8, using @SqlGroup is optional because Java 8 supports repeatable annotations and we can annotate test class with several @Sql annotations.
Find the sample code snippet to use @Sql annotations in integration test class.

@Sql({ "/drop_schema.sql", "/create_schema.sql" })
@Sql("/insert_data.sql")
@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = AppConfig.class)
public class SqlTest {
    ------
}

Here on this page we will provide @Sql annotation examples in details step-by-step.

Technologies Used
Maven Dependencies
SQL Scripts
Configure DataSource
Using @Sql
Using @SqlConfig
Using @SqlMergeMode
Using @SqlGroup
References
Download Source Code

Technologies Used

Find the technologies being used in our example.
1. Java 11
2. Spring 5.2.0.RELEASE
3. Spring Boot 2.2.0.RELEASE
4. JUnit 5
5. Maven 3.5.2
6. Eclipse 2018-09

Maven Dependencies

Find the Maven dependencies.
pom.xml

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.2.0.RELEASE</version>
	<relativePath/>
</parent>
<properties>
	<java.version>11</java.version>
</properties>
<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
	</dependency>	
	<dependency>
		<groupId>org.junit.jupiter</groupId>
		<artifactId>junit-jupiter-api</artifactId>
		<version>5.3.2</version>
		<scope>test</scope>
	</dependency>	
	<dependency>
		<groupId>org.junit.jupiter</groupId>
		<artifactId>junit-jupiter-engine</artifactId>
		<version>5.3.2</version>
		<scope>test</scope>
	</dependency>	
	<dependency>
		<groupId>org.junit.jupiter</groupId>
		<artifactId>junit-jupiter-params</artifactId>
		<version>5.3.2</version>
		<scope>test</scope>
	</dependency>		    
	<dependency>
		<groupId>org.junit.platform</groupId>
		<artifactId>junit-platform-launcher</artifactId>
		<version>1.3.2</version>
		<scope>test</scope>
	</dependency>	
	<dependency>
		<groupId>org.hsqldb</groupId>
		<artifactId>hsqldb</artifactId>
		<version>2.5.0</version>
		<scope>test</scope>
	</dependency>
</dependencies>

SQL Scripts

Find the SQL scripts being used in our demo.
drop_schema.sql

drop table if exists student;

create_schema.sql

CREATE TABLE student (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
);

insert_data1.sql

insert into student(id, name) values (101, 'Mohan');
insert into student(id, name) values (102, 'Krishna');

insert_data2.sql

#Insert initial data

insert into student(id, name) values (101, 'Mohan');
insert into student(id, name) values (102, 'Krishna');

insert_more_data1.sql

insert into student(id, name) values (103, 'Indra');
insert into student(id, name) values (104, 'Chandra');

insert_more_data2.sql

~insert more data

insert into student(id, name) values (103, 'Indra');
insert into student(id, name) values (104, 'Chandra');

Configure DataSource

For the demo we are using HSQLDB embedded database. Find the JavaConfig used in our example.
AppConfig.java

package com.concretepage;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class AppConfig {

	@Bean
	public DataSource dataSource() {
		return new EmbeddedDatabaseBuilder().setName("test-db").build();
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}

	@Bean
	public PlatformTransactionManager transactionManager(){
	    DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource());
	    return transactionManager;
	}
}

Using @Sql

The Spring @Sql annotation executes SQL scripts and SQL statements against a database in Spring integration test. The @Sql annotation is used at class level as well as method level. By default, method level @Sql declarations override class level @Sql declarations and we can change this behavior by configuring @SqlMergeMode. Find the optional elements of @Sql annotation.
config: Configures @SqlConfig and its scope is local to its @Sql annotation. It is used to configure commentPrefix, separator etc.
executionPhase: Decides when the SQL scripts and statements are executed. Default is BEFORE_TEST_METHOD.
statements: Configures inlined SQL statements to execute.
scripts: Configures the path for SQL scripts to execute.
value: It is the alias of scripts element. The scripts and value cannot be used together but they can be used with statements element.

Find the example.
SqlTest.java

package com.concretepage;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.util.List;
import java.util.Map;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit.jupiter.SpringExtension;

@Sql({ "/drop_schema.sql", "/create_schema.sql" })
@Sql(scripts = "/insert_data1.sql", statements = "insert into student(id, name) values (100, 'Shiva')")
@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = AppConfig.class)
public class SqlTest {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Test
	public void fetchRows1() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(3, students.size());
	}

	@Sql("/insert_more_data1.sql")
	@Test
	public void fetchRows2() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(5, students.size());
	}
}

Find the print screen of the output.

Using @SqlConfig

The Spring @SqlConfig determines how to parse SQL scripts configured via @Sql annotation. The @SqlConfig can be annotated at class level of Spring test class and that will serve as global configuration for all SQL scripts in that integration test class i.e. global scope. The @SqlConfig can also be configured using config attribute of the @Sql annotation and the scope will be within its @Sql annotation i.e. local scope. Local configuration of @SqlConfig within @Sql overrides the configuration of @SqlConfig annotated at class level. Find the optional elements of @SqlConfig annotation.
blockCommentStartDelimiter: Start delimiter for block comment in script file. Default is (/*).
blockCommentEndDelimiter: End delimiter for block comment in script file. Default is (*/).
commentPrefix: Defines prefix for single line comments. Default is (--).
commentPrefixes: Defines multiple prefixes for single line comments. Default is ["--"].
dataSource: Defines datasource name against which scripts are to be executed. It is needed only when there are more than one datasource beans.
encoding: Encoding used for SQL scripts. It is needed only if SQL scripts encoding is different from platform encoding.
errorMode: Configures error mode. Default is DEFAULT of SqlConfig.ErrorMode.
separator: Configures separator for individual statement in a script. Default is (\n).
transactionManager: Configures transactionManager bean. It is needed only if there are more than one transactionManager beans.
transactionMode: Configures mode that defines whether SQL scripts should be executed within a transaction. Default is DEFAULT of SqlConfig.TransactionMode.

Find the example.
SqlConfigTest.java

package com.concretepage;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.util.List;
import java.util.Map;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlConfig;
import org.springframework.test.context.junit.jupiter.SpringExtension;

@SqlConfig(commentPrefix = "#")
@Sql({ "/drop_schema.sql", "/create_schema.sql" })
@Sql(scripts = { "/insert_data2.sql" })
@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = AppConfig.class)
public class SqlConfigTest {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Test
	public void fetchRows1() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(2, students.size());
	}

	@Sql(scripts = "/insert_more_data2.sql", config= @SqlConfig(commentPrefix = "~"))
	@Test
	public void fetchRows2() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(4, students.size());
	}
}

Using @SqlMergeMode

The Spring @SqlMergeMode is annotated at class level or method level in an integration test class. The @SqlMergeMode decides whether method level @Sql declarations are merged with class level @Sql declarations. If @SqlMergeMode is annotated at class level as well as method level then method level @SqlMergeMode will override class level declarations. If we are not using @SqlMergeMode either at class level or test method level then the default value is OVERRIDE of SqlMergeMode.MergeMode.

Find the example.
SqlMergeModeTest.java

package com.concretepage;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.util.List;
import java.util.Map;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlMergeMode;
import org.springframework.test.context.jdbc.SqlMergeMode.MergeMode;
import org.springframework.test.context.junit.jupiter.SpringExtension;

@SqlMergeMode(MergeMode.MERGE)
@Sql({ "/drop_schema.sql", "/create_schema.sql", "/insert_data1.sql" })
@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = AppConfig.class)
public class SqlMergeModeTest {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Sql(statements = "insert into student(id, name) values (100, 'Shiva')")	
	@Test
	public void fetchRows1() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(3, students.size());
	}

	@SqlMergeMode(MergeMode.OVERRIDE)	
	@Sql("/insert_more_data1.sql")
	@Test
	public void fetchRows2() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(5, students.size());
	}
}

Using @SqlGroup

In Java 8 and onwards, using Spring @SqlGroup is optional. The Spring @SqlGroup is used to aggregate several @Sql annotations. Java 8 supports repeatable annotations. So we can annotate our test class or test method with several @Sql annotations and @SqlGroup is not needed. If we want to use @SqlGroup, we can use it as following.
SqlGroupTest.java

package com.concretepage;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.util.List;
import java.util.Map;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlGroup;
import org.springframework.test.context.junit.jupiter.SpringExtension;

@SqlGroup({
    @Sql({ "/drop_schema.sql", "/create_schema.sql" }),
    @Sql("/insert_data1.sql")
})
@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = AppConfig.class)
public class SqlGroupTest {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Test
	public void fetchRows1() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(2, students.size());
	}

	@SqlGroup({
		@Sql("/insert_more_data1.sql"),
		@Sql(statements = "insert into student(id, name) values (100, 'Shiva')")
	})	
	@Test
	public void fetchRows2() {
		List<Map<String, Object>> students = jdbcTemplate.queryForList("SELECT * FROM student");
		assertEquals(5, students.size());
	}
}