Java Articles

Spring @Sql Annotation Example

0
By on Spring

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.

You can download the source code here: pringSqlAnnotationExample.zip
Share.

Comments are closed.