Search code examples
mysqldbunit

DBUnit ignores column aliases, when creating dataset from query?

I have two datasets, which I want to compare with DBUnit: one defines as XML file, the other I am trying to create using a query:

dbConnection.createQueryTable("my_table", "SELECT ...");

Select goes over couple of tables, where the few of them has the same column names, so I am using aliases to distinguish between them. The query runs fine, but seems that while creating a dataset, DBUnit ignores the column aliases and uses the original names instead. This clearly is seen in DEBUG log:

01.03.13 14:18:43,228 [ DEBUG] AbstractDatabaseConnection - createQueryTable(resultName=my_table, sql=
        SELECT `table1`.`name`
            , `table2`.`label` AS `table2_label`
            , `table3`.`label`
        FROM `table1`
            , `table2`
            , `table3`
        WHERE `table1`.`id` = `table2`.`table1_id`
            AND `table2`.`id` = `table3`.`table2_id`
    ) - start
01.03.13 14:18:43,228 [ DEBUG] DatabaseDataSourceConnection - getConnection() - start
01.03.13 14:18:43,228 [ DEBUG] DatabaseConfig$Configurator - Statement fetch size set to 100
01.03.13 14:18:43,240 [ DEBUG] DatabaseDataSourceConnection - getConnection() - start
01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The 'schemaName' from the ResultSetMetaData is empty-string and not applicable hence. Will not try to lookup column properties via DatabaseMetaData.getColumns.
01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The 'schemaName' from the ResultSetMetaData is empty-string and not applicable hence. Will not try to lookup column properties via DatabaseMetaData.getColumns.
01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The 'schemaName' from the ResultSetMetaData is empty-string and not applicable hence. Will not try to lookup column properties via DatabaseMetaData.getColumns.
01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=0, columnName=name) - start
01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=0, columnName=label) - start
01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=0, columnName=label) - start
01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=1, columnName=name) - start
01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=1, columnName=label) - start
01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=1, columnName=label) - start
01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=2, columnName=name) - start
01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=2, columnName=label) - start
01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable - getValue(row=2, columnName=label) - start

As you see - getValue(row=X) uses label as name for table2 and table3 columns and not table2_label and label as defined in query...

How to solve this? I am using MySQL database.

Solution

  • This looks like its caused by a change in the MYSQL Java connector

    Caused by DatabaseTableMetaData calling ResultSetMetaData#getColumnName() rather than #getColumnLabel()

    You don't state what version of DBUnit/MySql you are using, but DBUnit 2.2 & 2.4.9 are affected with MySql connector 5.1.26.

    As a workaround, the MySQL devs have included a backward compatability option, discussed in the change above.

    To configure it, add a property to your JDBC url:

    jdbc:mysql://host/db_name?useOldAliasMetadataBehavior=true

    As per these instructions.