h2

name	h2
description	Expert in H2 embedded database - in-memory and file-based SQL database for Java applications. Use this when configuring H2 database, writing JDBC code, or setting up test databases.
license	MIT

H2 Database Expert

Expert in H2, the lightweight embedded Java SQL database engine for development, testing, and lightweight production use.

When to Use This Skill

• Setting up H2 database for Java applications
• Configuring JDBC connections
• Writing SQL for H2
• Integrating H2 with Python (via jaydebeapi)
• Setting up test databases
• Running H2 console

H2 Database Basics

Key Features

• Embedded: Runs in same JVM as application
• In-memory: Fast, lost on shutdown
• File-based: Persistent storage
• Server mode: Multiple connections
• Small footprint: ~2.5MB JAR file
• SQL compatibility: MySQL, PostgreSQL modes

JDBC URL Patterns

// In-memory database (lost on shutdown)
jdbc:h2:mem:testdb

// In-memory with name (shared in same JVM)
jdbc:h2:mem:mydb

// Keep database in memory until JVM exits
jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1

// File-based database
jdbc:h2:file:./db/mydb
jdbc:h2:file:/absolute/path/to/db

// File-based with options
jdbc:h2:file:./db/mydb;AUTO_SERVER=TRUE

// MySQL compatibility mode
jdbc:h2:mem:testdb;MODE=MySQL

// PostgreSQL compatibility mode
jdbc:h2:mem:testdb;MODE=PostgreSQL

Java Integration

Maven Dependency

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
    <scope>test</scope>
</dependency>

Basic JDBC Connection

import java.sql.*;

public class H2Example {
    public static void main(String[] args) throws Exception {
        // Load H2 driver
        Class.forName("org.h2.Driver");
        
        // Connect to database
        Connection conn = DriverManager.getConnection(
            "jdbc:h2:mem:testdb",
            "sa",  // username
            ""     // password
        );
        
        // Create table
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100))");
        
        // Insert data
        PreparedStatement pstmt = conn.prepareStatement(
            "INSERT INTO users VALUES (?, ?)"
        );
        pstmt.setInt(1, 1);
        pstmt.setString(2, "John Doe");
        pstmt.executeUpdate();
        
        // Query data
        ResultSet rs = stmt.executeQuery("SELECT * FROM users");
        while (rs.next()) {
            System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
        }
        
        // Close connections
        rs.close();
        stmt.close();
        pstmt.close();
        conn.close();
    }
}

Python Integration

Using jaydebeapi

import jaydebeapi

# Connect to H2
conn = jaydebeapi.connect(
    "org.h2.Driver",
    "jdbc:h2:./db/database",
    ["sa", ""],
    "/path/to/h2.jar"
)

cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100)
    )
""")

# Insert data
cursor.execute("INSERT INTO users VALUES (?, ?, ?)", (1, 'John', '[e-mail]'))
conn.commit()

# Query data
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

Installing Dependencies

# Install jaydebeapi and jpype1
poetry add jaydebeapi jpype1

# Or with pip
pip install jaydebeapi jpype1

H2 Console (Web UI)

Starting the Console

# Start H2 console
java -cp h2.jar org.h2.tools.Console

# Or with specific settings
java -cp h2.jar org.h2.tools.Console -web -webPort 8082

# Access at: http://localhost:8082

Console Connection

• JDBC URL: jdbc:h2:./db/mydb
• User: sa
• Password: (empty by default)

Common SQL Operations

Table Creation

-- Simple table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- With indexes
CREATE INDEX idx_email ON users(email);

CRUD Operations

-- Insert
INSERT INTO users (name, email) VALUES ('John', '[e-mail]');

-- Select
SELECT * FROM users WHERE email LIKE '[e-mail]';

-- Update
UPDATE users SET name = 'Jane' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Useful Functions

-- Auto-increment
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- Current timestamp
SELECT CURRENT_TIMESTAMP;

-- String functions
SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT UPPER(name) FROM users;
SELECT SUBSTRING(name, 1, 3) FROM users;

Database Modes

MySQL Compatibility

jdbc:h2:mem:testdb;MODE=MySQL;DATABASE_TO_LOWER=TRUE

Features:

• MySQL-style string concatenation
• LIMIT clause
• MySQL date functions

PostgreSQL Compatibility

jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE

Features:

• PostgreSQL-style string functions
• LIMIT/OFFSET
• PostgreSQL data types

Configuration Options

Common Options

// Auto server mode (multiple connections to file DB)
jdbc:h2:file:./db/mydb;AUTO_SERVER=TRUE

// Case-insensitive identifiers
jdbc:h2:mem:testdb;CASE_INSENSITIVE_IDENTIFIERS=TRUE

// Keep connection open
jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1

// Trace SQL statements
jdbc:h2:mem:testdb;TRACE_LEVEL_SYSTEM_OUT=2

// Encrypt database
jdbc:h2:file:./db/secure;CIPHER=AES

Testing with H2

JUnit Test Example

@BeforeEach
void setUp() throws SQLException {
    connection = DriverManager.getConnection(
        "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1",
        "sa",
        ""
    );
    
    // Initialize schema
    Statement stmt = connection.createStatement();
    stmt.execute("CREATE TABLE IF NOT EXISTS users (id INT, name VARCHAR)");
    stmt.close();
}

@AfterEach
void tearDown() throws SQLException {
    if (connection != null) {
        connection.close();
    }
}

@Test
void testInsertUser() throws SQLException {
    PreparedStatement pstmt = connection.prepareStatement(
        "INSERT INTO users VALUES (?, ?)"
    );
    pstmt.setInt(1, 1);
    pstmt.setString(2, "Test User");
    int rows = pstmt.executeUpdate();
    
    assertEquals(1, rows);
}

Troubleshooting

Common Issues

Database locked:

• Close all connections properly
• Use AUTO_SERVER mode for file databases
• Check if another process is using the database

File not found:

• Use absolute paths or ./relative/path
• Ensure directory exists
• Check file permissions

Out of memory:

• Use file-based database instead of in-memory
• Limit query result size
• Increase JVM heap size

Best Practices

1. Use in-memory for tests - Fast and clean
2. Close connections - Always close in finally/try-with-resources
3. Use prepared statements - Prevent SQL injection
4. Enable AUTO_SERVER for file DBs with multiple connections
5. Set DB_CLOSE_DELAY=-1 for test databases
6. Choose appropriate mode - MySQL/PostgreSQL compatibility if needed

Project Context

In this project:

• H2 JAR located at: db/h2.jar
• Currently using SQLite instead of H2
• H2 available for future migration if needed
• Can integrate with Python Flask API via jaydebeapi

Migration Example (SQLite to H2)

# Current: SQLite
DB_PATH = 'db/thoughtful_ai.db'
conn = sqlite3.connect(DB_PATH)

# Migrated: H2
import jaydebeapi
conn = jaydebeapi.connect(
    "org.h2.Driver",
    "jdbc:h2:./db/thoughtful_ai;DB_CLOSE_DELAY=-1",
    ["sa", ""],
    "./db/h2.jar"
)