H2 Database Usage

H2 most complete information: http://download.csdn.net/detail/yixiaoping/5956595 H2 Database usage

H2 Database Introduction

Common open Source database: H2,derby,hsqldb,mysql,postgresql. Where H2,hsqldb is similar, it is well suited for use as an embedded database, and most of the other databases need to be installed on separate client and server side.
Advantages of H2:
1, H2 is written in pure Java, so it is not limited by the platform.
2, H2 only a jar file, very suitable as an embedded database trial.
3. Advantages of performance and function

H2 's biggest advantage over Hsqldb is that H2 provides a very handy web console for manipulating and managing database content, which is much more useful than the HSQLDB swing and AWT consoles.

Comparison of H2 and database features.

Preparatory work

1, h2-2011-04-04.zip:http://www.h2database.com/html/download.html
2, unzip the file, here to%h2_home% as the extracted file directory. Running%h2_home%\bin\h2.bat will automatically open the following URLs. (Please verify that the JDK is installed and that the JAVA_HOME environment variable is set)
Http://192.168.140.1: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 Bag
H2.bat//windows Console Startup script
h2.sh//linux Console Startup script
H2w.bat//windows Console startup script (without a black screen window)
+docs Help Documentation
+service//through wrapper packaging into service.
+SRC//Source code
BUILD.bat Windows Build Script
build.sh Linux Build Script

Use of H2
Supports Embedded,server and In-memory modes as well as 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 . 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, 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

Related articles

• Open source database H2, HSQLDB, DERBY, PostgreSQL, MySQL difference/Compare chart (additional translation)
• Compact and easy-to-use H2 database