How To Use H2 DB In Core Java Project | CodersTea
Home Java How to use H2 DB in Core Java Project

How to use H2 DB in Core Java Project

by Imran Shaikh
Published: Last Updated on 2056 views
How to use H2 DB in Core Java Project

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 in the main project. I just want to give you a glimpse of how you can get started with 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 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, JDBC Connection Pooling Explained with HikariCP.

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

What is In-Memory Database and Why to 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 for 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 actually 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 🧠.

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


You can follow me on social media via @coderstea on TwitterLinkedinFacebook, or Instagram. We also share high-quality videos about programming on our Youtube channel. You can also publish your post on CodersTea, just share your thought on Contact Us or let us know in the comments.


Get Started with H2 DB in-memeory Database

As I said I will be focusing on the Testing side, where most of the people do. To get started with the H2 DB, you simply need to add it in 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 is H2 Database Engine.

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

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. 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.
 Class.forName("org.h2.Driver");

// 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")

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, 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) {
    e.printStackTrace();
}
return 0;
}

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 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 SCHEMA IF NOT EXISTS db;

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

INSERT INTO employee (id, name) VALUES (1, 'Imran');
INSERT INTO employee (id, name) VALUES (2, 'Mahesh');

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 via passing INIT 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'

Mocking the Actual DB with H2 DB

You can add the MODE to tell it the actual queries you are using of 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'

One thing to note, You have to add DROP ALL OBJECTS; 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);

And finally, you have tested the SQL queries. The link to this file with full code is on GitHub.

Conclusion

That’s it for this post. Hope you have enjoyed the post and learned it. Few key points. It’s In Memory so don’t use it as a permanent database. It had its own 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


Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

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