@Sql Example in Spring Test
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 { ------ }
@Sql
annotation examples in details step-by-step.
Contents
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 TABLE student ( id INT NOT NULL, name VARCHAR(50) NOT NULL, PRIMARY KEY(id) );
insert into student(id, name) values (101, 'Mohan'); insert into student(id, name) values (102, 'Krishna');
#Insert initial data insert into student(id, name) values (101, 'Mohan'); insert into student(id, name) values (102, 'Krishna');
insert into student(id, name) values (103, 'Indra'); insert into student(id, name) values (104, 'Chandra');
~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()); } }
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()); } }
References
Spring Testing AnnotationsExecuting SQL scripts declaratively with @Sql