Hey, Tea Lovers! Today let’s look at how you can use an in-memory database to test a core Java project, specifically H2 DB. The official name of H2 DB is H2 Database Engine.
I will focus on it in the testing and not on the main project. I just want to give you a glimpse of how you can get started with it.
I would highly recommend for the database logic or testing you shouldn’t rely upon In-Memory databases, always have Integration Test’s in this scenarios. Use this tool for a small or temporary test.
or use Testcontainers for Java~ One who saw the terror of it
This post is a continuation of my Junit 5 series, whose first chapter is JUnit 5 Introduction for TDD Development in Java. I know the order is weird, but I write things that are not readily available on the internet or are sometimes harder to understand for a beginner. So please bear with me, I will give you a post where I will correctly order things.
Let’s start the engine, the H2 Database Engine. The Code could be found on GitHub.
Please Subscribe to the newsletter to know about the latest posts from CodersTea.
What is In-Memory Database and Why use It?
As the name suggests, it is the database that relies on your system’s memory. It stores your data on your system’s RAM. All the data and its operations such as DDL or DML happen on the RAM itself. Since RAM is faster than disks, This way, it’s way faster.
Not so fun fact, The in-Memory database is also called IMDB (not that Movie Rating system).
There are many use cases of an in-memory database. One of them, of course, is to save the data. You can use them for temporary storage or the cache. But these are used widely for testing purposes.
As you know the test execution should be fast. Also, we need to sometime test our query too. We can’t connect to an actual database for unit tests, it will take a lot of time, and we need to purge the change again and again. That’s why we use the in-memory database in Tests to mock the databases. And one of the most popular in-memory databases is drum roll 🥁 , H2 DB.
Now we talked about what are the goods in the in-memory, now let’s talk bad about it. Ooh, gossip 🗣👂🏼.
Limitations of In-Memory Databases
What do you think the biggest drawback of a temporary database would be? Take your time. It’s temporary. Now, If you have guessed it correctly within 1 second, you are a genius 🧠.
Once again, I would highly recommend for the database logic or testing you shouldn’t rely upon In-Memory databases, always have Integration Test’s in this scenarios. Use this tool for a small or temporary test.
Or use Testcontainers for Java~ One who saw the terror of it
You can’t use it as your main resource for storing the data. Once the RAM is cleared, the data is wiped out. Another limitation is if you are using it to mock your actual database in testing. It’s not an ACTUAL DATABASE, It might not fully support the query you have written. What I mean is if you have written the query that contains the keywords or functions very specific to that particular Database. Such as Postgres or Oracle.
So Always keep in mind, if you want to test all your queries keep them generic. Or you can skip them and test on integration test only with an actual database.
Now that we have an idea about what the in-memory database is, why to use it, and its limitations, let us jump into the implementation of it with H2 DB.
Get Started with H2 DB in-memory Database
As I said I will be focusing on the Testing side, where most people do. To get started with the H2 DB, you simply need to add it to the dependency and that’s it. You can either use Maven or Gradle or if you want to download the jar, you can do so by visiting the official website H2 Database Engine.
Dependency for H2 DB
Following is the maven dependency, but you can choose according to your build tool on the H2 Maven Repo Link.
<!-- https://mvnrepository.com/artifact/com.h2database/h2 --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.200</version> <scope>test</scope> </dependency>Code language: HTML, XML (xml)
Start and Connect to H2 DB via JDBC
Since we have added the dependency, we can start using the H2 DB right away. To connect to the H2 DB via JDBC, you need to use
jdbc:h2 in URL. There is no need to put
localhost or such. A username and password are also not mandatory. The driver you will be using is
org.h2.Driver. So the complete JDBC connection would look like the following.
Once you run the project, it automatically starts the H2 DB. so there is no extra work needed to start the database. Simply connect to it.
Mock the Actual Database and Populate data in h2 DB
As I said, most of the time, you will be using H2 DB for testing. That’s why I will take an example similar to the real world. First I will use MySQL in the core logic and do a simple select query. Next, I will mock MySQL with H2 DB. Then, since we can’t get anything from an empty database, I will populate the database such that we will get some results from our select query.
Keep in mind, we have to separate the connection creation logic so that it will be easier to inject different URLs and properties. If you don’t know what I am talking about, I would suggest looking at my other post How to Achieve Greatness in JDBC Performance. It’s a must-read to improve and follow best practices for JDBC in Java.
For simplicity, I will pass the JDBC URL, username, and password to the function directly.
Code to Get Count from Table
In the above code, we are simply connecting to the database, and getting the count from
employee. Pretty simple, isn’t it? Now let us test it out. But before that, we have to create the
employee table for testing. Not only that, we need to populate with some data so that we will see some results. The link to the full code of this method on GitHub is here.
Populating the H2 DB
You have to set up the H2 DB to mimic the actual database for testing, right? It can be done via
@BeforeAll the method. Where you can execute queries according to your need such as creating a database, creating a table, and inserting records. But I had like to do it via SQL Script. This is how I do it.
Populate H2 DB with SQL Script
First, we have to create a script. That will contain the SQL to create a database, employee table, and insert a few records. I will be putting it in the test’s resource path i.e.
src/test/resources/employee.sql. The link to this file on Github is here.
Next, you have to tell H2 DB that this is the file it has to pick to populate before performing any connection request.
How can we do that? Simple, pass it to the JDBC URL itself. It can be done by passing
INIT the param to the URL. It takes an argument of
RUNSCRIPT FROM '<sql-file-path. Since we have it in our resource folder we can use classpath. So our JDBC URL would look like this.
jdbc:h2:~/db;INIT=RUNSCRIPT FROM 'classpath:employee.sql'Code language: PHP (php)
Mocking the Actual DB with H2 DB
You can add the
MODE to tell it the actual queries you are using the provided database. IN our case MySql. With this, it will test against the MySQL-specific keywords and functions. Of course, as I said, it doesn’t recognize 100% but can work with major ones. I will add it via
jdbc:h2:~/db;MODE=Mysql;INIT=RUNSCRIPT FROM 'classpath:employee.sql'Code language: PHP (php)
One thing to note, You have to add
DROP ALL OBJECTS; a query at top of the SQL script, since it runs the script every time it connects to the database.
Testing SQL with H2 DB
Now that everything is ready, let’s jump to the actual testing code. It will be a very simple one. We just call the getCountOfUsersFromDb method by passing the H2 DB details we created, storing the results in a variable, and asserting it. In the script we have inserted 2 records, we will assert it with 2. Here is how.
And finally, you have tested the SQL queries. The link to this file with the full code is on GitHub.
That’s it for this post. Hope you have enjoyed the post and learned from it. Few key points. It’s In Memory so don’t use it as a permanent database. It had its limitations. Not all Mocked database queries would work. use
DROP ALL Object to reset DB.The Code could be found on GitHub.
See you in the next post. HAKUNA MATATA
Please Subscribe to the newsletter to know about the latest posts from CodersTea.