import/export xml for dbunit

Question

Ｈow can we easily import/export database data which dbunit could take in the following format?

<dataset>
   <tablea cola="" colb="" />
   <tableb colc="" cold="" />
</dataset>

I'd like to find a way to export the existing data from database for my unit test.

Chericheria

What have you tried so far? Have you read the documentation at dbunit.org? – Casuist

Ｉ do it manually or use a postgresql client. Just wondering if there is any better way. @MarkRobinson – Chericheria

Answer 1

Blue, this will let you export your data in the format you wanted.

public class DatabaseExportSample {
    public static void main(String[] args) throws Exception {
        // database connection
        Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
        Connection jdbcConnection = DriverManager.getConnection(
                "jdbc:hsqldb:sample", "sa", "");
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

        // partial database export
        QueryDataSet partialDataSet = new QueryDataSet(connection);
        partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'");
        partialDataSet.addTable("BAR");
        FlatXmlDataSet.write(partialDataSet, new FileOutputStream("partial.xml"));

        // full database export
        IDataSet fullDataSet = connection.createDataSet();
        FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));

        // dependent tables database export: export table X and all tables that
        // have a PK which is a FK on X, in the right order for insertion
        String[] depTableNames = 
          TablesDependencyHelper.getAllDependentTables( connection, "X" );
        IDataSet depDataSet = connection.createDataSet( depTableNames );
        FlatXmlDataSet.write(depDataSet, new FileOutputStream("dependents.xml"));
    }
}

Casuist

@blue123, that snippet is from the FAQ, which has other information you may find useful: dbunit.org/faq.html#extract – Adrenal

This copy-pasted snippet contained the same error as in the FAQ – Narcotic

URL above is broken. Use this one: dbunit.sourceforge.net/faq.html#extract – Sixtyfourmo

Answer 2

Exporting is already answered. To complete the answer, this is how you can import your dataset into database. connection is of type IDatabaseConnection. The previous answer (of exporting) by Mark Robinson, contains the code of how to create a database connection.

FlatXmlDataSet dataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(new FileInputStream("dataset.xml"))));
DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);

Formication

The first line can be simply FlatXmlDataSet dataSet = new FlatXmlDataSet(new File("dataset.xml"), true); or FlatXmlDataSet dataSet = new FlatXmlDataSetBuilder().build(new File("dataset.xml")); – Formalize