本例介绍H2与web项目的集成
项目启动H2数据库
1、新建Maven Web项目，参考：【Maven】Eclipse 使用Maven创建Java Web项目
2、引入h2的jar包依赖
1 <dependency> 2 <groupId>com.h2database</groupId> 3 <artifactId>h2</artifactId> 4 <version>1.4.199</version> 5 </dependency>
3、编辑web.xml文件，注册h2内置的监听器，监听ServletContext 对象启动，而启动H2数据库
1 <!-- 监听启动H2 DB --> 2 <listener> 3 <listener-class>org.h2.server.web.DbStarter</listener-class> 4 </listener> 5 6 <!-- H2 DB 参数 --> 7 <context-param> 8 <param-name>db.url</param-name> 9 <param-value>jdbc:h2:/D:/data/sample</param-value> 10 </context-param> 11 <context-param> 12 <param-name>db.user</param-name> 13 <param-value>sa</param-value> 14 </context-param> 15 <context-param> 16 <param-name>db.password</param-name> 17 <param-value></param-value> 18 </context-param> 19 <!-- 运行其他服务器访问 --> 20 <context-param> 21 <param-name>db.tcpServer</param-name> 22 <param-value>-tcpAllowOthers</param-value> 23 </context-param>
org.h2.server.web.DbStarter监听器代码如下，可以看到监听器创建的H2数据库，并且将一个数据库连接放入ServletContext 对象中。
1 /* 2 * Copyright 2004-2019 H2 Group. Multiple-Licensed under the MPL 2.0, 3 * and the EPL 1.0 (http://h2database.com/html/license.html). 4 * Initial Developer: H2 Group 5 */ 6 package org.h2.server.web; 7 8 import java.sql.Connection; 9 import java.sql.DriverManager; 10 import java.sql.Statement; 11 12 import javax.servlet.ServletContext; 13 import javax.servlet.ServletContextEvent; 14 import javax.servlet.ServletContextListener; 15 16 import org.h2.tools.Server; 17 import org.h2.util.StringUtils; 18 19 /** 20 * This class can be used to start the H2 TCP server (or other H2 servers, for 21 * example the PG server) inside a web application container such as Tomcat or 22 * Jetty. It can also open a database connection. 23 */ 24 public class DbStarter implements ServletContextListener { 25 26 private Connection conn; 27 private Server server; 28 29 @Override 30 public void contextInitialized(ServletContextEvent servletContextEvent) { 31 try { 32 org.h2.Driver.load(); 33 34 // This will get the setting from a context-param in web.xml if 35 // defined: 36 ServletContext servletContext = servletContextEvent.getServletContext(); 37 String url = getParameter(servletContext, "db.url", "jdbc:h2:~/test"); 38 String user = getParameter(servletContext, "db.user", "sa"); 39 String password = getParameter(servletContext, "db.password", "sa"); 40 41 // Start the server if configured to do so 42 String serverParams = getParameter(servletContext, "db.tcpServer", null); 43 if (serverParams != null) { 44 String[] params = StringUtils.arraySplit(serverParams, ' ', true); 45 server = Server.createTcpServer(params); 46 server.start(); 47 } 48 49 // To access the database in server mode, use the database URL: 50 // jdbc:h2:tcp://localhost/~/test 51 conn = DriverManager.getConnection(url, user, password); 52 servletContext.setAttribute("connection", conn); 53 } catch (Exception e) { 54 e.printStackTrace(); 55 } 56 } 57 58 private static String getParameter(ServletContext servletContext, 59 String key, String defaultValue) { 60 String value = servletContext.getInitParameter(key); 61 return value == null ? defaultValue : value; 62 } 63 64 /** 65 * Get the connection. 66 * 67 * @return the connection 68 */ 69 public Connection getConnection() { 70 return conn; 71 } 72 73 @Override 74 public void contextDestroyed(ServletContextEvent servletContextEvent) { 75 try { 76 Statement stat = conn.createStatement(); 77 stat.execute("SHUTDOWN"); 78 stat.close(); 79 } catch (Exception e) { 80 e.printStackTrace(); 81 } 82 try { 83 conn.close(); 84 } catch (Exception e) { 85 e.printStackTrace(); 86 } 87 if (server != null) { 88 server.stop(); 89 server = null; 90 } 91 } 92 93 }
4、编辑测试Servlet，SampleServlet.java类
1 package com.test.h2.servlet; 2 3 import java.io.IOException; 4 import java.sql.Connection; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.Statement; 8 9 import javax.servlet.ServletException; 10 import javax.servlet.http.HttpServlet; 11 import javax.servlet.http.HttpServletRequest; 12 import javax.servlet.http.HttpServletResponse; 13 14 public class SampleServlet extends HttpServlet { 15 16 /** 17 * 18 */ 19 private static final long serialVersionUID = 1L; 20 21 @Override 22 protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 23 // 从ServletContext获取 连接对象 24 Connection con = (Connection) getServletContext().getAttribute("connection"); 25 26 try { 27 // 新建数据表 28 String ctreateTable = "DROP TABLE test IF EXISTS; create table test(id integer,name VARCHAR(22) )"; 29 Statement createStatement = con.createStatement(); 30 long f1 = createStatement.executeUpdate(ctreateTable); 31 System.out.println("创建表：" + f1); 32 33 // 插入数据 34 String insertSql = "INSERT INTO test VALUES(1,'小明')"; 35 Statement insertStatement = con.createStatement(); 36 long f2 = insertStatement.executeUpdate(insertSql); 37 System.out.println("插入数据：" + f2); 38 39 // 查询数据 40 String selectSql = "select id,name from test"; 41 PreparedStatement prepareStatement = con.prepareStatement(selectSql); 42 // 发送SQL 返回一个ResultSet 43 ResultSet rs = prepareStatement.executeQuery(); 44 45 // 编历结果集 46 while (rs.next())// 从数据库的取一行数据，是否还有下一行 47 { 48 int id = rs.getInt(1); // 从1开始 49 String name = rs.getString(2); 50 System.out.println("id:" + id + "\t名称:" + name); 51 } 52 resp.getWriter().print("SUCCESS"); 53 } catch (Exception e) { 54 resp.getWriter().print("FAILE"); 55 } 56 57 } 58 59 }
5、在web.xml中，注册SampleServlet
1 <servlet> 2 <servlet-name>SampleServlet</servlet-name> 3 <servlet-class>com.test.h2.servlet.SampleServlet</servlet-class> 4 </servlet> 5 <servlet-mapping> 6 <servlet-name>SampleServlet</servlet-name> 7 <url-pattern>/sample</url-pattern> 8 </servlet-mapping>
6、将项目发布到tomcat中，并启动，在浏览器中输入地址：http://localhost:8080/test-h2/sample，进行访问
使用H2控制台Servlet
7、在项目中集成H2控制台，在web.xml文件中注册h2 jar包中内置的Servlet
1 <!-- 使用H2控制台Servlet --> 2 <servlet> 3 <servlet-name>H2Console</servlet-name> 4 <servlet-class>org.h2.server.web.WebServlet</servlet-class> 5 <!-- 6 <init-param> 7 <param-name>webAllowOthers</param-name> 8 <param-value></param-value> 9 </init-param> 10 <init-param> 11 <param-name>trace</param-name> 12 <param-value></param-value> 13 </init-param> 14 --> 15 <load-on-startup>1</load-on-startup> 16 </servlet> 17 <servlet-mapping> 18 <servlet-name>H2Console</servlet-name> 19 <url-pattern>/console/*</url-pattern> 20 </servlet-mapping> 21 <!-- 22 <security-role> 23 <role-name>admin</role-name> 24 </security-role> 25 <security-constraint> 26 <web-resource-collection> 27 <web-resource-name>H2 Console</web-resource-name> 28 <url-pattern>/console/*</url-pattern> 29 </web-resource-collection> 30 <auth-constraint> 31 <role-name>admin</role-name> 32 </auth-constraint> 33 </security-constraint> 34 -->
8、重启服务，在浏览器中输入地址：http://localhost:8080/test-h2/console进行访问
向H2数据库注册自定义的数据库函数
9、编辑自定义函数类H2DBFunctionExt.java
1 package com.test.h2.function; 2 3 import java.util.UUID; 4 5 /** 6 * 对H2数据库函数的扩展 7 * @author hd 8 * @date 2019-05-28 18:46:07 9 * 10 */ 11 public class H2DBFunctionExt { 12 13 /** 14 * 用法：SELECT myid(); 15 * @return 16 */ 17 public static String myid(){ 18 return UUID.randomUUID().toString().replaceAll("-", ""); 19 } 20 21 }
10、编辑数据库注册监听类RegisterH2ExtFuncListener.java
1 package com.test.h2.listener; 2 3 import java.sql.Connection; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 7 import javax.servlet.ServletContext; 8 import javax.servlet.ServletContextEvent; 9 import javax.servlet.ServletContextListener; 10 11 public class RegisterH2ExtFuncListener implements ServletContextListener { 12 13 @Override 14 public void contextInitialized(ServletContextEvent servletContextEvent) { 15 16 ServletContext servletContext = servletContextEvent.getServletContext(); 17 18 // 1、注册myid函数的SQL语句，语句格式：CREATE ALIAS [IF NOT EXISTS] newFunctionAliasName [DETERMINISTIC] FOR classAndMethodName 19 String sql = "CREATE ALIAS IF NOT EXISTS myid FOR \"com.test.h2.function.H2DBFunctionExt.myid\""; 20 21 // 2、从ServletContext获取 连接对象 22 Connection con = (Connection) servletContext.getAttribute("connection"); 23 try { 24 25 // 3、获取Statement对象 26 Statement stmt = con.createStatement(); 27 28 // 4、执行sql 29 stmt.execute(sql); 30 31 System.out.println("H2数据库扩展函数注册成功！"); 32 33 } catch (SQLException e) { 34 // TODO Auto-generated catch block 35 System.out.println("H2数据库扩展函数注册失败！"); 36 e.printStackTrace(); 37 } 38 } 39 40 @Override 41 public void contextDestroyed(ServletContextEvent servletContextEvent) { 42 // TODO Auto-generated method stub 43 ServletContextListener.super.contextDestroyed(servletContextEvent); 44 } 45 46 }
11、注册监听器，注意放在H2启动监听器之后
1 <!-- 注册RegisterH2ExtFuncListener监听器 --> 2 <listener> 3 <listener-class>com.test.h2.listener.RegisterH2ExtFuncListener</listener-class> 4 </listener>
12、重启项目，登录H2控制台，使用sql：SELECT myid(); 查询
与Spring集成参考：【DataBase】Hsqldb与项目集成
