No Primary Key Table

This page illustrates how to the http://www.dbunit.org/properties/primaryKeyFilter property to refresh a table which do not have any primary key. In this situation, you must make sure one of the columns can be chosen as a "pseudo-primary key". In the table below, A1 will be our pseudo PK:

CREATE TABLE `NO_PK` (
  `A1` int(11) NOT NULL,
  `A2` varchar(50) default NULL
);

We want to populate this table with the following records (stored in a file called no-pk.xml):

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
  <NO_PK A1="1" A2="Test1" />
  <NO_PK A1="2" A2="Test2" />
  <NO_PK A1="3" />
</dataset>

The class below uses the http://www.dbunit.org/properties/primaryKeyFilter property by passing a new IColumnFilter which simply checks if the name of the column is the name of the pseudo-key we've chosen:

import java.io.File;
import java.sql.DriverManager;

import junit.framework.TestCase;

import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.filter.IColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.ext.mysql.MySqlConnection;
import org.dbunit.operation.DatabaseOperation;

public class NoPrimaryKeyTableTest extends TestCase {

    protected void setUp() throws Exception {
        super.setUp();

        // Create the SQL connection
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/sandbox", "root", "secret");

        // initialize your database connection here
        IDatabaseConnection connection = new MySqlConnection(con, null);

        // Set the property by passing the new IColumnFilter
        connection.getConfig().setProperty(
                "http://www.dbunit.org/properties/primaryKeyFilter",
                new MyPrimaryKeyFilter("A1"));
        // ...

        // initialize your dataset here
        IDataSet dataSet = new FlatXmlDataSet(new File("./no-pk.xml"));
        // ...

        try {
            DatabaseOperation.REFRESH.execute(connection, dataSet);
        } finally {
            connection.close();
        }
    }

    public void testMe() throws Exception {
        // ...
    }

    class MyPrimaryKeyFilter implements IColumnFilter {
        private String pseudoKey = null;

        MyPrimaryKeyFilter(String pseudoKey) {
            this.pseudoKey = pseudoKey;
        }

        public boolean accept(String tableName, Column column) {
            return column.getColumnName().equalsIgnoreCase(pseudoKey);
        }

    }
}
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License