h2 不能访问localhost,控制台无法访问处于内存模式的H2数据库

I am creating an in memory database in H2 database by the following code on servlet context startup

void initDb() {

try {

webserver = Server.createWebServer().start();

Class.forName("org.h2.Driver");

Connection conn = DriverManager.getConnection("jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1","SA","");

InputStream in = getClass().getResourceAsStream("script.sql");

if (in == null) {

System.out.println("Please add the file script.sql to the classpath, package " + getClass().getPackage().getName());

} else {

RunScript.execute(conn, new InputStreamReader(in));

Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("SELECT TO_CHAR(bday,'DD/MM/yyyy hh24:mi') FROM TEST2");

while (rs.next()) {

System.out.println(rs.getString(1));

}

rs.close();

stat.close();

conn.commit();

conn.close();

}

//accessed using url jdbc:h2:tcp://localhost/mem:db1

try{

CachedRowSet crs = new DBConnector().executeQuery("select * from test2");

while(crs.next()){

System.out.println("ARGUMENT_NAME:"+crs.getString(1));

// System.out.println(",DATA_TYPE:"+crs.getString("DATA_TYPE"));

}

crs.close();

}catch(SQLException e){

e.printStackTrace();

}

} catch (Exception e) { //this exception gets throws connection failed!

System.out.println("Exception initializing memory H2 database"+e);

}

}

I am later on accessing by url jdbc:h2:mem:db1 in the same JVM, which is working too. But when i want to access it by jdbc:h2:tcp://localhost/mem:db1 it is not working either in the same JVM or in different JVM.

I actually want to run the system in embedded mode and see the contents using the console. If I start the webserver in the same servlet context startup method I am able to see the Console but it is still not connecting to the in memory DB with url jdbc:h2:tcp://localhost/mem:db1.

If I start the server using Command line using

java -cp "WebContent/WEB-INF/lib/h2-1.3.148.jar;hsqldb.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %*

and url as 'jdbc:h2:tcp://localhost/mem:db1'

And then try to connect, surprisingly it connects but with no data. Seems like it is creating a seperate server on its own and its a different db. So there is no data.

解决方案

To make the in-memory database available for another process, you need to start a TCP server in the same process as the database was opened. Example:

package db;

import java.sql.Connection;

import java.sql.DriverManager;

import org.h2.tools.Server;

public class TestMem {

public static void main(String... args) throws Exception {

// open the in-memory database within a VM

Class.forName("org.h2.Driver");

Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");

conn.createStatement().execute("create table test(id int)");

// start a TCP server

// (either before or after opening the database)

Server server = Server.createTcpServer().start();

// .. use in embedded mode ..

// or use it from another process:

System.out.println("Server started and connection is open.");

System.out.println("URL: jdbc:h2:" + server.getURL() + "/mem:test");

// now start the H2 Console here or in another process using

// java org.h2.tools.Console -web -browser

System.out.println("Press [Enter] to stop.");

System.in.read();

System.out.println("Stopping server and closing the connection");

server.stop();

conn.close();

}

}