Java Embedded Database H2

H2 as an embedded database, its greatest benefit is that it can be embedded in our web applications and bundled with our web applications as part of our web application. Here's a demonstration of how to embed the H2 database in our web App.

First, 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 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:

　　

Second, start the H2 database

Now that we're embedding the H2 database as part of our web app, we're going to start the H2 database service in the Web app so we can connect to the H2 database, so we could write a listener (Listener) dedicated to starting the H2 database service. The Listener sample code is as follows:

 1 package Me.gacl.web.listener; 2 3 Import java.sql.SQLException; 4 Import javax.servlet.ServletContextEvent; 5 Import Javax.servlet.ServletContextListener; 6 Import Org.h2.tools.Server; 7 8/** 9 * @ClassName: H2dbserverstartlistener10 * @Description: The listener used to start the H2 database service, start the H2 database service at the time of application initialization * @author: Aloof Wolf 1      2 * @date: 2014-12-20 PM 11:43:3913 *14 */public class H2dbserverstartlistener implements Servletcontextlistener {16 17 H2 database Server Startup instance server;19/* * Web application initialization When launching H2 database */22 public void Contextin Itialized (Servletcontextevent sce) {try {System.out.println ("Starting H2 database ..."); 25//Make Using the Org.h2.tools.Server class to create a service for the H2 database and start the service, because no parameters are specified, the default port used by H2 database startup is 808226 server = Server.createtcpserver ( ). Start (); SYSTEM.OUT.PRINTLN ("H2 database started successfully ..."); catch (SQLException e) {System.out.println  ("Error starting H2 database:" + e.tostring ()); E.printstacktrace (); throw new RuntimeException (e); }34 * * * Web App to stop H2 database when destroying */38 public void contextdestroyed (Servletcontextev Ent sce) {this.server = null) {40//Stop H2 Database in This.server.stop (); His.server = null;43}44}45}

After the listener has been written, We register this listener in the Web. xml file, and because we want to embed the H2 database in our website, in order to facilitate access to the console provided by the H2 database, we can configure the servlet in the Web. xml file to access the H2 database console.

The Web. xml file is configured as follows:

 1 <!--start and stop the database using a listener--2 <listener> 3 <listener-class>me.gacl.web.listener.h2dbserverst Artlistener</listener-class> 4 </listener> 5 6 <!--the servlet H2 console using the H2 console is a standalone application, including its own web service Service, but it can act as a servlet as--7 <servlet> 8 <servlet-name>H2Console</servlet-name> 9 &L T;servlet-class>org.h2.server.web.webservlet</servlet-class>10 <init-param>11 <para M-name>weballowothers</param-name>12 <param-value></param-value>13 </init-para m>14 <init-param>15 <param-name>trace</param-name>16 <param-value& gt;</param-value>17 </init-param>18 <load-on-startup>1</load-on-startup>19 &lt ;/servlet>20 <!--mapping H2 console access path-->21 <servlet-mapping>22 <servlet-name>h2console</      Servlet-name>23   <url-pattern>/console/*</url-pattern>24 </servlet-mapping> 

After configuring listener and the servlet to access the console, we can use the H2 database as part of our web application.

When you deploy a Web app to a tomcat server, when you start the Tomcat server, you can see the message that the H2 database started successfully in the console, as shown in:

　　

In order to further verify whether the H2 database is really started through the listener, we can access the H2 database console, enter the access address: "http://localhost:8080/H2DBTest/console/" to access, as shown:

　　

You can see the login page of the H2 database console, indicating that the H2 database has started normally.

Iii. registering a custom database function with the H2 database

H2 as a database, and other types of databases, will bring some database functions for us to use, but the H2 database provides a limited database function to meet our development needs, fortunately, the H2 database supports the custom database functions, So we can write database functions that meet our needs based on the actual application scenario in development.

Here's a look. How to implement a custom function for a H2 database

There is a UUID function in the MySQL database that is used to generate the UUID, and a "select UUID ()" is performed to see the UUID generated by the UUID function, as shown in:

　　

By default, the H2 database is not provided with a UUID function for us to use, as shown in:

　　

Then we can now implement a UUID function, and then register to the H2 database, so that the H2 database supports the UUID function, the specific method is divided into two steps:

(1) A way to implement a custom function using Java.

(2) Register the Java custom function with the H2 database.

First we implement this UUID function, in Java, the method of generating a UUID is generated using a Randomuuid () method inside the Java.util.UUID class, encapsulated as a UUID method, with the following code:

1 package h2db.function.ext; 2  3 import Java.util.UUID; 4  5/** 6 * @ClassName: H2dbfunctionext 7 * @Description: Expansion for H2 database functions 8 * @author: Aloof Wolf 9 * @date: 2014-12-20 PM 11:20:3410 *11 * */public class H2dbfunctionext {     /**15     * Usage: SELECT uuid (); 16
   
    * H2 Database Register UUID function: CREATE ALIAS uuid for "h2db.function.ext.H2DBFunctionExt.uuid";     @Method: Uuid18     * @ Description: Implements the UUID function of MySQL database, used to generate UUID19     * @Anthor: Aloof wolf     *21     * @return22 * * *     Static String uuid () {uuid.randomuuid ()         . toString ();     }26}
   

Thus, even if our UUID function is written, it is important to note that the class and method must be public and that the method is static (static), as the connection object is used in the method to close it.

Next we want to register it in the H2 database, we need to execute the "CREATE ALIAS" statement, the SQL syntax is as follows:

1 CREATE ALIAS [IF not EXISTS] newfunctionaliasname [deterministic] for Classandmethodname

where [] The part is optional, this example must execute the statement: CREATE ALIAS UUID for "H2db.function.ext.H2DBFunctionExt.uuid", the execution result is as follows:

　　

In this way the H2 database has more than one UUID function can be used, we execute the "select UUID ()" statement can be H2 database normal parsing, the result is as follows:

　　

The above is an extension to the H2 database function, and we have added a new UUID function to the H2 database to generate the UUID. Therefore, when the functions provided by the H2 database do not meet the actual requirements in our development, we can use this method to extend the functions of the H2 database. Next, we present a one-time extension of multiple functions to the H2 database, and we write a H2dbfunctionext class that writes an extension function to the H2 database in the class code as follows:

 1 package h2db.function.ext; 2 3 Import java.net.InetAddress; 4 Import java.net.UnknownHostException; 5 Import java.text.ParseException; 6 Import Java.text.SimpleDateFormat; 7 Import Java.util.Date; 8 Import Java.util.UUID; 9/**11 * @ClassName: h2dbfunctionext12 * @Description: Extended for H2 database functions * @author: Aloof and Blue Wolf * @date: 2014-12-20 11:20:34 *16 * * * public class H2dbfunctionext {18 19/**20 * Usage: SELECT uuid (); * H2 Database register UUID function: CREATE ALIAS IF N OT EXISTS uuid for "H2db.function.ext.H2DBFunctionExt.uuid"; @Method: Uuid23 * @Description: Implementing the UUID function for MySQL database , used to generate UUID24 * @Anthor: Aloof Wolf *26 * @return27 * */public static String uuid () {return UUID. Randomuuid (). toString ();}31/**33 * H2 Database registration CurrentTime function: CREATE ALIAS IF not EXISTS currenttime for "h2d B.function.ext.h2dbfunctionext.now * @Method: Now35 * @Description: Implement the Now () function of the MySQL database to generate the current system time of the day * @Ant Hor: Lonely Wolf *38 * @return39 * *n public static String now () {tolocalestring return new Date ().}43/**45 * H2 Database Registration IP function: CREATE ALIAS IF not EXISTS IP for "h2db.function.ext.H2DBFunctionExt.getIp"; * @Method: GetIp47 * @Descript             ion:48 * @Anthor: Aloof Wolf *50 * @return51 * * * public static String GetIP () {54         InetAddress addr = Inetaddress.getlocalhost (); 55//Get native IP56 return addr.gethostaddress (); 57 } catch (Unknownhostexception e) {e.printstacktrace (); "Unknown IP address"; 60} 6 1}62/**64 * H2 Database registration Date_format function: CREATE ALIAS IF not EXISTS date_format for "H2db.function.ext.H2DBFu Nctionext.date_format * @Method: DATE_FORMAT66 * @Description: Implements the Date_format () function of the MySQL database for formatting dates * @Ant Hor: Aloof Wolf * @param date69 * @param pattern70 * @return71 * * * static String Date_format (Strin G date,string pattern) {73 if (date! = null) {SimpleDateFormat SDF = new SimpleDateFormat (pattern); Date temp = Sdf.parse (date), Sdf.format return (temp), and the catch (parseexceptio n e) {e.printstacktrace ();}81}82 return ""; 83}84}

In order to implement the bulk registration of the H2 database extension function, we can write a servlet, specifically to register the extension function, the code is as follows:

 1 package me.gacl.sys.init; 2 3 4 Import java.sql.Connection; 5 Import java.sql.Statement; 6 7 Import Javax.servlet.ServletException; 8 Import Javax.servlet.http.HttpServlet; 9 Import me.gacl.util.jdbcutil;11/**13 * @ClassName: registerh2extfuncservlet14 * @Description: Register extension function for H2 database * @au     Thor: Aloof Wolf * @date: 2014-12-20 PM 11:47:0317 *18 * * public class Registerh2extfuncservlet extends HttpServlet {20 21 /**22 * @Field: serialVersionUID23 */private static final long serialversionuid = 4379248469825545593L; 1 public void Init () throws Servletexception {27//, SQL statement registering UUID function, String sql1 = "CREATE ALIAS IF not EXISTS uuid for \ "h2db.function.ext.h2dbfunctionext.uuid\"; 29//2, register SQL statement for currenttime function String s          QL2 = "CREATE ALIAS IF not EXISTS currenttime for \" h2db.function.ext.h2dbfunctionext.now\ ""; 31//3, SQL statement for registered IP functions 32 String sql3 = "CREATE ALIAS IF not EXISTS IP for \" H2db.function.ext.H2DBFunctioNext.getip\ ""; 33//4, register SQL statement for date_format function String sql4 = "CREATE ALIAS IF not EXISTS date_format for \" h 2db.function.ext.h2dbfunctionext.date_format\ ""; Connection Connection = null;36 Statement stmt = null;3 7 try {38//Get database Connection connection = Jdbcutil.getconnection (); 40//Get Statement Object             stmt = Connection.createstatement (); 42//Add SQL43 Stmt.addbatch to be executed (SQL1); 44 Stmt.addbatch (SQL2); Stmt.addbatch (SQL3); Stmt.addbatch (SQL4); 47//Bulk execution of the above 4 s QL48 Stmt.executebatch (); SYSTEM.OUT.PRINTLN ("H2 Database extension function registered successfully! "); Stmt.clearbatch (); the catch (Exception e) {System.out.println (" H2 Database extension function registration failed!                 "); E.printstacktrace ();}finally{55 try {stmt.close (); 57 Connection.close (); Exception} catch (E2) {e2.printstacktrace (); 60}61}62}63} 

Registering Registerh2extfuncservlet in Web. xml

1 <servlet> 2     <description> Register extension function for H2 database </description> 3     <servlet-name> Registerh2dbextfunction</servlet-name> 4     <servlet-class> Me.gacl.sys.init.registerh2extfuncservlet</servlet-class> 5     <!--  6     1, The Load-on-startup element flags whether the container loads the servlet at startup (instantiates and invokes its init () method). 7     2, its value must be an integer, indicating that the servlet should be loaded in the order 8     3, value is 0 or greater than 0 o'clock, indicating that the container loads and initializes the servlet when the app starts, 9     4, less than 0, or unspecified. Indicates that the container will not load until the servlet is selected. 5, the lower the     value of a positive number, the higher the priority of the servlet, the first time the application starts to load.     6, the value of the same time, the container will choose their own order to load.     so, <load-on-startup>x</load-on-startup> the value 1,2,3,4,5 in X represents the priority, not the start delay time.      -->14      <load-on-startup>1</load-on-startup>15 </servlet>

Registerh2extfuncservlet to execute SQL statements in bulk, so you need to connect to the H2 database to be able to execute, the tool class Jdbcutil provides a way to get a database connection, the Jdbcutil code is as follows:

 1/** 2 * 3 */4 package me.gacl.util; 5 6 Import Java.io.InputStream; 7 Import java.sql.Connection; 8 Import java.util.Properties; 9 Import org.h2.jdbcx.jdbcconnectionpool;10 One public class Jdbcutil {/**14 * H2 database comes with connection pool */16 p rivate static Jdbcconnectionpool cp = Null;17 static{19 try {20//load SRC directory H2config.propert             Ies21 InputStream in = JdbcUtil.class.getClassLoader (). getResourceAsStream ("H2config.properties"); 22 Properties prop = new properties (); Prop.load (in); 24//CREATE database connection pool CP = Jdbcconnec Tionpool.create (Prop.getproperty ("Jdbc_url"), Prop.getproperty ("USER"), Prop.getproperty ("PASSWORD"));     Exception e) {System.out.println ("Connection pool initialization exception"); E.printstacktrace (); 29}30}31 /**33 * @Method: GetConnection34 * @Description: Get database connection * @Anthor: Aloof Wolf * @return37 * @throw S ExceptiON38 * */All public static Connection getconnection () throws exception{40 return Cp.getconnection (); 41} public static Jdbcconnectionpool getcp () {cp;45}46}

The configuration information for the h2config.properties is as follows:

Jdbc_url=jdbc:h2:tcp://localhost/~/h2dbuser=gaclpassword=123

When the web app starts, the Init method in the servlet is executed, and the internal processing of the Init method is to get a H2 database connection through the Jdbcutil tool class and then create the statement object Registerh2extfuncservlet , and then the statement object bulk Execute SQL registers the extension function with the H2 database.

Registerh2extfuncservlet execution of the process if there are no errors, it means that all the extension functions for the H2 database are registered successfully, we can go to H2 's console to verify the above 4 extension functions, as shown in:

　　

There are so many things to be said about embedding the H2 database in a web app and extending the H2 database functions.

Java Embedded Database H2