Java 从 Oracle 实例创建内存数据库结构

Create an in-memory database structure from an Oracle instance

提问by Romain Linsolas

I have an application where many "unit"tests use a real connection to an Oracle database during their execution.

我有一个应用程序，其中许多“单元”测试在执行期间使用与 Oracle 数据库的真实连接。

As you can imagine, these tests take too much time to be executed, as they need to initialize some Spring contexts, and communicate to the Oracle instance. In addition to that, we have to manage complex mechanisms, such as transactions, in order to avoid database modifications after the test execution (even if we use usefull classes from Spring like AbstractAnnotationAwareTransactionalTests).

可以想象，这些测试需要太多时间来执行，因为它们需要初始化一些 Spring 上下文，并与 Oracle 实例进行通信。除此之外，我们必须管理复杂的机制，例如事务，以避免在测试执行后修改数据库（即使我们使用 Spring 中的有用类，例如AbstractAnnotationAwareTransactionalTests）。

So my idea is to progressively replace this Oracle test instance by an in-memory database. I will use hsqldbor maybe better h2.

所以我的想法是逐步用内存数据库替换这个 Oracle 测试实例。我会使用hsqldb或者更好h2。

My question is to know what is the best approach to do that. My main concern is related to the construction of the in-memory database structure and insertion of reference data.

我的问题是要知道什么是最好的方法来做到这一点。我主要关心的是内存数据库结构的构建和参考数据的插入。

Of course, I can extract the database structure from Oracle, using some tools like SQL Developeror TOAD, and then modifying these scripts to adapt them to the hsqldbor h2language. But I don't think that's the better approach.

当然，我可以从 Oracle 中提取数据库结构，使用诸如SQL Developeror 之类的工具TOAD，然后修改这些脚本以使其适应hsqldborh2语言。但我认为这不是更好的方法。

---

---

In fact, I already did that on another project using hsqldb, but I have written manually all the scripts to create tables. Fortunately, I had only few tables to create. My main problem during this step was to "translate" the Oracle scripts used to create tables into the hsqldblanguage.

事实上，我已经在另一个项目上使用hsqldb，但我已经手动编写了所有脚本来创建表。幸运的是，我只有几个表要创建。在此步骤中，我的主要问题是将用于创建表的 Oracle 脚本“翻译”为该hsqldb语言。

For example, a table created in Oracle using the following sql command:

例如，在 Oracle 中使用以下 sql 命令创建的表：

CREATE TABLE FOOBAR (
    SOME_ID NUMBER,
    SOME_DATE DATE, -- Add primary key constraint
    SOME_STATUS NUMBER,
    SOME_FLAG NUMBER(1) DEFAULT 0 NOT NULL);

needed to be "translated" for hsqldbto:

需要“翻译”为hsqldb：

CREATE TABLE FOOBAR (
    SOME_ID NUMERIC,
    SOME_DATE TIMESTAMP PRIMARY KEY,
    SOME_STATUS NUMERIC,
    SOME_FLAG INTEGER DEFAULT 0 NOT NULL);

In my current project, there are too many tables to do that manually...

在我当前的项目中，有太多表无法手动完成...

---

---

So my questions:

所以我的问题：

• What are the advices you can give me to achieve that?
• Does h2or hsqldbprovide some tools to generate their scripts from an Oracle connection?

• 你可以给我什么建议来实现这一目标？
• 是否h2或hsqldb提供了一些工具来从 Oracle 连接生成脚本？

---

---

Technical information

技术信息

Java 1.6, Spring 2.5, Oracle 10.g, Maven 2

Java 1.6、Spring 2.5、Oracle 10.g、Maven 2

---

---

Edit

编辑

Some information regarding my unit tests:

关于我的单元测试的一些信息：

In the application where I used hsqldb, I had the following tests: - Some "basic" unit tests, which have nothing to do with DB. - For DAO testing, I used hsqldbto execute database manipulations, such as CRUD. - Then, on the service layer, I used Mockitoto mock my DAO objects, in order to focus on the service test and not the whole applications (i.e. service + dao + DB).

在我使用的应用程序中hsqldb，我进行了以下测试： - 一些与 DB 无关的“基本”单元测试。- 对于 DAO 测试，我曾经hsqldb执行数据库操作，例如 CRUD。- 然后，在服务层，我曾经Mockito模拟我的 DAO 对象，以便专注于服务测试而不是整个应用程序（即服务 + dao + DB）。

In my current application, we have the worst scenario: The DAO layer tests need an Oracle connection to be run. The services layer does notuse (yet) any mock objects to simulate the DAO. So services tests alsoneed an Oracle connection.

在我当前的应用程序中，我们遇到了最糟糕的情况：DAO 层测试需要运行 Oracle 连接。服务层并没有使用（但）任何模拟对象来模拟DAO。所以服务测试也需要一个 Oracle 连接。

I am aware that mocks and in-memory database are two separates points, and I will address them as soon as possible. However, my first step is to tryto remove the Oracle connection by an in-memory database, and then I will use my Mockitoknowledges to enhance the tests.

我知道模拟和内存数据库是两个不同的点，我会尽快解决它们。但是，我的第一步是尝试通过内存数据库删除Oracle连接，然后我将利用我的Mockito知识来增强测试。

Note that I also want to separate unit tests from integration tests. The latter will need an access to the Oracle database, to execute "real" tests, but my main concern (and this is the purpose of this question) is that almost all of my unit tests are not run in isolation today.

请注意，我还想将单元测试与集成测试分开。后者将需要访问 Oracle 数据库，以执行“真正的”测试，但我主要担心（这就是这个问题的目的）是，我今天几乎所有的单元测试都不是孤立运行的。

采纳答案by Thomas Mueller

Use an in-memory / Java database for testing. This will ensure the tests are closer to the real world than if you try to 'abstract away' the database in your test. Probably such tests are also easier to write and maintain. On the other hand, what you probably do want to 'abstract away' in your tests is the UI, because UI testing is usually hard to automate.

使用内存/Java 数据库进行测试。这将确保测试更接近真实世界，而不是尝试在测试中“抽象掉”数据库。可能这样的测试也更容易编写和维护。另一方面，您可能希望在测试中“抽象化”的是 UI，因为 UI 测试通常很难自动化。

The Oracle syntax you posted works well with the H2 database (I just tested it), so it seems H2 supports the Oracle syntax better than HSQLDB. Disclaimer: I'm one of the authors of H2. If something doesn't work, please post it on the H2 mailing list.

您发布的 Oracle 语法适用于 H2 数据库（我刚刚对其进行了测试），因此 H2 似乎比 HSQLDB 更好地支持 Oracle 语法。免责声明：我是 H2 的作者之一。如果某些东西不起作用，请将其发布在 H2 邮件列表中。

You should anyway have the DDL statements for the database in your version control system. You can use those scripts for testing as well. Possibly you also need to support multiple schema versions - in that case you could write version update scripts (alter table...). With a Java database you can test those as well.

无论如何，您的版本控制系统中应该有数据库的 DDL 语句。您也可以使用这些脚本进行测试。可能您还需要支持多个模式版本 - 在这种情况下，您可以编写版本更新脚本（更改表...）。使用 Java 数据库，您也可以测试它们。

By the way, you don't necessarily need to use the in-memory mode when using H2 or HSQLDB. Both databases are fast even if you persist the data. And they are easy to install (just a jar file) and need much less memory than Oracle.

顺便说一句，在使用 H2 或 HSQLDB 时，您不一定需要使用内存中模式。即使您保留数据，这两个数据库也很快。而且它们很容易安装（只是一个 jar 文件）并且需要比 Oracle 少得多的内存。

回答by rics

What are your unit tests for? If they test the proper working of DDLs and stored procedures then you should write the tests "closer" to Oracle: either without Java code or without Spring and other nice web interfaces at all focusing on the db.

你的单元测试是为了什么？如果他们测试 DDL 和存储过程的正常工作，那么您应该编写“更接近”Oracle 的测试：要么没有 Java 代码，要么没有 Spring 和其他专注于数据库的漂亮 Web 界面。

If you want to test the application logic implemented in Java and Spring then you may use mock objects/database connection to make your tests independent of the database.

如果您想测试在 Java 和 Spring 中实现的应用程序逻辑，那么您可以使用模拟对象/数据库连接来使您的测试独立于数据库。

If you want to test the working as a whole (what is against the modular development and testing principle) then you may virtualize your database and test on that instance without having the risk of doing some nasty irreversible modifications.

如果您想整体测试工作（什么是违反模块化开发和测试原则的），那么您可以虚拟化您的数据库并在该实例上进行测试，而不会冒做一些讨厌的不可逆修改的风险。

回答by fredt

Latest HSQLDB 2.0.1 supports ORACLE syntax for DUAL, ROWNUM, NEXTVAL and CURRVAL via a syntax compatibility flag, sql.syntax_ora=true. In the same manner, concatenation of a string with a NULL string and restrictions on NULL in UNIQUE constraints are handled with other flags. Most ORACLE functions such as TO_CHAR, TO_DATE, NVL etc. are already built in.

最新的 HSQLDB 2.0.1 通过语法兼容性标志 sql.syntax_ora=true 支持 DUAL、ROWNUM、NEXTVAL 和 CURRVAL 的 ORACLE 语法。以同样的方式，字符串与 NULL 字符串的连接以及 UNIQUE 约束中对 NULL 的限制是用其他标志处理的。大多数 ORACLE 函数，例如 TO_CHAR、TO_DATE、NVL 等，都已经内置。

At the moment, to use simple ORACLE types such as NUMBER, you can use a type definition:

目前，要使用简单的 ORACLE 类型（例如 NUMBER），您可以使用类型定义：

CREATE TYPE NUMBER AS NUMERIC

将类型编号创建为数字

The next snapshot will allow NUMBER(N) and other aspects of ORACLE type compatibility when the flag is set.

下一个快照将在设置标志时允许 NUMBER(N) 和 ORACLE 类型兼容性的其他方面。

Download from http://hsqldb.org/support/

从http://hsqldb.org/support/下载

[Update:] The snapshot issued on Oct 4 translates most Oracle specific types to ANSI SQL types. HSQLDB 2.0 also supports the ANSI SQL INTERVAL type and date / timestamp arithmetic the same way as Oracle.

[更新：] 10 月 4 日发布的快照将大多数 Oracle 特定类型转换为 ANSI SQL 类型。HSQLDB 2.0 也以与 Oracle 相同的方式支持 ANSI SQL INTERVAL 类型和日期/时间戳算法。

回答by oksayt

As long as your tests clean up after themselves (as you already seem to know how to set up), there's nothing wrong with running tests against a real database instance. In fact it's the approach I usually prefer, because you'll be testing something as close to production as possible.

只要您的测试自行清理（因为您似乎已经知道如何设置），针对真实数据库实例运行测试就没有错。事实上，这是我通常更喜欢的方法，因为您将测试尽可能接近生产的东西。

The incompatibilities seem small, but really end up biting back not so long afterwards. In a good case, you may get away with some nasty sql translation / extensive mockery. In bad cases, parts of the system will be just impossible to test, which I think is an unacceptable risk for business-critical systems.

不兼容性看起来很小，但不久之后真的会反咬一口。在一个好的情况下，你可能会逃脱一些讨厌的 sql 翻译/大量的嘲弄。在糟糕的情况下，系统的某些部分将无法进行测试，我认为这对于关键业务系统来说是一个不可接受的风险。