DbUnit Assertion floating-point numbers

Question

I'm testing my DAO layer using DbUnit. I'm prefilling database from XML dataset, doing some actions and then asserting against known result.

Assertion.assertEquals(expectedDataSet, actualDataSet);

Dataset contains column with floating point number. Then these columns are compared, I get:

junit.framework.ComparisonFailure: value (table=OrderLine_T, row=2, col=price) expected:<2.99[]> but was:<2.99[0000009536743]>.

The values are equal, but because floating-point numbers cannot be exactly represented in binary form, the assertion fails. In JUnit we have assertEquals(double expected, double actual, double delta). How do we set some delta in DbUnit for floating-point number comparison ?

Initial dataset:

<dataset>
    <Customer_T id="1" name="Anthony"/>
    <Customer_T id="2" name="John"/>

    <Order_T id="1" date="2012-06-07 14:30" customer_id="1" />
    <Order_T id="2" date="2012-06-07 15:31" customer_id="2" />

    <OrderLine_T id="1" order_id="1" product_id="1" price="2.99" quantity="5" />
    <OrderLine_T id="2" order_id="2" product_id="2" price="3.49" quantity="10" />
</dataset>

Expected result:

<dataset>
    <Customer_T id="1" name="Anthony"/>
    <Customer_T id="2" name="John"/>

    <Order_T id="1" date="2012-06-07 14:30" customer_id="1" />
    <Order_T id="2" date="2012-06-07 15:31" customer_id="2" />

    <OrderLine_T id="1" order_id="1" product_id="1" price="2.99" quantity="5" />
    <OrderLine_T id="2" order_id="2" product_id="2" price="3.49" quantity="10" />

<!--     Below added -->
    <Order_T id="3" date="1987-06-07 9:15:10" customer_id="2" />
    <OrderLine_T id="3" order_id="3" product_id="1" price="2.99" quantity="2" />
    <OrderLine_T id="4" order_id="3" product_id="5" price="3.55" quantity="8" />
</dataset>

Code:

/* Should save order correctly (including order lines) */
    @Test
    public void save() throws Exception {
        /* Create new order */
        Set<OrderLine> lines = new HashSet<OrderLine>();
        lines.add(new OrderLine(1, (float)2.99, 2));
        lines.add(new OrderLine(5, (float)3.55, 8));

        Calendar cal = Calendar.getInstance();
        cal.set(1987, 6, 7, 9, 15, 10);
        Date date = cal.getTime();

        Customer customer = customerDAO.findById(2); // John

        Order order = new Order(date, lines, customer);
        orderDAO.save(order);
        entityManager.flush();

        /* Assert order is saved */
        IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(Thread.currentThread()
                                                                           .getContextClassLoader()
                                                                           .getResourceAsStream("data-set-afterAddOrder.xml"));
        IDataSet actualDataSet = getDatabaseConnection().createDataSet();

        Assertion.assertEquals(expectedDataSet, actualDataSet);
    }

Edit:

Probably need to mention what I'm using in-memory HSQLDB. Just tried MySQL and it passes successfully.

Tried setting ToleratedDelta without success:

IDatabaseConnection connection = new DatabaseConnection(((SessionImpl) (entityManager.getDelegate())).connection());
HsqldbDataTypeFactory dataTypeFactory = new HsqldbDataTypeFactory();
dataTypeFactory.addToleratedDelta(new ToleratedDelta("OrderLine_T", "price", 0.01));
connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, dataTypeFactory);

Edit2:

I was using hibernate.hbm2ddl.auto = create, to export schema to database. After got idea from fredt, I changed the type of field price on my entity to BigDecimal and added additional column precision and scale params:

@Column(precision=10, scale=4)
private BigDecimal price;

This gets translated to PRICE NUMERIC(10,4). Problem solved, thanks to fredt.

Answer 1

If the column is defined as DECIMAL or NUMERIC with the required precision and scale, the value will have the exace number of digits after the decimal point and the issue can be avoided.