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.
