[ DBUnit ] How to reset auto incremental column

In the DBunit default implementation, method getSetupOperation() returns DatabaseOperation.CLEAN_INSERT, which is composed of DatabaseOperation.DELETE_ALL and DatabaseOperation.INSERT .

http://dbunit.sourceforge.net/components.html#databaseoperation

It works correctly if no field has auto-incremental property.

We have two tables like

CREATE TABLE Items(
    itemId  INTGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1000 ) PRIMARY KEY,
    name VARCHAR(32),
    .....
);

CREATE TABLE ItemCategory (
    itemId INTEGER NOT NULL,
    ......
    CONSTRAINT ITEMCATEGORY_PK_ALL PRIMARY KEY(ITEMID,CATEGORYID)
);

<< testDb.xml>>
<Items name="sword" description="......" />
<Items name="arrow" descriptio= "...." />

The field Items.itemId increases from 1000 by 1.

If two rows defined in the test xml file are inserted into the table "Items" after first test method execution, next integer value for Items.itemId will be 1002.

But each of test methods is executed independently, following test may fails due to the effect of former test method inserting two rows int the table.(DatabaseOperation.CLEAN_INSERT do not reset the incremental  column property)

The solution is to provide operation which reset the sequence.

public class SequenceResetOperation extends DatabaseOperation {
    @Override
    public void execute(IDatabaseConnection connection, IDataSet dataSet)
                throws DatabaseUnitException, SQLException {
        Connection conn = connection.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute("ALTER TABLE ITEMS ALTER COLUMN ITEMID RESTART WITH 10000");
            stmt.close();
        }

and override method getSetUpOperation() with new composite database operation

public class AbstractDBTest extends DatabaseTestCase {
    .....
    @Override
    protected DatabaseOperation getSetUpOperation() throws Exception {
        DatabaseOperation op = new CompositeOperation(
            new DatabaseOperation[]{
                DatabaseOperation.DELETE_ALL,
                new SequenceResetOperation(),
                DatabaseOperation.INSERT
            });
        return op;
    }
}