对于JUnit测试用例，有没有在内存中运行MySQL的方法?

I'm just trying to add test cases for services accessing a MySQL DB, and I would like to recreate the whole schema (and for some scenarios also just use a MySQL dump file with the data needed for each test case). I was looking around and found some guys using SQLite / H2 and others to do this, but I'm just wandering if there is any way to run MySQL in-memory so I don't need to worry about anything specific to the the MySQL dialect I might be using on our services.

我只是在尝试为访问MySQL DB的服务添加测试用例，我想重新创建整个模式(对于某些场景，也只是使用带有每个测试用例所需数据的MySQL转储文件)。我环顾四周,发现有些人使用SQLite / H2和别人这样做,但我只是流浪的如果有任何方式运行MySQL内存所以我不需要担心任何特定于MySQL的方言我可能使用我们的服务。

6 个解决方案

#1

The easiest way for using an in memory database that is fully compatible to MySQL and can be used within JUnit test cases is imho MariaDB4j. you just need a Gradle (/Maven) dependency (http://search.maven.org/#search%7Cga%7C1%7Ca%3A%22mariaDB4j%22) and a few lines of code to start:

使用与MySQL完全兼容并可以在JUnit测试用例中使用的内存数据库的最简单方法是imho MariaDB4j。您只需要一个等级(/Maven)依赖项(http://search.maven.org/#search%7Cga%7C1%7Ca%3A% 222 mariadb4j %22)和几行代码就可以开始了:

DB database = DB.newEmbeddedDB(3306);
database.start();
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");

a startupt script can be included via

可以通过以下方式包含startupt脚本

database.source("path/to/resource.sql");

More information on GitHub readme: https://github.com/vorburger/MariaDB4j

更多关于GitHub readme的信息:https://github.com/vorburger/MariaDB4j。

EDIT: I have a add some hints to this answer: The MariaDB4j seems to add files in the systems temporary folder. So it will work in an embedded way which means there is no need to install anything and you can just use the dependency via your desired build tool. But it's not a true in-memory-only solution and therefore we cannot speak of unit tests anymore because unit tests mustn't rely on files or databases

编辑:我为这个答案添加了一些提示:MariaDB4j似乎在系统临时文件夹中添加了文件。因此，它将以一种嵌入式的方式工作，这意味着不需要安装任何东西，您只需通过您想要的构建工具就可以使用依赖项。但是它不是真正的内存内解决方案，因此我们不能再讨论单元测试了，因为单元测试不能依赖于文件或数据库

#2

We use MySQL and flyway to handle the migration.

我们使用MySQL和flyway来处理迁移。

For unit testing and simple integration tests we use H2 in memory database with the MODE=MySQL param. Mode=MySQL enables the H2 db to handle most of the MySQL dialect.

对于单元测试和简单的集成测试，我们在内存数据库中使用H2，模式=MySQL param。Mode=MySQL允许H2 db处理大部分MySQL方言。

Our test datasource in the Spring config is set up like this:

在Spring配置中，我们的测试数据源是这样设置的:

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
      <property name="driverClassName" value="org.h2.Driver"/>
      <property name="url" value="jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE" />
    </bean>

(If you don't know Spring - the XML translates into calling new BasicDataSource and then call setDriverClassName and setUrl on the instance created)

(如果您不知道Spring——XML转换为调用新的basidrivercdatasource，然后在创建的实例上调用setDriverClassName和setUrl)

Then we use flyway on the datasource to create the schema and read in like we would against a regular MySQL db:

然后我们在数据源上使用flyway来创建模式，并像对普通MySQL db那样进行读取:

    <bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate">
       <property name="dataSource" ref="dataSource" />
       <property name="cleanOnValidationError" value="false" />
       <property name="initOnMigrate" value="true" />
       <property name="sqlMigrationSuffix" value=".ddl" />
    </bean>

You could also just use the dataSource bean in a jdbcTemplate and run some SQL scripts that way or run a number of MySQL scripts using the <jdbc:initialize-database...> tag.

您还可以在jdbcTemplate中使用数据源bean并以这种方式运行一些SQL脚本，或者使用标记。 …>

#3

This is one of the reasons why using proprietary SQL extensions is usually not a good idea.

这就是为什么使用私有SQL扩展通常不是一个好主意的原因之一。

What I would do is try to identify the places where you use non-standard SQL and refactor your code to move these parts to dedicated services. Then you can mock these when running unit tests.

我要做的是尝试确定使用非标准SQL的地方，并重构代码，将这些部分转移到专用服务。然后您可以在运行单元测试时模拟这些。

#4

You can use a different schema for the JUnit tests. If you're using Spring, it's JUnit extensions allow each test to run in a read-only transaction, so no data will be persistent in the database after the tests. If you need initial data for the tests, you put the needed data in the @Before marked method that participates in the transaction.

您可以为JUnit测试使用不同的模式。如果您正在使用Spring，它的JUnit扩展允许每个测试在只读事务中运行，所以在测试之后，数据库中没有数据是持久的。如果需要测试的初始数据，可以将需要的数据放入参与事务的@Before标记方法中。

#5

You may mount a ramdrive (using ImDisk), copy your datas files on it, and start Mysql services after changing the appropriate configuration in my.cnf Unit test databases being usually small (and you should keep them small for fast testing), they can normally fit in a ramdrive.

您可以挂载ramdrive(使用ImDisk)、在其上复制您的数据文件，并在修改了my.cnf单元测试数据库中的适当配置之后启动Mysql服务(您应该将它们保持在较小的位置以便进行快速测试)，它们通常可以安装在ramdrive中。

You may also consider using transaction in your spring tests instead of rebuilding tables at each test.

您还可以考虑在spring测试中使用事务，而不是在每个测试中重新构建表。

We used that for our dev team and it worked like a charm, we gained a order of magnitude in speed.

我们将它用于我们的开发团队，它像一个魅力，我们获得了一个数量级的速度。

#6

try http://hsqldb.org/, I don't have experience with it, but heard good things.

试试http://hsqldb.org/，我没有使用它的经验，但听到了一些好的东西。

EDIT Sorry - seems like you would need to remove any MySQL specific syntax...

编辑对不起-看起来你需要删除任何MySQL特定的语法…

#1

DB database = DB.newEmbeddedDB(3306);
database.start();
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");