Use of the H2 database

Source: Internet
Author: User

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

Use of the H2 database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.