H2 資料庫介紹(2)--使用

且行且码發表於2024-05-19
資料庫

本文主要介紹 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();
}

完整程式碼：

H2 資料庫介紹(2)--使用 
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"));
        }
    }
}
H2Case.java
H2 資料庫介紹(2)--使用 
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;
    }
}
JdbcUtil.java

相關文章