13

I'm writing some integration test using spring framework. I have different SQL scripts for different integration test classes. Something like this:

@ContextConfiguration(classes = ...)
@Sql("classpath:sportCenter-test.sql")
public class SportCenterResourceIT {
    ...
}

Everything works perfectly except for the fact that the SQL script is executed before each test, instead of one time per class. I have already searched for some time the spring documentation but I was not able to find something related to such an option.

Could anybody give me an hint?

erhun
  • 3,549
  • 2
  • 35
  • 44
Mihai238
  • 1,087
  • 13
  • 26
  • Do you have Before tag at your tests? – erhun Mar 19 '15 at 20:15
  • Please share your SportCenterResourceIT class with test methods no need what they indeed but i want to check is there any @Sql tag before test methods too. – erhun Mar 20 '15 at 11:08
  • @erhun There is no other ``@Sql`` tag in my test. The only one is at class level. – Mihai238 Mar 20 '15 at 12:07
  • 1
    Do you want your SQL script to be executed once for the `ApplicationContext` loaded for your test? Or do you _really_ want your SQL script to be executed only once, before the test class? – Sam Brannen Mar 22 '15 at 00:02
  • I have solved this problem by using a script (at class level) to clear all the database entries. See [spring documentation](http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#testcontext-executing-sql). – Mihai238 Mar 22 '15 at 14:37
  • hi, from the document, I did not found any info of @Sql one time per class. In `Sql.ExecutionPhase` there are only `before test method` and `after test method`, without `before test class`, `after test class` – zhuguowei Dec 30 '15 at 09:01
  • @zhuguowei I've found an workaround by clearing the database *after* each test. `@Sql` still executes *before* each test method. – Mihai238 Dec 30 '15 at 19:20

5 Answers5

9

Adding the org.springframework.transaction.annotation.Transactional annotation at the class level will prevent any changes from @Sql scripts from persisting between tests. So your code becomes:

@ContextConfiguration(classes = ...)
@Sql("classpath:sportCenter-test.sql")
@Transactional
public class SportCenterResourceIT {
    ...
}

This combination will result in the following:

  1. Your @Sql script will run before each test
  2. The test itself will run
  3. Any database changes from steps 1 or 2 will be reverted at the end of each test
mskluev
  • 131
  • 1
  • 3
  • 1
    The `@Sql` statement will run but since we have `@Transactional` set, the statement will run in a transaction that's not commited. That is your test will not be able to access the data, your `@Sql` statement inserted or updated. – Bassam Apr 25 '17 at 20:23
  • 2
    my sql script is inserting data. When 2nd test runs the sql is being loaded again which gives me a primary key constraint exception. How to prevent this? – Micho Rizo Sep 01 '18 at 16:13
  • in this way there is the side effect of executing the whole test in a transaction altering it's behavior; am I wrong? – lrkwz Mar 03 '23 at 10:25
8

I use this way to resolve the problem:

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
@Sql("classpath:sql/DataForRouteTesting.sql")
public class MyTest {}

Scripts are executed every time, but there are not conflict primary key.

tatka
  • 301
  • 1
  • 3
  • 9
4

If we want to execute sql script one time per class, we can execute the script in setup method and set flag to true once the script is executed for one method so it does not execute again. ` @Autowired private DataSource database;

private static boolean dataLoaded = false;

@Before
public void setup() throws SQLException {
    if(!dataLoaded) {
        try (Connection con = database.getConnection()) {
            ScriptUtils.executeSqlScript(con, new ClassPathResource("path_to_script.sql"));
            dataLoaded = true;
        }
    }
}`
Khyati Elhance
  • 662
  • 6
  • 11
1

I solved my problem by putting the sql script in the src/test/resources folder with the name data.sql. This file is executed once at startup of each integration test. For this to work, you need to remove the @Sql annotation.

buderu
  • 415
  • 7
  • 16
  • it's better than putting the script to each test class, but it doesn't solve all problems. On my case that script still executed multiple times.My script was create table, so I fixed the problem by adding a "if not exists" clause – ahmkara Mar 26 '20 at 07:50
-4

You can create an empty static method annotated with @BeforeAll and @Sql("classpath:sportCenter-test.sql")

@BeforeAll
@Sql("classpath:sportCenter-test.sql") 
public static void initSql() {
    
}
Ameur fahd
  • 21
  • 2
  • 5