java 如何在不面临 NoSuchTableException 的情况下使用普通 JDBC 和 HSQLDB 测试 DBUnit？

How do I test with DBUnit with plain JDBC and HSQLDB without facing a NoSuchTableException?

提问by neu242

I am trying to use DBUnit with plain JDBC and HSQLDB, and can't quite get it to work -- even though I've used DBUnit with Hibernate earlier with great success. Here's the code:

我正在尝试将 DBUnit 与普通的 JDBC 和 HSQLDB 一起使用，但不能完全让它工作——即使我早些时候将 DBUnit 与 Hibernate 一起使用并取得了巨大成功。这是代码：

import java.sql.PreparedStatement;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.XmlDataSet;
import org.junit.Test;

public class DummyTest {

    @Test
    public void testDBUnit() throws Exception {
        IDatabaseTester databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem", "sa", "");
        IDataSet dataSet = new XmlDataSet(getClass().getResourceAsStream("dataset.xml"));
        databaseTester.setDataSet(dataSet);
        databaseTester.onSetup();
        PreparedStatement pst = databaseTester.getConnection().getConnection().prepareStatement("select * from mytable");
    }
}

And this is the dataset.xml in question:

这是有问题的 dataset.xml：

<dataset>
    <table name="mytable">
        <column>itemnumber</column>
        <column>something</column>
        <column>other</column>
        <row>
            <value>1234abcd</value>
            <value>something1</value>
            <value>else1</value>
        </row>
    </table>
</dataset>

This test gives me a NoSuchTableException:

这个测试给了我一个 NoSuchTableException：

org.dbunit.dataset.NoSuchTableException: mytable
    at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:282)
    at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
    at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
    at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
    at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
    at DummyTest.testDBUnit(DummyTest.java:18)

If I remove the databaseTester.onSetup() line, I get an SQLException instead:

如果删除 databaseTester.onSetup() 行，则会得到 SQLException：

java.sql.SQLException: Table not found in statement [select * from mytable]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    at DummyTest.testDBUnit(DummyTest.java:19)

The dataset in itself is working, since I can access it like it should:

数据集本身正在运行，因为我可以像它应该的那样访问它：

ITable table = dataSet.getTable("mytable");
String firstCol = table.getTableMetaData().getColumns()[0];
String tName = table.getTableMetaData().getTableName();

What am I missing here?

我在这里错过了什么？

EDIT: As @mlk points out, DBUnit doesn't create tables. If I insert the following before adding the dataset, everything goes smoothly:

编辑：正如@mlk 指出的，DBUnit 不创建表。如果我在添加数据集之前插入以下内容，一切都会顺利进行：

PreparedStatement pp = databaseTester.getConnection().getConnection().prepareStatement(
     "create table mytable ( itemnumber varchar(255) NOT NULL primary key, "
   + " something varchar(255), other varchar(255) )");
pp.executeUpdate();

I posted a followup question as Is there any way for DBUnit to automatically create tables from a dataset or dtd?

我发布了一个后续问题，因为 DBUnit 有没有办法从数据集或 dtd 自动创建表？

回答by Michael Lloyd Lee mlk

dbUnit does not create tables. Nor could it with the limited information given in the XML file. Hibernate I believe can create the tables.

dbUnit 不创建表。也不能使用 XML 文件中提供的有限信息。Hibernate 我相信可以创建表。

This is one of the reasons I stopped using in-memory databases and instead got the DBA to give each developer their own database. Every developer then keeps the database up to date using the same scripts which are later ran on live. This adds a small overhead (all developers need to keep their databases up to date) but means you don't need to mess about building the database for each run and you can be sure that the queries ran in test work in live.

这是我停止使用内存数据库的原因之一，而是让 DBA 为每个开发人员提供自己的数据库。然后，每个开发人员都使用相同的脚本使数据库保持最新，这些脚本随后会实时运行。这增加了一个小的开销（所有开发人员都需要保持他们的数据库是最新的），但这意味着您不需要为每次运行构建数据库而混乱，并且您可以确保在测试工作中运行的查询实时运行。

The second reason was speed. I found creating the in memory-database took a lot longer than simply connecting to an existing database.

第二个原因是速度。我发现创建内存数据库比简单地连接到现有数据库花费的时间要长得多。

The third reason was the tear down is none-destructive (start up wipes the database). This means I can run the SQL under test on the database to help work out why a test is failing.

第三个原因是拆卸是非破坏性的（启动会擦除数据库）。这意味着我可以在数据库上运行测试中的 SQL 以帮助找出测试失败的原因。

---

---

Update: 20171115

更新：20171115

I've since switched to using JUnit rules that start up a real instance of the database serverand something like FlywayDBto build the database (and using the same scripts in live as in test, with the application responsible for building the database). It is significantly slower than using a prebuilt database. However using well defined microservices (and so reducing the functionality that needs testing) and being very tight on which tests gets a database you can migrate such issues and get the benefits of local database that always matches live.

从那以后，我转而使用 JUnit 规则来启动数据库服务器的真实实例，并使用FlywayDB 之类的东西来构建数据库（并使用与测试中相同的脚本，应用程序负责构建数据库）。它比使用预建数据库要慢得多。然而，使用定义明确的微服务（从而减少需要测试的功能）并且对哪些测试获取数据库非常严格，您可以迁移此类问题并获得始终匹配实时的本地数据库的好处。

It does alas mean the test tear down is always destructive, but a well-placed break point solves that.

唉，这确实意味着测试拆除总是具有破坏性的，但是一个位置合适的断点可以解决这个问题。

回答by Asa

...several years later now we have better options

...几年后现在我们有了更好的选择

Spring Boot/ Spring JDBC can initialize a database with plain JDBC.

Spring Boot/Spring JDBC 可以使用普通的 JDBC 初始化数据库。

Spring JDBC has a DataSource initializer feature. Spring Boot enables it by default and loads SQL from the standard locations schema.sqland data.sql(in the root of the classpath). In addition Spring Boot will load the schema-${platform}.sqland data-${platform}.sqlfiles (if present), where platform is the value of spring.datasource.platform, e.g. you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql etc.).

Spring JDBC 具有 DataSource 初始值设定项功能。Spring Boot 默认启用它并从标准位置schema.sql和 data.sql（在类路径的根中）加载 SQL 。此外，Spring Boot 将加载schema-${platform}.sql和data-${platform}.sql文件（如果存在），其中平台是 的值spring.datasource.platform，例如，您可以选择将其设置为数据库的供应商名称（hsqldb、h2、oracle、mysql、postgresql 等）。

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

回答by raoulsson

In case you do create your tables upfront like suggested hereand still get a NoSuchTableException, then there is something wrong with the schema. Before you now turn crazy, fiddling with it in all sorts of weird and wonderful ways, try setting the schema parameter to PUBLICwhen you create the IDatabaseConnection, like so:

如果您确实按照此处建议的方式预先创建了表，但仍然收到 NoSuchTableException，则说明架构有问题。在你现在变得疯狂之前，以各种奇怪和奇妙的方式摆弄它之前，尝试在创建 时将架构参数设置为PUBLICIDatabaseConnection，如下所示：

IDatabaseConnection databaseConnection = new HsqldbConnection(sqlConnection, "PUBLIC");

It took me some stepping through the DbUnit code with the debugger but this seems to do the trick.

我花了一些时间使用调试器逐步完成 DbUnit 代码，但这似乎可以解决问题。