H2 数据库快速入门

#Java 笔记

关键词：H2 Database, 关系数据库, SQL, 内存数据库。

H2 Database 是一个 Java 实现的关系型数据库，也是一个嵌入式数据库。

引入依赖

在 gradle 文件中引入依赖：

dependencies {
    compile group:'com.h2database', name: 'h2', version: '1.4.200'  // H2 
    compile group: 'junit', name: 'junit', version: '4.12'  // 用于写单测
}

示例1: 本地创建数据库

package h2;

import org.junit.Test;

import java.sql.*;

/**
 * 本地创建 DB
 */
public class TestH2_001 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ")";

    /**
     * 建表
     */
    @Test
    public void createTable() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        stmt.execute(DDL_CREATE_TABLE);
        stmt.close();
        conn.close();
    }

    /**
     * 添加数据到表中
     */
    @Test
    public void insertData() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("insert into user_info(name) values('张三')");
        stmt.executeUpdate("insert into user_info(name) values('李四')");
        stmt.close();
        conn.close();
    }

    /**
     * 查询数据
     */
    @Test
    public void queryAllData() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery("select id, name from user_info");
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            System.out.printf("id: %s, name: %s\n", id, name);
        }
        stmt.close();
        conn.close();
    }


    /**
     * 获取 H2 数据库连接
     */
    private Connection getH2Connection() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:~/test-no-pass");
//        return DriverManager.getConnection("jdbc:h2:~/test", "user-name", "password");
    }

}

使用DriverManager.getConnection("jdbc:h2:~/test-no-pass")获取数据库连接时，会在本地 HOME 目录下创建一个 test-no-pass.mv.db 数据库文件。

使用return DriverManager.getConnection("jdbc:h2:~/test", "user-name", "password"); 会在本地创建一个 test.mv.db 文件，且可访问的用户名和密码被设置为了user-name、password。

获取数据库连接后，就可以基于 JDBC 的接口进行数据操作了。

我们可以按照下面的顺序执行函数：

1、执行 createTable 函数，创建 user_info 表。

2、执行insertData函数，向 user_info 表中插入两条数据。

3、执行queryAllData函数，查询数据。查询结果：

id: 1, name: 张三
id: 2, name: 李四

示例2: 内存数据库

import org.junit.Test;

import java.sql.*;

/**
 * 内存中创建 DB
 */
public class TestH2_002 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ")";

    @Test
    public void test() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();

        // 建表
        stmt.execute(DDL_CREATE_TABLE);

        // 插入数据
        stmt.executeUpdate("insert into user_info(name) values('张三')");
        stmt.executeUpdate("insert into user_info(name) values('李四')");

        // 查询
        ResultSet resultSet = stmt.executeQuery("select id, name from user_info");
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            System.out.printf("id: %s, name: %s\n", id, name);
        }

        stmt.close();
        conn.close(); // 关闭后，数据库就不存在了

        // 再尝试查询数据会报错
        try {
            queryAllData();
        } catch (Exception ex) {
            System.out.println("查询出错");
        }

    }

    /**
     * 查询数据
     */
    private void queryAllData() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery("select id, name from user_info");
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            System.out.printf("id: %s, name: %s\n", id, name);
        }
        stmt.close();
        conn.close();
    }

    /**
     * 获取内存数据库连接
     */
    private Connection getH2Connection() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:mem:test-db");
    }

}

DriverManager.getConnection("jdbc:h2:mem:test-db") 获取连接时会创建一个临时的内存数据库，当连接被close，数据库也会被销毁。

test 方法执行结果：

id: 1, name: 张三
id: 2, name: 李四
查询出错

示例3: MySQL 兼容模式

默认情况下，MySQL 的某些语法，H2 是不兼容的。比如下面的加了表注释的建表语句：

CREATE TABLE `user_info` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`)
) COMMENT='用户信息';

H2 默认模式下，执行是会报错的。 示例代码：

import org.junit.Test;

import java.sql.*;

/**
 * MySQL 模式
 */
public class TestH2_003 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ") COMMENT='用户信息';";

    @Test
    public void test() throws Exception {

        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test-db");
        Statement stmt = conn.createStatement();
        
        stmt.execute(DDL_CREATE_TABLE);  // 会报错

        stmt.close();
        conn.close();
    }
}

执行test方法，会报错如下：

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE `USER_INFO` (
  `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `NAME` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`ID`)
) COMMENT[*]='用户信息';"; SQL statement:
CREATE TABLE `user_info` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`)
) COMMENT='用户信息'; [42000-200]

启用MySQL模式即可解决。

解决方式1: 先执行 SET MODE MYSQL;

import org.junit.Test;

import java.sql.*;

/**
 * MySQL 模式
 */
public class TestH2_003 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ") COMMENT='用户信息';";

    @Test
    public void test() throws Exception {

        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test-db");
        Statement stmt = conn.createStatement();

        // 建表
        stmt.execute("SET MODE MYSQL;");  // 增加这个就不会报错了
        stmt.execute(DDL_CREATE_TABLE);

        stmt.close();
        conn.close();
    }
}

解决方式2: JDBC url 中增加;MODE=MYSQL

代码示例：

import org.junit.Test;

import java.sql.*;

/**
 * MySQL 模式
 */
public class TestH2_003 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ") COMMENT='用户信息';";

    @Test
    public void test() throws Exception {

        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test-db;MODE=MYSQL");
        Statement stmt = conn.createStatement();

        // 建表
        stmt.execute(DDL_CREATE_TABLE);

        stmt.close();
        conn.close();
    }

}

示例4: 连接池

可以使用JdbcConnectionPool.create创建连接池。

示例代码:

import org.h2.jdbcx.JdbcConnectionPool;
import org.junit.Test;

import java.sql.*;

/**
 * 连接池
 */
public class TestH2_004 {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ")";

    @Test
    public void test() throws Exception {

        // 创建连接池
        JdbcConnectionPool connectionPool = JdbcConnectionPool.create("jdbc:h2:mem:test-db", "user-name", "password");
        connectionPool.setMaxConnections(10);

        // 获取连接
        Connection conn = connectionPool.getConnection();
        Statement stmt = conn.createStatement();

        // 建表
        stmt.execute(DDL_CREATE_TABLE);

        // 插入数据
        stmt.executeUpdate("insert into user_info(name) values('张三')");
        stmt.executeUpdate("insert into user_info(name) values('李四')");

        // 查询
        ResultSet resultSet = stmt.executeQuery("select id, name from user_info");
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            System.out.printf("id: %s, name: %s\n", id, name);
        }

        stmt.close();
        conn.close();

    }

}

示例5: C/S 模式

C/S 模式 即 客户端/服务器 模式。

服务端示例代码

import org.h2.tools.Server;

import java.lang.management.ManagementFactory;
import java.sql.SQLException;

public class TestH2_Server {

    public static void main(String[] args) throws SQLException {

        System.out.println("启动服务. " + ManagementFactory.getRuntimeMXBean().getName());
        Server server = Server.createTcpServer("-trace", "-ifNotExists", "-tcpPort", "8082").start();

        Runtime.getRuntime().addShutdownHook(new Thread() {
            @Override
            public void run() {
                System.out.println("服务暂停");
                server.stop();
            }
        });

    }
}

服务会监听 8082 端口。Server.createTcpServer 参数，可以看下相关源码。

客户端示例代码

import org.junit.Test;

import java.sql.*;

/**
 * 本地创建 DB
 */
public class TestH2_Client {

    final static String DDL_CREATE_TABLE = "CREATE TABLE `user_info` (\n" +
            "  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
            "  `name` VARCHAR(45) NOT NULL ,\n" +
            "  PRIMARY KEY (`id`)\n" +
            ")";
    
    // 建表
    @Test
    public void createTable() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        stmt.execute(DDL_CREATE_TABLE);
        stmt.close();
        conn.close();
    }
    
    // 插入数据
    @Test
    public void insertData() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("insert into user_info(name) values('张三')");
        stmt.executeUpdate("insert into user_info(name) values('李四')");
        stmt.close();
        conn.close();
    }

    // 查询数据
    @Test
    public void queryAllData() throws Exception {
        Connection conn = getH2Connection();
        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery("select id, name from user_info");
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            System.out.printf("id: %s, name: %s\n", id, name);
        }
        stmt.close();
        conn.close();
    }

    private Connection getH2Connection() throws SQLException {
        // 连接服务器，并告诉服务器在 HOME 目录创建 test-db-by-server 数据库
        return DriverManager.getConnection("jdbc:h2:tcp://127.0.0.1:8082/~/test-db-by-server");
    }
}

参考:

• https://www.h2database.com/
• https://stackoverflow.com/questions/42364935/how-to-add-the-mode-mysql-to-embedded-h2-db-in-spring-boot-1-4-1-for-datajpates
• http://www.h2database.com/html/cheatSheet.html
• https://github.com/h2database/h2database

( 本文完 )