H2 Database 使用详解

原创于 2025-10-09 15:36:36 发布 · 377 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

H2 Database 使用详解

1. H2 Database 简介

H2 是一个开源的嵌入式关系型数据库，使用 Java 语言编写。它主要特点包括：

轻量级：jar 包仅约 2MB
快速：性能优秀，启动迅速
嵌入式模式：可直接在应用中运行，无需单独安装
内存模式：数据存储在内存中，适合测试和缓存
支持标准 SQL：兼容大部分 SQL 标准
跨平台：基于 Java，可在任何支持 JVM 的平台运行

2. 安装与配置

Maven 依赖

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

Gradle 依赖

implementation 'com.h2database:h2:2.2.224'

3. 连接方式

3.1 嵌入式模式

// 嵌入式连接（本地文件存储）
String url = "jdbc:h2:~/test";  // 用户目录下的test数据库
String url = "jdbc:h2:/data/sample";  // 绝对路径

3.2 内存模式

// 内存数据库（应用关闭后数据丢失）
String url = "jdbc:h2:mem:testdb";

3.3 服务器模式

// TCP 服务器模式
String url = "jdbc:h2:tcp://localhost/~/test";

// SSL 连接
String url = "jdbc:h2:ssl://localhost/~/test";

4. 完整使用示例

4.1 基础连接示例

import java.sql.*;

public class H2Example {
    private static final String DB_DRIVER = "org.h2.Driver";
    private static final String DB_URL = "jdbc:h2:mem:testdb";
    private static final String DB_USERNAME = "sa";
    private static final String DB_PASSWORD = "";
    
    public static void main(String[] args) {
        try {
            // 加载驱动
            Class.forName(DB_DRIVER);
            
            // 建立连接
            Connection conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            
            // 创建表
            createTable(conn);
            
            // 插入数据
            insertData(conn);
            
            // 查询数据
            queryData(conn);
            
            // 关闭连接
            conn.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static void createTable(Connection conn) throws SQLException {
        String sql = """
            CREATE TABLE IF NOT EXISTS users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50) NOT NULL,
                email VARCHAR(100),
                age INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """;
        
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("表创建成功");
        }
    }
    
    private static void insertData(Connection conn) throws SQLException {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 插入多条数据
            Object[][] users = {
                {"张三", "zhangsan@email.com", 25},
                {"李四", "lisi@email.com", 30},
                {"王五", "wangwu@email.com", 28}
            };
            
            for (Object[] user : users) {
                pstmt.setString(1, (String) user[0]);
                pstmt.setString(2, (String) user[1]);
                pstmt.setInt(3, (Integer) user[2]);
                pstmt.executeUpdate();
            }
            System.out.println("数据插入成功");
        }
    }
    
    private static void queryData(Connection conn) throws SQLException {
        String sql = "SELECT * FROM users";
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("用户列表:");
            System.out.println("ID\t姓名\t邮箱\t\t\t年龄\t创建时间");
            
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                int age = rs.getInt("age");
                Timestamp createdAt = rs.getTimestamp("created_at");
                
                System.out.printf("%d\t%s\t%s\t%d\t%s%n", 
                    id, name, email, age, createdAt);
            }
        }
    }
}

4.2 使用连接池

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class H2ConnectionPool {
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setPassword("");
        config.setDriverClassName("org.h2.Driver");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setIdleTimeout(300000);
        
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static void closeDataSource() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

4.3 Spring Boot 集成配置

# application.yml
spring:
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password:
    hikari:
      maximum-pool-size: 10
      
  h2:
    console:
      enabled: true
      path: /h2-console
      
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: create-drop
    show-sql: true
    
  sql:
    init:
      mode: always

5. H2 控制台使用

启动控制台

// 在应用中启动 H2 控制台
import org.h2.tools.Server;

public class H2ConsoleStarter {
    public static void main(String[] args) throws SQLException {
        // 启动 TCP 服务器和控制台
        Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092").start();
        Server.createWebServer("-web", "-webAllowOthers", "-webPort", "8082").start();
        
        System.out.println("H2 控制台已启动: http://localhost:8082");
    }
}

访问控制台

浏览器访问 http://localhost:8082
连接 URL: jdbc:h2:mem:testdb
用户名: sa
密码: 空

6. 高级功能

6.1 事务管理

public class TransactionExample {
    public void transferMoney(Connection conn, int fromId, int toId, double amount) 
            throws SQLException {
        
        conn.setAutoCommit(false);
        
        try {
            // 扣款
            String deductSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(deductSql)) {
                pstmt.setDouble(1, amount);
                pstmt.setInt(2, fromId);
                pstmt.executeUpdate();
            }
            
            // 存款
            String addSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(addSql)) {
                pstmt.setDouble(1, amount);
                pstmt.setInt(2, toId);
                pstmt.executeUpdate();
            }
            
            conn.commit();
            System.out.println("转账成功");
            
        } catch (SQLException e) {
            conn.rollback();
            System.out.println("转账失败，已回滚");
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

6.2 批量操作

public class BatchExample {
    public void batchInsert(Connection conn, List<User> users) throws SQLException {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (User user : users) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getEmail());
                pstmt.setInt(3, user.getAge());
                pstmt.addBatch();
            }
            
            int[] results = pstmt.executeBatch();
            System.out.println("批量插入完成，影响行数: " + results.length);
        }
    }
}

6.3 数据库备份与恢复

public class BackupExample {
    // 备份数据库
    public static void backupDatabase(String sourceDb, String backupFile) 
            throws SQLException {
        String backupSql = String.format(
            "BACKUP TO '%s'", backupFile);
        
        try (Connection conn = DriverManager.getConnection(sourceDb);
             Statement stmt = conn.createStatement()) {
            stmt.execute(backupSql);
        }
    }
    
    // 从脚本恢复
    public static void restoreFromScript(Connection conn, String scriptFile) 
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("RUNSCRIPT FROM '" + scriptFile + "'");
        }
    }
}

7. 性能优化技巧

7.1 连接参数优化

String url = "jdbc:h2:mem:testdb;" +
    "DB_CLOSE_DELAY=-1;" +           // 保持数据库在连接关闭后不删除
    "DB_CLOSE_ON_EXIT=FALSE;" +      // JVM退出时不关闭数据库
    "CACHE_SIZE=65536;" +           // 缓存大小
    "LOCK_MODE=0";                  // 无锁模式

7.2 索引优化

-- 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_age ON users(age);

-- 复合索引
CREATE INDEX idx_user_name_email ON users(name, email);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

8. 常见问题与解决方案

8.1 连接问题

// 解决连接被占用问题
String url = "jdbc:h2:~/test;AUTO_SERVER=TRUE";

// 解决文件锁问题
String url = "jdbc:h2:~/test;FILE_LOCK=NO";

8.2 内存数据库数据持久化

// 将内存数据库保存到文件
public void saveInMemoryToFile(Connection conn, String filePath) throws SQLException {
    try (Statement stmt = conn.createStatement()) {
        stmt.execute("SCRIPT TO '" + filePath + "'");
    }
}

// 从文件加载到内存数据库
public void loadFileToInMemory(Connection conn, String filePath) throws SQLException {
    try (Statement stmt = conn.createStatement()) {
        stmt.execute("RUNSCRIPT FROM '" + filePath + "'");
    }
}

9. 测试中使用 H2

JUnit 测试示例

public class UserRepositoryTest {
    private Connection conn;
    private UserRepository repository;
    
    @BeforeEach
    void setUp() throws SQLException {
        // 创建内存数据库连接
        conn = DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", "");
        repository = new UserRepository(conn);
        
        // 初始化测试数据
        initTestData();
    }
    
    @AfterEach
    void tearDown() throws SQLException {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
    
    @Test
    void testFindUserById() throws SQLException {
        User user = repository.findById(1);
        assertNotNull(user);
        assertEquals("张三", user.getName());
    }
    
    private void initTestData() throws SQLException {
        // 创建表和测试数据
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("""
                CREATE TABLE users (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    name VARCHAR(50),
                    email VARCHAR(100)
                )
                """);
                
            stmt.execute("INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@test.com')");
        }
    }
}