{"id":844,"date":"2012-01-10T21:12:00","date_gmt":"2012-01-10T21:12:00","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/2012\/10\/db-unit-testing-with-dbunit-json-hsqldb-and-junit-rules.html"},"modified":"2012-10-21T22:48:16","modified_gmt":"2012-10-21T22:48:16","slug":"db-unit-testing-with-dbunit-json-hsqldb","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2012\/01\/db-unit-testing-with-dbunit-json-hsqldb.html","title":{"rendered":"DB unit testing with dbUnit, JSON, HSQLDB and JUnit Rules"},"content":{"rendered":"
In this week\u2019s run of my TDD course, I thought it would be interesting to write a little fixture to make it easier to use dbUnit<\/a>. My original thought was just to teach dbUnit about JSON, but it turns out that Lieven Doclo<\/a> has done that already. So I decided to go a step further and also combine dbUnit with JUnit Rules<\/a>, and provide automatic bootstrapping of an HSQLDB<\/a> in-memory object store.
\n<\/span>
\nThe following test shows what I ended up with:<\/p>\n
package com.danhaywood.tdd.dbunit.test;\r\n\r\nimport static org.hamcrest.Matchers.equalTo;\r\nimport static org.hamcrest.Matchers.is;\r\nimport static org.junit.Assert.assertThat;\r\n\r\nimport java.sql.ResultSet;\r\nimport java.sql.Statement;\r\n\r\nimport org.dbunit.Assertion;\r\nimport org.dbunit.dataset.ITable;\r\nimport org.hsqldb.jdbcDriver;\r\nimport org.junit.Rule;\r\nimport org.junit.Test;\r\n\r\nimport com.danhaywood.tdd.dbunit.DbUnitRule;\r\nimport com.danhaywood.tdd.dbunit.DbUnitRule.Ddl;\r\nimport com.danhaywood.tdd.dbunit.DbUnitRule.JsonData;\r\n\r\npublic class DbUnitRuleExample {\r\n\r\n    @Rule\r\n    public DbUnitRule dbUnit = new DbUnitRule(\r\n            DbUnitRuleExample.class, jdbcDriver.class,\r\n            \"jdbc:hsqldb:file:src\/test\/resources\/testdb\", \"SA\", \"\");\r\n\r\n@Ddl(\"customer.ddl\")\r\n    @JsonData(\"customer.json\")\r\n    @Test\r\n    public void update_lastName() throws Exception {\r\n        \/\/ when\r\n        Statement statement = dbUnit.getConnection().createStatement();\r\n        statement.executeUpdate(\"update customer set last_name='Bloggs' where id=2\");\r\n\r\n        \/\/ then (verify directly)\r\n        ResultSet rs2 = dbUnit.executeQuery(\"select last_name from customer where id = 2\");\r\n        assertThat(rs2.next(), is(true));\r\n        assertThat(rs2.getString(\"last_name\"), equalTo(\"Bloggs\"));\r\n\r\n        \/\/ then (verify using datasets)\r\n        ITable actualTable = dbUnit.createQueryTable(\"customer\", \"select * from customer order by id\");\r\n        ITable expectedTable = dbUnit.jsonDataSet(\"customer-updated.json\").getTable(\"customer\");\r\n\r\n        Assertion.assertEquals(expectedTable, actualTable);\r\n    }\r\n}\r\n<\/pre>\n
where customer.ddl<\/tt> is:<\/p>\n
drop table customer if exists;\r\ncreate table customer (\r\n id         int         not null primary key\r\n   ,first_name varchar(30) not null\r\n   ,initial    varchar(1)  null\r\n   ,last_name  varchar(30) not null\r\n)\r\n<\/pre>\n
and customer.json<\/tt> (the initial data set) is:<\/p>\n
{\r\n  \"customer\":\r\n   [\r\n     {\r\n       \"id\": 1,\r\n       \"first_name\": \"John\",\r\n       \"initial\": \"K\",\r\n       \"last_name\": \"Smith\"\r\n     },\r\n     {\r\n       \"id\": 2,\r\n       \"first_name\": \"Mary\",\r\n       \"last_name\": \"Jones\"\r\n     }\r\n   ]\r\n}\r\n<\/pre>\n
and customer-updated.json<\/tt> (the final data set) is:<\/p>\n
{\r\n  \"customer\":\r\n   [\r\n     {\r\n       \"id\": 1,\r\n       \"first_name\": \"John\",\r\n       \"initial\": \"K\",\r\n       \"last_name\": \"Smith\"\r\n     },\r\n     {\r\n       \"id\": 2,\r\n       \"first_name\": \"Mary\",\r\n       \"last_name\": \"Bloggs\"\r\n     }\r\n   ]\r\n}\r\n<\/pre>\n
As you\u2019ve probably figured out, the @Ddl<\/tt> annotation optionally specifies DDL script(s) to run against the database, while the @JsonData<\/tt> defines a JSON-formatted dataset.<\/p>\n
The actual implementation of the DbUnitRule<\/tt> class is:<\/p>\n
package com.danhaywood.tdd.dbunit;\r\n\r\nimport java.lang.annotation.ElementType;\r\nimport java.lang.annotation.Retention;\r\nimport java.lang.annotation.RetentionPolicy;\r\nimport java.lang.annotation.Target;\r\nimport java.nio.charset.Charset;\r\nimport java.sql.Connection;\r\nimport java.sql.ResultSet;\r\nimport java.sql.SQLException;\r\n\r\nimport org.dbunit.IDatabaseTester;\r\nimport org.dbunit.JdbcDatabaseTester;\r\nimport org.dbunit.database.IDatabaseConnection;\r\nimport org.dbunit.dataset.DataSetException;\r\nimport org.dbunit.dataset.IDataSet;\r\nimport org.dbunit.dataset.ITable;\r\nimport org.junit.rules.MethodRule;\r\nimport org.junit.runners.model.FrameworkMethod;\r\nimport org.junit.runners.model.Statement;\r\n\r\nimport com.google.common.io.Resources;\r\n\r\npublic class DbUnitRule implements MethodRule {\r\n\r\n    @Retention(RetentionPolicy.RUNTIME)\r\n    @Target({ ElementType.METHOD })\r\n    public static @interface Ddl {\r\n        String[] value();\r\n    }\r\n\r\n    @Retention(RetentionPolicy.RUNTIME)\r\n    @Target({ ElementType.METHOD })\r\n    public static @interface JsonData {\r\n        String value();\r\n    }\r\n\r\n    private final Class<?> resourceBase;\r\n\r\n    private IDatabaseTester databaseTester;\r\n    private IDatabaseConnection dbUnitConnection;\r\n\r\n    private Connection connection;\r\n    private java.sql.Statement statement;\r\n\r\n    public DbUnitRule(Class<?> resourceBase, Class<?> driver, String url, String user, String password) {\r\n        this.resourceBase = resourceBase;\r\n        try {\r\n            databaseTester = new JdbcDatabaseTester(driver.getName(), url, user, password);\r\n            dbUnitConnection = databaseTester.getConnection();\r\n            connection = dbUnitConnection.getConnection();\r\n            statement = connection.createStatement();\r\n        } catch (Exception e) {\r\n            throw new RuntimeException(e);\r\n        }\r\n    }\r\n\r\n    @Override\r\n    public Statement apply(final Statement base, final FrameworkMethod method, final Object target) {\r\n\r\n        return new Statement() {\r\n\r\n            @Override\r\n            public void evaluate() throws Throwable {\r\n\r\n                try {\r\n                    Ddl ddl = method.getAnnotation(Ddl.class);\r\n                    if (ddl != null) {\r\n                        String[] values = ddl.value();\r\n                        for (String value : values) {\r\n                            executeUpdate(Resources.toString(\r\n                                resourceBase.getResource(value), Charset.defaultCharset()));\r\n                        }\r\n                    }\r\n\r\n                    JsonData data = method.getAnnotation(JsonData.class);\r\n                    if (data != null) {\r\n                        IDataSet ds = new JSONDataSet(resourceBase.getResourceAsStream(data.value()));\r\n                        databaseTester.setDataSet(ds);\r\n                    }\r\n\r\n                    databaseTester.onSetup();\r\n                    base.evaluate();\r\n                } finally {\r\n                    databaseTester.onTearDown();\r\n                }\r\n            }\r\n        };\r\n    }\r\n\r\n    public java.sql.Connection getConnection() {\r\n        return connection;\r\n    }\r\n\r\n    public void executeUpdate(String sql) throws SQLException {\r\n        statement.executeUpdate(sql);\r\n    }\r\n\r\n    public ResultSet executeQuery(String sql) throws SQLException {\r\n        return statement.executeQuery(sql);\r\n    }\r\n\r\n    public IDataSet jsonDataSet(String datasetResource) {\r\n        return new JSONDataSet(resourceBase.getResourceAsStream(datasetResource));\r\n    }\r\n\r\n    public ITable createQueryTable(String string, String string2) throws DataSetException, SQLException {\r\n        return dbUnitConnection.createQueryTable(string, string2);\r\n    }\r\n}\r\n<\/pre>\n
This uses Lieven Doclo\u2019s JSONDataSet (copied here for your convenience):
 
<\/div> <\/div><\/p>\n
import org.codehaus.jackson.map.ObjectMapper;\r\n\r\nimport org.dbunit.dataset.*;\r\nimport org.dbunit.dataset.datatype.DataType;\r\n\r\nimport java.io.File;\r\nimport java.io.FileInputStream;\r\nimport java.io.IOException;\r\nimport java.io.InputStream;\r\nimport java.util.*;\r\n\r\n\/**\r\n * DBUnit DataSet format for JSON based datasets. It is similar to the flat XML layout,\r\n * but has some improvements (columns are calculated by parsing the entire dataset, not just\r\n * the first row). It uses Jackson, a fast JSON processor.\r\n * <br\/><br\/>\r\n * The format looks like this:\r\n * <br\/>\r\n * <pre>\r\n * {\r\n *    \"&lt;table_name&gt;\": [\r\n *        {\r\n *             \"&lt;column&gt;\":&lt;value&gt;,\r\n *             ...\r\n *        },\r\n *        ...\r\n *    ],\r\n *    ...\r\n * }\r\n * <\/pre>\r\n * <br\/>\r\n * I.e.:\r\n * <br\/>\r\n * <pre>\r\n * {\r\n *    \"test_table\": [\r\n *        {\r\n *             \"id\":1,\r\n *             \"code\":\"JSON dataset\",\r\n *        },\r\n *        {\r\n *             \"id\":2,\r\n *             \"code\":\"Another row\",\r\n *        }\r\n *    ],\r\n *    \"another_table\": [\r\n *        {\r\n *             \"id\":1,\r\n *             \"description\":\"Foo\",\r\n *        },\r\n *        {\r\n *             \"id\":2,\r\n *             \"description\":\"Bar\",\r\n *        }\r\n *    ],\r\n *    ...\r\n * }\r\n * <\/pre>\r\n *\r\n * @author Lieven DOCLO\r\n *\/\r\npublic class JSONDataSet extends AbstractDataSet {\r\n    \/\/ The parser for the dataset JSON file\r\n    private JSONITableParser tableParser = new JSONITableParser();\r\n\r\n    \/\/ The tables after parsing\r\n    private List<ITable> tables;\r\n\r\n    \/**\r\n     * Creates a JSON dataset based on a file\r\n     * @param file A JSON dataset file\r\n     *\/\r\n\r\n    public JSONDataSet(File file) {\r\n        tables = tableParser.getTables(file);\r\n    }\r\n\r\n    \/**\r\n     * Creates a JSON dataset based on an inputstream\r\n     * @param is An inputstream pointing to a JSON dataset\r\n     *\/\r\n    public JSONDataSet(InputStream is) {\r\n        tables = tableParser.getTables(is);\r\n    }\r\n\r\n    @Override\r\n    protected ITableIterator createIterator(boolean reverse) throws DataSetException {\r\n        return new DefaultTableIterator(tables.toArray(new ITable[tables.size()]));\r\n    }\r\n\r\n    private class JSONITableParser {\r\n\r\n        private ObjectMapper mapper = new ObjectMapper();\r\n\r\n        \/**\r\n         * Parses a JSON dataset file and returns the list of DBUnit tables contained in\r\n         * that file\r\n         * @param jsonFile A JSON dataset file\r\n         * @return A list of DBUnit tables\r\n         *\/\r\n        public List<ITable> getTables(File jsonFile) {\r\n            try {\r\n                return getTables(new FileInputStream(jsonFile));\r\n            } catch (IOException e) {\r\n                throw new RuntimeException(e.getMessage(), e);\r\n            }\r\n        }\r\n\r\n        \/**\r\n         * Parses a JSON dataset input stream and returns the list of DBUnit tables contained in\r\n         * that input stream\r\n         * @param jsonStream A JSON dataset input stream\r\n         * @return A list of DBUnit tables\r\n         *\/\r\n        @SuppressWarnings(\"unchecked\")\r\n        public List<ITable> getTables(InputStream jsonStream) {\r\n            List<ITable> tables = new ArrayList<ITable>();\r\n            try {\r\n                \/\/ get the base object tree from the JSON stream\r\n                Map<String, Object> dataset = mapper.readValue(jsonStream, Map.class);\r\n                \/\/ iterate over the tables in the object tree\r\n                for (Map.Entry<String, Object> entry : dataset.entrySet()) {\r\n                    \/\/ get the rows for the table\r\n                    List<Map<String, Object>> rows = (List<Map<String, Object>>) entry.getValue();\r\n                    ITableMetaData meta = getMetaData(entry.getKey(), rows);\r\n                    \/\/ create a table based on the metadata\r\n                    DefaultTable table = new DefaultTable(meta);\r\n                    int rowIndex = 0;\r\n                    \/\/ iterate through the rows and fill the table\r\n                    for (Map<String, Object> row : rows) {\r\n                        fillRow(table, row, rowIndex++);\r\n                    }\r\n                    \/\/ add the table to the list of DBUnit tables\r\n                    tables.add(table);\r\n                }\r\n\r\n            } catch (IOException e) {\r\n                throw new RuntimeException(e.getMessage(), e);\r\n            }\r\n            return tables;\r\n        }\r\n\r\n        \/**\r\n         * Gets the table meta data based on the rows for a table\r\n         * @param tableName The name of the table\r\n         * @param rows The rows of the table\r\n         * @return The table metadata for the table\r\n         *\/\r\n        private ITableMetaData getMetaData(String tableName, List<Map<String, Object>> rows) {\r\n            Set<String> columns = new LinkedHashSet<String>();\r\n            \/\/ iterate through the dataset and add the column names to a set\r\n            for (Map<String, Object> row : rows) {\r\n                for (Map.Entry<String, Object> column : row.entrySet()) {\r\n                    columns.add(column.getKey());\r\n                }\r\n            }\r\n            List<Column> list = new ArrayList<Column>(columns.size());\r\n            \/\/ create a list of DBUnit columns based on the column name set\r\n            for (String s : columns) {\r\n                list.add(new Column(s, DataType.UNKNOWN));\r\n            }\r\n            return new DefaultTableMetaData(tableName, list.toArray(new Column[list.size()]));\r\n        }\r\n\r\n        \/**\r\n         * Fill a table row\r\n         * @param table The table to be filled\r\n         * @param row A map containing the column values\r\n         * @param rowIndex The index of the row to te filled\r\n         *\/\r\n        private void fillRow(DefaultTable table, Map<String, Object> row, int rowIndex) {\r\n            try {\r\n                table.addRow();\r\n                \/\/ set the column values for the current row\r\n                for (Map.Entry<String, Object> column : row.entrySet()) {\r\n                    table.setValue(rowIndex, column.getKey(), column.getValue());\r\n\r\n                }\r\n            } catch (Exception e) {\r\n                throw new RuntimeException(e.getMessage(), e);\r\n            }\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
The libraries I used for this (ie are dependencies) are:<\/p>\n