Java Embedded H2 Database tutorial in Web applications

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:

Second, start the H2 database

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

The code is as follows

Copy Code

Package Me.gacl.web.listener; Import java.sql.SQLException; Import javax.servlet.ServletContextEvent; Import Javax.servlet.ServletContextListener; Import Org.h2.tools.Server; /** * @ClassName: H2dbserverstartlistener * @Description: The listener used to start the H2 database service and start the H2 database service when the application system is initialized * @author: Lonely Wolf * @date: 2014-12-20 11:43:39 * */ public class H2dbserverstartlistener implements Servletcontextlistener { H2 Database Server Startup instance Private server server; /* * Start the H2 database when the Web application is initialized */ public void contextinitialized (Servletcontextevent sce) { try { System.out.println ("Starting the H2 Database ..."); Use the Org.h2.tools.Server class to create a H2 database service and start the service, and because no parameters are specified, the default port used for H2 database startup is 8082 Server = Server.createtcpserver (). Start (); SYSTEM.OUT.PRINTLN ("H2 database started successfully ..."); catch (SQLException e) { System.out.println ("Start H2 Database error:" + e.tostring ()); E.printstacktrace (); throw new RuntimeException (e); } } /* * Stop H2 Database when Web application is destroyed */ public void contextdestroyed (Servletcontextevent sce) { if (this.server!= null) { Stop H2 Database This.server.stop (); This.server = null; } } }

After the listener is written, We register this listener in the Web.xml file and, in addition, because we want to embed the H2 database into our web application, 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 files are configured as follows:

The code is as follows

Copy Code

<!--start and stop a database using a listener--> <listener> <listener-class>me.gacl.web.listener.H2DBServerStartListener</listener-class> </listener> <!--the servlet H2 console using the H2 console is a stand-alone application that includes its own web server, but it can act as a servlet--> <servlet> <servlet-name>H2Console</servlet-name> <servlet-class>org.h2.server.web.WebServlet</servlet-class> <init-param> <param-name>webAllowOthers</param-name> <param-value></param-value> </init-param> <init-param> <param-name>trace</param-name> <param-value></param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <!--map H2 access path to the console--> <servlet-mapping> <servlet-name>H2Console</servlet-name> <url-pattern>/console/*</url-pattern> </servlet-mapping>

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

Deploy the Web application to a tomcat server, and when you start the Tomcat server, you can see the message that the H2 database started successfully at the console, as shown in the following illustration:

To further verify that the H2 database is really starting through the listener normally, we can access the console of the H2 database and enter the access address: "http://localhost:8080/H2DBTest/console/" for access, as shown in the following illustration:

You can see the login page for the H2 database console, which shows that the H2 database has been started properly.

Iii. registering a custom database function with the H2 database

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

Here's how to implement a custom function for a H2 database

In the MySQL database, a UUID function is used to generate the UUID, and executing the Select UUID () will see the UUID generated by the UUID function, as shown in the following illustration:

By default, the H2 database does not provide us with the UUID function, as shown in the following illustration:

So let's implement a UUID function and register it in the H2 database so that the H2 database supports the UUID function, which is divided into two steps:

(1) Methods of implementing custom functions using Java.

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

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

The code is as follows

Copy Code

Package h2db.function.ext; Import Java.util.UUID; /** * @ClassName: H2dbfunctionext * @Description: Extensions for H2 database functions * @author: Lonely Wolf * @date: 2014-12-20 11:20:34 * */ public class H2dbfunctionext { /** * Usage: SELECT uuid (); * H2 Database registration UUID function: CREATE ALIAS uuid for "h2db.function.ext.H2DBFunctionExt.uuid"; * @Method: UUID * @Description: Implementing the UUID function of the MySQL database to generate the UUID * @Anthor: Lonely Wolf * * @return */ public static String uuid () { Return Uuid.randomuuid (). toString (); } }

In this way, even if our UUID function is written, it is important to note that the class and method must be public and the method should be static, such as the connection object used in the method to close it.

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

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

where the [] enclosed part is optional, the statement to be executed is: CREATE ALIAS UUID for "H2db.function.ext.H2DBFunctionExt.uuid", and the results of the execution are as follows:

As a result, a single UUID function can be used in the H2 database, and we execute the Select UUID () statement again to be parsed by the H2 database, as shown in the following illustration:

This is an extension of the H2 database function, and we have added a 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, I'll show you how to extend multiple functions to the H2 database, we write a H2dbfunctionext class, and we write an extension function for the H2 database in the class, the code reads as follows:

The code is as follows

Copy Code

Package h2db.function.ext; Import java.net.InetAddress; Import java.net.UnknownHostException; Import java.text.ParseException; Import Java.text.SimpleDateFormat; Import Java.util.Date; Import Java.util.UUID; /** * @ClassName: H2dbfunctionext * @Description: Extensions for H2 database functions * @author: Lonely Wolf * @date: 2014-12-20 11:20:34 * */ public class H2dbfunctionext { /** * Usage: SELECT uuid (); * H2 Database registration UUID function: CREATE ALIAS IF not EXISTS uuid for "h2db.function.ext.H2DBFunctionExt.uuid"; * @Method: UUID * @Description: Implementing the UUID function of the MySQL database to generate the UUID * @Anthor: Lonely Wolf * * @return */ public static String uuid () { Return Uuid.randomuuid (). toString (); } /** * H2 Database Registration CurrentTime function: CREATE ALIAS IF not EXISTS currenttime for "H2db.function.ext.H2DBFunctionExt.now"; * @Method: Now * @Description: Implement the Now () function of the MySQL database to generate the current system time * @Anthor: Lonely Wolf * * @return */ public static String now () { return new Date (). toLocaleString (); } /** * H2 Database Registration IP function: CREATE ALIAS IF not EXISTS IP for "h2db.function.ext.H2DBFunctionExt.getIp"; * @Method: GetIP * @Description: * @Anthor: Lonely Wolf * * @return */ public static String GetIP () { try { InetAddress addr = Inetaddress.getlocalhost (); Get native IP return addr.gethostaddress (); catch (Unknownhostexception e) { E.printstacktrace (); Return "Unknown IP address"; } } /** * H2 Database Registration Date_format function: CREATE ALIAS IF not EXISTS date_format for "H2db.function.ext.H2DBFunctionExt.date_format"; * @Method: Date_format * @Description: Implement the Date_format () function of the MySQL database for formatting dates * @Anthor: Lonely Wolf * @param date * @param pattern * @return */ public static string Date_format (string date,string pattern) { if (date!= null) { SimpleDateFormat SDF = new SimpleDateFormat (pattern); try { Date temp = sdf.parse (date); return Sdf.format (temp); catch (ParseException e) { E.printstacktrace (); } } Return ""; } }

In order to implement the extended function of the batch registration H2 database, we can write a servlet that is designed to register an extension function, as follows:

The code is as follows

Copy Code

Package me.gacl.sys.init; Import java.sql.Connection; Import java.sql.Statement; Import javax.servlet.ServletException; Import Javax.servlet.http.HttpServlet; Import Me.gacl.util.JdbcUtil; /** * @ClassName: Registerh2extfuncservlet * @Description: Register extension functions for H2 database * @author: Lonely Wolf * @date: 2014-12-20 11:47:03 * */ public class Registerh2extfuncservlet extends HttpServlet { /** * @Field: Serialversionuid */ Private static final long serialversionuid = 4379248469825545593L; public void Init () throws Servletexception { 1, register the UUID function SQL statement String SQL1 = "CREATE ALIAS IF not EXISTS uuid for" h2db.function.ext.H2DBFunctionExt.uuid "; 2, register the CURRENTTIME function SQL statement String sql2 = "CREATE ALIAS IF not EXISTS currenttime for" H2db.function.ext.H2DBFunctionExt.now "; 3, register the IP function of the SQL statement String sql3 = "CREATE ALIAS IF not EXISTS IP for" h2db.function.ext.H2DBFunctionExt.getIp "; 4, register the DATE_FORMAT function SQL statement String sql4 = "CREATE ALIAS IF not EXISTS date_format for" H2db.function.ext.H2DBFunctionExt.date_format "; Connection Connection = null; Statement stmt = null; try { Getting a database connection Connection = Jdbcutil.getconnection (); Get Statement Object stmt = Connection.createstatement (); Add SQL to execute Stmt.addbatch (SQL1); Stmt.addbatch (SQL2); Stmt.addbatch (SQL3); Stmt.addbatch (SQL4); Batch execution of the above 4 SQL Stmt.executebatch (); SYSTEM.OUT.PRINTLN ("H2 Database extension function registered successfully!") "); Stmt.clearbatch (); catch (Exception e) { SYSTEM.OUT.PRINTLN ("H2 Database extension function registration failed!") "); E.printstacktrace (); }finally{ try { Stmt.close (); Connection.close (); catch (Exception E2) { E2.printstacktrace (); } } } }

Register Registerh2extfuncservlet in Web.xml

The code is as follows

Copy Code

<servlet> <description> Register extension functions for H2 database </description> <servlet-name>RegisterH2DBExtFunction</servlet-name> <servlet-class>me.gacl.sys.init.RegisterH2ExtFuncServlet</servlet-class> <!-- 1. The Load-on-startup element tag container loads the servlet at startup (instantiating and invoking its init () method). 2, its value must be an integer, indicating the order in which the servlet should be loaded 3. A value of 0 or greater than 0 o'clock indicates that the container loads and initializes the servlet when the application is started; 4, when the value is less than 0 or unspecified, the container is loaded when the servlet is selected. 5. The smaller the value of a positive number, the higher the priority of the servlet, and the more it loads first when the application starts. 6, the value of the same time, the container will choose their own order to load. So, <load-on-startup>x</load-on-startup&gt, the value of X in 1,2,3,4,5 represents the priority, not the start delay time. --> <load-on-startup>1</load-on-startup> </servlet>

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

The code is as follows

Copy Code

/** * */ Package me.gacl.util; Import Java.io.InputStream; Import java.sql.Connection; Import java.util.Properties; Import Org.h2.jdbcx.JdbcConnectionPool; public class Jdbcutil { /** * H2 database with the connection pool */ private static jdbcconnectionpool CP = NULL; static{ try { Loading the h2config.properties under the SRC directory InputStream in = JdbcUtil.class.getClassLoader (). getResourceAsStream ("h2config.properties"); Properties prop = new properties (); Prop.load (in); Creating a database connection pool CP = Jdbcconnectionpool.create (Prop.getproperty ("Jdbc_url"), Prop.getproperty ("USER"), Prop.getproperty ("PASSWORD" )); catch (Exception e) { SYSTEM.OUT.PRINTLN ("Connection pool initialization exception"); E.printstacktrace (); } } /** * @Method: getconnection * @Description: Getting a database connection * @Anthor: Lonely Wolf * @return * @throws Exception */ public static Connection getconnection () throws exception{ return Cp.getconnection (); } public static Jdbcconnectionpool getcp () { return CP; } }

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

Jdbc_url=jdbc:h2:tcp://localhost/~/h2db

User=gacl

Password=123

When the Web application is started, the Init method in this 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 the statement object performs a batch of SQL to register the extension function to the H2 database.

If there are no errors in the execution of the registerh2extfuncservlet, it means that all the extensions for the H2 database have been registered successfully, and we can go to the console of H2 to verify the above 4 extension functions, as shown in the following figure: