How to Run MySQL In-Memory for Junit Test Cases

What is a good in memory database for junit test?

Databases such as HSQLDB do not behave exactly like MySQL. The advantage of testing with HSQLDB is to flag some of the unintended errors in your code which might pass under MySQL. For example some invalid aggregate queries work under MySQL but not HSQLDB.

How to write in test case with in-memory-database?

You should initialize your Hibernate SessionFactory with another hibernate config, that uses H2 in-memory database, example test-hibenate.properties:

hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.connection.url=jdbc:h2:mem:orm
javax.persistence.schema-generation.database.action=drop-and-create

Then in your tests you can use your DAO's just in regular way.

If you use plain JDBC, you can create connection to H2 in-memory database this way:

Connection connection = DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "", "");

And you will need H2 database dependency:
https://mvnrepository.com/artifact/com.h2database/h2

Configure specific in memory database for testing purpose in Spring

Spring profiles can be used for this. This would be a specific way:

Have environment specific properties files:

application.properties:

spring.profiles.active: dev

application-dev.properties

spring.jpa.database: MYSQL
spring.jpa.hibernate.ddl-auto: update

spring.datasource.url: jdbc:mysql://localhost:3306/dbname
spring.datasource.username: username
spring.datasource.password: password

application-test.properties

spring.jpa.database: HSQL

Have both MySQL and H2 drivers in pom.xml, like this:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>test</scope>
</dependency>

Last but not the least, annotate Test classes with @ActiveProfiles("test").

Use in-memory db to write tests for Rest controllers

Assuming you annotate class with @SpringBootApplication, which enables auto-configuration and you have H2 dependency on classpath(see below) Spring Boot will see H2 in-memory database dependency and it will create javax.sql.DataSource implementation. Default connection URL is jdbc:h2:mem:testdb and the default username and password are: username: sa and password: empty.

application.properties file

spring.datasource.url=jdbc:h2:mem:tesdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    spring.datasource.driverClassName=org.h2.Driver
    spring.datasource.username=sa
    spring.datasource.password=

    spring.datasource.testWhileIdle = true
    spring.datasource.validationQuery = SELECT 1

    spring.jpa.show-sql = true
    spring.h2.console.enabled=true // if you need console

H2 Dependency

    <dependency>
      <groupId>com.h2database</groupId>
       <artifactId>h2</artifactId>
      <scope>runtime</scope>
   </dependency>

   <dependency> // If you need h2 web console 
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
   </dependency>

You can gain access to h2 console for management http://localhost:8080/h2-console

How to create unit tests against non in-memory database such as MySQL in Play framework, with resetting to known state?

First, I would recommend you to use the same RDBMS for testing and production as it could avoid some hard-to-find bugs.

Concerning the need to clean your database between each test, you can use Ebean DdlGenerator to generate scripts to create a clean database and JUnit's @Before annotation to automatically execute these scripts before every test.

Using the DdlGenerator can be done like this :

    EbeanServer server = Ebean.getServer(serverName);
    ServerConfig config = new ServerConfig();
    DdlGenerator ddl = new DdlGenerator((SpiEbeanServer) server, new MySqlPlatform(), config);

This code can be placed in a base-class that you could make inherit your tests (or inside a custom Runner that you can use with the @RunWith annotation).

It will also allow you to easily automate the FakeApplication creation, avoiding some boilerplate code.

Some links that can be helpful :

• http://blog.matthieuguillermin.fr/2012/03/unit-testing-tricks-for-play-2-0-and-ebean/
• https://gist.github.com/nboire/2819920

Unit Testing(Mocking) Databases, How To Verify Database Methods With Mocking?

Imagine the following... You have two cars, one is a real car, another is a fake (mock) car. Now you get into the real car and drive off. Why do you expect not to move? You didn't do anything with the fake car, so even if you told it "Do not move if I start driving", this doesn't change the fact that the real car WILL move when you drive off.

Same thing here, you created a fake DBConnection and a real one. The real one will do whatever the real one does. The fake one can be configured to do different stuff, like you did with Mockito.when(...). But that doesn't change the behavior of the real one.

Your test is effectively meaningless, because your only choice here is to get rid of the mock, because it doesn't serve any meaningful purpose. You do not test a mock. What for? That's like creating a fake car and testing that - it doesn't tell you anything about the real car. You do not need to test the fake car to find out that it will do X if you told it to do X in that test.

There are two ways you can test your DBConnection class:

a) You can connect it to a database and check that it does what it should. This would, of course, be an integration test and not a unit test anymore. In some cases you can use a in-memory database like HSQLDB to speed up this test. But at least in the end you can be reasonably sure that your code does what it is supposed to be doing.

b) IF and only IF DBConnection has some objects internally that do the actual talking to the database, you can, perhaps, mock those and then test DBConnection. Of course, this only moves your problem to another layer, because then you aren't sure that those objects work - you will only know that DBConnection works IF those objects work. While this is good to know, it does not answer the question if your db code will work in the end.

In the end, you can only test db connections completely by connecting to a db. Everything else is not a complete test.

---

Related Topics