DDLUtils and autoincrement values

When trying to use DDLUtils it always seems to take over the id values for the columns that are set to autoincrement. How can I prevent this?

For example, I have a Dogs table with a column called ownerID. The column ownerID is set to autoincrement. However my list of owners is not continuous, th开发者_开发知识库ere are gaps (for example, ownerID's 2, 4, 5, 6, 7, 10 exist but not 1, 3, 8, 9 because they've since been deleted). The problem is that on DdlToDatabase restoring, the owner ids are reset to 1, 2, 3, 4, and so on. This means that the links in my Dogs table through ownerID are now all incorrect.

How can I get the DDlUtils to correctly import the values for autoincrementing fields?

I don't know much about DDLUtils, but for this sort of ETL (export, transform, load) operation, you have to do a bit more work. I know two ways that are common

• Insert the new Dog record. Keep a map of the old ID to the new ID. When inserting into another table that has an FK to ownerID, use the map to choose the new ID.

    Original Dogs           New Dogs  
    [ID]  [Name]            [ID]  [Name]
    2     Henry              1    Henry
    5     Oliver             2    Oliver

When you insert both records, you end up with an old to new map of [ 2 => 1, 5 => 2 ]. Then when you insert any record that uses an ID of 2, you instead use 1.

• Another way is to disable the auto increment nature of the table when loading your data. This is DB specific.

For instance, in SQL Server, you can use set identity_insert Dogs on

In MySQL, you can simple insert records and specify the ID you want.

It looks like you can't import the values from the auto-increment rows, which means if they are referenced in other tables, you're in trouble. Instead what you should do is use UUID's if you wish to go this route.

It's some time ago - but i had the same problem The Solution: First of all your choosen Platform must support identityOverrideAllowed.

Platform mySqlPlatform = PlatformFactory.createNewPlatformInstance(mysqlDataSource);
mySqlPlatform.getPlatformInfo().setIdentityOverrideAllowed(true);

You also have to set isIdentityOverrideOn An example how to set it at you can find in the sourcecode.DDLUtils

org.apache.ddlutils.platform.mssql.MsSqlPlatform...

/**
 * Determines whether we need to use identity override mode for the given table.
 * 
 * @param table The table
 * @return <code>true</code> if identity override mode is needed
 */
private boolean useIdentityOverrideFor(Table table)
{
    return isIdentityOverrideOn() &&
           getPlatformInfo().isIdentityOverrideAllowed() &&
           (table.getAutoIncrementColumns().length > 0);
}

/**
 * {@inheritDoc}
 */
protected void beforeInsert(Connection connection, Table table) throws SQLException
{
    if (useIdentityOverrideFor(table))
    {
        MSSqlBuilder builder = (MSSqlBuilder)getSqlBuilder();
     connection.createStatement().execute(builder.getEnableIdentityOverrideSql(table));
    }
}

/**
 * {@inheritDoc}
 */
protected void afterInsert(Connection connection, Table table) throws SQLException
{
    if (useIdentityOverrideFor(table))
    {
        MSSqlBuilder builder = (MSSqlBuilder)getSqlBuilder();
    connection.createStatement().execute(builder.getDisableIdentityOverrideSql(table));
    }
}