How to persist data in H2 database

2 min read 06-10-2024

Persisting Data in H2 Database: A Simple Guide

H2 is a popular embedded database known for its simplicity and ease of use. Often, you'll want to store data persistently beyond the life of your application. This article will guide you through the process of persisting data in an H2 database, providing clear explanations and examples.

Understanding the Problem

The challenge lies in ensuring that your data remains accessible even after your application is closed. If you're working with an in-memory database, all data is lost when the application terminates. To overcome this, we need to store the data in a persistent file on the disk.

The Scenario and Code

Let's illustrate this with a simple Java example:

import org.h2.jdbc.JdbcConnection;
import java.sql.*;

public class H2PersistenceExample {

    public static void main(String[] args) throws SQLException {
        // Connect to the database (create a new file if it doesn't exist)
        Connection conn = JdbcConnection.create("jdbc:h2:~/testdb", "user", "password");

        // Create a table (if it doesn't exist)
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(255))");

        // Insert some data
        PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
        insertStmt.setInt(1, 1);
        insertStmt.setString(2, "John Doe");
        insertStmt.executeUpdate();

        // Close resources
        insertStmt.close();
        stmt.close();
        conn.close();
    }
}

This code does the following:

Connects to an H2 database file named testdb in your user home directory.
Creates a table named users if it doesn't already exist.
Inserts data into the table.
Closes all resources.

Insights and Explanation

File-based storage: When you use the jdbc:h2:~/testdb URL, H2 will create a file named testdb.mv.db in your user home directory. This file stores the database data persistently.
Persistence options: H2 offers various connection URLs, each with different persistence behavior. The jdbc:h2:mem: URL, for instance, uses in-memory storage and data will be lost upon application termination.
Database configuration: The CREATE TABLE IF NOT EXISTS syntax ensures the table is created only if it doesn't exist, preventing errors in subsequent runs.
Data manipulation: The PreparedStatement object allows you to execute SQL queries and insert data securely.

Additional Value and Considerations

Database management: Consider using a tool like H2 Console (accessible at http://localhost:8082/) to manage and query your database.
Security: When deploying your application, ensure the database file is stored securely and access is restricted based on your security requirements.
Alternative options: Explore other database options like PostgreSQL, MySQL, or SQLite if your needs require advanced features or scalability.

References

H2 Documentation: https://h2database.com/html/main.html
Java JDBC API: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

By understanding these key concepts and following best practices, you can confidently persist data in an H2 database, allowing your application to store information beyond its runtime.