H2 数据库介绍(2)--使用

本文主要介绍 H2 的基本使用，文中所使用到的软件版本：Java 1.8.0_341、H2 2.2.224、PostgreSQL 驱动 42.5.5。

1、嵌入式(本地)模式

直接使用 JDBC 连接数据库即可，如果数据库不存在会自动创建。

1.1、持久数据库

@Test
public void localFile() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:file:d:/temp/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

private void business(Connection con, String dbName) throws SQLException {
    String tableName = "a_student";
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("select * from INFORMATION_SCHEMA.TABLES");
    while (rs.next()) {
        log.info("table_catalog={},table_schema={},table_name={}", rs.getString("table_catalog"), rs.getString("table_schema"), rs.getString("table_name"));
    }
    String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_catalog)=? and upper(table_schema)=? and upper(table_name)=?";
    PreparedStatement pst = con.prepareStatement(sql);
    pst.setString(1, dbName.toUpperCase());
    pst.setString(2, "PUBLIC");
    pst.setString(3, tableName.toUpperCase());
    rs = pst.executeQuery();
    if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
        st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
        st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
        st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
    }

    rs = st.executeQuery("select * from " + tableName);
    while (rs.next()) {
        log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
    }
}

1.2、内存数据库

@Test
public void localMem() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:mem:" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2、服务器模式

可以通过 bin/h2.bat(或 bin/h2.sh) 命令启动 H2 控制台，同时会启动 Web 服务器合 PG 服务器，也可以通过以下方式启动服务器：

java -cp h2*.jar org.h2.tools.Server [param1] [param2] [...]

改方式可以添加参数来调整服务器的默认行为，查看所有参数：

java -cp h2*.jar org.h2.tools.Server -?

相关参数如下：

[-web]                  Start the web server with the H2 Console
[-webAllowOthers]       Allow other computers to connect - see below
[-webExternalNames]       The comma-separated list of external names and IP addresses of this server, used together with -webAllowOthers
[-webDaemon]            Use a daemon thread
[-webPort <port>]       The port (default: 8082)
[-webSSL]               Use encrypted (HTTPS) connections
[-webAdminPassword]     Password of DB Console administrator
[-browser]              Start a browser connecting to the web server
[-tcp]                  Start the TCP server
[-tcpAllowOthers]       Allow other computers to connect - see below
[-tcpDaemon]            Use a daemon thread
[-tcpPort <port>]       The port (default: 9092)
[-tcpSSL]               Use encrypted (SSL) connections
[-tcpPassword <pwd>]    The password for shutting down a TCP server
[-tcpShutdown "<url>"]  Stop the TCP server; example: tcp://localhost
[-tcpShutdownForce]     Do not wait until all connections are closed
[-pg]                   Start the PG server
[-pgAllowOthers]        Allow other computers to connect - see below
[-pgDaemon]             Use a daemon thread
[-pgPort <port>]        The port (default: 5435)
[-properties "<dir>"]   Server properties (default: ~, disable: null)
[-baseDir <dir>]        The base directory for H2 databases (all servers)
[-ifExists]             Only existing databases may be opened (all servers)
[-ifNotExists]          Databases are created when accessed
[-trace]                Print additional trace information (all servers)
[-key <from> <to>]      Allows to map a database name to another (all servers)

2.1、启动 Web 服务器

java -cp h2-2.2.224.jar  org.h2.tools.Server -web -webAllowOthers

使用浏览器访问 H2 控制台：http://localhost:8082

2.2、启动 TCP 服务器

java -cp h2-2.2.224.jar  org.h2.tools.Server -tcp -tcpAllowOthers -ifNotExists

应用程序使用 JDBC 访问数据库。

2.2.1、持久数据库

@Test
public void tcpFile() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/file:d:/temp/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2.2.2、内存数据库

@Test
public void tcpMem() throws Exception {
    String dbName = "test";
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/mem:" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2.3、启动 PG 服务器

java -cp h2-2.2.224.jar  org.h2.tools.Server -pg -pgAllowOthers -baseDir d:/temp -ifNotExists

应用程序引入 PG JDBC 驱动并访问数据库。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.5</version>
</dependency>

@Test
public void pg() throws Exception {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.postgresql.Driver", "jdbc:postgresql://localhost:5435/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

使用 PG 客户端访问时，默认的 schema 为小写：public，而使用本地或 TCP 模式访问时，默认的 schema 为大写：PBBLIC；因此不能使用 PG 客户端访问本地或 TCP模式创建的库，也不能使用本地或 TCP模式访问 PG 客户端创建的库，否则会报错误："Schema "PUBLIC" not found" 或 "Schema "public" not found"。

3、混合模式

混合模式本应用使用本地模式访问，其他应用使用远程模式访问，需要在应用中通过 API 访问相应的服务器。

3.1、启动 Web 服务器

@Test
public void web() throws Exception {
    Server server = Server.createWebServer().start();

    Thread.sleep(1000 * 100);
    server.stop();
}

使用浏览器访问 H2 控制台：http://localhost:8082

3.2、启动 TCP 服务器

应用中启动 TCP 服务器，其他应用通过 JDBC 访问数据库。

3.2.1、持久数据库

@Test
public void tcpFile2() throws Exception {
    //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
    Server server = Server.createTcpServer("-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            tcpFile();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();

    countDownLatch.await();
    server.stop();
}

3.2.2、内存数据库

@Test
public void tcpMem2() throws Exception {
    //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
    Server server = Server.createTcpServer("-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            tcpMem();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();

    countDownLatch.await();
    server.stop();
}

3.3、启动 PG 服务器

应用中启动 PG 服务器，其他应用通过 PG 驱动访问数据库。

@Test
public void pg2() throws Exception {
    //如果数据库不存在，该方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库；该方式还需要指定数据库文件目录
    Server server = Server.createPgServer("-baseDir", "d:/temp", "-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            pg();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();

    countDownLatch.await();
    server.stop();
}

完整代码：

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;
import org.h2.tools.Server;
import org.junit.Test;

import java.sql.*;
import java.util.concurrent.CountDownLatch;

@Slf4j
public class H2Case {
    @Test
    public void localFile() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:file:d:/temp/" + dbName, "admin", "123456");
        log.info("con={}", con);
        business(con, dbName);
        con.close();
    }

    @Test
    public void localMem() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:mem:" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void tcpFile() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/file:d:/temp/" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void tcpMem() throws Exception {
        String dbName = "test";
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/mem:" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void pg() throws Exception {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.postgresql.Driver", "jdbc:postgresql://localhost:5435/" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void web() throws Exception {
        Server server = Server.createWebServer().start();

        Thread.sleep(1000 * 10);
        server.stop();
    }

    @Test
    public void tcpFile2() throws Exception {
        //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
        Server server = Server.createTcpServer("-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                tcpFile();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();

        countDownLatch.await();
        server.stop();
    }

    @Test
    public void tcpMem2() throws Exception {
        //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
        Server server = Server.createTcpServer("-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                tcpMem();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();

        countDownLatch.await();
        server.stop();
    }

    @Test
    public void pg2() throws Exception {
        //如果数据库不存在，该方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库；该方式还需要指定数据库文件目录
        Server server = Server.createPgServer("-baseDir", "d:/temp", "-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                pg();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();

        countDownLatch.await();
        server.stop();
    }

    private void business(Connection con, String dbName) throws SQLException {
        String tableName = "a_student";
        Statement st = con.createStatement();
        String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_catalog)=? and upper(table_schema)=? and upper(table_name)=?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, dbName.toUpperCase());
        pst.setString(2, "PUBLIC");
        pst.setString(3, tableName.toUpperCase());
        ResultSet rs = pst.executeQuery();
        if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
            st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
            st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
            st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
        }

        rs = st.executeQuery("select * from " + tableName);
        while (rs.next()) {
            log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
        }
    }
}

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;


@Slf4j
public class JdbcUtil {
    private JdbcUtil() {}

    public static Connection getConnection(String driver, String url, String username, String password) {
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            log.warn("url={},username={},password={}", url, username, password);
            e.printStackTrace();
        }
        return con;
    }
}