如何模拟数据库进行测试(Java)? [英] How to simulate a DB for testing (Java)?

问题描述

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

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:

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

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

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:

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

所以，你可以发现我在测试时不喜欢 DB(当然，我必须在某个时候达到这一点，但我宁愿在我的测试中稍后到达那里，因为我发现了大多数使用其余测试方法的错误).但我在寻找什么?

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?

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

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

您熟悉这种工具吗?

感谢您的回答！虽然我要求提供工具，但您也向我提供了有关该问题的一些提示:) 我需要一些时间来查看您的报价，因此我现在无法确定您的回答是否令人满意.

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.

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

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:

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

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

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

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

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

推荐答案

旧问题的新答案(但事情已经向前推进了一点):

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

如何模拟数据库进行测试(Java)?

How to simulate a DB for testing (Java)?

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

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

拥有个人测试数据库几乎是不可能的.您必须使用所有人都可以访问的公共"数据库

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

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

unfortunately a lot of companies still use that model but now we have docker and 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

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

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

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

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

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

• 你也可以把整个db放到tmpfs中

• 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

另一个有用的策略是设置测试组并在默认情况下关闭数据库测试(如果它们确实减慢了您的构建速度).这样，如果有人实际在 db 上工作，他需要在 cmd 行中传递额外的标志或使用 IDE(testng 组和自定义测试选择器非常适合)

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)

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

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:

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

• 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

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

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

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

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.insert 542 times and then your dao.count returns 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!

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

other tools that may help you:

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

1. testcontainers will help you provide real db.

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

dbunit - will help you clean the data between tests

缺点:

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

testegration - 旨在为您提供完整、随时可用且可扩展的生命周期(披露:我是创造者).

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

缺点:

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

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

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

这篇关于如何模拟数据库进行测试(Java)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！