测试在内部使用特定于MySQL的查询的代码的适当方法是什么

I am collecting data and store this data in a MySQL database using Java. Additionally, I use Maven for building the project, TestNG as a test framework, and Spring-Jdbc for accessing the database. I've implemented a DAO layer that encapsulates the access to the database. Besides adding data using the DAO classes I want to execute some queries which aggregate the data and store the results in some other tables (like materialized views).

我正在使用Java收集数据并将这些数据存储在MySQL数据库中。另外，我使用Maven构建项目，TestNG作为测试框架，使用Spring-Jdbc访问数据库。我已经实现了一个封装了对数据库的访问的DAO层。除了使用DAO类添加数据之外，我还想执行一些查询，这些查询聚合数据并将结果存储在其他一些表中（如物化视图）。

Now, I would like to write some testcases which check whether the DAO classes are working as they should. Therefore, I thought of using an in-memory database which will be populated with some test data. Since I am also using MySQL-specific SQL queries for aggregating data, I went into some trouble:

现在，我想编写一些测试用例来检查DAO类是否正常工作。因此，我想到了一个内存数据库，它将填充一些测试数据。由于我也使用MySQL特定的SQL查询来聚合数据，我遇到了一些麻烦：

1. Firstly, I've thought of simply using the embedded-database functionality provided by Spring-Jdbc to instantiate an embedded database. I've decided to use the H2 implementation. There I ran into trouble because of the aggregation queries, which are using MySQL-specific content (e.g. time-manipulation functions like DATE()). Another disadvantage of this approach is that I need to maintain two ddl files - the actual ddl file defining the tables in MySQL (here I define the encoding and add comments to tables and columns, both features are MySQL-specific); and the test ddl file that defines the same tables but without comments etc. since H2 does not support comments.
2. 首先，我想过简单地使用Spring-Jdbc提供的嵌入式数据库功能来实例化嵌入式数据库。我决定使用H2实现。在那里我遇到了麻烦，因为聚合查询使用特定于MySQL的内容（例如时间操作函数，如DATE（））。这种方法的另一个缺点是我需要维护两个ddl文件 - 在MySQL中定义表的实际ddl文件（这里我定义了编码并为表和列添加注释，这两个特性都是特定于MySQL的）;和测试ddl文件定义相同的表但没有注释等，因为H2不支持注释。
3. I've found a description for using MySQL as an embedded database which I can use within the test cases (http://literatitech.blogspot.de/2011/04/embedded-mysql-server-for-junit-testing.html). That sounded really promising to me. Unfortunately, it didn't worked: A MissingResourceExcpetion occurred "Resource '5-0-21/Linux-amd64/mysqld' not found". It seems that the driver is not able to find the database daemon on my local machine. But I don't know what I have to look for to find a solution for that issue.
4. 我找到了使用MySQL作为嵌入式数据库的描述，我可以在测试用例中使用它（http://literatitech.blogspot.de/2011/04/embedded-mysql-server-for-junit-testing.html） 。这听起来真的很有希望。不幸的是，它没有用：一个MissingResourceExcpetion发生了“资源'5-0-21 / Linux-amd64 / mysqld'找不到”。似乎驱动程序无法在我的本地计算机上找到数据库守护程序。但我不知道我需要寻找什么才能找到解决该问题的方法。

Now, I am a little bit stuck and I am wondering if I should have created the architecture differently. Do someone has some tips how I should setup an appropriate system? I have two other options in mind:

现在，我有点陷入困境，我想知道我是否应该以不同的方式创建架构。有人有一些提示我应该如何设置合适的系统？我还有两个其他选择：

1. Instead of using an embedded database, I'll go with a native MySQL instance and setup a database that is only used for the testcases. This options sounds slow. Actually, I might want to setup a CI server later on and I thought that using an embedded database would be more appropriate since the test run faster.
2. 我将使用本机MySQL实例并设置仅用于测试用例的数据库，而不是使用嵌入式数据库。这个选项听起来很慢。实际上，我可能想稍后设置CI服务器，我认为使用嵌入式数据库会更合适，因为测试运行得更快。
3. I erase all the MySQL-specific stuff out of the SQL queries and use H2 as an embedded database for testing. If this option is the right choice, I would need to find another way to test the SQL queries that aggregates the data into materialized views.
4. 我从SQL查询中删除了所有特定于MySQL的东西，并使用H2作为嵌入式数据库进行测试。如果这个选项是正确的选择，我需要找到另一种方法来测试将数据聚合到物化视图的SQL查询。
5. Or is there a 3rd option which I don't have in mind?
6. 还是有第三种选择，我没有想到的？

I would appreciate any hints.

我会很感激任何提示。

Thanks, XComp

谢谢，XComp

2 个解决方案

#1

If it is not possible to get the in-memory MySQL database to work I suggest using the H2 database for the "simple" tests and a dedicated MySQL instance to test MySQL-specific queries.

如果无法让内存中的MySQL数据库工作，我建议使用H2数据库进行“简单”测试，并使用专用的MySQL实例来测试特定于MySQL的查询。

Additionally, the tests for the real MySQL database can be configured as integration tests in a separate maven profile so that they are not part of the regular maven build. On the CI server you can create an additional job that runs the MySQL tests periodically, e.g. daily or every few hours. With such a setup you can keep and test your product-specific queries while your regular build will not slow down. You can also run a normal build even if the test database is not available.

此外，真实MySQL数据库的测试可以在单独的maven配置文件中配置为集成测试，这样它们就不会成为常规maven构建的一部分。在CI服务器上，您可以创建一个定期运行MySQL测试的其他作业，例如每天或每隔几个小时。通过这样的设置，您可以保留并测试特定于产品的查询，而常规构建不会变慢。即使测试数据库不可用，您也可以运行正常构建。

There is a nice maven plugin for integration tests called maven-failsafe-plugin. It provides pre- and post- integration test steps that can be used to setup the test data before the tests and to cleanup the database after the tests.

有一个很好的maven插件用于集成测试，称为maven-failsafe-plugin。它提供了集成前和集成后测试步骤，可用于在测试之前设置测试数据并在测试后清理数据库。

#2

I've created Maven plugin exactly for this purpose: jcabi-mysql-maven-plugin. It starts a local MySQL server on pre-integration-test phase and shuts it down on post-integration-test.

我已经为此创建了Maven插件：jcabi-mysql-maven-plugin。它在预集成测试阶段启动本地MySQL服务器，并在集成后测试时关闭它。