Spring @Sql Annotation Default Scripts Example

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]e3 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.