如何模拟数据库进行测试（Java）？

How to simulate a DB for testing (Java)?

提问by Eyal Roth

I'm programming in Java and my applications are making a lot of use of DB. Hence, it is important for me to be able to test my DB usage easily.
What DB tests are all about? For me, they should supply two simple requirements:

我正在用 Java 编程，我的应用程序大量使用 DB。因此，能够轻松测试我的数据库使用情况对我来说很重要。
什么数据库测试是关于什么的？对我来说，他们应该提供两个简单的要求：

1. Verify SQL syntax.
2. More importantly, check that the data is selected/updated/inserted correctly, according to a given situation.

1. 验证 SQL 语法。
2. 更重要的是，根据给定的情况检查数据是否被正确选择/更新/插入。

Well then, it seems that all I need is a DB.
But actually, I prefer not, as there are few difficulties using a DB for a test:

那么，看来我所需要的只是一个数据库。
但实际上，我不喜欢，因为使用 DB 进行测试几乎没有困难：

• "Just get yourself a testing DB, how hard could it be?" - Well, in my working place, to have a personal testing DB is pretty impossible. You have to use a "public" DB, which is accessible for everyone.
• "These tests sure ain't fast..." - DB tests tend to be slower than usual tests. It's really not ideal to have slow tests.
• "This program should handle any case!" - It becomes somewhat annoying and even impossible to try and simulate each and every case in a DB. For each case a certain amount of insert/update queries should be made, which is annoying and takes time.
• "Wait a second, how do you know there are 542 rows in that table?" - One of the main principles in testing, is to be able to test the functionality in a way different from that of your tested-code. When using a DB, there's usually one way to do something, therefore the test is exactly the same as the core-code.

• “给自己找一个测试数据库，这有多难？” - 嗯，在我的工作场所，拥有一个个人测试数据库是非常不可能的。您必须使用每个人都可以访问的“公共”数据库。
• “这些测试肯定不快......” - 数据库测试往往比通常的测试慢。慢测试真的很不理想。
• “这个程序应该可以处理任何情况！” - 尝试模拟数据库中的每个案例变得有些烦人，甚至不可能。对于每种情况，都应该进行一定数量的插入/更新查询，这很烦人并且需要时间。
• “等一下，你怎么知道那个表有542行？” - 测试的主要原则之一是能够以与测试代码不同的方式测试功能。使用数据库时，通常有一种方法可以做某事，因此测试与核心代码完全相同。

So, you can figure out I don't like DBs when it comes to tests (of course I will have to get to this in some point, but I'd rather get there later on my testing, after I found most bugs using the rest of the test methods). But what am I looking for?

所以，你可以发现我在测试时不喜欢 DBs（当然，我必须在某个时候达到这一点，但我宁愿稍后在我的测试中到达那里，在我使用其余测试方法）。但我在寻找什么？

I'm looking for a way to simulate a DB, a mock DB, using the file system or just virtual memory. I thought that maybe there's a Java tool/package which allows to simply construct (using code interface) a DB mock per test, with simulated tables and rows, with SQL verification, and with a code interface for monitoring its status (rather then using SQL).

我正在寻找一种使用文件系统或仅使用虚拟内存来模拟数据库、模拟数据库的方法。我想也许有一个 Java 工具/包允许简单地构造（使用代码接口）每个测试的 DB 模拟，带有模拟表和行，带有 SQL 验证，以及用于监视其状态的代码接口（而不是使用 SQL ）。

Are you familiar with this kind of tool?

你熟悉这种工具吗？

---

---

Edit:Thanks for the answers! Although I was asking for a tool, you also provided me with some tips concerning the problem :) It will take me some time to check out your offers, so I can't say right now whether your answers were satisfying not.

编辑：感谢您的回答！虽然我要求提供一个工具，但您也向我提供了一些有关该问题的提示:) 我需要一些时间来查看您的报价，所以我现在不能说您的回答是否令人满意。

Anyway, here's a better view of what I'm looking for - Imagine a class named DBMonitor, that one of its features is finding the number of rows in a table. Here is an imaginary code of how I would like to test that feature using JUnit:

无论如何，这里有一个我正在寻找的更好的视图 - 想象一个名为 DBMonitor 的类，它的功能之一是查找表中的行数。这是我想如何使用 JUnit 测试该功能的假想代码：

public class TestDBMonitor extends TestCase {

    @Override
    public void setUp() throws Exception {

       MockConnection connection = new MockConnection();

       this.tableName = "table1";
       MockTable table = new MockTable(tableName);

       String columnName = "column1";
       ColumnType columnType = ColumnType.NUMBER;
       int columnSize = 50;
       MockColumn column = new MockColumn(columnName, columnType, columnSize);
       table.addColumn(column);

       for (int i = 0; i < 20; i++) {
           HashMap<MockColumn, Object> fields = new HashMap<MockColumn, Object>();
           fields.put(column, i);
           table.addRow(fields);
       }

       this.connection = connection;
    }

    @Test
    public void testGatherStatistics() throws Exception {

       DBMonitor monitor = new DBMonitor(connection);
       monitor.gatherStatistics();
       assertEquals(((MockConnection) connection).getNumberOfRows(tableName),
                    monitor.getNumberOfRows(tableName));
    }

    String tableName;
    Connection connection;
}

I hope this code is clear enough to understand my idea (excuse me for syntax errors, I was typing manually without my dear Eclipse :P).

我希望这段代码足够清晰，可以理解我的想法（请原谅我的语法错误，我在没有亲爱的 Eclipse 的情况下手动输入：P）。

By the way, I use ORM partially, and my raw SQL queries are quite simple and shouldn't differ from one platform to another.

顺便说一下，我部分使用了 ORM，我的原始 SQL 查询非常简单，并且不应该因平台而异。

采纳答案by piotrek

new answer to old question (but things have moved forward a bit):

旧问题的新答案（但事情已经向前推进了一点）：

How to simulate a DB for testing (Java)?

如何模拟数据库进行测试（Java）？

you don't simulate it. you mock your repositiories and you don't test them or you use the same db in your tests and you test your sqls. All the in-memory dbs are not fully compatible so they won't give you full coverage and reliability. and never ever try to mock/simulate the deep db objects like connection, result set etc. it gives you no value at all and is a nightmare to develop and maintain

你不模拟它。你模拟你的存储库而不测试它们，或者你在测试中使用相同的数据库并测试你的 sql。所有内存数据库并不完全兼容，因此它们不会为您提供完整的覆盖范围和可靠性。并且永远不要尝试模拟/模拟深层数据库对象，如连接、结果集等。它根本没有给你任何价值，并且是开发和维护的噩梦

to have a personal testing DB is pretty impossible. You have to use a "public" DB, which is accessible for everyone

拥有个人测试数据库几乎是不可能的。您必须使用每个人都可以访问的“公共”数据库

unfortunately a lot of companies still use that model but now we have dockerand there are images for almost every db. commercial products have some limitations (like up to a few gb of data) that are non-important for tests. also you need your schema and structure to be created on this local db

不幸的是，许多公司仍在使用该模型，但现在我们有了docker并且几乎每个数据库都有图像。商业产品有一些限制（比如最多几 GB 的数据），这些限制对于测试来说并不重要。您还需要在此本地数据库上创建架构和结构

"These tests sure ain't fast..." - DB tests tend to be slower than usual tests. It's really not ideal to have slow tests.

“这些测试肯定不快......” - 数据库测试往往比通常的测试慢。慢测试真的很不理想。

yes, db tests are slower but they are not that slow. I did some simple measurementsand a typical test took 5-50ms. what takes time is the application startup. there are plenty of ways to speed this up:

是的，数据库测试速度较慢，但​​并没有那么慢。我做了一些简单的测量，一次典型的测试需要 5-50 毫秒。需要时间的是应用程序启动。有很多方法可以加快速度：

• first DI frameworks (like spring) offers a way run only some part of your application. if you write your application with a good separation of db and non-db related logic, then in your test you can start only the db part
• each db have plenty of tuning options that makes it less durable and much faster. that's perfect for testing. postgres example

• you can also put the entire db into tmpfs

• another helpful strategy is to have groups of tests and keep db tests turned off by default (if they really slows your build). this way if someone is actually working on db, he needs to pass additional flag in the cmd line or use IDE (testng groups and custom test selectors are perfect for this)

• 第一个 DI 框架（如 spring）提供了一种仅运行应用程序某些部分的方式。如果您编写的应用程序很好地分离了数据库和非数据库相关逻辑，那么在您的测试中，您只能启动数据库部分
• 每个 db 都有很多调整选项，这使得它的耐用性和速度更快。这非常适合测试。postgres 示例

• 您也可以将整个数据库放入 tmpfs

• 另一个有用的策略是让测试组默认关闭数据库测试（如果它们真的减慢了你的构建速度）。这样，如果有人实际在 db 上工作，他需要在 cmd 行中传递额外的标志或使用 IDE（testng 组和自定义测试选择器非常适合此）

For each case a certain amount of insert/update queries should be made, which is annoying and takes time

对于每种情况，都应该进行一定数量的插入/更新查询，这很烦人并且需要时间

'takes time' part was discussed above. is it annoying? I've seen two ways:

上面讨论了“需要时间”部分。烦人吗？我见过两种方式：

• prepare one dataset for your all test cases. then you have to maintain it and reason about it. usually it's separated from code. it has kilobytes or megabytes. it's to big to see on one screen, to comprehend and to reason about. it introduces coupling between tests. because when you need more rows for test A, your count(*)in test B fails. it only grows because even when you delete some tests, you don't know which rows were used only by this one test
• each tests prepares its data. this way each test is completely independent, readable and easy to reason about. is it annoying? imo, not at all! it let you write new tests very quickly and saves you a lot of work in future

• 为您的所有测试用例准备一个数据集。那么你必须维护它并对其进行推理。通常它与代码分开。它有千字节或兆字节。在一个屏幕上看到，理解和推理是很大的。它引入了测试之间的耦合。因为当您需要更多行用于测试 A 时，您count(*)的测试 B 会失败。它只会增长，因为即使你删除了一些测试，你也不知道哪些行只被这个测试使用
• 每个测试准备其数据。这样每个测试都是完全独立的、可读的并且易于推理。烦人吗？imo，一点也不！它让您可以非常快速地编写新测试，并在未来为您节省大量工作

how do you know there are 542 rows in that table?" - One of the main principles in testing, is to be able to test the functionality in a way different from that of your tested-code

您怎么知道该表中有 542 行？” - 测试的主要原则之一是能够以与测试代码不同的方式测试功能

uhm... not really. the main principle is to check if your software generates desired output in response to specific input. so if you call dao.insert542 times and then your dao.countreturns 542, it means your software works as specified. if you want, you can call commit/drop cache in between. Of course, sometimes you want to test your implementation instead of the contract and then you check if your dao changed the state of the db. but you always test sql A using sql B (insert vs select, sequence next_val vs returned value etc). yes, you'll always have the problem 'who will test my tests', and the answer is: no one, so keep them simple!

嗯...不是真的。主要原则是检查您的软件是否根据特定输入生成所需的输出。因此，如果您调用dao.insert542 次，然后dao.count返回 542，则表示您的软件按规定工作。如果需要，您可以在两者之间调用提交/删除缓存。当然，有时你想测试你的实现而不是合约，然后你检查你的 dao 是否改变了数据库的状态。但是您总是使用 sql B 测试 sql A（插入 vs 选择、序列 next_val vs 返回值等）。是的，你总会遇到“谁来测试我的测试”的问题，答案是：没有人，所以让它们保持简单！

other tools that may help you:

其他可能对您有帮助的工具：

1. testcontainerswill help you provide real db.

2. dbunit- will help you clean the data between tests

   cons:

   • a lot of work is required to create and maintain schema and data. especially when your project is in a intensive development stage.
   • it's another abstraction layer so if suddenly you want to use some db feature that is unsupported by this tool, it may be difficult to test it

3. testegration- intents to provide you full, ready to use and extensible lifecycle (disclosure: i'm a creator).

   cons:

   • free only for small projects
   • very young project

4. flywayor liquibase- db migration tools. they help you easily create schema and all the structures on your local db for tests.

1. testcontainers将帮助您提供真实的数据库。

2. dbunit- 将帮助您清理测试之间的数据

   缺点：

   • 创建和维护模式和数据需要大量工作。特别是当您的项目处于密集开发阶段时。
   • 这是另一个抽象层，因此如果您突然想使用此工具不支持的某些数据库功能，则可能很难对其进行测试

3. testegration- 旨在为您提供完整的、随时可用的和可扩展的生命周期（披露：我是一个创造者）。

   缺点：

   • 仅对小型项目免费
   • 非常年轻的项目

4. 飞路或liquibase-数据库迁移工具。它们可以帮助您轻松地在本地数据库上创建架构和所有结构以进行测试。

回答by Eddie

I've used Hypersonicfor this purpose. Basically, it's a JAR file (a pure Java in-memory database) that you can run in its own JVM or in your own JVM and while it's running, you have a database. Then you stop it and your database goes away. I've used it -- so far -- as a purely in-memory database. It's very simple to start and stop via Ant when running unit tests.

为此，我使用了Hypersonic。基本上，它是一个 JAR 文件（纯 Java 内存数据库），您可以在其自己的 JVM 或您自己的 JVM 中运行，并且在它运行时，您拥有一个数据库。然后你停止它，你的数据库消失了。到目前为止，我已经将它用作纯粹的内存数据库。在运行单元测试时通过 Ant 启动和停止非常简单。

回答by ykaganovich

Java comes with Java DB.

Java 带有Java DB。

That said, I would advise against using a different type of DB than what you use in production unless you go through an ORM layer. Otherwise, your SQL might not be as cross-platform as you think.

也就是说，除非您通过 ORM 层，否则我建议不要使用与生产中使用的数据库类型不同的数据库。否则，您的 SQL 可能不会像您想象的那样跨平台。

Also check out DbUnit

另请查看DbUnit

回答by Khangharoth

Well to begin with ,are you using any ORM Layer for DB access?
If not : then what you are thinking would be of no use.What's the use of testing when you are not sure that SQL you are firing will work with your DB in production as in test cases you are using something else.
If yes:Then you can have look at various options pointed out.

首先，您是否使用任何 ORM 层进行数据库访问？
如果不是：那么您的想法将毫无用处。当您不确定要触发的 SQL 是否可以在生产中与您的数据库一起使用时，测试有什么用，就像在测试用例中使用其他东西一样。
如果是：那么您可以查看指出的各种选项。

回答by Jim Ferrans

We're creating a database test environment at work right now. We feel we must use a realdatabase management system with simulated data. One problem with a simulated DBMS is that SQL never really totally gelled as a standard, so an artificial testing environment would have to faithfully support our production database's dialect. Another problem is that we make extensive use of column value constraints, foreign key constraints, and unique constraints, and since an artificial tool probably wouldn't implement these, our unit tests could pass but our system tests would fail when they first hit the real constraints. If tests take too long, this indicates an implementation error and we would tune our queries (typically test data sets are miniscule compared to production).

我们正在创建一个正在工作的数据库测试环境。我们觉得我们必须使用带有模拟数据的真实数据库管理系统。模拟 DBMS 的一个问题是 SQL 从未真正完全胶凝为标准，因此人工测试环境必须忠实地支持我们生产数据库的方言。另一个问题是我们大量使用列值约束、外键约束和唯一约束，由于人工工具可能不会实现这些，我们的单元测试可以通过，但我们的系统测试在它们第一次遇到真实时就会失败。约束。如果测试时间过长，这表明存在实施错误，我们将调整我们的查询（与生产相比，通常测试数据集是微不足道的）。

We've installed a real DBMS on each developer machine and on our continuous integration and test server (we use Hudson). I don't know what your work policy restrictions are, but it's pretty easy to install and use PostgreSQL, MySQL, and Oracle XE. These are all free for development use (even Oracle XE), so there's no rational reason to prohibit their use.

我们已经在每台开发人员机器上以及我们的持续集成和测试服务器（我们使用 Hudson）上安装了一个真正的 DBMS。我不知道您的工作策略限制是什么，但是安装和使用 PostgreSQL、MySQL 和 Oracle XE 非常容易。这些都可以免费用于开发用途（甚至是 Oracle XE），因此没有合理的理由禁止使用它们。

The key issue is how do you guarantee that your tests always start out with the database in a consistent state? If the tests were all read-only, no problem. If you could engineer mutating tests to always run in transactions that never commit, no problem. But typically you need to worry about reversing updates. To do this you can export the initial state to a file, then importing it back post-test (Oracle's exp and imp shell commands do this). Or you can use a checkpoint/rollback. But a more elegant way is to use a tool like dbunit, which works well for us.

关键问题是你如何保证你的测试总是从处于一致状态的数据库开始？如果测试都是只读的，没问题。如果您可以设计变异测试以始终在从未提交的事务中运行，那没问题。但通常您需要担心反向更新。为此，您可以将初始状态导出到文件，然后在测试后将其导入回（Oracle 的 exp 和 imp shell 命令执行此操作）。或者您可以使用检查点/回滚。但更优雅的方法是使用像dbunit这样的工具，它对我们来说效果很好。

The key advantage to this is that we catch many more bugs up front where they're far easier to fix and our real system testing doesn't get blocked while developers feverishly try to debug problems. This means we produce better code faster and with less effort.

这样做的关键优势在于，我们可以提前捕获更多错误，从而更容易修复它们，并且在开发人员狂热地尝试调试问题时，我们的真实系统测试不会被阻止。这意味着我们可以更快、更轻松地生成更好的代码。

回答by Blair Zajac

We recently switched to JavaDB or Derbyto implement this. Derby 10.5.1.1 now implements an in-memory representation so it runs very fast, it doesn't need to go to disk: Derby In Memory Primer

我们最近切换到 JavaDB 或Derby来实现这一点。Derby 10.5.1.1 现在实现了内存中表示，因此它运行得非常快，不需要转到磁盘： Derby In Memory Primer

We design our application to run on Oracle, PostgreSQL and Derby so we don't get too far down the road on any one platform before finding out that one database supports a feature that other ones don't.

我们将应用程序设计为在 Oracle、PostgreSQL 和 Derby 上运行，因此在发现一个数据库支持其他数据库不支持的功能之前，我们不会在任何一个平台上走得太远。

回答by Martlark

If you are using Oracle at work you can use the Restore Point in Flashback Database feature to make the database return to a time before your tests. This will clear away any changes you personally made to the DB.

如果您在工作中使用 Oracle，您可以使用闪回数据库中的还原点功能使数据库返回到测试之前的时间。这将清除您个人对数据库所做的任何更改。

See:

看：

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV71000

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV71000

If you need a test database for use with Oracle production/work then lookup the XE, express edition database from Oracle. This is free for personal use, with a limit of database less than 2gb in size.

如果您需要一个用于 Oracle 生产/工作的测试数据库，请从 Oracle 查找 XE 快速版数据库。这是免费供个人使用，数据库大小限制小于 2GB。

回答by Paul Keeble

There are lots of points of view on how to test integration points such as the Database connection via SQL. My personal set of rules that has worked well for me is as follows:

关于如何测试集成点（例如通过 SQL 的数据库连接）有很多观点。我个人的一套对我很有效的规则如下：

1) Separate out the Database accessing logic and functions from general business logic and hide it behind an interface. Reason: In order to test the grand majority of logic in the system it is best to use a dummy/stub in place of the actual database as its simpler. Reason 2: It is dramatically faster

1) 将数据库访问逻辑和功能与一般业务逻辑分离，并将其隐藏在接口后面。原因：为了测试系统中的绝大多数逻辑，最好使用虚拟/存根代替实际数据库，因为它更简单。原因 2：速度明显更快

2) Treat tests for the database as integration tests that are separated from the main body of unit tests and need to run on a setup database Reason: Speed and quality of tests

2) 将数据库测试视为与单元测试主体分离并需要在设置数据库上运行的集成测试原因：测试的速度和质量

3) Every developer will need their own distinct database. They will need an automated way to update its structure based on changes from their team mates and introduce data. See points 4 and 5.

3) 每个开发人员都需要自己独特的数据库。他们将需要一种自动方式来根据团队成员的变化更新其结构并引入数据。见第 4 点和第 5 点。

4) Use a tool like http://www.liquibase.orgto manage upgrades in your databases structure. Reason: Gives you agility in the ability to change the existing structure and move forward in versions

4) 使用像http://www.liquibase.org这样的工具来管理数据库结构的升级。原因：使您能够灵活地更改现有结构并在版本中前进

5) Use a tool like http://dbunit.sourceforge.net/to manage the data. Set up scenario files (xml or XLS) for particular test cases and base data and only clear down what is needed for any one test case. Reason: Much better than manually inserting and deleting data Reason 2: Easier for testers to understand how to adjust scenarios Reason 3: Its quicker to execute this

5）使用像http://dbunit.sourceforge.net/这样的工具来管理数据。为特定的测试用例和基础数据设置场景文件（xml 或 XLS），并且只清除任何一个测试用例所需的内容。原因：比手动插入和删除数据要好得多 原因 2：测试人员更容易理解如何调整场景 原因 3：执行起来更快

6) You need functional tests which also have DBUnit like scenario data, but this are far larger sets of data and execute the entire system. This completes the step of combining the knowledge that a) The unit tests run and hence the logic is sound b) That the integration tests to the database run and SQL is correct resulting in "and the system as a whole works together as a top to bottom stack"

6) 您需要功能测试，其中也有 DBUnit 之类的场景数据，但这是更大的数据集并执行整个系统。这完成了结合以下知识的步骤：a) 单元测试运行，因此逻辑是合理的 b) 对数据库运行和 SQL 的集成测试是正确的，导致“并且整个系统作为一个顶部一起工作底部堆栈”

This combination has served me well so far for achieving a high quality of testing and product as well as maintaining speed of unit test development and agility to change.

到目前为止，这种组合对我实现高质量的测试和产品以及保持单元测试开发的速度和变化的敏捷性非常有用。

回答by banjollity

"Just get yourself a testing DB, how hard could it be?" - Well, in my working place, to have a personal testing DB is pretty impossible. You have to use a "public" DB, which is accessible for everyone.

“给自己找一个测试数据库，这有多难？” - 嗯，在我的工作场所，拥有一个个人测试数据库是非常不可能的。您必须使用每个人都可以访问的“公共”数据库。

Sounds like you've got cultural problems at work that are providing a barrier to you being able to do your job to the fullest of your abilities and the benefit of your product. You might want to do something about that.

听起来您在工作中遇到了文化问题，阻碍了您充分发挥自己的能力和产品的优势来完成工作。你可能想为此做点什么。

On the other hand, if your database schema is under version control then you could always have a test build that creates a database from the schema, populates it with test data, runs your tests, gathers the results and then drops the database. It'd only be in existence for the duration of the tests. It can be a new database on an existing installation if hardware is a problem. This is similar to what we do where I work.

另一方面，如果您的数据库架构处于版本控制之下，那么您始终可以使用测试构建从架构创建数据库，用测试数据填充它，运行您的测试，收集结果，然后删除数据库。它只会在测试期间存在。如果硬件有问题，它可以是现有安装上的新数据库。这与我们在我工作的地方所做的类似。

回答by Nat

I agree with banjollity. Setting up isolated development and test environments should be a high priority. Every database system I've used is either open source or has a free developer edition you can install on your local workstation. This lets you develop against the same database dialect as production, gives you full admin access to development databases and is faster than using a remote server.

我同意班卓琴。设置隔离的开发和测试环境应该是一个高优先级。我使用过的每个数据库系统要么是开源的，要么有免费的开发者版本，您可以在本地工作站上安装。这使您可以针对与生产相同的数据库方言进行开发，为您提供对开发数据库的完全管理员访问权限，并且比使用远程服务器更快。

回答by Artic

Try to use derby. It is easy and portable. With Hibernate your app becomes flexible. Test on derby, production on anything you like and trust.

尝试使用derby。它简单便携。使用 Hibernate，您的应用程序变得灵活。测试德比，生产任何你喜欢和信任的东西。