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 [...]改方式可以添加参数来调整服务器的默认行为，检察所有参数：
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.jarorg.h2.tools.Server -web -webAllowOthers使用浏览器访问 H2 控制台：http://localhost:8082
2.2、启动 TCP 服务器
java -cp h2-2.2.224.jarorg.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.jarorg.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();
}
完整代码：
https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttps://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gifpackage 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.javahttps://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttps://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gifpackage 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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]