In this article we will see an example of spring @Sql
annotation to configure sql scripts that need to be executed against a database. We can configure the scripts to run either before a test or after a test method. @Sql
annotation can be declared at class level or test method level. A method level @Sql
will override one declared at the class level.
We provide paths to the scripts in the attribute scripts,
here is an example.
In this example, we will see how spring detects the default scripts if no SQL scripts are specified.
Default @Sql Scripts
If no SQL scripts are specified, then spring looks for the default scripts. The default script name depends on whether the annotation is declared class level or method level. If the annotated is declared at class level then the test class name will be used as the script file name. For example, if the test class is com.javarticles.spring.SpringDefSqlAnnotationExample
, the corresponding default script is classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.sq
If the annotation is declared at method level then the script name would be ‘className.methodName.sql’. For example, if the test method is method1()
, script name would be SpringDefSqlAnnotationExample.method1.sql
.
Below are the default class level and method level scripts.
The sql files contain employee table schema and data.
SpringDefSqlAnnotationExample.sql:
drop table if exists employee; CREATE TABLE employee ( name VARCHAR(20) NOT NULL, PRIMARY KEY(name) ); insert into employee(name) values ('Roy');
Method level script for method1()
.
SpringDefSqlAnnotationExample.method1.sql:
drop table if exists employee; CREATE TABLE employee ( name VARCHAR(20) NOT NULL, PRIMARY KEY(name) ); insert into employee(name) values ('Joe'); insert into employee(name) values ('Sherley'); insert into employee(name) values ('Satish');
Method level script for method2()
.
SpringDefSqlAnnotationExample.method2.sql:
drop table if exists employee; CREATE TABLE employee ( name VARCHAR(20) NOT NULL, PRIMARY KEY(name) ); insert into employee(name) values ('John'); insert into employee(name) values ('Kim');
In the below test class we have @Sql
annotation declared at class level and method levels. One of the test method method3()
has no @Sql
annotation so the class level @Sql
annotation applies on it. In other cases, method level @Sql
annotation will override the class level @Sql
annotation.
Each test case verifies the employee count and prints employee rows so we know which @Sql
annotation is been applied and the tests run.
SpringDefSqlAnnotationExample:
package com.javarticles.spring; import static org.junit.Assert.assertEquals; import java.util.List; import javax.sql.DataSource; import org.junit.Test; 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 public class SpringDefSqlAnnotationExample { @Autowired private JdbcTemplate jdbcTemplate; @Test @Sql public void method1() { assertEquals(3, countEmpRows()); } @Test @Sql public void method2() { assertEquals(2, countEmpRows()); } @Test public void method3() { assertEquals(1, countEmpRows()); } private int countEmpRows() { List empNames = jdbcTemplate.queryForList("select name from employee", String.class); System.out.println(empNames); return empNames.size(); } @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: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method1.sql] in 10 ms. [Joe, Satish, Sherley] Mar 10, 2016 8:19:53 AM org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener detectDefaultScript INFO: Detected default SQL script "classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql" for test method [public void com.javarticles.spring.SpringDefSqlAnnotationExample.method2()]Mar 10, 2016 8:19:53 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql] Mar 10, 2016 8:19:53 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql] in 0 ms. [John, Kim] Mar 10, 2016 8:19:53 AM org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener detectDefaultScript INFO: Detected default SQL script "classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.sql" for test class [com.javarticles.spring.SpringDefSqlAnnotationExample] Mar 10, 2016 8:19:53 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.sql] Mar 10, 2016 8:19:53 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.sql] in 0 ms. [Roy] Mar 10, 2016 8:19:53 AM org.springframework.context.support.GenericApplicationContext doClose INFO: Closing [email protected]7c74e5: startup date [Thu Mar 10 08:19:53 IST 2016]; root of context hierarchy
SQL Execution Phase
We can also control When the SQL scripts should be executed, before or after the corresponding test method. We add a new test method method4()
which has @Sql
annotation with executionPhase
set to ExecutionPhase.AFTER_TEST_METHOD
.
drop_schema.sql:
drop table if exists employee;
SpringDefSqlAnnotationExample:
package com.javarticles.spring; import static org.junit.Assert.assertEquals; import java.util.List; import javax.sql.DataSource; 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.jdbc.Sql.ExecutionPhase; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration @Sql public class SpringDefSqlAnnotationExample { @Rule public TestName testName = new TestName(); @Autowired private JdbcTemplate jdbcTemplate; @Test @Sql public void method1() { assertEquals(3, countEmpRows()); } @Test @Sql public void method2() { assertEquals(2, countEmpRows()); } @Test public void method3() { assertEquals(1, countEmpRows()); } @Test @Sql(scripts = "drop_schema.sql", executionPhase = ExecutionPhase.AFTER_TEST_METHOD) public void method4() { System.out.println("running " + testName.getMethodName()); } private int countEmpRows() { List empNames = jdbcTemplate.queryForList("select name from employee", String.class); System.out.println(empNames); return empNames.size(); } @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:
Mar 10, 2016 10:44:03 AM org.springframework.test.context.support.AbstractContextLoader generateDefaultLocations INFO: Could not detect default resource locations for test class [com.javarticles.spring.SpringDefSqlAnnotationExample]: no resource found for suffixes {-context.xml}. Mar 10, 2016 10:44:03 AM org.springframework.test.context.support.AbstractDelegatingSmartContextLoader processContextConfiguration INFO: AnnotationConfigContextLoader detected default configuration classes for context configuration [[email protected] declaringClass = 'com.javarticles.spring.SpringDefSqlAnnotationExample', classes = '{class com.javarticles.spring.SpringDefSqlAnnotationExample$Config}', locations = '{}', inheritLocations = true, initializers = '{}', inheritInitializers = true, name = [null], contextLoaderClass = 'org.springframework.test.context.ContextLoader']. Mar 10, 2016 10:44:03 AM org.springframework.test.context.support.DefaultTestContextBootstrapper getDefaultTestExecutionListenerClassNames INFO: Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener] Mar 10, 2016 10:44:03 AM org.springframework.test.context.support.DefaultTestContextBootstrapper instantiateListeners INFO: Could not instantiate TestExecutionListener [org.springframework.test.context.web.ServletTestExecutionListener]. Specify custom listener classes or make the default listener classes (and their required dependencies) available. Offending class: [javax/servlet/ServletContext] Mar 10, 2016 10:44:03 AM org.springframework.test.context.support.DefaultTestContextBootstrapper getTestExecutionListeners INFO: Using TestExecutionListeners: [org.springframework.test[email protected]6aaa5eb0, org.springframewor[email protected]3498ed, org.springfra[email protected]1a407d53, org.springframew[email protected]3d8c7aca, org.sp[email protected]5ebec15] Mar 10, 2016 10:44:03 AM org.springframework.context.support.GenericApplicationContext prepareRefresh INFO: Refreshing [email protected]21b0a2: startup date [Thu Mar 10 10:44:03 IST 2016]; root of context hierarchy Mar 10, 2016 10:44:03 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' Mar 10, 2016 10:44:04 AM org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener detectDefaultScript INFO: Detected default SQL script "classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.method1.sql" for test method [public void com.javarticles.spring.SpringDefSqlAnnotationExample.method1()]Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method1.sql] Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method1.sql] in 9 ms. [Joe, Satish, Sherley] Mar 10, 2016 10:44:04 AM org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener detectDefaultScript INFO: Detected default SQL script "classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql" for test method [public void com.javarticles.spring.SpringDefSqlAnnotationExample.method2()]Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql] Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.method2.sql] in 2 ms. [John, Kim] Mar 10, 2016 10:44:04 AM org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener detectDefaultScript INFO: Detected default SQL script "classpath:com/javarticles/spring/SpringDefSqlAnnotationExample.sql" for test class [com.javarticles.spring.SpringDefSqlAnnotationExample] Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.sql] Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/SpringDefSqlAnnotationExample.sql] in 0 ms. [Roy] running method4 Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executing SQL script from class path resource [com/javarticles/spring/drop_schema.sql] Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript INFO: Executed SQL script from class path resource [com/javarticles/spring/drop_schema.sql] in 1 ms. Mar 10, 2016 10:44:04 AM org.springframework.context.support.GenericApplicationContext doClose INFO: Closing [email protected]21b0a2: startup date [Thu Mar 10 10:44:03 IST 2016]; root of context hierarchy Mar 10, 2016 10:44:04 AM org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory shutdownDatabase INFO: Shutting down embedded database: url='jdbc:hsqldb:mem:empty-sql-scripts-without-tx-mgr-test-db'
Download the source code
This was an example about @Sql annotation and the default scripts.