Spring DBUnit 插入数据的时候如何处理自增ID
Spring DBUnit 插入数据的时候如何处理自增ID
今天在补单元测试的时候遇到一个问题，在给插入数据的方法写单元测试的时候，第一遍运行的时候是没问题，第二遍运行就报错了，如下：
junit.framework.ComparisonFailure: value (table=FACULTY_ROOM, row=2, col=id)
Expected :3
Actual :4
<Click to see difference>
at org.dbunit.assertion.JUnitFailureFactory.createFailure(JUnitFailureFactory.java:39)
at org.dbunit.assertion.DefaultFailureHandler.createFailure(DefaultFailureHandler.java:97)
at org.dbunit.assertion.DefaultFailureHandler.handle(DefaultFailureHandler.java:223)
at org.dbunit.assertion.DbUnitAssert.compareData(DbUnitAssert.java:524)
at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:409)
at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:312)
at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:274)
at org.dbunit.assertion.DbUnitAssert.assertEqualsIgnoreCols(DbUnitAssert.java:122)
at org.dbunit.Assertion.assertEqualsIgnoreCols(Assertion.java:74)
at com.github.springtestdbunit.assertion.NonStrictDatabaseAssertion.assertEquals(NonStrictDatabaseAssertion.java:56)
at com.github.springtestdbunit.assertion.NonStrictDatabaseAssertion.assertEquals(NonStrictDatabaseAssertion.java:48)
at com.github.springtestdbunit.DbUnitRunner.verifyExpected(DbUnitRunner.java:154)
at com.github.springtestdbunit.DbUnitRunner.verifyExpected(DbUnitRunner.java:120)
at com.github.springtestdbunit.DbUnitRunner.afterTestMethod(DbUnitRunner.java:86)
at com.github.springtestdbunit.DbUnitTestExecutionListener.afterTestMethod(DbUnitTestExecutionListener.java:190)
at org.springframework.test.context.TestContextManager.afterTestMethod(TestContextManager.java:319)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:94)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)`
第一遍运行成功是因为第一次插入数据到测试数据库时，从原始数据ID=2增长到ID=3，和预期一致，后面执行完毕，数据虽然回滚删除了，但是自增ID的游标没变，下次插入数据ID就是从4开始的，所以与预期的ID=3不一致，我的代码和数据文件如下：
@Test
@ExpectedDatabase(value = "facultyService.createFaculty.expectedData.xml", assertionMode = DatabaseAssertionMode.NON_STRICT)
public void testCreateFaculty() {
Faculty faculty = new Faculty();
faculty.setCode("rjgc");
faculty.setName("软件工程");
faculty.setAbbreviation("rjgc");
facultyService.createFaculty(faculty);
}
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<FACULTY id="1" code="jsj" name="计算机系" abbreviation="jsj"/>
<FACULTY id="2" code="cstmgc" name="测试更新土木工程系" abbreviation="cstmgc"/>
</dataset>
解决方法：在插入操作之前把ID自增的游标定位到当前初始数据的最大ID处，下一次插入数据的ID就是最大ID+1，代码如下:
package test.edu.util;
import org.springframework.context.ApplicationContext;
import org.springframework.core.env.Environment;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class DbTestUtil {
//test.reset.sql.maxId = SELECT max(Id) FROM %s;
private static final String MAX_ID = "test.reset.sql.maxId";
//test.reset.sql.template = ALTER TABLE %s AUTO_INCREMENT = maxId;
private static final String RESET = "test.reset.sql.template";
private DbTestUtil() {
}
public static void resetAutoIncrementColumns(ApplicationContext applicationContext,
String... tableNames) throws SQLException {
DataSource dataSource = applicationContext.getBean(DataSource.class);
String resetSqlTemplate = getResetSqlTemplate(applicationContext, RESET);
String getMaxIdTemplate = getResetSqlTemplate(applicationContext, MAX_ID);
try (Connection dbConnection = dataSource.getConnection()) {
//Create SQL statements that reset the auto increment columns and invoke
//the created SQL statements.
for (String resetSqlArgument : tableNames) {
try (Statement statement = dbConnection.createStatement()) {
String maxId = String.format(getMaxIdTemplate, resetSqlArgument);
ResultSet resultSet = statement.executeQuery(maxId);
if (resultSet.next()) {
String id = resultSet.getString(1);
String resetSql = String.format(resetSqlTemplate, resetSqlArgument);
resetSql = resetSql.replace("maxId", id);
statement.execute(resetSql);
}
}
}
}
}
private static String getResetSqlTemplate(ApplicationContext applicationContext, String option) {
//Read the SQL template from the properties file
Environment environment = applicationContext.getBean(Environment.class);
return environment.getRequiredProperty(option);
}
}
@DatabaseSetup("facultyService.data.xml")
public class FacultyServiceTest extends BaseServiceTest {
@Autowired
private FacultyService facultyService;
@Autowired
private ApplicationContext applicationContext;
@Before
public void setUp() throws SQLException {
DbTestUtil.resetAutoIncrementColumns(applicationContext, "faculty", "faculty_room");
}
@Test
@ExpectedDatabase(value = "facultyService.createFaculty.expectedData.xml", assertionMode = DatabaseAssertionMode.NON_STRICT)
public void testCreateFaculty() {
Faculty faculty = new Faculty();
faculty.setCode("rjgc");
faculty.setName("软件工程");
faculty.setAbbreviation("rjgc");
facultyService.createFaculty(faculty);
}
}
如果还有什么问题，可以在下面留言。
