H2 Database (http://www.h2database.com/html/main.html) 是一个开源的嵌入式数据库引擎，采用java语言编写，支持SQL，同时H2提供了一个十分方便的web控制台用于操作和管理数据库内容。
H2 官网介绍如下：
H2, the Java SQL database. The main features of H2 are:
- Very fast, open source, JDBC API
- Embedded and server modes; in-memory databases
- Browser based Console application
- Small footprint: around 1.5 MB jar file size
Connection Modes
The following connection modes are supported:
- Embedded mode (local connections using JDBC)
- Server mode (remote connections using JDBC or ODBC over TCP/IP)
- Mixed mode (local and remote connections at the same time)
Embedded Mode
This database can be used in embedded mode, or in server mode. To use it in embedded mode, you need to:
- Add the h2*.jar to the classpath (H2 does not have any dependencies)
- Use the JDBC driver class: org.h2.Driver
- The database URL jdbc:h2:~/test opens the database test in your user home directory
- A new database is automatically created
Server Mode
H2 currently supports three server: a web server (for the H2 Console), a TCP server (for client/server connections) and an PG server (for PostgreSQL clients). Please note that only the web server supports browser connections. The servers can be started in different ways, one is using the Server tool. Starting the server doesn't open a database - databases are opened as soon as a client connects.
Starting the Server Tool from Command Line
To start the Server tool from the command line with the default settings, run:
java -cp h2*.jar org.h2.tools.Server
Connecting to the TCP Server
o remotely connect to a database using the TCP server, use the following driver and database URL:
- JDBC driver class: org.h2.Driver
- Database URL: jdbc:h2:tcp://localhost/~/test
Database URL
| Topic | URL Format and Examples |
|---|---|
| Embedded (local) connection | jdbc:h2:[file:][<path>]<databaseName> jdbc:h2:~/test jdbc:h2:file:/data/sample jdbc:h2:file:C:/data/sample (Windows only) |
| In-memory (private) | jdbc:h2:mem: |
| In-memory (named) | jdbc:h2:mem:<databaseName> jdbc:h2:mem:test_mem |
| Server mode (remote connections) using TCP/IP | jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName> jdbc:h2:tcp://localhost//test<br>jdbc:h2:tcp://dbserv:8084//sample jdbc:h2:tcp://localhost/mem:test |
| Using encrypted files | jdbc:h2:<url>;CIPHER=AES jdbc:h2:ssl://localhost//test;CIPHER=AES<br>jdbc:h2:file:/secure;CIPHER=AES |
实战
maven依赖：
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.193</version>
</dependency>
创建本地文件数据库
代码如下：
Connection conn = null;
try{
Class.forName("org.h2.Driver");
conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");
//do your business
} catch (Exception e){
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
创建本地内存数据库
Connection conn = null;
try{
Class.forName("org.h2.Driver");
conn = DriverManager. getConnection("jdbc:h2:mem:test_mem", "sa", "sa");
// do your business
} catch (Exception e){
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
创建表
SQL：
CREATE TABLE t_user (
id BIGINT NOT NULL,
name VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age SMALLINT,
birthday TIMESTAMP,
PRIMARY KEY (id)
);
代码如下：
package com.bytebeats.codelab.h2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2017-03-05 14:43
*/
public class CreateTable {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("org.h2.Driver");
conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");
stmt = conn.createStatement();
int result = stmt.executeUpdate("CREATE TABLE t_user (" +
"id BIGINT NOT NULL, " +
"name VARCHAR(20) NOT NULL," +
"password VARCHAR(32) NOT NULL, " +
"age SMALLINT, " +
"birthday TIMESTAMP," +
"PRIMARY KEY (id)" +
");");
System.out.println("result:"+result);
} catch (Exception e){
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
插入记录
INSERT INTO t_user VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');
代码如下：
package com.bytebeats.codelab.h2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2017-03-05 14:57
*/
public class InsertQuery {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("org.h2.Driver");
conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");
stmt = conn.createStatement();
int update = stmt.executeUpdate("INSERT INTO t_user VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');");
System.out.println(update);
} catch (Exception e){
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
查询记录
SQL：
SELECT id, name, password, age, birthday FROM t_user where id=1
代码如下：
package com.bytebeats.codelab.h2;
import java.sql.*;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2017-03-05 14:57
*/
public class SelectQuery {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet result = null;
try{
Class.forName("org.h2.Driver");
conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", "sa");
stmt = conn.prepareStatement("SELECT id, name, password, age, birthday FROM t_user WHERE id=?");
stmt.setLong(1, 1);
result = stmt.executeQuery();
while(result.next()){
System.out.println(result.getInt("id")+" | "+
result.getString("name")+" | "+
result.getString("password")+" | "+
result.getShort("age")+" | "+
result.getTimestamp("birthday"));
}
} catch (Exception e){
e.printStackTrace();
} finally {
try {
result.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
参考资料
更多用法参考：H2 Database features
