H2 database文档学习

http://h2database.com/html/quickstart.html
Embedding H2 in an Application
嵌入式应用方式，使用方式：
1、Add the h2*.jar to the classpath (H2 does not have any dependencies)
2、Use the JDBC driver class: org.h2.Driver
3、The database URL jdbc:h2:~/test opens the database test in your user home directory
4、A new database is automatically created
特点是自动创建数据库

The H2 Console Application
The Console lets you access a SQL database using a browser interface.
这个控制台能够看到表结构，也能执行SQL语句
Step-by-Step
Click [Start], [All Programs], [H2], and [H2 Console (Command Line)]:
A new console window appears:
浏览器输入：
URL http://localhost:8082.
登陆界面：Generic H2   org.h2.Driver  jdbc:h2:test   sa
执行一个例子：
drop table if exists TEST;
create table TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUE(1,"Hello");
INSERT INTO TEST VALUE(2,"World");
SELECT * FROM TEST ORDER BY ID;
UPDATE TEST SET NAME="Hi" WHERE ID=1;
DELETE FROM TEST WHERE ID=2;
执行完成后，左边出现TEST
On the left side, a new entry TEST is added below the database icon. The operations and results of the statements are shown below the script.

然后继续读教程
http://h2database.com/html/tutorial.html

Starting and Using the H2 Console
上面已经有了基本描述
The H2 Console application lets you access a database using a browser. This can be a H2 database, or another database that supports the JDBC API. ---其实除了H2，没有人用。所以支持其他数据库有点画蛇添足，增加了代码复杂性

This is a client/server application, so both a server and a client (a browser) are required to run it.
Depending on your platform and environment, there are multiple ways to start the H2 Console:
启动方式平台相关：
Windows: Click [Start], [All Programs], [H2], and [H2 Console (Command Line)]
 浏览器输入：http://localhost:8082.
 Windows另外一个打开方式
 Open a file browser, navigate to h2/bin, and double click on h2.bat.
 浏览器输入：http://localhost:8082
 
 Any平台： 直接用java启动JAR
 Double click on the h2*.jar file. This only works if the .jar suffix is associated with Java.
 或者
 Open a console window, navigate to the directory h2/bin, and type:
 java -jar h2*.jar
 
 A small firewall is already built into the server: other computers may not connect to the server by default. To change this, go to 'Preferences' and select 'Allow connections from other computers'.
 自带一个防火墙，感觉也是多次一举
 
 Testing Java
 java -version
 必须安装JAVA
 
 Error Message 'Port may be in use'
 You can only start one instance of the H2 Console
 一个机器只启动一个server. 当然更换端口可以启动多个，没有必要
 Using another Port
 If the default port of the H2 Console is already in use by another application, then a different port needs to be configured. The settings are stored in a properties file. For details, see Settings of the H2 Console. The relevant entry is webPort. --- 更换端口的配置
 
 Connecting to the Server using a Browser
  本地：http://localhost:8082.
  异地：http://192.168.0.2:8082
 If you enabled TLS on the server side, the URL needs to start with https:// --- 这估计是新版本支持的
 
 Multiple Concurrent Sessions
 Multiple concurrent browser sessions are supported. As that the database objects reside on the server, the amount of concurrent work is limited by the memory available to the server application.
 支持并发数量依赖服务器内存
 
 Login
 At the login page, you need to provide connection information to connect to a database. Set the JDBC driver class of your database, the JDBC URL, user name, and password. If you are done, click [Connect].
 
 Adding Database Drivers
 To register additional JDBC drivers (MySQL, PostgreSQL, HSQLDB,...), add the jar file names to the environment variables H2DRIVERS or CLASSPATH. Example (Windows): to add the HSQLDB JDBC driver C:\Programs\hsqldb\lib\hsqldb.jar, set the environment variable H2DRIVERS to C:\Programs\hsqldb\lib\hsqldb.jar.
 
 Using the H2 Console --- 使用简单
 The H2 Console application has three main panels: the toolbar on top, the tree on the left, and the query/result panel on the right.  --- 3部分
 The database objects (for example, tables) are listed on the left. Type a SQL command in the query panel and click [Run]. The result appears just below the command.
 
 Settings of the H2 Console
 The settings of the H2 Console are stored in a configuration file called .h2.server.properties in you user home directory. For Windows installations, the user home directory is usually C:\Documents and Settings\[username] or C:\Users\[username]. 
 --注意配置文件的位置
 he configuration file contains the settings of the application and is automatically created when the H2 Console is first started. Supported settings are:
 支持的设置
 1. webAllowOthers: allow other computers to connect.
 2.webPort: the port of the H2 Console
 3.webSSL: use encrypted TLS (HTTPS) connections
 4.webAdminPassword: password to access preferences and tools of H2 Console.
 
 In addition to those settings, the properties of the last recently used connection are listed in the form <number>=<name>|<driver>|<url>|<user> using the escape character \. Example: 1=Generic H2 (Embedded)|org.h2.Driver|jdbc\:h2\:~/test|sa
 
 Connecting to a Database using JDBC
import java.sql.*;
public class Test {
    public static void main(String[] a)
            throws Exception {
        Connection conn = DriverManager.
            getConnection("jdbc:h2:~/test", "sa", "");
        // add application code here
        conn.close();
    }
}
This code opens a connection (using DriverManager.getConnection()). The driver name is "org.h2.Driver". 
The database URL always needs to start with jdbc:h2: to be recognized by this database. 
The second parameter in the getConnection() call is the user name (sa for System Administrator in this example). 
The third parameter is the password. 
In this database, user names are not case sensitive, but passwords are.

Creating New Databases
By default, if the database specified in the embedded URL does not yet exist, a new (empty) database is created automatically. The user that created the database automatically becomes the administrator of this database.
不存在会自动建立一个数据库

Auto-creation of databases can be disabled, see Opening a Database Only if it Already Exists.
关闭

H2 Console does not allow creation of databases unless a browser window is opened by Console during its startup or from its icon in the system tray and remote access is not enabled. A context menu of the tray icon can also be used to create a new database

You can also create a new local database from a command line with a Shell tool:
> java -cp h2-*.jar org.h2.tools.Shell

By default remote creation of databases from a TCP connection or a web interface is not allowed.
 It's not recommended to enable remote creation of databases due to security reasons. 
 It's recommended to create all databases locally using an embedded URL, local H2 Console, or the Shell tool.
 
If you really need to allow remote database creation, you can pass -ifNotExists parameter to TCP, PG, or Web servers (but not to the Console tool). 
Its combination with -tcpAllowOthers, -pgAllowOthers, or -webAllowOthers effectively creates a remote security hole in your system, if you use it, always guard your ports with a firewall or some other solution and use such combination of settings only in trusted networks.
尽量本地创建，别远程

Using the Server
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
java -cp h2*.jar org.h2.tools.Server
java -cp h2*.jar org.h2.tools.Server -?

Connecting to the TCP Server
To remotely connect to a database using the TCP server, use the following driver and database URL:
1.JDBC driver class: org.h2.Driver
2.Database URL: jdbc:h2:tcp://localhost/~/test

Starting the TCP Server within an Application
import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

Stopping a TCP Server from Another Process
java org.h2.tools.Server -tcpShutdown tcp://localhost:9092 -tcpPassword password
或者
org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9092", "password", false, false);

This function will only stop the TCP server. If other server were started in the same process, they will continue to run. 

Using Hibernate
This database supports Hibernate version 3.1 and newer. You can use the HSQLDB Dialect, or the native H2 Dialect.

Using TopLink and Glassfish
忽略不学

http://h2database.com/html/features.html
特性非常多
Multiple index types (b-tree, tree, hash)
Supports the EXPLAIN PLAN statement; sophisticated trace options

Connection Modes
1.Embedded mode (local connections using JDBC)
2.Server mode (remote connections using JDBC or ODBC over TCP/IP)
3.Mixed mode (local and remote connections at the same time)

Embedded Mode
In embedded mode, an application opens a database from within the same JVM using JDBC.
both persistent and in-memory databases are supported

Database URL Overview
1.Embedded (local) connection
jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (Windows only)

2.In-memory (private)
jdbc:h2:mem:

3.In-memory (named)
jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test_mem

Connecting to an Embedded (Local) Database
The database URL for connecting to a local database is jdbc:h2:[file:][<path>]<databaseName>. The prefix file: is optional. 
If no or only a relative path is used, then the current working directory is used as a starting point. The case sensitivity of the path and database name depend on the operating system, however it is recommended to use lowercase letters only. 
The database name must be at least three characters long (a limitation of File.createTempFile). The database name must not contain a semicolon. To point to the user home directory, use ~/, as in: jdbc:h2:~/test.

Database Files Encryption

Database File Locking
Whenever a database is opened, a lock file is created to signal other processes that the database is in use.
 If database is closed, or if the process that opened the database terminates, this lock file is deleted.
 The following file locking methods are implemented:
 1.The default method is FILE and uses a watchdog thread to protect the database file. The watchdog reads the lock file each second.
 2.The second method is SOCKET and opens a server socket. The socket method does not require reading the lock file every second. The socket method should only be used if the database files are only accessed by one (and always the same) computer.
 3.The third method is FS. This will use native file locking using FileChannel.lock.
 4.It is also possible to open the database without file locking; in this case it is up to the application to protect the database files. Failing to do so will result in a corrupted database. Using the method NO forces the database to not create a lock file at all. Please note that this is unsafe as another process is able to open the same database, possibly leading to data corruption.
 --单线程可以使用无锁i模式
 
 Opening a Database Only if it Already Exists
 By default, when an application calls DriverManager.getConnection(url, ...) with embedded URL and the database specified in the URL does not yet exist, a new (empty) database is created.
 缺省没有就创建一个新的库
 
  In some situations, it is better to restrict creating new databases, and only allow to open existing databases. To do this, add ;IFEXISTS=TRUE to the database URL. 
  特殊情况，不自动创建。
  In this case, if the database does not already exist, an exception is thrown when trying to connect. The connection only succeeds when the database already exists. The complete URL may look like this:
  String url = "jdbc:h2:/data/sample;IFEXISTS=TRUE";
  这个应该不常用
  
 Locking, Lock-Timeout, Deadlocks
 Please note MVCC is enabled in version 1.4.x by default, when using the MVStore.
In this case, table level locking is not used. If multi-version concurrency is not used, the database uses table level locks to give each connection a consistent state of the data. 

here are two kinds of locks: read locks (shared locks) and write locks (exclusive locks). All locks are released when the transaction commits or rolls back. 
hen using the default transaction isolation level 'read committed', read locks are already released after each statement.
隔离级别：读提交 

Note that delete, insert and update operations issue table level locks with PageStore engine, but does not issue them with default MVStore engine.
有两个存储引擎：PageStore, MVStore

Database File Layout
各种文件名称的含义
test.mv.db  ---<database>.mv.db  数据库文件 1 per database
test.newFile ---     Temporary file for database compaction. Contains the new MVStore file.
test.tempFile -- Contains the temporary MVStore file.

test.h2.db -- Database file. Contains the transaction log, indexes, and data for all tables.
Format: <database>.h2.db
PageStore engine is used

test.lock.db ---锁文件，自动创建

test.trace.db--

test.123.temp.db--Contains a temporary blob or a large result set.

test.lobs.db/*  Legacy PageStore databases from old versions of H2 can have the following additional files:

Page Size
The page size for new databases is 2 KB (2048), unless the page size is set explicitly in the database URL using PAGE_SIZE= when the database is created. 
缺省2048

Multi-Dimensional Indexes
A tool is provided to execute efficient multi-dimension (spatial) range queries.
This database does not support a specialized spatial index (R-Tree or similar). Instead, the B-Tree index is used. 
or each record, the multi-dimensional key is converted (mapped) to a single dimensional (scalar) value. This value specifies the location on a space-filling curve.

Currently, Z-order (also called N-order or Morton-order) is used; Hilbert curve could also be used, but the implementation is more complex. The algorithm to convert the multi-dimensional value is called bit-interleaving. The scalar value is indexed using a B-Tree index (usually using a computed column).

http://h2database.com/html/advanced.html
H2数据库的优点

Result Sets
Statements that Return a Result Set
The following statements return a result set: SELECT, TABLE, VALUES, EXPLAIN, CALL, SCRIPT, SHOW, HELP. EXECUTE may return either a result set or an update count. 

Result of a WITH statement depends on inner command. All other statements return an update count.

Limiting the Number of Rows
Before the result is returned to the application, all rows are read by the database. 
Server side cursors are not supported currently. 
If only the first few rows are interesting for the application, then the result set size should be limited to improve the performance. This can be done using FETCH in a query (example: SELECT * FROM TEST FETCH FIRST 100 ROWS ONLY), or by using Statement.setMaxRows(max).

Large Result Sets and External Sorting
For large result set, the result is buffered to disk. The threshold can be defined using the statement SET MAX_MEMORY_ROWS. 
If ORDER BY is used, the sorting is done using an external sort algorithm. In this case, each block of rows is sorted using quick sort, then written to disk; when reading the data, the blocks are merged together.

Large Objects
Storing and Reading Large Objects
If it is possible that the objects don't fit into memory, then the data type CLOB (for textual data) or BLOB (for binary data) should be used. 
When to use CLOB/BLOB
By default, this database stores large LOB (CLOB and BLOB) objects separate from the main table data. Small LOB objects are stored in-place, the threshold can be set using MAX_LENGTH_INPLACE_LOB, but there is still an overhead to use CLOB/BLOB. 

Transaction Isolation
The default MVStore engine supports read uncommitted, read committed, repeatable read, snapshot, and serializable (partially, see below) isolation levels:

The PageStore engine supports read uncommitted, read committed, and serializable isolation levels:

Multi-Version Concurrency Control (MVCC)
With default MVStore engine delete, insert and update operations only issue a shared lock on the table. 
An exclusive lock is still used when adding or removing columns or when dropping the table. Connections only 'see' committed data, and own changes.

Table Level Locking (PageStore engine)
With PageStore engine to make sure all connections only see consistent data, table level locking is used.
This mechanism does not allow high concurrency, but is very fast. 

Clustering / High Availability
This database supports a simple clustering / high availability mechanism. 
he architecture is: two database servers run on two different computers, and on both computers is a copy of the same database. 
If both servers run, each database operation is executed on both computers. If one server fails (power, hardware or network failure), the other server can still continue to work. From this point on, the operations will be executed only on one server until the other server is back up.

Pluggable File System
This database supports a pluggable file system API. The file system implementation is selected using a file name prefix. Internally, the interfaces are very similar to the Java 7 NIO2 API, but do not (yet) use or require Java 7. The following file systems are included:
1.zip: read-only zip-file based file system. Format: zip:~/zipFileName!/fileName.
2.split: file system that splits files in 1 GB files (stackable with other file systems).
3.nio: file system that uses FileChannel instead of RandomAccessFile (faster in some operating systems).
4.ioMapped: file system that uses memory mapped files (faster in some operating systems). Please note that there currently is a file size limitation of 2 GB when using this file system. To work around this limitation, combine it with the split file system: split:nioMapped:~/test.
5....
As an example, to use the nio file system with PageStore storage engine, use the following database URL: jdbc:h2:nio:~/test;MV_STORE=FALSE. With MVStore storage engine nio file system is used by default.

To register a new file system, extend the classes org.h2.store.fs.FilePath, FileBase, and call the method FilePath.register before using it.

http://h2database.com/html/architecture.html
结构介绍
H2 implements an embedded and standalone ANSI-SQL89 compliant SQL engine on top of a B-tree based disk store.

As of October 2013, Thomas is still working on our next-generation storage engine called MVStore. 
This will in time replace the B-tree based storage engine.

Top-down Overview
Working from the top down, the layers look like this:
1.JDBC driver.
2.Connection/session management.
3.SQL Parser.
4.Command execution and planning.
5.Table/Index/Constraints.
6.Undo log, redo log, and transactions layer.
7.B-tree engine and page-based storage allocation.
8.Filesystem abstraction.

JDBC Driver
The JDBC driver implementation lives in org.h2.jdbc, org.h2.jdbcx

Connection/session management
org.h2.engine.Database
org.h2.engine.SessionInterface
org.h2.engine.Session
org.h2.engine.SessionRemote

Parser
The parser lives in org.h2.command.Parser.

Command execution and planning
org.h2.command.ddl
org.h2.command.dml

Table/Index/Constraints
org.h2.table
org.h2.index

Undo log, redo log, and transactions layer
We also have an undo log, which is per session, to undo an operation (an update that fails for example) and to rollback a transaction.
Theoretically, the transaction log could be used, but for simplicity, H2 currently uses it's own "list of operations" (usually in-memory).
With the MVStore, this is no longer needed (just the transaction log).

B-tree engine and page-based storage allocation.
The primary package of interest is org.h2.store.
This implements a storage mechanism which allocates pages of storage (typically 2k in size) and also implements a b-tree over those pages to allow fast retrieval and update

Filesystem abstraction.
The primary class of interest is org.h2.store.FileStore.
This implements an abstraction of a random-access file. This allows the higher layers to treat in-memory vs. on-disk vs. zip-file databases the same.

http://h2database.com/html/mvstore.html
最新的存储引擎
Overview
The MVStore is a persistent, log structured key-value store. It is used as default storage subsystem of H2, but it can also be used directly within an application, without using JDBC or SQL.
--MVStore stands for "multi-version store".
--Each store contains a number of maps that can be accessed using the java.util.Map interface.
--Both file-based persistence and in-memory operation are supported.
--It is intended to be fast, simple to use, and small.
--Concurrent read and write operations are supported.
--Transactions are supported (including concurrent transactions and 2-phase commit).
--The tool is very modular. It supports pluggable data types and serialization, pluggable storage (to a file, to off-heap memory), pluggable map implementations (B-tree, R-tree, concurrent B-tree currently), BLOB storage, and a file system abstraction to support encrypted files and zip files.

Example Code
import org.h2.mvstore.*;

// open the store (in-memory if fileName is null)
MVStore s = MVStore.open(fileName);

// create/get the map named "data"
MVMap<Integer, String> map = s.openMap("data");

// add and read some data
map.put(1, "Hello World");
System.out.println(map.get(1));

// close the store (this will persist changes)
s.close();

Features

 

 

本文地址：https://blog.csdn.net/hb_zxl/article/details/107143773