Inject sql data in a Spring junit test with an annotation

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 & 开发者_JS百科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.

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<String> statements = new LinkedList<String>();
        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 can even use relative paths or absolute path.

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

Maybe @Sql annotation can do this better.