H2数据库远程访问

红太狼 2022-05-23 02:50 291阅读 0赞

一  h2服务端开启

第一种方式，通过spring的工厂方法创建

<bean id="h2Server" class="org.h2.tools.Server"  
           factory-method="createTcpServer" init-method="start" destroy-method="stop">  
           <constructor-arg value="-tcp,-tcpAllowOthers,-tcpPort,9092" />  
    </bean>

第二种方式，直接使用Server创建。

![70][]

windows服务器端的数据连接配置如下

url=jdbc:h2:tcp://192.168.108.221:6678/\{文件的绝对路径\}

linux或者windows的操作系统的默认根目录+db文件路径

url=jdbc:h2:tcp://192.168.108.221:6678/~/\{文件的相对路径\}

比如linux系统默认目录为usr/vasuser/  tomcat目录为/usr/vasuser/tomcat/webapps/data/cashDb

则访问端url 为**~/tomcat/webapps/data/cashDb**

如果使用绝对路径，报错如下：

![70 1][]

二 调用远程h2服务

package com.cyb.h2;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.h2.tools.Server;
    
    /**
     * 作者 : iechenyb<br>
     * 类描述: 说点啥<br>
     * 创建时间: 2018年5月31日
     */
    public class H2Demo {
    	private Server server;
    	private String port = "9080";
    	//linux   ~/tomcat/webapps/data/cashDb
    	private String dbDir = "d://data/sample;CACHE_SIZE=32384;MAX_LOG_SIZE=32384;mv_store=false";// ./h2db/
    	private String user = "sa";
    	private String password = "";
    
    	public void startServer() {
    		try {
    			System.out.println("正在启动h2...");
    			server = Server.createTcpServer(new String[] { "-tcp", "-tcpAllowOthers", "-tcpPort", port }).start();
    		} catch (SQLException e) {
    			System.out.println("启动h2出错：" + e.toString());
    			e.printStackTrace();
    			throw new RuntimeException(e);
    		}
    	}
    
    	public void stopServer() {
    		if (server != null) {
    			System.out.println("正在关闭h2...");
    			server.stop();
    			System.out.println("关闭成功.");
    		}
    	}
    
    	public void useH2() {
    		try {
    			Class.forName("org.h2.Driver");
    			Connection conn = DriverManager.getConnection("jdbc:h2:tcp://192.168.16.211:" + port + "/" + dbDir, user,
    					password);
    			Statement stat = conn.createStatement();
    			// insert data
    			stat.execute("DROP TABLE IF EXISTS TEST");
    			stat.execute("CREATE TABLE TEST(NAME VARCHAR)");
    			stat.execute("INSERT INTO TEST VALUES('Hello World')");
    			// use data
    			ResultSet result = stat.executeQuery("select name from test ");
    			int i = 1;
    			while (result.next()) {
    				System.out.println(i++ + ":" + result.getString("name"));
    			}
    			result.close();
    			stat.close();
    			conn.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static void main(String[] args) {
    		System.out.println("*********start***********");
    		H2Demo h2 = new H2Demo();
    		h2.startServer();//调用远端服务时，不需要开启server
    		h2.useH2();
    		// h2.stopServer();
    		System.out.println("*********end***********");
    	}
    }

三 开放h2访问界面

<listener>
            <listener-class>me.gacl.web.listener.H2DBServerStartListener</listener-class>
        </listener>
        
        
        <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>
        
        <servlet-mapping>
            <servlet-name>H2Console</servlet-name>
            <url-pattern>/console/*</url-pattern>
        </servlet-mapping>

参考文档：

https://www.cnblogs.com/xdp-gacl/p/4190424.html

https://blog.csdn.net/firetaker/article/details/5909375

[70]: /images/20220523/61f0ecf0269a4f81aef8e71046085d5b.png
[70 1]: /images/20220523/5f8506d242db4d6790a38ec373b61255.png