Oracle + dbunit 得到 AmbiguousTableNameException

Oracle + dbunit gets AmbiguousTableNameException

提问by Josep Rodríguez López

I am using dbunit to create database backups, which can be imported and exported. My application can use several database engines: MySQL, PostgreSQL, SQLServer, H2 and Oracle.

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

All of the above work fine with the following code:

以上所有内容都可以使用以下代码正常工作：

            // 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));  

But on Oracle I get this exception:

但是在 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)

回答by Adam Hawkes

From the docs:

从文档：

public class AmbiguousTableNameException extends DataSetException

This exception is thrown by IDataSetwhen multiple tables having the same name are accessible. This usually occurs when the database connection have access to multiple schemas containing identical table names.

Possible solutions:

1) Use a database connection credential that has access to only one database schema.

2) Specify a schema name to the DatabaseConnectionor DatabaseDataSourceConnectionconstructor.

3) Enable the qualified table name support (see How-to documentation).

public class AmbiguousTableNameException extends DataSetException

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

可能的解决方案：

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

2) 为DatabaseConnectionorDatabaseDataSourceConnection构造函数指定模式名称 。

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

回答by Esca Tran

For whom uses SpringDBUnit. I had struggled with this very annoying issue. I had ended up solving the issue by adding the configuration for com.github.springtestdbunit.bean.DatabaseConfigBeanand com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean.

为谁使用 SpringDBUnit。我一直在努力解决这个非常烦人的问题。我已经结束了通过增加配置解决问题com.github.springtestdbunit.bean.DatabaseConfigBean和com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean。

This is my full spring context for SpringDBUnit

这是我对 SpringDBUnit 的完整 spring 上下文

<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>

回答by knutesten

Setting the database schema fixed it for me:

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

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

回答by Paul Podgorsek

You might encounter issues when importing data from Hibernate before DBUnit runs. According to the database you are using, the casing of table and column names could be important.

在 DBUnit 运行之前从 Hibernate 导入数据时，您可能会遇到问题。根据您使用的数据库，表名和列名的大小写可能很重要。

For example, in HSQL, database names must be declared in uppercase. In case you import data via Hibernate's import.sql, make sure the table names are also in uppercase there, otherwise you'll end up with the following problem:

例如，在 HSQL 中，数据库名称必须以大写形式声明。如果您通过 Hibernate 的 import.sql 导入数据，请确保表名也是大写的，否则您最终会遇到以下问题：

• Hibernate creates the tables in lower case
• DBUnit reads the table names from the DB in lower case
• DBUnit tries to import its datasets using upper case table names
• You end up in a mess, with the ambiguous name exception.

• Hibernate 以小写形式创建表
• DBUnit 从 DB 中读取小写的表名
• DBUnit 尝试使用大写表名导入其数据集
• 您最终陷入混乱，名称不明确的例外。

Remember to also check whether multiple tables were created during a previous run (both upper and lower case), in which case you need to clean it up too.

请记住还要检查是否在上次运行期间创建了多个表（大写和小写），在这种情况下，您也需要清理它。

回答by Smitha Nair

I had the same AmbiguousTableNameException while executing Dbunits aginst Oracle DB. It was working fine and started throwing error one day.

在执行 Dbunits aginst Oracle DB 时，我遇到了相同的 AmbiguousTableNameException。它运行良好，有一天开始抛出错误。

Rootcause: while calling a stored procedure, it got modified by mistake to lower case. When changed to upper case it stared working.

根本原因：在调用存储过程时，错误地将其修改为小写。当更改为大写时，它开始工作。

I could solve this also by setting the shema name to IDatabaseTester like iDatabaseTester.setSchema("SCHEMANAMEINCAPS")

我也可以通过将 shema 名称设置为 IDatabaseTester 来解决这个问题，比如 iDatabaseTester.setSchema("SCHEMANAMEINCAPS")

Also please make sure your connection doesn't access only to many schemas having same table name.

另外请确保您的连接不会只访问许多具有相同表名的模式。