java - H2 内存中创建服务器以通过 Shell 访问

Question

我正在使用 dropwizard 和 H2 的 DataSourceFactory 来创建内存数据库以进行测试。这是我所拥有的

private static final String DBNAME = String.format("JDBITest-%d", System.currentTimeMillis());

protected final DataSourceFactory config = new DataSourceFactory();
{
    final String url = String.format("jdbc:h2:mem:%s;", DBNAME) +
                       "MODE=MySQL;" +
                       "TRACE_LEVEL_FILE=3;" +
                       "DB_CLOSE_DELAY=-1;" +
                       "IGNORECASE=TRUE";
    System.out.println("Creating in memory H2 using " + url);

    BootstrapLogging.bootstrap();
    config.setUrl(url);
    config.setUser("sa");
    config.setDriverClass("org.h2.Driver");
    config.setValidationQuery("SELECT 1");
}

@Before
public void setUp() throws Exception {
    Server server = Server.createTcpServer().start(); // (4)
    System.out.println("Server started and connection is open.");
    System.out.println("URL: jdbc:h2:" + server.getURL() + "/mem:" + DBNAME);
}

当它运行时我看到

Creating in memory H2 using jdbc:h2:mem:JDBITest-1541641621470;MODE=MySQL;TRACE_LEVEL_FILE=3;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE
Server started and connection is open.
URL: jdbc:h2:tcp://0.0.17.56:9092/mem:JDBITest-1541641621470

为什么 TCP 是 0.0.17.56？我无法访问它，也无法使用 H2.jar 访问 shell。

Answer 1

这里有几个错误的地方。

H2有不同的连接模式。在您的示例中，您为内存中连接配置DataSourceFactory，但随后在@Before方法中创建H2的新实例> 使用基于 TCP 连接。有关连接模式的更多信息 here .

所以现在你基本上有 2 个 H2 实例，在内存和 TCP 中，它们完全不相关。因此，您可能只需要为测试配置 1 个连接类型。

如果您想连接到 JVM 之外的 H2 数据库(例如从浏览器) - 那么您需要有基于 tcp 的连接。

为了能够从浏览器连接到数据库，您还需要运行控制台应用程序。它应该位于 h2.jar 内，使用类似 java -jar h2*.jar 的命令。有关此的更多信息 here .

最后，这种平静的代码应该适合您(使用内存连接):

private static final String DBNAME = String.format("JDBITest-%d", System.currentTimeMillis());
private ManagedDataSource dataSource;

@Before
public void setUp() {
    System.out.println("Server started and connection is open.");

    final String url = String.format("jdbc:h2:mem:%s;", DBNAME) +
            "MODE=MySQL;" +
            "TRACE_LEVEL_FILE=3;" +
            "DB_CLOSE_DELAY=-1;" +
            "IGNORECASE=TRUE";
    System.out.println("Creating in memory H2 using " + url);

    DataSourceFactory config = new DataSourceFactory();
    BootstrapLogging.bootstrap();
    config.setUrl(url);
    config.setUser("sa");
    config.setDriverClass("org.h2.Driver");
    config.setValidationQuery("SELECT 1");

    dataSource = config.build(null, "test");
}

@Test
public void test() throws SQLException {
    Connection connection = dataSource.getConnection();

    connection.createStatement().executeUpdate("CREATE TABLE TEST (`id` INT)");
    connection.createStatement().executeUpdate("INSERT INTO TEST (`id`) VALUES (1)");
    ResultSet resultSet1 = connection.createStatement().executeQuery("SELECT * FROM TEST WHERE `id` = 1");
    resultSet1.next();
    resultSet1.getInt(1);

    System.out.println("Found ID: " + resultSet1.getInt(1));
}

输出:

Server started and connection is open.
Creating in memory H2 using jdbc:h2:mem:JDBITest-1541649996267;MODE=MySQL;TRACE_LEVEL_FILE=3;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE
Found ID: 1