How Java uses embedded databases in Web applications H2

Source: Internet
Author: User
Tags stmt uuid

I. Build test environment and project

1.1. Build Javaweb Test Project

Create a "h2dbtest" Javaweb project and locate the jar file for the H2 database, as shown in the following illustration:

The H2 database is a jar file that contains the driver classes used to connect the H2 database using JDBC, adding "H2-1.4.183.jar" to the "H2dbtest" project, as shown in the following illustration:

1.2, open the H2 database

Go to the H2bin directory, as shown in the following illustration:

Make sure that the 8082 ports used by the H2 database are not occupied by other applications, and enter "http://localhost:8082" for a simple test after normal startup, as shown in the following illustration:

To this end, the test environment that uses Java operations to H2 a database is built.

Operation of H2 database in Java

2.1. Connect H2 database with embedded (local) connection mode

This connection method allows only one client to connect to the H2 database by default, and once the client is connected to the H2 database, the database files are locked so that no other clients can connect.

Connection syntax: jdbc:h2:[file:][ ]

For example:

Jdbc:h2:~/test//connection to test database in user directory

Jdbc:h2:file:/data/sample

Jdbc:h2:file:e:/h2/gacl (Windows only)

Write the test code as follows:

The code is as follows Copy Code

/**
*
*/
Package jdbc.conn.h2.test;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.Statement;
Import Java.util.UUID;

/**
* <p>ClassName:H2ConnTest1<p>
* <p>description:java connect H2 database via JDBC <p>
* @author XUDP
* @version 1.0 V
* @createTime 2014-12-18 11:22:12
*/
public class H2conntest1 {
The database connection URL, which is currently connected to the GACL database in the E:/H2 directory
private static final String Jdbc_url = "Jdbc:h2:e:/h2/gacl";
User name to use when connecting to the database
private static final String USER = "GaCl";
Password used when connecting to the database
private static final String PASSWORD = "123";
The drive class that is used to connect to the H2 database, Org.h2.Driver This class is provided by the H2 database and can be found in the H2 database jar package
private static final String driver_class= "Org.h2.Driver";

public static void Main (string[] args) throws Exception {
Load H2 Database Driver
Class.forName (Driver_class);
According to the connection URL, user name, password get database connection
Connection conn = drivermanager.getconnection (Jdbc_url, USER, PASSWORD);
Statement stmt = Conn.createstatement ();
Delete the User_info table if there is a user_info table
Stmt.execute ("DROP TABLE IF EXISTS user_info");
Create a User_info table
Stmt.execute ("CREATE TABLE user_info (id VARCHAR () PRIMARY key,name VARCHAR (MB), Sex VARCHAR (4))");
New
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Big day Tathagata ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' qinglong ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' White Tiger ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' rosefinch ', ' female ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Xuanwu ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Pale wolf ', ' Male ') ");
Delete
Stmt.executeupdate ("DELETE from User_info WHERE name= ' Big Day");
Modify
Stmt.executeupdate ("UPDATE user_info SET name= ' aloof wolf ' WHERE name= ' Cang lang '");
Inquire
ResultSet rs = stmt.executequery ("SELECT * from User_info");
Traverse result set
while (Rs.next ()) {
System.out.println (rs.getstring ("id") + "," + rs.getstring ("name") + "," + rs.getstring ("sex"));
}
Releasing resources
Stmt.close ();
Close connection
Conn.close ();
}
}

The results of the implementation are as follows:

Log in to the H2 console and you can also see the data created in the User_info tables and tables, as shown in the following illustration:

Here's how you can connect to a H2 database using this "jdbc:h2:e:/h2/gacl" approach, if you've logged into the GaCl database in H2 's Webconsole console, as shown in the following illustration:

The GACL database is locked at this point, and the following error occurs when you connect to the GACL database through Java code, as shown in:

The code is as follows Copy Code
Exception in thread "main" org.h2.jdbc.JdbcSQLException:Database could already in use: "E:/h2/gacl.mv.db". Possible Solutions:close All other connection (s); Use the server mode [90020-183]
At Org.h2.message.DbException.getJdbcSQLException (dbexception.java:345)
At Org.h2.message.DbException.get (dbexception.java:168)
At Org.h2.mvstore.db.MVTableEngine.init (mvtableengine.java:108)
At Org.h2.engine.Database.getPageStore (database.java:2376)
At Org.h2.engine.Database.open (database.java:666)
At Org.h2.engine.Database.openDatabase (database.java:266)
At Org.h2.engine.database.<init> (database.java:260)
At Org.h2.engine.Engine.openSession (engine.java:60)
At Org.h2.engine.Engine.openSession (engine.java:167)
At Org.h2.engine.Engine.createSessionAndValidate (engine.java:145)
At Org.h2.engine.Engine.createSession (engine.java:128)
At Org.h2.engine.Engine.createSession (engine.java:26)
At Org.h2.engine.SessionRemote.connectEmbeddedOrServer (sessionremote.java:347)
At Org.h2.jdbc.jdbcconnection.<init> (jdbcconnection.java:108)
At Org.h2.jdbc.jdbcconnection.<init> (jdbcconnection.java:92)
At Org.h2.Driver.connect (driver.java:72)
At Java.sql.DriverManager.getConnection (drivermanager.java:571)
At Java.sql.DriverManager.getConnection (drivermanager.java:215)
At Jdbc.conn.h2.test.H2ConnTest1.main (h2conntest1.java:33)
caused By:java.lang.IllegalStateException:The file is locked:nio:e:/h2/gacl.mv.db [1.4.183/7]
At Org.h2.mvstore.DataUtils.newIllegalStateException (datautils.java:768)
At Org.h2.mvstore.FileStore.open (filestore.java:170)
At Org.h2.mvstore.mvstore.<init> (mvstore.java:346)
At Org.h2.mvstore.mvstore$builder.open (mvstore.java:2754)
At Org.h2.mvstore.db.mvtableengine$store.<init> (mvtableengine.java:162)
At Org.h2.mvstore.db.MVTableEngine.init (mvtableengine.java:98)
... More

The reason for this error is because the GACL database file has been locked, so the Java code here can not be accessed, in order to allow Java code to be able to access, you must put the Webconsole console side of the connection first disconnected,

After disconnecting from the database, the Java code can be connected on this side.

2.2, the use of TCP/IP server mode (remote connection) way to connect the H2 database (recommended)

This connection is similar to other databases and is connected based on service, allowing multiple clients to connect to the H2 database at the same time

Connection syntax: jdbc:h2:tcp:// [: ] [[ ]]

Example: Jdbc:h2:tcp://localhost/~/test

The test code is as follows:

The code is as follows Copy Code

/**
*
*/
Package jdbc.conn.h2.test;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.Statement;
Import Java.util.UUID;

/**
* <p>ClassName:H2ConnTest1<p>
* <p>description:java connect H2 database via JDBC <p>
* @author XUDP
* @version 1.0 V
* @createTime 2014-12-18 11:22:12
*/
public class H2conntest2 {
Database connection URL, which is currently connected to the GACL database under the E:/H2 directory by using TCP/IP server mode (remote connection)
private static final String Jdbc_url = "Jdbc:h2:tcp://localhost/e:/h2/gacl";
private static final String Jdbc_url = "Jdbc:h2:tcp://127.0.0.1/e:/h2/gacl";
private static final String Jdbc_url = "Jdbc:h2:tcp://192.168.1.144/data/gacl";
User name to use when connecting to the database
private static final String USER = "GaCl";
Password used when connecting to the database
private static final String PASSWORD = "123";
The drive class that is used to connect to the H2 database, Org.h2.Driver This class is provided by the H2 database and can be found in the H2 database jar package
private static final String driver_class= "Org.h2.Driver";

public static void Main (string[] args) throws Exception {
Load H2 Database Driver
Class.forName (Driver_class);
According to the connection URL, user name, password get database connection
Connection conn = drivermanager.getconnection (Jdbc_url, USER, PASSWORD);
Statement stmt = Conn.createstatement ();
Delete the User_info table if there is a user_info table
Stmt.execute ("DROP TABLE IF EXISTS user_info");
Create a User_info table
Stmt.execute ("CREATE TABLE user_info (id VARCHAR () PRIMARY key,name VARCHAR (MB), Sex VARCHAR (4))");
New
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Big day Tathagata ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' qinglong ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' White Tiger ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' rosefinch ', ' female ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Xuanwu ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Pale wolf ', ' Male ') ");
Delete
Stmt.executeupdate ("DELETE from User_info WHERE name= ' Big Day");
Modify
Stmt.executeupdate ("UPDATE user_info SET name= ' aloof wolf ' WHERE name= ' Cang lang '");
Inquire
ResultSet rs = stmt.executequery ("SELECT * from User_info");
Traverse result set
while (Rs.next ()) {
System.out.println (rs.getstring ("id") + "," + rs.getstring ("name") + "," + rs.getstring ("sex"));
}
Releasing resources
Stmt.close ();
Close connection
Conn.close ();
}
}

2.3, H2 Database memory mode

The H2 database is called a memory database because it supports creating databases and tables in memory

Examples are as follows:

The code is as follows Copy Code

Package jdbc.conn.h2.test;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.Statement;
Import Java.util.UUID;

/**
* @ClassName: TestMemH2
* @Description: H2 database Memory mode (data only exists in memory)
* @author: Lonely Wolf
* @date: 2014-12-18 10:47:01
*
*/
public class TestMemH2 {

Database connection URL, by using TCP/IP server mode (remote connection), which is currently connected to the GACL database in memory
private static final String Jdbc_url = "Jdbc:h2:tcp://localhost/mem:gacl";
User name to use when connecting to the database
private static final String USER = "GaCl";
Password used when connecting to the database
private static final String PASSWORD = "123";
The drive class that is used to connect to the H2 database, Org.h2.Driver This class is provided by the H2 database and can be found in the H2 database jar package
private static final String driver_class= "Org.h2.Driver";

public static void Main (string[] args) throws Exception {
Load H2 Database Driver
Class.forName (Driver_class);
According to the connection URL, user name, password get database connection
Connection conn = drivermanager.getconnection (Jdbc_url, USER, PASSWORD);
Statement stmt = Conn.createstatement ();
Delete the User_info table if there is a user_info table
Stmt.execute ("DROP TABLE IF EXISTS user_info");
Create a User_info table
Stmt.execute ("CREATE TABLE user_info (id VARCHAR () PRIMARY key,name VARCHAR (MB), Sex VARCHAR (4))");
New
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Big day Tathagata ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' qinglong ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' White Tiger ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' rosefinch ', ' female ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Xuanwu ', ' Male ') ");
Stmt.executeupdate ("INSERT into User_info VALUES (' + uuid.randomuuid () +" ', ' Pale wolf ', ' Male ') ");
Delete
Stmt.executeupdate ("DELETE from User_info WHERE name= ' Big Day");
Modify
Stmt.executeupdate ("UPDATE user_info SET name= ' aloof wolf ' WHERE name= ' Cang lang '");
Inquire
ResultSet rs = stmt.executequery ("SELECT * from User_info");
Traverse result set
while (Rs.next ()) {
System.out.println (rs.getstring ("id") + "," + rs.getstring ("name") + "," + rs.getstring ("sex"));
}
Releasing resources
Stmt.close ();
Close connection
Conn.close ();
}
}

The results of the operation are as follows:

Note: If you use the memory mode of the H2 database, the databases and tables we create are only in memory and once the server is restarted, the database and tables in memory do not exist.

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.