I'm trying to write an integration test with dbUnit for a table in MySQL that has a column with autoincrement
.
The integration test looks like:
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes={
JdbcRepositoryConfiguration.class,
DbUnitConnectionConfiguration.class
})
@TestExecutionListeners({
DependencyInjectionTestExecutionListener.class,
DirtiesContextTestExecutionListener.class,
DbUnitTestExecutionListener.class
})
@DirtiesContext(classMode=ClassMode.AFTER_CLASS)
@DbUnitConfiguration(databaseConnection="dbUnitConnection")
public class IntegrationTest {
@Autowired private JdbcRepositoryConfiguration configuration;
private Loader loader;
@Before
public void setup() throws JSchException {
loader = new Loader(configuration.jdbcTemplate());
}
@Test
@DatabaseSetup("classpath:dataset.xml")
public void loads() throws Exception {
assertThat(loader.load(), contains("something"));
}
}
I have the same integration test structure for a table that has no increment
column and the test works just fine.
The dataset.xml
looks like:
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<sometable
id="1"
regexp="something"
descr="descr"
/>
</dataset>
Debugging I can see that the actions taken to setup the data are to delete all and to perform an insert, more specifically:
insert into sometable (id, regexp, descr) values (?, ?, ?)
The error I get is:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'regexp, descr) values (1, 'something', 'descr')' at line 1
For completeness, the DbUnitConfiguration.class
has the following spring bean setup:
@Bean
public IDatabaseConnection dbUnitConnection() throws SQLException, DatabaseUnitException, JSchException {
Connection dbConn = configuration.jdbcTemplate().getDataSource().getConnection();
IDatabaseConnection connection = new DatabaseConnection(dbConn) {
@Override
public void close() throws SQLException {}
};
DatabaseConfig dbConfig = connection.getConfig();
dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
return connection;
}
Turns out has nothing to do with the autoincrement.
The error was thrown because the column regexp
is a reserved word in MySQL.
To workaround this, the dbUnit setup has to have the following line:
dbConfig.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN , "`?`");
And the test just works now.