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', 'john@example.com'))
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', 'john@example.com');

-- Select
SELECT * FROM users WHERE email LIKE '%@example.com';

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