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; } }
热门相关:蔬菜宝贝历险记 玻璃之花与坏掉的世界 致命弯道 女职员-职场恋爱 淫欲模特