H2 Memory Database supports storage to file

Preparatory work

1, download the JDK (i downloaded the version is JDK1.7), set the environment variable java_home, set path (%java_home%\bin%). 2, download and decompression: H2-2014-07-13.zip official website: http://www.h2database.com/html/main.html 3, set the environment variable h2_home. The%h2_home% is represented as the extracted file directory. Running%h2_home%\bin\h2.bat will automatically open the following URLs. (Make sure you have the JDK installed and set the JAVA_HOME environment variable) http://localhost:8082/login.jsp?jsessionid=244e36a683f97f0d4f3b000f33530ed1

3, click Connect, Login.

4, execute the red part of the SQL statement, successfully created the test table.

Because the database file location is not specified, it is automatically output to C:\Users\Administrator.

H2 file Structure

%h2_home%-h2-bin H2-1.3.154.jar//jar Package H2.bat//windows Console startup script h2.sh//linux console startup script H2w.bat//windows console startup script (without a black screen window) +d The OCS Help document +service//is packaged as a service through wrapper. +SRC//source code build.bat Windows build Script build.sh Linux build script

The use of H2 supports Embedded,server and in-memory modes.

Embedded mode

1, New Java Project Project H2test.

2,%h2_home%\bin\h2-1.3.154.jar Copy to \h2test\lib, and add the project reference. 3. New generic H2 (Embedded) database, specify: JDBC url:jdbc:h2:e:\research\workspace\h2test\db\test, then execute the test SQL statement above to create a test table.

4, the new TestH2 class main code

public static void Main (string[] a)
Throws Exception {
Class.forName ("Org.h2.Driver");
Connection conn = DriverManager.
Getconnection ("Jdbc:h2:e:\\research\\workspace\\h2test\\db\\test", "sa", "");
Add application code here
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("SELECT * from TEST");
while (Rs.next ()) {
System.out.println (Rs.getint ("ID") + "," +rs.getstring ("NAME"));
}
Conn.close ();
}

Console print out: 1,hi

Server mode

1. Change the JDBC URL directly to: Jdbc:h2:tcp://localhost/~/test on the line. Because we have created the test database in C:\Users\Administrator at the first step above. You can also create a new database, which is saved under C:\Users\Administrator by default. Note: You must start the service:%h2_home%\bin\h2.bat or start in service mode:%h2_home%\service\0_run_server_debug.bat, there are several scripts to deploy H2 as a service mode. The H2 service starts automatically after each machine starts. 2, the new TestServerH2 class main code

public static void Main (string[] a)
Throws Exception {
Class.forName ("Org.h2.Driver");
Connection conn = DriverManager.
Getconnection ("Jdbc:h2:tcp://localhost/~/test", "sa", "");
Add application code here
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("SELECT * from TEST");
while (Rs.next ()) {
System.out.println (Rs.getint ("ID") + "," +rs.getstring ("NAME"));
}
Conn.close ();
}

The result of the operation is the same as above.

Memory mode (data is only available in memory)

1, the new TestMemH2 class main code

public static void Main (string[] a)
Throws Exception {
Class.forName ("Org.h2.Driver");
Connection conn = DriverManager.
Getconnection ("Jdbc:h2:tcp://localhost/mem:test2", "sa", "");
Add application code here
Statement stmt = Conn.createstatement ();

Stmt.executeupdate ("CREATE TABLE test_mem (ID INT PRIMARY key,name VARCHAR (255));");
Stmt.executeupdate ("INSERT into Test_mem VALUES (1, ' hello_mem ');");
ResultSet rs = stmt.executequery ("SELECT * from Test_mem");
while (Rs.next ()) {
System.out.println (Rs.getint ("ID") + "," +rs.getstring ("NAME"));
}
Conn.close ();
}

Console print out: 1,hello_mem

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------

Cluster/High Availability

The database supports a simple cluster/high availability mechanism. The schema is: two database services run on two different computers, two computers have a copy of the same database, if two servers are running, each database operation is executed on two computers, if a server down (power outage, hardware failure, network failure, etc.), Another computer is still available, and from this point on, the database operation is performed only on one server until another server resumes running.

The cluster can only be used in server mode (inline mode does not support clustering). The cluster can be recovered while the database is running, but it is a manual process to restore the cluster, since it is not applied to change the data of the first database during the second database recovery.

To initialize the cluster, use the following steps:

· Create a database

· Create a database using the Createcluster tool to get to another place and initialize the cluster so that you have two databases of the same data

· Start two database services (a copy of each database)

· You can now connect to the database through the app client

Using the Create cluster tool

To see how the cluster works, try the example below, where two databases reside on the same computer, but typically two databases are on different computers.

· Create two directories: Server1,server2. Each directory will simulate a single computer

· In the first directory to start the TCP service, you can run the following command:

· Java org.h2.tools.Server

· -tcp-tcpport 9101

· -basedirserver1

· In the second directory to start the TCP service, simulate the second server (redundant running), you can use the following command:

· Java org.h2.tools.Server

· -tcp-tcpport 9102

· -basedirserver2

· Initialize the cluster with the Createcluster tool, if the database does not exist, create a new, empty database and run the following command line:

· Java org.h2.tools.CreateCluster

· -urlsourcejdbc:h2:tcp://localhost:9101/~/test

· -urltargetjdbc:h2:tcp://localhost:9102/~/test

· -user SA

· -serverlist localhost:9101,localhost:9102

· The application or H2 console can connect to the database via the following JDBC URL: jdbc:h2:tcp://localhost:9101,localhost:9102/~/test

· If you stop a service (by killing the process), you notice that another machine continues to work, and the database still provides access.

· To recover a cluster, you need to delete the outage database first, then restart the service of the downtime database, and then rerun the Createcluster cluster tool.

Detecting clusters in a running state

Find out which nodes are currently running by executing the following SQL statement:

SELECT VALUE from INFORMATION_SCHEMA. SETTINGS wherename= ' CLUSTER '

The result is returned as ' (two single quotes), indicating that the cluster mode is masked, otherwise the list of clustered servers will be enclosed in quotation marks including the return, such as ' server1:9191,server2:9191 '.

2, the above URL changed to Jdbc:h2:~/mem:test is also possible. If it is localhost, the service must be started.

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------

Database connection URL Description

The database supports multiple connection modes and connection settings, different connection modes and connection settings are differentiated by different URLs, and the settings in the URL are not case sensitive.

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 database (private)	Jdbc:h2:mem:
In-memory database (named)	Jdbc:h2:mem:<databasename> Jdbc:h2:mem:test_mem
Server mode with TCP/IP (remote connection)	Jdbc:h2:tcp://<server>[:<port>]/[<path>]<databasename> Jdbc:h2:tcp://localhost/~/test Jdbc:h2:tcp://dbserv:8084/~/sample
Server mode with SSL/TLS (remote connection)	Jdbc:h2:ssl://<server>[:<port>]/<databasename> jdbc:h2:ssl://secureserv:8085/~/sample;
Using encrypted files	jdbc:h2:<url>; cipher=[aes| XTEA] Jdbc:h2:ssl://secureserv/~/testdb; Cipher=aes jdbc:h2:file:~/secure; Cipher=xtea
File lock	jdbc:h2:<url>; file_lock={no| file| SOCKET} Jdbc:h2:file:~/quickanddirty; File_lock=no jdbc:h2:file:~/private; Cipher=xtea; File_lock=socket
Open only databases that exist	Jdbc:h2:<url>;ifexists=true Jdbc:h2:file:~/sample;ifexists=true
The database is not closed when the virtual machine exits	Jdbc:h2:<url>;D B_close_on_exit=false
User name and password	jdbc:h2:<url>[; user=<username>][; Password=<value>] Jdbc:h2:file:~/sample; User=sa; Password=123
Update into index	jdbc:h2:<url>; log=2 jdbc:h2:file:~/sample; log=2
Debug Tracking Item Settings	jdbc:h2:<url>; Trace_level_file=<level 0..3> jdbc:h2:file:~/sample; Trace_level_file=3
Ignore location parameter settings	Jdbc:h2:<url>;ignore_unknown_settings=true
Specify file read-write mode	jdbc:h2:<url>; ACCESS_MODE_LOG=RWS; Access_mode_data=rws
The database in the zip file	Jdbc:h2:zip:<zipfilename>!/<databasename> Jdbc:h2:zip:~/db.zip!/test
Compatibility mode	jdbc:h2:<url>; Mode=<databasetype> jdbc:h2:~/test; Mode=mysql
Automatic reconnection	jdbc:h2:<url>; Auto_reconnect=true jdbc:h2:tcp://localhost/~/test; Auto_reconnect=true
Auto Blending mode	jdbc:h2:<url>; Auto_server=true jdbc:h2:~/test; Auto_server=true
Change other settings	Jdbc:h2:<url>;<setting>=<value>[;<setting>=<value> jdbc:h2:file:~/sample; Trace_level_system_out=3

H2 Memory Database supports storage to file