Oracle + dbunit获取AmbiguousTableNameException

Question

我正在使用dbunit创建数据库备份，这些备份可以导入和导出。我的应用程序可以使用多种数据库引擎: MySQL、PostgreSQL、SQLServer、H2和Oracle。

使用以下代码可以很好地执行上述所有操作：

            // Connect to the database 
        conn =BackupManager.getInstance().getConnection();
        IDatabaseConnection connection = new DatabaseConnection(conn);
        InputSource xmlSource = new InputSource(new FileInputStream(new File(nameXML)));
        FlatXmlProducer flatXmlProducer = new FlatXmlProducer(xmlSource);
        flatXmlProducer.setColumnSensing(true);

        DatabaseOperation.CLEAN_INSERT.execute(connection,new FlatXmlDataSet(flatXmlProducer));  

但在Oracle上，我得到了这样的异常：

!ENTRY es.giro.girlabel.backup 1 0 2012-04-11 11:51:40.542
!MESSAGE Start import backup
org.dbunit.database.AmbiguousTableNameException: AQ$_SCHEDULES
    at org.dbunit.dataset.OrderedTableNameMap.add(OrderedTableNameMap.java:198)
    at org.dbunit.database.DatabaseDataSet.initialize(DatabaseDataSet.java:231)
    at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:281)
    at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
    at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
    at es.giro.girlabel.backup.ImportBackup.createData(ImportBackup.java:39)
    at es.giro.girlabel.backup.handlers.Import.execute(Import.java:45)

Answer 1

从docs

public class AmbiguousTableNameException extends DataSetException

当可以访问多个具有相同名称的表时，IDataSet会抛出此异常。当数据库连接可以访问包含相同表名的多个模式时，通常会发生这种情况。

可能的解决方案：

1)使用只能访问一个数据库架构的数据库连接凭据。

2)为DatabaseConnection或DatabaseDataSourceConnection构造函数指定架构名称。

3)启用限定表名支持(请参阅How-to文档)。

Answer 2

为谁使用SpringDBUnit。我一直在努力解决这个非常恼人的问题。我最终通过添加com.github.springtestdbunit.bean.DatabaseConfigBean和com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean的配置解决了这个问题。

这是我关于SpringDBUnit的完整spring context

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521/XE" />
        <property name="username" value="xxxx" />
        <property name="password" value="xxxx" />
    </bean>


    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
            </props>
        </property>
        <property name="annotatedClasses">
            <list>
                <value>xxx.example.domain.Person</value>
            </list>
        </property>
    </bean>

    <bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
        <property name="skipOracleRecyclebinTables" value="true" />
        <property name="qualifiedTableNames" value="true" />
        <!-- <property name="caseSensitiveTableNames" value="true"/> -->
    </bean>
    <bean id="dbUnitDatabaseConnection"
        class="com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="databaseConfig" ref="dbUnitDatabaseConfig" />
        <property name="schema" value="<your_schema_name>"/>
    </bean>

Answer 3

设置数据库模式为我修复了它：

@Bean
public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(final DataSource dataSource){
    final DatabaseDataSourceConnectionFactoryBean connectionFactory = new DatabaseDataSourceConnectionFactoryBean();
    connectionFactory.setDataSource(dataSource);
    connectionFactory.setSchema(DB_SCHEMA);
    return connectionFactory;
}