Home Java How to use H2 DB in Core Java Project

How to use H2 DB in Core Java Project

Published: Last Updated on 3 comments

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.

You can check out my other posts on JDBC such as, How to Achieve Greatness in JDBC Performance, and JDBC Connection Pooling Explained with HikariCP.

Let’s start the engine, the H2 Database Engine. The Code could be found on GitHub.

I would be happy to connect with you guys on social media. It’s @coderstea on TwitterLinkedinFacebook, Instagram, and YouTube.

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

// you don't need to register classname this after Java 7 I guess.

// connection no need to provide username or password, but you can do so if you want
Connection con = DriverManager.getConnection("jdbc:h2:~/your-db-name")Code language: JavaScript (javascript)

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

try (
    Connection conn = DriverManager.getConnection(url, username, passowrd);
    Statement stmt = conn.createStatement();
    ResultSet resultSet = stmt.executeQuery("SELECt count(*) FROM employee");
) {
    if (resultSet.next()) {
        return resultSet.getInt(1);
} catch (SQLException e) {
return 0;
}Code language: JavaScript (javascript)

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.


CREATE TABLE employee (
    id int,
    name varchar(250)

INSERT INTO employee (id, name) VALUES (1, 'Imran');
INSERT INTO employee (id, name) VALUES (2, 'Mahesh');Code language: JavaScript (javascript)

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 ;MODE=Mysql.

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.

SimpleDatabaseOperation obj = new SimpleDatabaseOperation();
String jdbcUrl = "jdbc:h2:~/db;MODE=Mysql;INIT=RUNSCRIPT FROM 'classpath:employee.sql'";

int actualEmpCount = obj.getCountOfUsersFromDb(jdbcUrl, "", "");
Assertions.assertEquals(2, actualEmpCount);Code language: JavaScript (javascript)

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

I would be happy to connect with you guys on social media. It’s @coderstea on TwitterLinkedinFacebook, Instagram, and YouTube.

Please Subscribe to the newsletter to know about the latest posts from CodersTea.

Notify of
Newest Most Voted
Inline Feedbacks
View all comments

@2023 All Right Reserved. Designed and Developed by CodersTea

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More