Inject sql data in a Spring junit test with an annotation

Question

Hi, im using Junit with spring-test and i would like to have a classic transactionnal test with this annotation:

@Injectdata("classpath:src/test/mydata.sql") @Test public void myTest throws Exception{ ... }

This data will be injected with the jdbcspring template in the same transaction & those datas will be available for only this test.

Actually, im injecting data this way :

@Test public void myTest throws Exception{

jdbcTemplate.update("my sql query); }

I know that Unitils framework do the samething but with a dataset dbunit file.

Thanks in advance.

Answer 1

I have found the solution by creating one myself.

First create the listener used by Spring test:

public class InjectDataTestExecutionListener extends DependencyInjectionTestExecutionListener {

    private static JdbcTemplate jdbcTemplate;
    private static  DataSource datasource ;
    private static String ENCODING="UTF-8";


    @Override
    /**
     * Execute un éventuel script SQL indiqué via l'annotation  {@link SqlFileLocation} 
     * avant l'execution d'un test.
     */
    public void beforeTestMethod(TestContext testContext) throws Exception {
        super.beforeTestClass(testContext);

       Method MyMethdo = testContext.getTestMethod();
       SqlFileLocation dsLocation = MyMethdo.getAnnotation(SqlFileLocation.class);
        if (dsLocation!=null){
            executeSqlScript(testContext,dsLocation.value());
        }
    }

    /**
     * Execute un script sur un chemin d'accès au fichier.
     * @param testContext le context du test
     * @param sqlResourcePath le chemin du fichier Sql
     * @throws DataAccessException en cas d'erreur d'accès au fichier
     */
    private  void executeSqlScript(TestContext testContext, String sqlResourcePath) throws DataAccessException {
      JdbcTemplate jdbcTemplate = getJdbCTemplate(getDatasource(testContext));
      Resource resource = testContext.getApplicationContext().getResource(sqlResourcePath);
      executeSqlScript(jdbcTemplate, new EncodedResource(resource,ENCODING));
    }

    private DataSource getDatasource(TestContext testContext) {
        if (datasource==null){
            datasource = testContext.getApplicationContext().getBean(DataSource.class);
        }  
        return datasource;
    }

    private JdbcTemplate getJdbCTemplate(DataSource datasource) {
    if (jdbcTemplate==null){
        jdbcTemplate = new JdbcTemplate(datasource);
    }  
    return jdbcTemplate;
}

    /**
     * Execute une resource via un jdbcTemplate donné.
     * @throws DataAccessException enc as de pb d'acces au fichier.
     */
    private static void executeSqlScript(JdbcTemplate simpleJdbcTemplate,
            EncodedResource resource) throws DataAccessException {

        List statements = new LinkedList();
        try {
            LineNumberReader lnr = new LineNumberReader(resource.getReader());
            String script = JdbcTestUtils.readScript(lnr);
            char delimiter = ';';
            if (!JdbcTestUtils.containsSqlScriptDelimiters(script, delimiter)) {
                delimiter = '\n';           
            }
            JdbcTestUtils.splitSqlScript(script, delimiter, statements);
            for (String statement : statements) {
                try {
                    simpleJdbcTemplate.update(statement);
                }
                catch (DataAccessException ex) {
                        throw ex;
                }
            }
        }
        catch (IOException ex) {
            throw new DataAccessResourceFailureException("Impossible d'ouvrir le script depuis " + resource, ex);
        }
    }
}

Than on your class test add:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={....})
@Transactionnal
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class,DirtiesContextTestExecutionListener.class,TransactionalTestExecutionListener.class
    ,InjectDataTestExecutionListener.class    
})

The TRICK was to add ALL listeners normally added automatically by Spring if you dont add listeners. Avoiding that lead to weird errors.

This is not documented but ive found that without listener with a transactionnal spring test those 3 listeners are added automatically by Spring (thanks the debug mode!)

And finally you cand us this cool annotation like this :

    @SqlFileLocation("classpath:sql/myfil.sql")
    @Test
    public void testGetAll() throws Exception {...}

You canduse even use relative paths or absolute path.

And naturally the insertion will be like others inserts automatically rollback at the end.