Use @Sql
annotation to run SQL scripts against a given database. This is useful if you want to provide your tests with a schema and sample data.
@Sql annotation Example
In this example, we will show you usage of @Sql
annotation to create schema and some sample data.
Here are the scripts to drop and create schema.
drop_schema.sql:
drop table if exists employee;
schema.sql:
CREATE TABLE employee ( name VARCHAR(20) NOT NULL, PRIMARY KEY(name) );
Script to insert some sample data.
data.sql:
insert into employee(name) values ('Sam'); insert into employee(name) values ('John');
SpringSqlAnnotationExample:
package com.javarticles.spring; import java.util.List; import javax.sql.DataSource; import static org.junit.Assert.*; import org.junit.Before; import org.junit.Rule; import org.junit.Test; import org.junit.rules.TestName; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.jdbc.Sql; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration @Sql({ "drop_schema.sql", "schema.sql", "data.sql" }) public class SpringSqlAnnotationExample { @Autowired private JdbcTemplate jdbcTemplate; @Rule public TestName testName = new TestName(); @Before public void printTestName() { System.out.println(testName.getMethodName()); } @Test public void printRows() { List empNames = jdbcTemplate.queryForList("select name from employee", String.class); assertEquals(2, empNames.size()); System.out.println(empNames); } @Configuration static class Config { @Bean public DataSource dataSource() { return new EmbeddedDatabaseBuilder()// .setName("empty-sql-scripts-without-tx-mgr-test-db")// .build(); } @Bean public JdbcTemplate jdbcTemplate() { return new JdbcTemplate(dataSource()); } } }
Output:
INFO: Refreshing [email protected]96442d: startup date [Tue Feb 09 09:36:57 IST 2016]; root of context hierarchy Feb 09, 2016 9:36:57 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase INFO: Starting embedded database: url='jdbc:hsqldb:mem:empty-sql-scripts-without-tx-mgr-test-db', username='sa' Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/drop_schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/drop_schema.sql] in 9 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/schema.sql] in 13 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/data.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/data.sql] in 24 ms. printRows [John, Sam]
Override sql scripts at Method level
You can also use @Sql
annotation at method-level an dthis will override any @Sql
declared at class-level.
Also, beginning with Java 8, @Sql
can be used as a repeatable annotation.
In overrideSqlAndPrintRows()
, we have used method level @Sql
to override the @Sql
configuration setup at class level. Also, we have used multiple @Sql
annotations.
override_data.sql:
insert into employee(name) values ('Joe'); insert into employee(name) values ('Sherley'); insert into employee(name) values ('Satish');
SpringSqlAnnotationExample:
package com.javarticles.spring; import java.util.List; import javax.sql.DataSource; import static org.junit.Assert.*; import org.junit.Before; import org.junit.Rule; import org.junit.Test; import org.junit.rules.TestName; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.jdbc.Sql; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration @Sql({ "drop_schema.sql", "schema.sql", "data.sql" }) public class SpringSqlAnnotationExample { @Autowired private JdbcTemplate jdbcTemplate; @Rule public TestName testName = new TestName(); @Before public void printTestName() { System.out.println(testName.getMethodName()); } @Test public void printRows() { List empNames = jdbcTemplate.queryForList("select name from employee", String.class); assertEquals(2, empNames.size()); System.out.println(empNames); } @Test @Sql("drop_schema.sql") @Sql({ "schema.sql", "override_data.sql" }) public void overrideSqlAndPrintRows() { List empNames = jdbcTemplate.queryForList("select name from employee", String.class); assertEquals(3, empNames.size()); System.out.println(empNames); } @Configuration static class Config { @Bean public DataSource dataSource() { return new EmbeddedDatabaseBuilder()// .setName("empty-sql-scripts-without-tx-mgr-test-db")// .build(); } @Bean public JdbcTemplate jdbcTemplate() { return new JdbcTemplate(dataSource()); } } }
Output:
INFO: Refreshing [email protected]96442d: startup date [Tue Feb 09 09:36:57 IST 2016]; root of context hierarchy Feb 09, 2016 9:36:57 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory initDatabase INFO: Starting embedded database: url='jdbc:hsqldb:mem:empty-sql-scripts-without-tx-mgr-test-db', username='sa' Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/drop_schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/drop_schema.sql] in 9 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/schema.sql] in 13 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/data.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/data.sql] in 24 ms. printRows [John, Sam] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/drop_schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/drop_schema.sql] in 6 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/schema.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/schema.sql] in 2 ms. Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/override_data.sql] Feb 09, 2016 9:36:58 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/override_data.sql] in 2 ms. overrideSqlAndPrintRows [Joe, Satish, Sherley]
Download the source code
This was an example about spring @Sql annotation.